#97 SET autocommit=1 called after every transactions

closed
nobody
None
5
2012-05-13
2011-07-25
Anonymous
No

I have activated trace on the mysql Connector/J driver and I see a SET autocommit=0, my queries, commit, rollback and SET autocommit=1 for each and every transaction:

Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [QUERY] at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881) duration: 1 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0, message: /* java thread: cluster-heartbeat */ SET autocommit=0
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [FETCH] at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881) duration: 0 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [PREPARE] at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213) duration: 39 ms, connection-id: 1788771, statement-id: 709, resultset-id: -1, message: select ...
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [EXECUTE] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) duration: 3 ms, connection-id: 1788771, statement-id: 709, resultset-id: -1, message: select ...
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [FETCH] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) duration: 1 ms, connection-id: 1788771, statement-id: 709, resultset-id: 0
...
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [QUERY] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:803) duration: 2 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0, message: /* java thread: cluster-heartbeat */ commit
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [FETCH] at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:803) duration: 0 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [QUERY] at com.mchange.v2.c3p0.impl.C3P0ImplUtils.resetTxnState(C3P0ImplUtils.java:275) duration: 2 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0, message: /* java thread: cluster-heartbeat */ rollback
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [FETCH] at com.mchange.v2.c3p0.impl.C3P0ImplUtils.resetTxnState(C3P0ImplUtils.java:275) duration: 0 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0
Mon Jul 25 17:16:44 EDT 2011 INFO: Profiler Event: [QUERY] at com.mchange.v2.c3p0.impl.C3P0ImplUtils.resetTxnState(C3P0ImplUtils.java:277) duration: 1 ms, connection-id: 1788771, statement-id: 999, resultset-id: 0, message: /* java thread: cluster-heartbeat */ SET autocommit=1

I traced the code and saw that com.mchange.v2.c3p0.impl.C3P0ImplUtils.resetTxnState(Connection, boolean, boolean, boolean) does:
if ( !forceIgnoreUnresolvedTransactions && !pCon.getAutoCommit() )
{
if (! autoCommitOnClose && ! txnKnownResolved)
{
//System.err.println("Rolling back potentially unresolved txn...");
pCon.rollback();
}
pCon.setAutoCommit( true ); //implies commit if not already rolled back.
}
}

I digged further and found that txnKnownResolved is always false when my transaction finishes. In com.mchange.v2.c3p0.impl.NewProxyConnection (generated code), the "txn_known_resolved" variable is set to false in every method call except for commit, rollback and setAutoCommit.

The problem is that hibernate (in org.hibernate.jdbc.ConnectionManager.closeConnection()) fetch warnings using java.sql.Connection.getWarnings(). This reset the "txn_known_resolved" variable which then generate the "rollback", and "SET autocommit=1".

I would really like to avoid sending useless rollback and "SET autocommit=1" to the database. Is it really usefull to do a "txn_known_resolved=false" in the getWarnings and clearWarnings methods of the NewProxyConnection?

Discussion

  • Steve Waldman
    Steve Waldman
    2012-05-13

    • status: open --> closed
     
  • Steve Waldman
    Steve Waldman
    2012-05-13

    OK. getWarnings and clearWarnings no longer will affect the txn_known_resolved flag one way or the other.