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?
2 Answers
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.
Comments
Test scenario:
- set autocommit=false
- use transactions
- 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.
