MariaDB的“FOR UPDATE”子句是否有效？

Question

我试图摆脱写歪斜，并尝试使用可序列化的隔离级别，但我反而遇到了死锁。我发现可序列化隔离级别可能会导致死锁，原因如下：

这个级别类似于 REPEATABLE READ，但 InnoDB 隐式将所有纯 SELECT 语句转换为 SELECT ...锁定共享模式

所以，我试着像这样使用< code>REPEATABLE READ(没有id为“some_id”的行):

-- connection 1:
 START TRANSACTION;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 select * from some_table where id="some_id" for update;
 
-- connection 2:
 START TRANSACTION;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 select * from some_table where id="some_id" for update;
 insert into some_table values("some_id");

结果，我在连接2中收到了以下消息：＜code＞超过了锁定等待超时；尝试重新启动事务。

MariaDB更新文档称：

仅当autocommit设置为0或SELECT包含在事务中时，SELECT的FOR UPDATE子句才适用。获取行上的锁，并且阻止其他事务写入行、获取锁和读取锁(除非它们的隔离级别是READ UNCOMMITTED)。

但看起来它不会阻止其他事务获取锁或读取行。

我做错了什么？

Answer 1

这篇文章回答了我的问题：如何锁定尚不存在的InnoDB行？

虽然上面的答案是正确的，因为SELECT… FORUPDATE将阻止并发会话/事务插入相同的记录，但这并不是全部事实。我目前正在与同样的问题作斗争，并得出结论，SELECT… FORUPDATE在这种情况下几乎毫无用处，原因如下：

并发事务/会话也可以进行选择...对于完全相同的记录/索引值的更新，MySQL会很乐意立即接受(非阻塞的)并且不会抛出错误。当然，一旦另一个会话这样做了，您的会话也不能再插入记录。无论是您的还是其他会话/事务都没有得到任何有关这种情况的信息，并认为他们可以安全地插入记录，直到他们实际尝试这样做。根据具体情况，尝试插入会导致死锁或重复键错误。

换句话说，选择...FOR UPDATE 可防止其他会话插入相应的记录，但即使您执行 SELECT ...对于 UPDATE 并且找不到相应的记录，则很可能您实际上无法插入该记录。恕我直言，这使得“先查询，然后插入”方法无用。

问题的原因是MySQL没有提供任何方法来真正锁定不存在的记录。两个并发会话/事务可以同时锁定不存在的记录“FOR UPDATE”，这确实是不可能的，并且使开发变得更加困难。

解决此问题的唯一方法似乎是使用信号量表或在插入时锁定整个表。有关锁定整个表或使用信号量表的进一步参考，请参阅MySQL文档。

只是我的两分钱...

我没有先找到这个问题，所以我不会删除这个问题（我知道，事实上，这是一个重复的），以便其他人更容易搜索。

结果，我创建了唯一的索引，并将其与可重复的读取隔离级别一起使用（没有“FOR UPDATE”）。它允许我检测并发插入并在代码中处理这种情况（在这种情况下我决定返回错误）。

我在尝试回答这个问题时找到的相关资源:

https://dev . MySQL . com/doc/ref man/5.7/en/innodb-deadlock-example . html

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

https://mariadb.com/kb/en/set-transaction/

https://mariadb.com/kb/en/lock-in-share-mode/

https://mariadb.com/kb/en/for-update/

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

https://dev . MySQL . com/doc/ref man/8.0/en/innodb-deadlocks . html

具有可序列化隔离级别的事务中的死锁

锁定数据库表以避免插入“重复项”

如何避免MySQL在试图获取锁时发现死锁；'尝试重新启动交易'