CODEWITHSUNDEEP
mysqljdbcconnection-poolingautocommit
David
David

Reputation: 300

in mysql jdbc does auto commit affect all connections?

when using a connection pool, will setting a connection's autocommit = false affect this connection only?

if i close this connection without setting autocommit = true and get a new connection will this connection's policy be set to autocommit= true?

Upvotes: 3

Views: 1902

Answers (2)

Boris Krassi
Boris Krassi

Reputation: 213

Test scenario:

  1. set autocommit=false
  2. use transactions
  3. close the connection without setting it back to autocommit=true

Outcome: when this particular connection is reused, it still has autocommit=false (other new connections have the default autocommit=true). So, once I kill this connection on the database side and call my code again, only then the pool gives me a fresh connection with the default autocommit=true.

Conclusion: it affects only this connection, but make sure you set it back to autocommit=true before closing! (This is based on real testing, not an assumption)

Note: this beahvior is noted here too: http://www.coderanch.com/t/583969/JDBC/databases/Tomcat-connection-pool-auto-commit

I use JDBC with Tomcat7, Java 1.7, MySQL5.6, Connector/J 5.1.

Upvotes: 0

BalusC
BalusC

Reputation: 1108902

The answer would to the point be: "depends on the connection pool used".

However, if I was a connection pool, I would have restored the autocommit state as per the initial configuration. I think that other connection pools would do the same.

Upvotes: 2

Related Questions