Oracle提交和选择之间的滞后 [英] Oracle lag between commit and select
问题描述
我们有一个Java工作流应用程序，它使用Oracle数据库来跟踪其步骤和与其他服务的交互。在工作流运行期间，将执行多个插入/更新/选择，并且偶尔选择将不返回更新的数据，即使在成功完成之前运行的insert / update提交。在工作流错误（由于不良数据）后，如果我们返回并通过第三方应用程序检查数据库，新的/更新的数据将显示。似乎有一个滞后之间，我们的提交通过，当他们是可见的。这发生在所有工作流运行的大约2％中，并且在大量数据库使用期间增加。
We have an Java workflow application that uses an Oracle database to track its steps and interactions with other services. During a workflow run several insert/update/selects are performed and occasionally the select will not return the updated data, even though the insert/update commit that ran before it completed successfully. After the workflow errors out (due to the bad data), if we go back and check the database through a 3rd party app the new/updated data will show up. There seems to be a lag between when our commits go through and when they are visible. This happens in roughly 2% of all workflow runs and it increases during heavy database usage.
我们的数据库支持团队建议将参数max-commit-propagation-delay更改为0，因为它默认为700.这似乎是一个可能的解决方案，不能解决我们的问题。
Our database support team suggested to change a parameter max-commit-propagation-delay to 0, as it defaulted to 700. This appeared to be a possible solution but ultimately did not fix our problem.
应用程序在WebSphere上运行，Oracle数据库配置为JDBC数据源。我们使用Oracle 10.1g。该应用程序是用Java 1.5编写的。
The application runs on WebSphere and the Oracle database is configured as a JDBC datasource. We are using Oracle 10.1g. The application is written in Java 1.5.
任何帮助将不胜感激。
p>
edit: sample code
DataSource ds; // spring configured
String sql = "INSERT INTO " + currentTable + " (" + stepId + ',' + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " ) VALUES (?, ?, ?, null, ?, ?, ?, null, ?, null)";
Connection conn = ds.getConnection();
PreparedStatement stmt = conn.prepareStatement(sql);
// set values
stmt.executeUpdate();
// close connections
// later on in the code...
Connection conn = ds.getConnection();
PreparedStatement stmt = null;
ResultSet rset = null;
String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?";
stmt = conn.prepareStatement(sql);
stmt.setLong(1, entryId);
rset = stmt.executeQuery();
//close connections
推荐答案
您描述的行为应该是不可能的 - 已提交事务中所做的更改可立即用于所有会话。但是，也有例外：
By default, the behavior you described should be impossible - changes made in a committed transaction become available immediately to all sessions. However, there are exceptions:
-
是否使用COMMIT命令中的任何WRITE选项？如果不是，请确认COMMIT_WRITE初始化参数的值。如果使用写批处理或特别是写批处理程序，您可能会打开并发问题。 WRITE BATCH NOWAIT通常用于写事务的速度比可能的并发问题更重要的情况。如果初始化参数使用WRITE变量，则可以在事务基础上覆盖它，方法是在提交中指定IMMEDIATE子句（见COMMIT ）
在其他事务提交之前读取调用SET TRANSACTION的数据？使用SET TRANSACTION指定SERIALIZATION LEVEL READ ONLY或SERIALIZABLE将导致事务看不到在调用SET TRANSACTION之后发生的其他已提交会话发生的更改（ see SET TRANSACTION ）
Is the transaction that is attempting to read the data invoking SET TRANSACTION prior to the other transaction committing? Using SET TRANSACTION to specify SERIALIZATION LEVEL READ ONLY or SERIALIZABLE will result in the the transaction seeing no changes that occur from other committed sessions that occurred after the invocation of SET TRANSACTION (see SET TRANSACTION)
编辑：我看到你正在使用一个DataSource类。我不熟悉这个类 - 我认为它是一个连接共享资源。我意识到，您当前的应用程序设计可能不容易在整个工作流程中使用相同的连接对象（这些步骤可能设计为独立操作，并且您没有在一个设施中将连接对象从一个步骤传递到下一步），但您应该验证返回到DataSource对象的连接对象是否干净，特别是对于打开的事务。可能你不是在代码中调用SET TRANSACTION，但是其他地方的DataSource的另一个使用者可能这样做，并且将连接返回到数据源，会话仍然处于SERIALIZABLE或READ ONLY模式。当连接共享时，必须回滚所有连接，然后将它们移交给新的用户。
edit: I see that you're using a DataSource class. I'm not familiar with this class - I assume it's a connection sharing resource. I realize that your current app design may not make it easy to use the same connection object throughout your work flow (the steps may designed to operate independently, and you didn't build in a facility to pass a connection object from one step to the next), but you should verify that connection objects being returned to the DataSource object are "clean", especially with regard to open transactions. It may be possible that you are not invoking SET TRANSACTION in your code, but another consumer of DataSource elsewhere may be doing so, and returning the connection back to the datasource with the session still in SERIALIZABLE or READ ONLY mode. When connection sharing, it is imperative that all connections be rolled back before handing them off to a new consumer.
如果您对DataSource的行为没有控制权或可见性类，您可以尝试对新获取的连接尝试执行ROLLBACK，以确保其没有已建立的延期交易。
If you have no control or visibility to the behavior of the DataSource class, you may wish to try executing a ROLLBACK on the newly acquired connection to insure it has no lingering transaction already established.
这篇关于Oracle提交和选择之间的滞后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
