为什么提交不会导致死锁 [英] Why commit does not cause deadlock

问题描述

我已经阅读了有关InnoDB锁的mysql参考手册.收到 : (从: http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html )

I have read mysql reference manual about the InnoDB locks. I got that : (from: http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html)

INSERT在插入的行上设置排他锁.该锁是索引记录锁，不是下一键锁(即没有间隙锁)，并且不会阻止其他会话插入到插入行之前的间隙中.

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

在插入行之前，设置了一种称为插入意图间隙锁的间隙锁.此锁发出插入意图的信号是，如果多个事务未插入间隙中的相同位置，则无需等待插入到同一索引间隙中的多个事务.假设有索引记录，其值为4和7.尝试在插入行上获得排他锁之前，尝试插入值5和6的各个事务分别使用插入意图锁来锁定4和7之间的间隙，但不会彼此阻塞，因为行没有冲突.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

如果发生重复键错误，则会在重复索引记录上设置一个共享锁.如果另一个会话已经具有互斥锁，则如果有多个会话试图插入同一行，则使用共享锁可能会导致死锁.如果另一个会话删除该行，则会发生这种情况.假设InnoDB表t1具有以下结构:

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB table t1 has the following structure:

创建表t1(i INT，主键(i))引擎= InnoDB; 现在，假设三个会话按顺序执行以下操作:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB; Now suppose that three sessions perform the following operations in order:

会议1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会议2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会议3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会议1:

ROLLBACK;

会话1的第一个操作获取该行的排它锁.会话2和3的操作都导致重复键错误，并且都请求该行的共享锁.会话1回滚时，它将释放该行上的排他锁，并授予会话2和3排队的共享锁请求.此时，会话2和会话3死锁:由于对方拥有共享锁，因此两者都无法获得该行的排他锁.

The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

我做了一个实验，发现是事实.

I made a experiment and I found is is the fact.

我的问题是:

(1)我发现，如果我提交会话1，则不会发生死锁.为什么?当我提交会话1时，X行锁也将被释放.所以我不明白.

(1)I found that, if I commit session 1, and deadlock did not happen. Why? When I commit session 1, The X row lock will also be released. So I do not understand.

谢谢.

推荐答案

我怀疑这是一个边缘案例，不值得提高效率.请注意，您必须进行3次会话才能尝试抓住同一行-罕见的情况.而且您做了回滚-也很罕见.因此，发生的僵局是过大的，但不值得解决.因此，必须准备在任何地方处理死锁.

I suspect this is an edge case that is not worth making more efficient. Note that you had to get 3 sessions trying to grab the same row -- a rare happening. And you did a ROLLBACK -- also rare. So the deadlock that occurred is overkill, but not worth fixing. For this reason, one must be prepared to handle deadlocks everywhere.

仅供参考，如果这是Galera群集的3个节点，则COMMIT上将存在代码必须处理的错误.我怀疑如果您多次将此事务(回滚或提交)应用于多个节点，将会发生更多奇怪的事情.

FYI, If this were 3 nodes of a Galera cluster, there would be errors on COMMIT that the code would have to handle. I suspect there are even more combinations of strange things going on if you apply this transaction (with rollback or commit) multiple times to multiple nodes.

现在要问您的问题...可能不会发生死锁，因为其中一个线程获得了排他锁，而另一个线程却被等待"而不是死锁"击中.

Now to your question... Presumably the deadlock did not happen because one of the threads got the exclusive lock and the other was hit with a "wait" instead of a "deadlock".

这篇关于为什么提交不会导致死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！