此更新选择组合查询线程安全吗？

Question

我有一张名为“座位”的桌子，它的模式如下所示

身份证，已取

对于每个用户，我接受一个随机的未取id并将其分配给该用户，为了简单起见，我在这里做了make =1。

   update seats u inner join  (
        SELECT id from seats  
        where taken is null limit 1) s 
   on s.id = u.id set taken = 1;

这个查询使用带空标志的随机位置，对于这个位置，它使标志1。当这个查询正常工作时，这个线程安全吗？

考虑到这个场景，我有两个用户并行。对于user1，我选择行X，在运行update查询之前，user2签入，而对于该用户，select查询返回与user1相同的行。因此，我将两次结束对同一行的更新。

这个查询是否可能出现这种情况？

Answer 1

评估

您的查询对MySQL查询优化器来说有点危险。

我有一个旧职位(MySQL子查询问题)。提出的问题涉及到这个问题。

DELETE FROM test WHERE id =
(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1);

尽管MySQL查询分析器将使用该查询并接受它的语法，但MySQL文档意味着某些数据可能在某些优化阶段消失。在您的示例中，您只访问一行以获得id，然后更新该行。尽管如此，从子查询访问数据的远程可能性可能因为id可能会间歇性地消失而不会返回，这对我来说并不是事务安全的，更不用说线程安全了。

您的查询看起来与旧文章中的查询类似。

• 非选择查询
• 选择子查询
• 子查询使用限制

这个问题不仅仅局限于线程安全与否。即使是这样，您也不应该相信MySQL对在子查询中抢占可用位置的解释。您需要您的查询才能确保事务安全。

建议#1

我给您的建议是在一个事务中将它重写为三个查询。

也许是这样的：

START TRANSACTION;
SELECT id from seats WHERE taken is null limit 1 FOR UPDATE;
SELECT id INTO @available_id from seats WHERE taken is null limit 1;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

或者有两个查询

START TRANSACTION;
SELECT id INTO @available_id from seats WHERE taken is null limit 1 FOR UPDATE;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

欲了解更多信息，请阅读的MySQL文档SELECT ... FOR UPDATE。

建议#2

我还将确保seats表在所获取的列上有索引。

如果没有，则添加此索引。

ALTER TABLE seats ADD INDEX (taken);

在Dev/暂存环境中执行此操作，并在生产中执行此操作之前测试您的查询。

试试看！

Answer 2

如果使用的是InnoDB，则可以使用SELECT ... FOR UPDATE语句保护事务。

http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

这样，您可以在事务未完成时对行执行独占锁。其他会话将等到行锁释放。