mysql 中的竞争条件选择 sql [英] race condition in mysql select sql

问题描述

我试图完成的事情似乎很简单，

What I try to accomplish seems simple,

数据库类型:MyISAM
表结构:card_id, status
查询:从表中选择一个未使用的card_id，并将该行设置为已使用".

Db type: MyISAM
Table Structure: card_id, status
Query: select an unused card_id from a table, and set the row as "used".

当两个查询同时运行时，在状态更新之前，相同的 card_id 被提取两次是否是竞争条件?

Is it race condition that when two queries running at the same time, and before status is updated, the same card_id is fetched twice?

我已经做了一些搜索.似乎 Lock table 是一种解决方案，但对我来说这太过分了并且需要 Lock Privilege.

I did some search already. It seems Lock table is a solution, but it's overkill to me and need Lock Privilege.

有什么想法吗?

谢谢！

推荐答案

这实际上取决于您正在运行的语句.

It really depends on what statements you are running.

对于针对 MyISAM 表的普通旧 UPDATE 语句，MySQL 将获得对整个表的锁定，因此那里的两个会话之间没有竞争"条件.一个会话将等到锁被释放，然后继续它自己的更新(或将等待指定的时间，并以超时"中止.)

For plain old UPDATE statements against a MyISAM table, MySQL will obtain a lock on the entire table, so there is no "race" condition between two sessions there. One session will wait until the lock is released, and then proceed with it's own update (or will wait for a specified period, and abort with a "timeout".)

但是，如果您要问的是两个会话都对表运行 SELECT，以检索要更新的行的标识符，并且两个会话都检索相同的行标识符，然后两个会话都尝试更新同一行，那么是的，这是一个确定的可能性，而且确实必须考虑.

BUT, if what you are asking about is two sessions both running a SELECT against a table, to retrieve an identifier for a row to be updated, and both sessions retrieving the same row identifier, and then both sessions attempting to update the same row, then yes, that's a definite possibility, and one which really does have to be considered.

如果这种情况没有得到解决，那么基本上就是上次更新获胜"的问题，第二次会话将(可能)覆盖上一次更新所做的更改.

If that condition is not addressed, then it's basically going to be a matter of "last update wins", the second session will (potentially) overwrite the changes made by a previous update.

如果这对您的应用程序来说是站不住脚的情况，那么确实需要解决这个问题，要么采用不同的设计，要么采用某种机制来防止第二次更新覆盖第一次更新所应用的更新.

If that's an untenable situation for your application, then that does need to be addressed, either with a different design, or with some mechanism that prevents the second update from overwriting the update applied by the first update.

正如您提到的，一种方法是通过首先获取表上的排他锁(使用 LOCK TABLES 语句)，然后运行 ​​SELECT 以获取标识符，然后运行 ​​UPDATE 以更新标识的来避免这种情况行，然后最后释放锁(使用 UNLOCK TABLES 语句.)

One approach, as you mentioned, is to avoid this situation by first obtaining an exclusive lock on the table (using a LOCK TABLES statement), then running a SELECT to obtain an identifier, and then running an UPDATE to update the identified row, and then finally, releasing the lock (using an UNLOCK TABLES statement.)

对于一些低容量、低并发的应用程序来说，这是一种可行的方法.但它确实有一些明显的缺点.主要关注的是并发性降低，因为在单个资源上获得了排他锁，这有可能导致性能瓶颈.

That's a workable approach for some low volume, low concurrency applications. But it does have some significant drawbacks. Of primary concern is reduced concurrency, due to the exclusive locks obtained on a single resource, which has the potential to cause a performance bottleneck.

另一种选择是称为乐观锁定"的策略.(与前面描述的方法相反，后者可以被描述为悲观锁定".)

Another alternative is an strategy called "optimistic locking". (As opposed to the previously described approach, which could be described as "pessimistic locking".)

对于乐观锁定"策略，表中添加了一个额外的计数器"列.每当对表中的一行应用更新时，该行的计数器就会增加 1.

For an "optimistic locking" strategy, an additional "counter" column is added to the table. Whenever an update is applied to a row in the table, the counter for that row is incremented by one.

为了利用这个计数器"列，当查询检索将(或可能)稍后更新的行时，该查询还会检索计数器列的值.

To make use of this "counter" column, when a query retrieves a row that will (or might) be updated later, that query also retrieves the value of the counter column.

当尝试更新时，该语句还会将该行中计数器"列的当前值与先前检索到的计数器列值进行比较.(我们只包含 UPDATE 语句的谓词(例如在 WHERE 子句中).例如，

When an UPDATE is attempted, the statement also compares the current value of the "counter" column in the row with the previously retrieved value of the counter column. (We just include a predicate (e.g. in the WHERE clause) of the UPDATE statement. For example,

UPDATE mytable
   SET counter = counter + 1
     , col = :some_new_value       
 WHERE id = :previously_fetched_row_identifier
   AND counter = :previously_fetched_row_counter

如果某个其他会话对我们尝试更新的行应用了更新(在我们的会话检索该行的时间和我们的会话尝试进行更新之前的某个时间)，则计数器"列的值该行将已更改.

If some other session has applied an update to the row we are attempting to update (sometime between the time our session retrieved the row and before our session is attempting to do the update), then the value of the "counter" column on that row will have been changed.

我们的 UPDATE 语句中的谓词对此进行检查，如果计数器"已更改，将导致我们的更新不被应用.然后我们可以检测到这种情况(即受影响的行数将为 0 而不是 1)并且我们的会话可以采取一些适当的行动.(嘿！其他一些会话更新了我们打算更新的一行！")

