数据库竞争条件 [英] Database race conditions

问题描述

我听说许多应用程序开发人员在数据库处理中的竞争条件方面遇到了一些麻烦。一个典型的例子是这样的：

I've heard about many application developers having a bit of trouble in regards to race conditions in database processing. A typical example goes something like this:

• 用户1选择一个字段，例如numStock，即3


• 用户2还选择了numStock，它仍然是3


• 用户1减少了numStock（在应用程序中），并在数据库中将其设置为2。


• 用户2也会减少numStock（在应用程序中），并将其在数据库中设置为2。

• User 1 selects a field, say, numStock, which is 3
• User 2 also selects numStock, which is still 3
• User 1 decrements numStock (in the app), and sets it to 2 in the database.
• User 2 also decrements numStock (in the app), and sets it to 2 in the database.

例如，numStock字段应该已经变为1，但是由于用户之间的竞争而被设置为2。

In this example, the numStock field should have become 1, but it was set to 2 instead due to the race between users.

因此当然可以使用锁，但是我我想到了另一种处理方式-将所有行详细信息作为WHERE标准传递。让我解释一下...

So of course locks can be used, but I've thought of another way of handling this - passing all row details as WHERE criteria. Let me explain...

在上面的示例中，SQL代码可能看起来像这样：

In the example above, the SQL codes might look like this:

/ / select

//select

SELECT itemID, numStock FROM items WHERE itemID = 45

//更新

UPDATE items SET numStock = 2 WHERE itemID = 45

我解决种族的想法：

// select

//select

SELECT itemID, numStock FROM items WHERE itemID = 45

//更新

UPDATE items SET numStock = 2 WHERE itemID = 45 AND numStock = 3

因此，查询检查数据是否从它选择了数据。 所以我的问题是：（1）这种方法（总是）有效吗？ （2）与数据库锁定机制（例如MySQL事务）相比，这是否是更好的选择？

Thus, the query checks if the data has changed since it SELECT-ed the data. So my question is: (1) Would this [always] work? and (2) is this a better option compared to database locking mechanisms (eg, MySQL Transactions)?

感谢您的时间。

推荐答案

此策略有效，被称为乐观锁定。那是因为您在处理成功的前提下进行了处理，只有在最后才真正检查它是否成功。

This strategy works and known as 'optimistic locking'. Thats because you do your processing assuming it will succeed and only at the end actually check if it did succeed.

当然，您需要一种重试事务的方法。而且，如果失败的机会很高，那么它可能会变得无效。但是在大多数情况下，它都可以正常工作。

Of course you need a way to retry the transaction. And if the chances of failure are very high it might become inefficient. But in most cases it works just fine.

这篇关于数据库竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！