Oracle 在提交和选择之间滞后

Oracle lag between commit and select

提问by Andrew

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.

我们有一个 Java 工作流应用程序，它使用 Oracle 数据库来跟踪其步骤以及与其他服务的交互。在工作流运行期间，会执行多次插入/更新/选择，有时即使在成功完成之前运行的插入/更新提交，选择也不会返回更新的数据。在工作流程出错后（由于数据错误），如果我们返回并通过第 3 方应用程序检查数据库，则会显示新的/更新的数据。我们的提交通过和可见之间似乎存在延迟。这种情况发生在大约 2% 的所有工作流运行中，并且在大量使用数据库时会增加。

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.

我们的数据库支持团队建议将参数 max-commit-propagation-delay 更改为 0，因为它默认为 700。这似乎是一个可能的解决方案，但最终没有解决我们的问题。

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.

该应用程序在 WebSphere 上运行，Oracle 数据库配置为 JDBC 数据源。我们使用的是 Oracle 10.1g。该应用程序是用 Java 1.5 编写的。

Any help would be appreciated.

任何帮助，将不胜感激。

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 Steve Broberg

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:

默认情况下，您描述的行为应该是不可能的 - 在提交的事务中所做的更改立即对所有会话可用。但是，也有例外：

1. Are you using any of the WRITE options in the COMMIT command? If you are not, confirm the value of your COMMIT_WRITE initialization parameter. If either is using the "WRITE BATCH" or especially "WRITE BATCH NOWAIT", you could be opening yourself up to concurrency issues. "WRITE BATCH NOWAIT" would typically be used in cases where the speed of your write transactions is of greater importance than possible concurrency issues. If your initialization parameter is using the "WRITE" variants, you can override it on a transaction basis by specifying the IMMEDIATE clause in your commits (see COMMIT)

2. 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)

1. 您是否在 COMMIT 命令中使用了任何 WRITE 选项？如果不是，请确认 COMMIT_WRITE 初始化参数的值。如果其中一个正在使用“WRITE BATCH”或特别是“WRITE BATCH NOWAIT”，您可能会面临并发问题。“WRITE BATCH NOWAIT”通常用于写入事务的速度比可能的并发问题更重要的情况。如果您的初始化参数使用“WRITE”变体，您可以通过在提交中指定 IMMEDIATE 子句在事务基础上覆盖它（请参阅 COMMIT）

2. 尝试读取数据的事务是否在其他事务提交之前调用了 SET TRANSACTION？使用 SET TRANSACTION 指定 SERIALIZATION LEVEL READ ONLY 或 SERIALIZABLE 将导致事务看不到调用 SET TRANSACTION 后发生的其他已提交会话发生的更改（请参阅 SET TRANSACTION）

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 类。我不熟悉这个类 - 我认为它是一个连接共享资源。我意识到您当前的应用程序设计可能无法让您在整个工作流程中轻松使用相同的连接对象（这些步骤可能设计为独立运行，并且您没有构建将连接对象从一个步骤传递到另一个步骤的工具） next)，但您应该验证返回到 DataSource 对象的连接对象是否“干净”，尤其是对于打开的事务。您可能没有在代码中调用 SET TRANSACTION，但其他地方的 DataSource 的另一个使用者可能正在这样做，并将连接返回到数据源，会话仍处于 SERIALIZABLE 或 READ ONLY 模式。连接共享时，

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.

如果您无法控制或查看 DataSource 类的行为，您可能希望尝试对新获取的连接执行 ROLLBACK 以确保它没有已经建立的延迟事务。

回答by Vincent Malgrat

If the DBA team tried to modify the max_commit_propagation_delayparameter, it probably means you are connecting to a RAC instance (i-e: several distinct servers accessing one single database).

如果 DBA 团队试图修改该max_commit_propagation_delay参数，则可能意味着您正在连接到一个 RAC 实例（即：多个不同的服务器访问一个数据库）。

In that case, when you're closing and reopening the connection in your java code there is a chance that you will be answered by a different server. The delay parameter means that there is a small time frame when the two instances won't be at exactly the same point in time. The answer you are getting is consistent with a point in time but may not be the most current.

在这种情况下，当您在 Java 代码中关闭并重新打开连接时，您可能会被另一台服务器回答。延迟参数意味着当两个实例不会在完全相同的时间点时，有一个小的时间范围。您得到的答案与某个时间点一致，但可能不是最新的。

As proposed by KM, the easiest solution would be to keep the connection opened after the commit.

正如 KM 提出的，最简单的解决方案是在提交后保持连接打开。

Alternatively, you could also add a delay after having closed the connection if it is practical (if this is a batch job and response time is not critical for example).

或者，如果可行，您也可以在关闭连接后添加延迟（例如，如果这是一个批处理作业并且响应时间并不重要）。

回答by KM.

are use using an ORM? it might be selecting from cache and not form the db after the change.

使用 ORM 吗？它可能是从缓存中选择而不是在更改后形成数据库。

回答by spencer7593

This sounds like an issue with RAC, with connections to two different instances and the SCN is out of sync.

这听起来像是 RAC 的问题，连接到两个不同的实例并且 SCN 不同步。

