java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver

When using the 12c JDBC driver calling commit on a connection that has auto commit true will result in following exception thrown on the client side

java.sql.SQLException: Could not commit with auto-commit set on
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4439)
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4486)

Even though the exception is thrown the SQL will get executed. Same however would execute without any error when jdbc driver 11.2 is used. Below java code demonstrate the test case.

public static void main(String[] args) {
        try {
            OracleDataSource ds = new OracleDataSource();
            ds.setUser("asanga");
            ds.setPassword("asa");
            ds.setURL("jdbc:oracle:thin:@192.168.0.93:9120:std11g21");

            Connection con = ds.getConnection();

//            con.setAutoCommit(false);
            System.out.println("Auto commit status : " + con.getAutoCommit());
            DatabaseMetaData meta = con.getMetaData();

            System.out.println("Driver Name " + meta.getDriverName());
            System.out.println("Driver Version " + meta.getDriverVersion());
            System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
            System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
            System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
            System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());
            PreparedStatement pr = con.prepareStatement("insert into x values(?)");
            pr.setInt(1, 10);
            pr.execute();
            pr.close();
            con.commit();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

Running with 12c Driver the the following output is seen on the client side and querying the table X will show a new row has been inserted successfully.

Auto commit status : true
Driver Name Oracle JDBC driver
Driver Version 12.1.0.1.0
Driver Major Version 12
Driver Minor Version 1
Database Major Version 12
Database Minor Version 1
java.sql.SQLException: Could not commit with auto-commit set on
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4439)
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4486)

However running with 11.2 driver doesn't give any exception

Auto commit status : true
Driver Name Oracle JDBC driver
Driver Version 11.2.0.3.0
Driver Major Version 11
Driver Minor Version 2
Database Major Version 12
Database Minor Version 1

One way to overcome the issue is to set auto commit false once the connection is checked out of the pool or not calling the commit. However for application where source code is not accessible upgrading to 12c driver could be problematic. According to Oracle this referrers to bug 15891607 which is not a bug but expected behavior. Sure enough the APIs also says the same that calling commit on a connection object with auto commit set true will cause an exception JDK 7 API and JDK 6 API. However this wasn't observed when the code is run so moving to 12c driver from 11g may require code changes in certain cases.

Related Posts
getBoolean in 12c JDBC Driver and java.sql.SQLException: Fail to convert to internal representation
Change in 12c JDBC Behavior - setDate & getDate Does Not Truncate Timestamp




Update on 2020-04-02

As per MOS 1564509.1 adding

-Doracle.jdbc.autoCommitSpecCompliant=false

system property will revert to the legacy behaviour. This could be useful when code changes are not possible.