Broken transaction demarcation with OraclePool

[tsegismont]

When queries are executed between two transactions, the second transaction fail to start with:

io.vertx.oracleclient.OracleException: Error : 1453, Position : 0, Sql = SET TRANSACTION ISOLATION LEVEL READ COMMITTED, OriginalSql = SET TRANSACTION ISOLATION LEVEL READ COMMITTED, Error Msg = ORA-01453: SET TRANSACTION must be first statement of transaction

Here is a reproducer:

  @Test
  public void testTransactionsInConsecutiveConnectionAcquisitions(TestContext ctx) {
    Pool pool = getPool();
    pool.withTransaction(client -> client.query("INSERT INTO mutable (id,val) VALUES (1,'bim')").execute().<Void>mapEmpty())
      .compose(v -> pool.withConnection(client -> client.query("DELETE FROM mutable WHERE id = 1").execute().<Void>mapEmpty()))
      .compose(v -> pool.withTransaction(client -> client.query("SELECT 1 FROM DUAL").execute().<Void>mapEmpty()))
      .onComplete(ctx.asyncAssertSuccess());
  }

The problem is that the client does not restore the auto-commit mode after a transaction is committed or rolled-back.
So when we think we're starting another transaction, in fact another one is already active and statements have been executed inside.

This problem was not visible before 4.2.5 because the Oracle client pool was a fake one: connections were "physically" closed when returned to the pool.

[DavideD]

Thanks for figuring it out.