The predicate on our UPDATE statement checks for that, and if the "counter" has been changed, that will cause our update to NOT be applied. We can then detect this condition (i.e. the affected rows count will be a 0 rather than a 1) and our session can take some appropriate action. ("Hey! Some other session updated a row we were intending to update!")

有一些关于如何实现乐观锁定"策略的好文章.

There are some good write-ups on how to implement an "optimistic locking" strategy.

一些 ORM 框架(例如 Hibernate、JPA)为这种类型的锁定策略提供支持.

Some ORM frameworks (e.g. Hibernate, JPA) provide support for this type of locking strategy.

遗憾的是，MySQL 不支持 UPDATE 语句中的 RETURNING 子句，例如:

Unfortunately, MySQL does NOT provide support for a RETURNING clause in an UPDATE statement, such as:

UPDATE ... 
   SET status = 'used'
 WHERE status = 'unused'
   AND ROWNUM = 1
RETURNING card_id INTO ...

其他 RDBMS(例如 Oracle)确实提供了这种功能.有了可用的 UPDATE 语句的该功能，我们可以简单地运行 UPDATE 语句以同时执行 1) 使用 status = 'unused' 定位一行，2) 更改值status = 'used'，以及 3) 返回我们刚刚更新的行的 card_id(或我们想要的任何列).

Other RDBMS (e.g. Oracle) do provide that kind of functionality. With that feature of the UPDATE statement available, we can simply run the UPDATE statement to both 1) locate a row with status = 'unused', 2) change the value of status = 'used', and 3) return the card_id (or whatever columns we want) of the row the we just updated.

这解决了必须运行一个 SELECT 然后运行一个单独的 UPDATE 的问题，其他一些会话可能会更新我们的 SELECT 和我们的 UPDATE 之间的行.

That gets around the problem of having to run a SELECT and then running a separate UPDATE, with the potential of some other session updating the row between our SELECT and our UPDATE.

但是 RETURNING 子句在 MySQL 中不受支持.而且我还没有找到在 MySQL 中模拟这种类型功能的任何可靠方法.

But the RETURNING clause is not supported in MySQL. And I've not found any reliable way of emulating this type functionality from within MySQL.

这可能对你有用

我不完全确定为什么我之前使用用户变量放弃了这种方法(我在上面提到过我已经玩过这个.我想也许我需要一些更通用的东西，它会更新不止一行并返回一组of id 值.或者，也许有些东西不能保证用户变量的行为.(再说一次，我只在精心构造的 SELECT 语句中引用用户变量；我不在 DML 中使用用户变量；它可能因为我不能保证他们的行为.)

I'm not entirely sure why I previously abandoned this approach using user variables (I mentioned above that I had played around with this. I think maybe I needed something more general, which would update more than one row and return a set of of id values. Or, maybe there was something that wasn't guaranteed about the behavior of user variables. (Then again, I only reference user variables in carefully constructed SELECT statements; I don't use user variables in DML; it may be because I don't have a guarantee of their behavior.)

由于您只对一行感兴趣，因此这三个语句的序列可能适合您:

Since you are interested in exactly ONE row, this sequence of three statements may work for you:

SELECT @id := NULL ;

UPDATE mytable
   SET card_id = (@id := card_id) 
     , status = 'used'
 WHERE status = 'unused'
 LIMIT 1 ;

SELECT ROW_COUNT(), @id AS updated_card_id ;

这三个语句在 SAME 数据库会话中运行很重要(即保持数据库会话；不要放开它并获得一个新的.)

It's IMPORTANT that these three statements run in the SAME database session (i.e. keep a hold of the database session; don't let go of it and get a new one.)

首先，我们将用户变量 (@id) 初始化为一个值，我们不会将该值与表中的真实 card_id 值混淆.(SET @id := NULL 语句也可以工作，而不像 SELECT 语句那样返回结果.)

First, we initialize a user variable (@id) to a value which we won't confuse with a real card_id value from the table. (A SET @id := NULL statement would work as well, without returning a result, like the SELECT statement does.)

接下来，我们运行 UPDATE 语句以 1) 找到 status = 'unused' 的一行，2) 更改 status 的值code> 列设置为 'used'，并且 3) 将 @id 用户变量的值设置为我们更改的行的 card_id 值.(我们希望 card_id 列是整数类型，而不是字符，以避免任何可能的字符集转换问题.)

Next, we run the UPDATE statement to 1) find one row where status = 'unused', 2) change the value of the status column to 'used', and 3) set the value of the @id user variable to the card_id value of the row we changed. (We'd want that card_id column to be integer type, not character, to avoid any possible character set translation issues.)

接下来，我们使用 ROW_COUNT() 函数运行查询以获取由前一个 UPDATE 语句更改的行数(我们将需要在客户端验证这是 1)，并检索 @id 用户变量的值，该值将是已更改行中的 card_id 值.

Next, we run a query get the number of rows changed by the previous UPDATE statement, using the ROW_COUNT() function (we are going to need to verify that this is 1 on the client side), and retrieve the value of the @id user variable, which will be the card_id value from the row that was changed.

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