MySQL锁定重复键错误

Question

来自docs :

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.

按照文档中的示例，

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

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

现在假设三个session依次执行以下操作:

第 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;

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.

我有一些问题:

1) 插入查询对其插入的行采用独占锁。因此，假设 T1 在第 1 行插入，它将锁定第 1 行。现在当 T2 开始写入时，INNODB 会在执行之前评估查询并发现它将写入相同的 PK(i = 1 的行)让T2等待？或者它会开始执行 T2 并发现它给出重复 key 错误或 PK 违规。

2) 为什么 T2 和 T3 使用共享锁？共享锁如何在插入过程中出现？

Answer 1

1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.

我认为您正在简化术语/流程。查询解析完成后，执行前，需要获取必要的锁。正是在这一点上，它确定:

• session 1 获得独占锁，因为它正在插入并且没有其他锁
• session 2 和 3 排队等待共享锁，因为排他锁已被 session 1 持有，并且 session 2 和 3 处于重复键错误中

2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?

根据上述内容， session 2 和 3 排队等待共享锁，因为它们处于重复键错误中。但是，当 session 1 删除 key 并释放独占锁时，现在 session 2 和 3 都获得了共享锁。此时双方都尝试获取独占锁来完成插入。但是，两者都不能，因为另一个已经持有共享锁。所以独占锁没有授予任何一个，他们陷入僵局。