Auto-commit changes to TRUE after a while using a connection pool
So here is what is in place today, I force the AutoCommit
attribute on each connection created by the pool.
This works, so it definitely may be a bug of the pool classes.
Edit: I also had a problem with transaction isolation that was set the same way but not taken into account. After some research I found that this could be related to the Mysql connector/J I use (http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html) .
I found this interesting parameter in the doc :
useLocalSessionState
Should the driver refer to the internal values of autocommit and transaction isolation that are set by Connection.setAutoCommit() and Connection.setTransactionIsolation() and transaction state as maintained by the protocol, rather than querying the database or blindly sending commands to the database for commit() or rollback() method calls?
Default value : false
Comments
-
Michael Laffargue almost 2 years
I got some strange problem.
I use a Pool to create and manage DB Connections, I set the
DefaultAutocommit
option toFALSE
.But after a while, when an error occur and a
rollback
is called an Exception is thrown :Can't call rollback when autocommit=true
Relaunching JBoss will solve the problem as a new DataSource will be created.
Here is how I create my Datasource :
protected DataSource getDataSource(String driverClassName, String dbUrl, String dbUser, String dbPwd) { PoolProperties poolProperties = new PoolProperties(); poolProperties.setUrl(dbUrl); poolProperties.setDriverClassName(driverClassName); poolProperties.setUsername(dbUser); poolProperties.setPassword(dbPwd); poolProperties.setDefaultAutoCommit(false); poolProperties.setTestWhileIdle(false); poolProperties.setTestOnBorrow(true); poolProperties.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); poolProperties.setValidationQuery("SELECT 1"); poolProperties.setTestOnReturn(false); poolProperties.setLogAbandoned(false); poolProperties.setRemoveAbandoned(true); poolProperties.setRemoveAbandonedTimeout(20); poolProperties.setMaxActive(100); poolProperties.setInitialSize(10); poolProperties.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"); return new DataSource(poolProperties); }
And how I get the connections :
xxx.getDataSource().getConnection();
I didn't try yet but my first call will be to force the autocommit directly on the connection using
setAutoCommit(false)
.Though I don't understand why the
poolProperties.setDefaultAutoCommit(false);
is stopping doing the job.Stack trace :
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4805) at sun.reflect.GeneratedMethodAccessor302.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:125) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:94) at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:71) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:94) at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:140) at $Proxy333.rollback(Unknown Source)