Hikaricp: Setting Connection Transaction Isolation

[KaoruDev]

Hi I'm trying to figure out if setting a transaction level on the connection will persist after the connection has been "closed" or maybe, I'm assuming, returned back to the pool.

try (Connection conn = pool.getConnection()) {
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    // Some sql queries
}

// is the connection's transaction isolation still `TRANSACTION_SERIALIZABLE`?

I have autoCommit set to the default (true). It's unclear if the isolation level persists throughout the whole session vs just in this try block.

Thanks!

Environment

HikariCP version: 2.4.6
JDK version     : 1.8.0_144
Database        : PostgreSQL
Driver version  : x.x.x

---

Have you searched the CLOSED issues already? How about checking stackoverflow?
Yes

[billoneil]

It should be resetting the isolation level to whatever the pools default is set at. If the pool's default has not been set it should use the JDBC drivers default.

https://github.com/brettwooldridge/HikariCP/blob/3d8dd29fbf1556e59bfeb8e957f4f20dd7f74dc5/src/main/java/com/zaxxer/hikari/pool/PoolBase.java#L222-L225

[KaoruDev]

Ah, is there a way to set the isolation level on the transaction itself?

i.e.:

DSL.using(c).transaction((t) -> {
    t.setIsolation(Connection.TRANSACTION_SERIALIZABLE);
    // etc...
});

To me, this makes it clear the isolation level is only for this particular transaction. setIsolation isn't an existing method, I guess I'm asking for an existing one 😛

[brettwooldridge]

HikariCP tries to emulate the behavior of a native DataSource as much as possible.

For example, if you call getConnection() on the driver DataSource directly, change transaction levels or auto-commit behavior, and then close that Connection, you do not expect that those changes are carried over to the next connection obtained by getConnection().

The same is true of HikariCP. Even though connections are re-used, the pool goes to great lengths to make the connection appear as it would in the absence of the pool.

[KaoruDev]

thank you for the explanation and fantastic work @brettwooldridge