英文:
JDBC with Oracle - executeUpdate automatically changes status of AutoCommit
问题
我们有一个使用连接池的Java应用程序。通常，在每个函数/过程的开始，我们使用setAutoCommit(false);来关闭AutoCommit。在finally部分，在提交/回滚之后，我们使用setAutoCommit(true);将其重新打开。
不时，在一些函数中，我们会遇到以下异常：
java.sql.SQLException: Could not commit with auto-commit set on
经过调查，我们发现随机地，在executeUpdate之后，AutoCommit状态从OFF/false更改为ON/true，没有明显的原因。
status = connection.getAutoCommit(); // 存储为false
statement.executeUpdate(sQuery.toString());
status = connection.getAutoCommit(); // 存储为true
正如我所说，这并不总是发生。这两行之间没有任何其他函数调用可能会更改状态。
作为一种解决方法，如果状态发生更改，我们在executeUpdate之后恢复了AutoCommit状态。但我们需要更多信息，因为executeUpdate在应用程序中使用了几十次。
是否有人遇到类似的问题？除上述方法之外，还有其他解决方案吗？
我们使用Java 1.8与Oracle 11和ojdbc8-19.3.0.0.jar。
英文:
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
答案1
得分: 2
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. 1: https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_AUTO_COMMIT_SPEC_COMPLIANT
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论