setAutoCommit(false) not working with c3p0
c3p0, connection-pooling, jetty, postgresql
Solution
The JDBC spec states that, "The default is for auto-commit mode to be enabled when the Connection object is created." That's a cross DBMS default, regardless of how the database behaves in other contexts. JDBC programmers may rely on autoCommit being set unless they explicitly call `setAutoCommit( false )`. c3p0 honors this.
c3p0 allows ConnectionCustomizers to persistently override Connection defaults in the `onAcquire()` method when the no single behavior is specified. For example, the spec states that "The default transaction level for a Connection object is determined by the driver supplying the connection." So, for transactionIsolation, if you reset that in `onAcquire(...)`, c3p0 will remember the default you have chosen, and always restore the transactionIsolation back to that default prior to checkout. However, c3p0 explicitly will not permit you to disable `autoCommit` once in `onAcquire(...)` and have `autoCommit` be disabled by default. at the moment of check-out, c3p0 insists you have a spec conformant Connection.
You can get the behavior that you want by overriding the `onCheckOut(...)` method. The Connection is already checked-out when `onCheckOut(...)` is called, you can do anything you want there, c3p0 has exhausted it obligations to the specification gods at that point. If you want your clients to always see non-autoCommit Connections, call `setAutoCommit( false )` in `onCheckOut(...)`. But do beware that this renders your client code unportable. If you leave c3p0 and switch to a different DataSource, you'll need to use some other library-specific means of always disabling `autoCommit` or else you'll find that your application misbehaves. Because even for postgres, JDBC Connections are `autoCommit` by default.
Note: The Connection properties whose values are not fixed by the spec and so can be persistently overridden in an `onAcquire(...)` method are `catalog`, `holdability`, `transactionIsolation`, `readOnly`, and `typeMap`.
p.s. don't set `forceIgnoreUnresolvedTransactions` to `true`. yuk.
Problem
I'm working with postgresql 9.2 and C3p0 0.9.2.1, and I created a connection customizer to disable `autoCommit` and set `transactionMode` but when I do a lookup on `InitialContext` to retrieve the `dataSource`, `autoCommit` is not disabled on the connection (log at bottom). How can I disable auto commit ? Connection Customizer : ``` public class IsolationLevelConnectionCustomizer extends AbstractConnectionCustomizer { @Override public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws Exception { super.onAcquire(c, parentDataSourceIdentityToken); System.out.println("Connection acquired, set autocommit off and repeatable read transaction mode."); c.setAutoCommit(false); c.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); } } ``` Class to retrieve datasource for DAOs : ``` public class DAOAcquire { private ComboPooledDataSource m_cpdsDataSource = null; private static final String LOOKUP_CONNECT = "jdbc/mydb"; public DAOAcquire() throws NamingException { InitialContext context = new InitialContext(); m_cpdsDataSource = (ComboPooledDataSource) context.lookup(LOOKUP_CONNECT); if (m_cpdsDataSource != null) { try { System.out.println("Autocommit = "+String.valueOf(m_cpdsDataSource.getConnection().getAutoCommit())); } catch (SQLException e) { System.out.println("Could not get autocommit value : "+e.getMessage()); e.printStackTrace(); } } } public ComboPooledDataSource getComboPooledDataSource() { return m_cpdsDataSource; } /** * @return the jdbcTemplate * @throws NamingException */ public JdbcTemplate getJdbcTemplate() throws NamingException { return new JdbcTemplate(m_cpdsDataSource); } /** * Commit transactions * @throws SQLException */ public void commit() throws SQLException { if (m_cpdsDataSource != null) { m_cpdsDataSource.getConnection().commit(); } else { throw new SQLException("Could not commit. Reason : Unable to connect to database, dataSource is null."); } } /** * rollback all transactions to previous save point * @throws SQLException */ public void rollback() throws SQLException { if (m_cpdsDataSource != null) { m_cpdsDataSource.getConnection().rollback(); } else { throw new SQLException("Could not rollback. Reason : Unable to connect to database, dataSource is null."); } } } ``` Log : ``` Connection acquired, set autocommit off and repeatable read transaction mode. Connection acquired, set autocommit off and repeatable read transaction mode. Connection acquired, set autocommit off and repeatable read transaction mode. Autocommit = true ``` By default, postgresql auto commit mode is disabled so why does c3p0 activate it automatically ? Should I set forceIgnoreUnresolvedTransactions to true ? EDIT : whenever I commit a transaction after retrieving the datasource, I get this error : `org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.`