JDBC - Ebean Transaction handling

[Daryl Stultz]

Hi Rob,

Ages ago you implemented (Avaje) Bug 379 at my request. This is JdbcTransaction setting the connection auto commit to false if it's true and resetting it at the end if it was true to start with. This did the trick for me at first but I figured out it was a problem if I started a transaction in my legacy JDBC code before calling something in Ebean. I didn't determine the problem back then, I just worked off my own build of 2.8.1 where the feature was removed.

Recently we've been working on upgrading PostgreSQL to 9.3. The JDBC driver for 9.3 is complaining about the auto commit setting in a way 8.4 was not. In looking in to that problem and also working with Ebean 4 the issue has come back in to focus for me.

The way we handled transactions before Ebean (and still) in JDBC is with auto commit always true then to execute actual queries "begin", "commit", and "rollback" rather setAutoCommit(false) followed by commit() or rollback() on the Connection. With the 379 work I have a unit test that fails, something like this:

start transaction in JDBC

update something

"find" something in Ebean

commit the transaction

try to find the same thing in JDBC

value found is not the updated value

I discovered the "find" in Ebean issues a "rollback" call. What I'd like to do is intercept my JDBC executions of "begin", "commit", and "rollback" and replace them with calls to setAutoCommit(false), commit() and rollback(). But it seems even that will fail. Can you give me some insight as to why an apparently simple "select/find" query issues a rollback? What are the various Ebean actions that result in a call to commit()/rollback() when there is no change to the database?

Thanks.

/Daryl

[Rob Bygrave]

>> Can you give me some insight as to why an apparently simple "select/find" query issues a rollback? 

At transaction isolation level of read_committed (which is the default and expected isolation level) you could argue that there is no need for the commit or rollback at the end of a 'query only transaction'.   At the other isolation levels like serialisable isolation you need to commit or rollback to the 'transaction' (or at least effectively reset the connection for further use).

Now, I strongly suspect 99.9% of use will be at read_committed (for various good reasons and this won't change) so I'll review this but my thought is that we technically don't need the extra commit/rollback (for query only transactions that are at read_committed) and can look to remove that call.

>> What are the various Ebean actions that result in a call to commit()/rollback() when there is no change to the database?

- Query only transactions

- An update where the bean has not been modified at all

- Some methods on a Transaction instance when called mean that Ebean can't be sure it is read only. From memory that would be transaction#getConnection() but I'd need to check the source.

I'll have a review of this tonight.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob Bygrave]

>> ... simple "select/find" query issues a rollback? 

Note that you can configure this behaviour.  In ebean.properties set:

ebean.transaction.onqueryonly=close

Refer to TransactionManager.getOnQueryOnly().  If "close" is chosen then it will check the transaction isolation level to ensure it is read_committed before turning on this behaviour.

Refer to JdbcTransaction.commitQueryOnly() ... which will then choose CLOSE_ON_READCOMMITTED

>>

start transaction in JDBC

update something

"find" something in Ebean

commit the transaction

<<

How are you using the same transaction in your JDBC code and Ebean?  Are you using Transaction.getConnection() or are you using ExternalJdbcTransaction() or a custom TransactionManager or something else?

Cheers, Rob.

[Daryl Stultz]

On Mon, Jun 30, 2014 at 6:34 AM, Rob Bygrave <robin....@gmail.com> wrote:

How are you using the same transaction in your JDBC code and Ebean?  Are you using Transaction.getConnection() or are you using ExternalJdbcTransaction() or a custom TransactionManager or something else?

Our current handling of transactions is often weak, probably. Sometimes it's only Ebean involved, so ebs.beginTransaction(), etc. Often we have legacy code that starts a transaction on the connection with executeUpdate("begin") and I don't do anything special with Ebean (other than making sure it's not already in a transaction).

I have a DataSource that provides the same connection to the JDBC code and to Ebean.

/Daryl

[Daryl Stultz]

On Mon, Jun 30, 2014 at 6:34 AM, Rob Bygrave <robin....@gmail.com> wrote:

>> ... simple "select/find" query issues a rollback? 

Note that you can configure this behaviour.  In ebean.properties set:

ebean.transaction.onqueryonly=close

This had no effect, looks like it needs to be this:

transaction.onqueryonly=CLOSE

 

Refer to TransactionManager.getOnQueryOnly().  If "close" is chosen then it will check the transaction isolation level to ensure it is read_committed before turning on this behaviour.

Refer to JdbcTransaction.commitQueryOnly() ... which will then choose CLOSE_ON_READCOMMITTED

I've verified that TransactionManager.onQueryOnly is CLOSE_ON_READCOMMITTED yet it still calling connection.rollback().

Thoughts?

/Daryl

[Rob Bygrave]

Where is connection.rollback() being called from?

[Daryl Stultz]

On Wed, Jul 2, 2014 at 5:12 AM, Rob Bygrave <robin....@gmail.com> wrote:

Where is connection.rollback() being called from?

Here are 2 tracebacks: 

at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:642)

at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:630)

at com.avaje.ebeaninternal.server.core.OrmQueryRequest.endTransIfRequired(OrmQueryRequest.java:203)

at com.avaje.ebeaninternal.server.core.DefaultServer.findId(DefaultServer.java:1206)

at com.avaje.ebeaninternal.server.core.DefaultServer.find(DefaultServer.java:1068)

at com.avaje.ebeaninternal.server.core.DefaultServer.find(DefaultServer.java:1055)

at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:642)

at com.avaje.ebeaninternal.server.transaction.JdbcTransaction.rollback(JdbcTransaction.java:630)

at com.avaje.ebeaninternal.server.core.OrmQueryRequest.endTransIfRequired(OrmQueryRequest.java:203)

at com.avaje.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1472)

at com.avaje.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:907)

at com.avaje.ebeaninternal.util.DefaultExpressionList.findList(DefaultExpressionList.java:178)

/Daryl

[Rob Bygrave]

Thanks, I'll have a look at those tonight.

--