重复密钥错误中的MySQL锁定

Question

来自文档：

如果发生重复键错误，则在重复索引记录上设置共享锁.如果有多个会话试图插入同一行(如果另一个会话已经具有独占锁)，则共享锁的这种使用会导致死锁。如果另一个会话删除行，则可能发生这种情况。

看看文档中的例子，

假设InnoDB表t1具有以下结构：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

现在假设三个会话按照顺序执行以下操作：

第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死锁:由于对方持有的共享锁，这两个会话都不能为该行获取排他锁。

我有一些问题：

1) insert查询对正在插入的行采用独占锁。因此，假设T1正在第1行插入，它将锁定第1行。现在，当T2开始编写时，INNODB会在执行它之前评估查询并发现它将写入相同的PK (行I= 1)并使T2等待吗？或者它会开始执行T2并发现它会产生重复的键错误或PK违规。

2)为什么T2和T3要使用共享锁？如何在插入期间显示共享锁？

Answer 1

我在一个简单的bash脚本中对这种情况做了一个小的模拟：

➜  rsandbox_5_6_30 cat test.sh 
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select  sleep(3); rollback;" test 2> tx1 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx3 &

./m -e "SHOW ENGINE INNODB STATUS\G" test > istatus

结果：

➜  rsandbox_5_6_30 cat tx1 
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx2
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx3

交易状况：

---TRANSACTION 265035, not started
MySQL thread id 4, OS thread handle 0x700000b0b000, query id 204 localhost msandbox cleaning up
---TRANSACTION 265017, not started
MySQL thread id 3, OS thread handle 0x700000ac7000, query id 171 localhost msandbox cleaning up
---TRANSACTION 265041, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 0x700000ca3000, query id 210 localhost msandbox User sleep
select sleep(5)

如果在一定延迟内执行第一个事务，则会在一个帧上引发，其中一个后续事务被死锁检测所扼杀，从而使另一个事务插入该值。

活动事务的状态反映仍然需要显式提交(撤消日志条目1)。

-编辑第二版

“由于对方持有的共享锁，这两种锁都不能为该行获取独占锁。”实际上，死锁只杀死一个事务，而不是同时杀死两个事务。因此，EL是在第二届会议之前获得的。

由于间隙锁定的工作方式，我们看到了执行之间的区别。当检测到重复密钥错误时，死锁发生在第三个事务获取共享锁的意图。当发生这种情况时，第二个事务获得共享锁，从而产生死锁并杀死第三个事务。这是对同一记录的并发写入量的限制。

Answer 2

问题2：

2)为什么T2和T3要使用共享锁？如何在插入期间显示共享锁？

它需要对现有条目进行锁定，以便以后插入重复记录的尝试始终失败。