InTransaction does not call transactionHandler.inTransaction when initalAutoCommit=false

[RezaLabudeBSM]

When you have configured a connection which has autoCommit=false then you can not use the
handle.inTransaction as expected, as it never calls commit. You must explizitly call commit to actually commit the changes.

I debugged this issue and found out that the relevant method inTransaction never gets to the transactionHandler.inTransaction part:

 public <R, X extends Exception> R inTransaction(HandleCallback<R, X> callback) throws X {
   return isInTransaction()
      ? callback.withHandle(this)
     : transactionHandler.inTransaction(this, callback);
}

The reason is this check:

  public boolean isInTransaction(Handle handle) {
        try {
            return handlerState == State.IN_TRANSACTION || !handle.getConnection().getAutoCommit();
        } catch (SQLException e) {
            throw new TransactionException("Failed to test for transaction status", e);
        }
    }

The second OR part is always true for a connection which has been set to AutoCommit false.

This problem is not seen as there is no real tests which verifies that a connection with AutoCommit = false actually calls commit.
I would therefore suggest to add this test:

@Test
public void testCallback() {
    String woot = h.inTransaction(x -> "Woot!");
    assertThat(commit).isOne();
    assertThat(woot).isEqualTo("Woot!");
}

@Test
public void testCallbackWithAutoCommitFalse() {
	  Jdbi jdbi = Jdbi.create(() -> {
          // create connection with auto-commit == false
          Connection connection = DriverManager.getConnection(h2Extension.getUri());
          connection.setAutoCommit(false);
          return connection;
      });
	  jdbi.setTransactionHandler(txSpy);
    String woot = jdbi.inTransaction(x -> "Woot!");
    assertThat(commit).isOne();
    assertThat(woot).isEqualTo("Woot!");
}

My Bugfix would be to remove the || !handle.getConnection().getAutoCommit() and trust the enum statemachine.
(This text is a summary of my findings in #2662)

[stevenschlansker]

Hi @RezaLabudeBSM , I am sorry you had troubles with this behavior.
If you use Jdbi transactions, Jdbi expects to fully control autocommit, and manually changing it confuses the state machine.

Can you explain a bit more about what you're trying to achieve by turning autocommit off?

[RezaLabudeBSM]

We have bad experiences with this auto-commit feature and therefore generally turn it off (we do this right a the start when we configure hikari to tell it that auto-commit should be turned off for all connection in this pool), so that we can decide in the transactions when it has to be commited or roll back. We use JPA, JDBC Template and now JDBI, so there are many different persistence technologies in our stack.
Anyway: i think it is a valid decission to turn autocommit off and therefore this behaviour should be fixed in JDBI.

[RezaLabudeBSM]

I just understood that you expect not to change this value so that JDBI can use it to signal wether a transaction is already running or not in the case of nested transactions. But i think that using the auto-commit status as an indicator is not the right way. As i said: it is very easy to configure Hikari in such a way that every connection it issues has the auto-commit set to false and i also think that it is a valid useage of the connection.

[stevenschlansker]

Thanks for the feedback. This is currently not supported, as you note, but we can consider changing this in the future. This isn't the first time this has been requested.