从
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;
现在假设三个会话按顺序执行以下操作:
第一节:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
第二节:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
第3节:
START TRANSACTION;
INSERT INTO t1 VALUES(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)insert查询对其插入的行进行独占锁定.因此,假设T1插入第1行,它将锁定第1行.现在,当T2开始写入时,INNODB会在执行之前评估查询,并发现它将写入相同的PK(i = 1的行)让T2等待?或者它会开始执行T2并发现它会出现重复键错误或PK违规.
2)为什么T2和T3采用共享锁?插入过程中共享锁如何进入画面?