mysql - mysql 中的竞争条件选择 sql

Question

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

数据库类型:MyISAM
表结构:card_id，状态
查询:从表中选择一个未使用的card_id，并将该行设置为“已使用”。

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

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

有什么想法吗？

谢谢!

Answer 1

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

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

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

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

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

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

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

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

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

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

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

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

如果某个其他 session 对我们尝试更新的行应用了更新(在我们的 session 检索该行和我们的 session 尝试进行更新之前的某个时间)，则该行上的“计数器”列的值将被改变。

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

有一些关于如何实现“乐观锁定”策略的好文章。

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

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

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 (或我们想要的任何列)我们刚刚更新的行。

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

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

这可能对你有用

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

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

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 数据库 session 中运行(即保持数据库 session ；不要放开它并获得一个新的。)

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

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

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