Created attachment 37344 [details] Packet capturing using wireshark I think I found a bug in the Tomcat JDBC Pool. If I start a connection with autoCommit set to false and default autoCommit is set to true, the active transaction is not rollbacked when the connection is closed. I think the bug is located in the terminateTransaction method in the ConnectionPool class: if one sets the autoCommit manually to false and afterwards the connection is closed without committing or rollback, the transaction stays open. See line 888 of ConnectionPool. Code to reproduce: conn = dataSource.getConnection(); conn.setAutoCommit(false); ps = conn.prepareStatement(sqlStatement, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(100); paramsSetter.accept(ps); rs = ps.executeQuery(); while(rs.next()) { // do something with result } rs.close() ps.close() conn.close() => Postgres connection stays in state: Ready for query (in a transaction) All other connection pools rollback active transaction on close of connection. See wireshark capture: Correct behaviour: packet 2698 -> 2701 (same for commons-dbcp, hikari and c3p0) Incorrect behaviour: packet 12018 -> missing rollback
(In reply to Ronald from comment #0) > Created attachment 37344 [details] > Packet capturing using wireshark > > I think I found a bug in the Tomcat JDBC Pool. > > If I start a connection with autoCommit set to false and default autoCommit > is set to true, the active transaction is not rollbacked when the connection > is closed. > > I think the bug is located in the terminateTransaction method in the > ConnectionPool class: if one sets the autoCommit manually to false and > afterwards the connection is closed without committing or rollback, the > transaction stays open. > > See line 888 of ConnectionPool. > > Code to reproduce: > > conn = dataSource.getConnection(); > conn.setAutoCommit(false); > ps = conn.prepareStatement(sqlStatement, ResultSet.TYPE_FORWARD_ONLY, > ResultSet.CONCUR_READ_ONLY); > ps.setFetchSize(100); > paramsSetter.accept(ps); > rs = ps.executeQuery(); > while(rs.next()) { > // do something with result > } > rs.close() > ps.close() > conn.close() > > > => Postgres connection stays in state: Ready for query (in a transaction) > > All other connection pools rollback active transaction on close of > connection. > > > See wireshark capture: > Correct behaviour: packet 2698 -> 2701 (same for commons-dbcp, hikari and > c3p0) > Incorrect behaviour: packet 12018 -> missing rollback What happens if you check the connection back out of the pool after it's been returned (the first time)? Does your transaction get rolled-back or committed? I think it's a matter of taste as to whether or not a rollback() should be performed when returning the connection to the pool. If the application is working properly, then a rollback is a wasted command to the database.
https://tomcat.apache.org/tomcat-11.0-doc/jdbc-pool.html See "rollbackOnReturn" attribute. It is false by default.
Closing based on comment #2