setAutoCommit(false) not working with c3p0

Question

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.

Answer 1

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.