Deffered setAutoCommit call leads to idle in transaction sessions in PostgreSQL

[radist-nt]

We have issues with idle in transaction sessions in PostgreSQL database initiated from datasource configured in Wildfly 18.0.0.Final. The last query differs from one connection to another (and sometimes the last query is internal query issued by PostgreSQL JDBC driver). Long transactions and hanging idle in transaction sessions are a potential problem, as said at almost every PostgreSQL-related event.
We found that our code calls connection.setAutoCommit(true); before returning connection to pool with connection.close();. On the another side, the setAutoCommit method from PostgreSQL JDBC driver makes the commit (which will actually rollback in the case of failed transaction) if the transaction is active (idle in transaction state).
Further investigation leads to the deffered setAutoCommit call in the {{org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection}} class and issue JBJCA-1338 with PR #602 which was merged and then reverted due to JBEAP-9730. So then another fix has been provided in PR #623, but it fixed only JBJCA-1338, but not the more serious problem: the underlined jdbc connection is left at the unexpected autoCommit state during the unpredictable time of waiting in pool.

According to the analisys in this message, the test in JBEAP-9730 was failed due to commit command in postgresql jdbc driver. But actually, commit command always leads to rollback in PostgreSQL, so it's unlikely a real problem.
I have not found the failing test sources, so cannot find how they detected the commit called from database driver (by mocking the jdbc driver internal classes?), so cannot decide how to make fix in cleanup method. But why not to add the mc.checkTransaction(); call into the org.jboss.jca.adapters.jdbc.WrappedConnection.close() method to set the underlying connection to the expected state before returning connection to pool?

Currently the workaround is to call any method which calls org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransaction() internally before closing connection, but it looks like a dirty hack.

[tadamski]

Hi Andrey, Thank you for the thorough analysis. We are familiar with problems with autoCommit and working on the solution currently. I'm considering the solution that you proposed as one of the fix options but still plan to discuss it with the team. Regards, Tomek

…

On Wed, Aug 11, 2021 at 11:20 PM Andrey Marinchuk ***@***.***> wrote: We have issues with idle in transaction sessions in PostgreSQL database initiated from datasource configured in Wildfly 18.0.0.Final. The last query differs from one connection to another (and sometimes the last query is internal query issued by PostgreSQL JDBC driver). Long transactions and hanging idle in transaction sessions are a potential problem, as said at almost every PostgreSQL-related event. We found that our code calls connection.setAutoCommit(true); before returning connection to pool with connection.close();. On the another side, the setAutoCommit method from PostgreSQL JDBC driver makes the commit (which will actually rollback in the case of failed transaction) if the transaction is active (idle in transaction state). Further investigation leads to the deffered setAutoCommit call in the {{org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection}} class and issue JBJCA-1338 <https://issues.redhat.com/browse/JBJCA-1338> with PR #602 <#602> which was merged and then reverted due to JBEAP-9730 <https://issues.redhat.com/browse/JBEAP-9730>. So then another fix has been provided in PR #623 <#623>, but it fixed only JBJCA-1338 <https://issues.redhat.com/browse/JBJCA-1338>, but not the more serious problem: the underlined jdbc connection is left at the unexpected autoCommit state during the unpredictable time of waiting in pool. According to the analisys in this <https://issues.redhat.com/browse/JBJCA-1338?focusedCommentId=13387331&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-13387331> message, the test in JBEAP-9730 <https://issues.redhat.com/browse/JBEAP-9730> was failed due to commit command in postgresql jdbc driver. But actually, commit command always leads to rollback in PostgreSQL, so it's unlikely a real problem. I have not found the failing test sources, so cannot find how they detected the commit called from database driver (by mocking the jdbc driver internal classes?), so cannot decide how to make fix in cleanup method. But why not to add the {{mc.checkTransaction();}} call into the {{org.jboss.jca.adapters.jdbc.WrappedConnection.close()}} method to set the underlying connection to the expected state before returning connection to pool? Currently the workaround is to call any method which calls org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransaction() internally before closing connection, but it looks like a dirty hack. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#734>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAY2OFO226LPUYMCHAT22NTT4LSPVANCNFSM5B7QE6SQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email> .

-- Regards, Tomek