Is there any way to execute pure queries in auto-commit mode?

[vincentlauvlwj]

Exposed is great!

But it seems any operation in Exposed must be called within a transaction block. Sometimes it might be unnecessary to to use a real native transaction because the operations in it are pure queries.

val jamesList = transaction {
    Users.select { Users.firstName eq "James" }
}

I've read the source code in TreadLocalTransactionManager.kt and found that the autoCommit property is set to false after connection established.

In my option, it's better to provide a execAutoCommit method(or any other name) to encapsulate pure query operations, so that we can run queries in auto-commit mode, which might be good to performance in some cases.

val jamesList = execAutoCommit {
    Users.select { Users.firstName eq "James" }
}

Anyway, thanks for your contribution to this framework, I like it very much. Please consider adding this feature.

[Tapac]

Thank you for your feedback.
I'm not sure that using Exposed's transactions with auto-commit is a good idea because in that case, we shouldn't use built-in Entity cache or we can see the wrong state of entities.
So, let's postpone that issue until anyone else asks for that.

BTW, you can implement same behavior in your app:

fun <T> execAutoCommit(statement: Transaction.() -> T) = transaction {
    connection.autoCommit = true
    statement()
}

[tjaneczko]

We started running into some performance issues with Exposed requiring transactions for every query on a new application hitting an Oracle DB through HikariCP. It looks like we will need to switch away from Exposed because of these performance issues, which is really unfortunate as I'm a huge fan of the type-safety and the DSL-syntax. It looks like transactions are really an integral part to the framework, so I'd imagine it would be a massive effort to reduce or remove the reliance on them and to use sessions instead for one-off auto-commit queries.

[Tapac]

@tjaneczko , could you explain your case more detailed? AFAIK any database requires a transaction to execute a query. Or you talk about Exposed Transaction class?

[tjaneczko]

@Tapac Sure, we're building a REST API service which is replacing a legacy one which currently uses JDBI (http://jdbi.org/), and we're trying to match the performance of the legacy system. The service is currently a read-only API, meaning we don't need transactional consistency. You don't need a transaction to execute a query on a database, just a session (or connection) on which prepared statements are created and executed. We're currently evaluating KotliQuery ( https://github.com/seratch/kotliquery ), which provides a very thin wrapper around JDBC connections and allows queries to be run without transactions (but also provides the ability to use transactions when needed). Unfortunately we lose out on everything that makes Exposed awesome, if we could solve these performance issues I would definitely switch back.

For a stats comparison, one of our endpoints is executing 4 queries of varying complexity against our database. With Exposed, even keeping all 4 within a single transaction causes our 95th percentile request time to be ~210ms . With KotliQuery (without the transactional overhead) that time drops to ~85ms. These numbers were calculated using apache-bench hitting that endpoint 100 times with 10 concurrency.

Hope this gives some insight, would be happy to provide more if you need any!

[Tapac]

I think that there is might be a drawback on preparing queries to execute, not real execution.
Is it possible to ask you to replace DSL calls with exec() calls and measure the impact of preparing statements from a Query?

[tjaneczko]

Just ran a side-by-side comparison of Exposed's exec() with prepared statements (using #118 (comment) ) against KotliQuery:

Exposed: 185ms 95th percentile
KotliQuery: 92ms 95th percentile

So it looks like preparing statements via the DSL has very minimal effect on the actual timing

[Tapac]

@tjaneczko, there were some performance improvements in the latest Exposed versions in the place of the queries building. If you still have your benchmark available could you please check it once again? Thank you in advance.

[sergeypopov83]

We also have the very same issue on Postgres with exposed version 0.23.1. During an intensive reading from a DB, we also see the committed rate grows as well as commit delay. In its turn, it leads to the growth of CPU utilization and overall performance of the DB decreases. It would be very nice to be able to avoid creating transactions

[vincentlauvlwj]

It's been two years but this issue is still open. This is one of the reasons I developed Ktorm, another ORM framework for Kotlin.

[david-kubecka]

The suggested solution with auto-commit

fun <T> execAutoCommit(statement: Transaction.() -> T) = transaction {
    connection.autoCommit = true
    statement()
}

doesn't work in recent version 0.26.1, or perhaps it never did (has someone tried?). The reason is that there is an explicit commit after statement which is not allowed in auto commit session, e.g. Postgres driver complains

Cannot commit when autoCommit is enabled

I'm using Postgres advisory locks for two-phase commit and I really need to commit any DML operation in my statement. I can do this explicitly by calling commit() in my statement but that's kind of ugly (at least Postgres issues a warning upon subsequent commit outside transaction). Moreover I need to switch the isolation mode to TRANSACTION_READ_COMMITTED otherwise concurrent sessions don't see immediate results of each other which is what I want.

Basically, I'm forced to simulate non-transactional scope with various tricks which might not work in general case. It would be really good if the transaction scope was not mandatory in Exposed.

[mranders-hltv]

We recently integrated our application to NewRelic, which has given access to some interesting data.

Used frameworks are exposed-0.37.3, mariadb-java-client-2.7.1 and HikariCP-4.0.3.

Approximately 20% of our database time is spent on "MySQL other". Diving into this, we can see that each call to the transaction block in exposed performs the following SQL statements (besides the "actual" queries)

set autocommit=0
SELECT @@tx_isolation
COMMIT
set autocommit=1

set autocommit=0 is called by exposed in ThreadLocalTransaction#connectionLazy
SELECT @@tx_isolation is called by exposed when instantiating the var transactionIsolation in JdbcConnectionImpl
COMMIT is called by exposed in the global function inTopLevelTransaction
set autocommit=1 is called by HikariCP in PoolBase#resetConnectionState (because the autocommit flag is seen as dirty))

The vast majority of our database interactions are reads, and could run without changing autocommit and committing.

Would it be possible to create an exposed variant of transaction which is in "read only" mode, that skips the statements above, or would that be very difficult because it goes against some core principles of the framework?

[ohlmanjesse]

My company is running into the same issues with Exposed. A "read only" mode could really save this framework.