两个 INSERT 发生死锁原因剖析

从上面这个输出来看，我们看到的现场是两个 insert 请求发生了死锁。单纯看这 2 个 SQL 的话，应该是产生锁等待才对，而不是死锁。

按照我们常规理解，session1 未 commit 前，应该是持有 id=1 上的 record lock(X)，而 session2 和 session3 则都在等待这个锁的释放。而实际上呢，肯定不是这样的，否则也不至于发生死锁了。

这次的案例其实在 MySQL 官方文档上已经解释过了，而且也给了演示案例（如本例）。文档中是这么说的：

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.

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.

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.

核心内容是：当需要进行唯一性冲突检测时，需要先加一个 S 锁。

这样的话，上面案例的加锁过程就不是之前推测的那样，而是像下面这样了：

下面是另一个类似的案例：

通过上面这两个案例，其实想要告诉大家的是：发生死锁时，不能只看现场，还得分析过程，才能知道真正的原因，死锁发生的原因也并不复杂，但是得能想办法还原过程。

下面提供一个更加诡异的死锁案例，这个死锁案例出现了 S GAP 锁，可能从来没有见过。

mysql> create table testunj1 (id1 int primary key,id2 int unique key,name varchar(20));
mysql> insert into testunj1 values(1,1,'gaopeng'),(10,10,'gaopeng'),(20,20,'gaopeng');
mysql> select * from testunj1;
+-----+------+---------+
| id1 | id2  | name    |
+-----+------+---------+
|   1 |    1 | gaopeng |
|  10 |   10 | gaopeng |
|  20 |   20 | gaopeng |
+-----+------+---------+
3 rows in set (0.01 sec)

死锁模拟：

这时候我们看下 InnoDB STATUS 的输出：

《两个 INSERT 发生死锁原因剖析》