We have a Java application that uses connection pooling. Usuallay at the start of each function/process we use setAutoCommit(false); to switch off AutoCommit. And in the finally section, after commit/rollback we switch it back to ON using setAutoCommit(true);
From time to time we were getting this Exception in a few functions:
java.sql.SQLException: Could not commit with auto-commit set on
Upon investigation we found that randomly the AutoCommit status changes from OFF/false to ON/true after executeUpdate without any apparent reason.
status = connection.getAutoCommit(); // stores false
statement.executeUpdate(sQuery.toString());
status = connection.getAutoCommit(); // stores true
As I said it doesn't always happen. There is no other function call between these lines that could possibly change the status.
As a workaround we have restored the AutoCommit status after executeUpdate if it changes. But we need to know more as executeUpdate is used a few dozen time in the application.
Has anyone faced similar issue? Any solution other than the above?
We are using Java 1.8 with Oracle 11 and ojdbc8-19.3.0.0.jar
A more plausible explanation is that the connection object is used by more than one thread concurrently, and the other thread has changed the auto-commit mode between your two getAutoCommit() calls. It is strongly recommended to not concurrently share a JDBC connection across threads for various reasons, one of which is the behavior you're seeing, but you could also end up dead-locking your database session.
The JDBC spec states that the calling commit() on a connection that has auto-commit mode enabled should throw an exception. But there is a JDBC property that can be used to relax this behavior: set "oracle.jdbc.autoCommitSpecCompliant" to "false". See the Javadoc here.