HikariCP: SELECT queries execute roll back due to dirty commit state on close()

Question

I am trying to use HikarCP in a legacy system. I configured autocommit to false, which is what we want, and realized that my logs are filled with

[c.z.h.p.ProxyConnection][ProxyConnection.java:232] ora - Executed rollback on connection net.sf.log4jdbc.ConnectionSpy@3f2bba67 due to dirty commit state on close().

This is happening when a connection acquired from the pool is closed after issuing a finder query. No insert/update/delete's are happening within the life of connection. Is this how it should be for select queries? Should I be doing a COMMIT after each select?

Answer 1

Yes, you should be committing. Even SELECT queries initiate transactions and acquire locks. Particularly with various isolation levels, and depending on the database even with TRANSACTION_READ_COMMITTED.

HikariCP treats a non-explicit commit when autocommit is false as an application error. Some other pools support configuring "commit-on-close", but HikariCP considers that risky, and a hack to support applications that were never properly written.

The JDBC specification is explicitly silent on whether a Connection without auto commit should automatically commit or rollback. That is an implementation detail left up the the driver developers.