As a workaround, consider not closing the database connection and getting a new one, but reuse the same connection.

作为一种解决方法，请考虑不关闭数据库连接并获取新连接，而是重用相同的连接。

If that's not workable, then add a retry to the query that attempts to retrieve the inserted row. If the row is not returned, then sleep a bit, and retry the query again. Put that into a loop, after a specified number of retries, you can then fail.

如果这不可行，则向尝试检索插入行的查询添加重试。如果未返回该行，则稍作休息，然后重试查询。将其放入循环中，在指定的重试次数后，您可能会失败。

[ADDENDUM]

[附录]

In his answer, Steve Broberg (+1!) raises interesting ideas. I hadn't considered:

在他的回答中，史蒂夫布罗伯格（+1！）提出了有趣的想法。我没有考虑过：

• the COMMITmight be anything other than IMMEDIATE WAIT
• the transaction isolation level might be anything other than READ COMMITTED

• 将COMMIT可能比其他任何IMMEDIATE WAIT
• 事务隔离级别可能不是 READ COMMITTED

I did consider the possibility of flashback query, and dismissed that out of hand without mentioning it, as there's no apparent reason the OP would be using flashback query, and no evidence of such a thing in the code snippet.)

我确实考虑了闪回查询的可能性，并在没有提及的情况下立即驳回了这一点，因为 OP 没有明显的理由使用闪回查询，并且在代码片段中没有证据表明存在这种情况。）

[/ADDENDUM]

[/附录]

回答by Tusc

A possible workaround may be to use JTA transaction. It keeps your connection open "behind the scene" over multiple open/close jdbc conns. Maybe it will keep your connection on the same server and avoid this sync' problem.

一种可能的解决方法是使用 JTA 事务。它通过多个打开/关闭 jdbc conns 使您的连接在“幕后”保持打开状态。也许它会将您的连接保持在同一台服务器上并避免此同步问题。

UserTransaction transaction = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction");
transaction.begin();
// doing multiple open/close cxs
transaction.commit();

回答by Gary Myers

The code snippet didn't actually include the commit.

代码片段实际上并不包含提交。

If you are assuming/relying on the close connection doing the commit, it may not be synchronous (ie the java may report the connection as closed when it tells Oracle to close the connection, which means it may be before the the commit is completed by Oracle).

如果您假设/依赖关闭连接进行提交，则它可能不是同步的（即 Java 可能会在告诉 Oracle 关闭连接时报告连接已关闭，这意味着它可能在提交完成之前甲骨文）。

回答by slovon

I see no commit in your code. They are most important statements in such an app so I would want to have them explicitely written every time, not relying to close() or such.

我在您的代码中没有看到任何提交。它们是这样一个应用程序中最重要的语句，所以我希望每次都明确地编写它们，而不是依赖于 close() 之类的。

You may also have autocommit set to true by default on your connection(s) which would exactly explain the behavior (it commits after every insert/update).

您还可以在默认情况下在您的连接上将自动提交设置为 true，这将准确解释行为（它在每次插入/更新后提交）。

Can you check, that you have commits exactly where you want them, e.g. at the end of the transaction and not before?

你能检查一下，你是否在你想要的地方提交了，例如在交易结束时而不是之前？

If there are commits when you are partially through, then you have a race condition between your threads which would also explain why there are more problems when load is bigger.

如果在您部分完成时有提交，那么您的线程之间存在竞争条件，这也可以解释为什么当负载较大时会出现更多问题。

回答by slovon

"even though the insert/update commit that ran before it completed successfully."

“即使在成功完成之前运行的插入/更新提交。”

This suggests to me that you are issuing a commit(), and then afterwards expect to read exactly the same data again (that's repeatable read).

这向我表明您正在发出 commit()，然后期望再次读取完全相同的数据（这是可重复读取）。

This suggests to me that you shouldn't be committing. As long as you want to make sure that NO OTHER TASK is able to modify ANY of the data that you EXPLICITLY EXPECT to remain stable, you cannot afford to release locks (which is what commit does).

这向我表明你不应该承诺。只要你想确保 NO OTHER TASK 能够修改任何你明确期望保持稳定的数据，你就不能释放锁（这就是 commit 所做的）。

Note that while you keep a lock on some resource, other threads will be stacking up "waiting for that resource to become available". The likelyhood of that stack being non-empty at the time you release your lock, gets higher as general system load gets higher. And what your DBMS will conclude when you (finally) issue "commit", is to conclude that, "hey, wow, this guy is finally done with this resource, so now I can go about letting all the other waiting guys try and do their thing with it (AND there is NOTHING to prevent "their thing" from being an update !)".

请注意，当您锁定某些资源时，其他线程将“等待该资源可用”。当您释放锁时，该堆栈非空的可能性会随着一般系统负载的增加而增加。当您（最终）发出“提交”时，您的 DBMS 将得出的结论是，“嘿，哇，这个人终于完成了这个资源，所以现在我可以让所有其他等待的人尝试去做他们的东西（并且没有什么可以阻止“他们的东西”成为更新！）”。

Maybe there are issues to do with Oracle's snapshot isolation that I'm overlooking. Apologies if so.

也许我忽略了与 Oracle 快照隔离有关的问题。如果是这样道歉。