Issue when auto commit is false.

[mcarabolante]

Using JDBI 3.1.0.
When using autocommit = false, JDBI will fail to open a transaction, it will throw a exception saying that the transaction is already open.

From what I have debugged, the issue seems to be in the class LocalTransactionHandler.

public boolean isInTransaction(Handle handle)
     {
            return !handle.getConnection().getAutoCommit();
    }
   
 public <R, X extends Exception> R inTransaction(Handle handle,
                                                    HandleCallback<R, X> callback) throws X
   if (isInTransaction(handle)) {
            throw new IllegalStateException("Already in transaction");
   }

This doent happen if the autocommit is true.

Also is there any way I can help you with this issue ?

Thanks

[qualidafial]

This is an idiosyncrasy of JDBC: if you set auto-commit to false, the next database operation will implicitly start a transaction.

https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

If you use Jdbi methods to manage transactions, Jdbi expects to be the arbiter over that auto-commit property. Otherwise we have no way to know whether you're managing auto-commit by hand, vs using the begin() / commit() / rollback() methods improperly.

We have an open issue to try to use save-points to allow for "sub-transactions." See #937

[mcarabolante]

While I don't have any issue using auto-commit=true, this behavior is strange, since the developer may call begin() / commit() / rollback() improperly anyway.

My main issue is that the behavior is undocumented, I only figured it out by debugging it. Also I was migrating a application from hibernate to JDBI, and in hibernate world they recommend using auto-commit=false.

Also, from a API standpoint, wouldn't it be confusing to use "sub-transactions." implicitly rather than explicitly ?

[qualidafial]

While I don't have any issue using auto-commit=true, this behavior is strange, since the developer may call begin() / commit() / rollback() improperly anyway.

We actually have a check in handle.close() that throws an exception if there is an unresolved (i.e. not committed or rolled back) transaction. You can disable that check, but the default behavior is to catch you using the API improperly, roll back the transaction automatically, and throw an exception stating that the transaction was not terminated properly.

My main issue is that the behavior is undocumented, I only figured it out by debugging it.

That's fair. We've put a lot of work into improving documentation in the v3 release, but obviously there's room for improvement.

If you would care to submit a pull request with the documentation you wish had been there, that would be super helpful. We try to be thorough, but when you've been in the code for a while it can be difficult to see the way it looks to a newcomer.

Also I was migrating a application from hibernate to JDBI, and in hibernate world they recommend using auto-commit=false.

I would agree that setting auto-commit=false is a best practice for Hibernate / JPA projects. The reason you want to set it with Hibernate is so that all all records retrieved in a Session come from the same database state.

Jdbi doesn't use sessions--we use database transactions directly. With Jdbi you can read the live data, or use a transaction so successive reads come from a consistent database state--at your discretion. You can also execute multiple independent transactions on the same connection if your use case calls for it. So the auto-commit=false recommendation is somewhat of an anachronism in Jdbi.

Also, from a API standpoint, wouldn't it be confusing to use "sub-transactions." implicitly rather than explicitly ?

Some JDBC drivers support the concept of "savepoints" (alternatively called "checkpoints") in transactions. By mapping nested transactions to these savepoints, it would provide a way to atomically commit or rollback a subset of operations without having to commit or rollback the transaction as a whole.

I suppose we could provide an inSavepoint(String name, TransactionCallback) which would automatically create the savepoint, and either release (equivalent to commit) or rollback the savepoint based on whether the callback threw an exception.

[mcarabolante]

Thank you for you time!

I submit the documentation PR this weekend.