选择和更新之间的竞争条件 [英] Race condition between select and update

问题描述

我需要以一种不会在我的基于Web的应用程序中悄悄掩盖来自另一个客户端的更改的方式来更新Oracle数据库中的一行.

I need to update a row in an Oracle database in a way that I don't silently clobber the changes from another client in my web based application.

在当前系统中，我执行以下操作:

In my current system I perform the following:

SELECT * FROM table WHERE id=:ID AND lastmodified=:LASTMOD

如果该行在开始时仍具有相同的上次修改日期，则我们知道没有人更改过该行，因此我们可以使用上次修改时间进行更新.

if the row still exists with the same last modified date when we started we know nobody has changed it so we can update using the last modified time.

但是，当使用两个会话手动执行此操作时，我注意到如果两个客户端大致同时选择一个客户端，则有可能错过该行，因为该行在选择和更新步骤之间发生了更改，原因是两者都在同一秒或毫秒内发生

However when doing this manually using two sessions I noticed if two clients select at roughly the same time its possible for one to miss that the row has been changed between the select and update step due to both occurring within the same second or millisecond.

最终结果是我屏蔽了另一个用户的更改，并且没有发生更改的警告.

The end result is that I clobber the changes of the other user and there is no warning that it occurred.

我当时正在考虑使用SELECT FROM UPDATE，但显然这是一个错误的想法(尤其是对于网络应用程序)，该文章建议重新阅读(这是我在上面所做的事情)，但我仍然认为我有竞争状况的风险.

I was thinking of using SELECT FROM UPDATE but apparently that's a bad idea (especially for web apps), the article recommends re-reading (which is what I'm doing above) but I still think I'm at risk of a race condition.

很清楚，我担心引用时间的方式.

Made it clear I was concerned about the way time is referenced.

推荐答案

我假设您的UPDATE语句本身正在验证您在SELECT语句中读取的lastmodified值，如ninside建议的那样.

I'm assuming that your UPDATE statement itself is verifying the lastmodified value that you read in your SELECT statement as ninesided suggests.

如果lastmodified是DATE，则由于DATE仅具有秒精度，因此如果在同一秒内对同一行进行多次更新，则存在潜在的竞争条件.另一方面，如果lastmodified是TIMESTAMP，则发生竞争状况的窗口将受到更大的限制，因为TIMESTAMP将具有3到9位之间的亚秒精度(在大多数Windows上为3位)机器，在大多数Unix机器上为6).在同一毫秒甚至同一微秒内有两次更新的可能性很小，但并非并非不可能.但这不是万无一失的.

If lastmodified is a DATE, then there is a potential race condition if there are multiple updates to the same row in the same second since a DATE only has granularity to the second. If lastmodified is a TIMESTAMP, on the other hand, the window in which the race condition can occur is much more limited since a TIMESTAMP will have between 3 and 9 digits of sub-second precision (3 on most Windows machines, 6 on most Unix machines). It's pretty unlikely though not impossible that you'd have two updates at the same millisecond or even the same microsecond. But it's not infallible.

您可以使用序列生成的值代替上次修改的日期.这样可以保证您不会丢失任何更新，因为NOCYCLE序列不会两次返回相同的值.但是，如果沿着这条路走下去，您将失去在每行上都有最后更新日期的信息优势，或者在表的每一行中存储了一些额外的数据字节.根据您的应用程序，这些折衷方案中的任何一项可能都是值得的，或者可能会产生更多无法解决的问题.

You can use a sequence-generated value instead of a last modified date. That can guarantee that you won't lose an update since a NOCYCLE sequence won't return the same value twice. But if you go down that path, you're either losing the information benefit of having a last update date on every row or you're storing a few extra bytes of data in every row of the table. Either of those trade-offs may be worth it depending on your application or either might create more problems than it resolves.

这篇关于选择和更新之间的竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！