一则唯一索引并发insert死锁

数据准备

为了观察死锁发送具体过程，关闭死锁检测

set global innodb_deadlock_detect='OFF';


表准备：

CREATE TABLE t1(
id int NOT NULL AUTO_INCREMENT,
a int NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY (a)
) ENGINE=InnoDB ;


数据准备

insert into t1 values(1,1),(5,5),(10,10),(15,15),(20,20);


死锁测试

测试1

监控SQL:

查看锁等待

select a.thread_id,
ENGINE_TRANSACTION_ID trx_id, object_name,
INDEX_NAME,lock_type,lock_mode,lock_status,LOCK_DATA,
c.BLOCKING_ENGINE_TRANSACTION_ID blk_trx_id,
c.BLOCKING_THREAD_ID blk_thd_id
from performance_schema.data_locks a left join performance_schema.data_lock_waits c
on a.ENGINE_TRANSACTION_ID=c.REQUESTING_ENGINE_TRANSACTION_ID and a.thread_id=c.REQUESTING_THREAD_ID
order by thread_id,trx_id;


查看请求锁

select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;


show engine innodb status G;

T2:

T3:

SESSION1: 阻塞，在(3,2)位置等待锁lock mode S waiting

SESSION2:在(3,2)位置上持有锁 lock_mode X locks rec but not gap Record lock

T4时刻：

SESSION1:

等待(3,2)位置上的"lock mode S waiting Record lock"

SESSION2:

持有(3,2)位置上的"lock_mode X locks rec but not gap Record lock"

等待(3,2)位置上的IN"lock_mode X locks gap before rec insert intention waiting"

如果不关闭死锁检测，则会报如下错误（实验为了查看等待关系已关闭死锁检测）

SESSION1:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

说明：

session2插入后持有(3,2)记录锁，

session1 插入唯一索引记录前进行唯一性查询，(3,2)位置上的S锁被被session 的X阻塞

session2 插入新记录(4,2)，在插入前需要先查询，然后在唯一索引，主键索引加IN锁，加锁需要加上已存在的具体数据上即唯一索引(a,id) (3,2)位置上加locks gap before，insert intention waiting，insert intention waiting 被gap锁阻塞

至此互相等待形成，session1 等待 session2 释放X锁，session2 第二步insert intention 在锁队列中又等待session1 S锁,死锁形成

测试2

把实验特定条件修改一下还会有死锁产生吗，如下

T4: session2 第二步请求的insert intention 位于(5,5) ，加插入意向锁也是(3,2)~~(5,5) 不会形成等待。

结论

1. 慎用唯一索引，不同于主键，唯一索引很难保障单调递增，即也容易产生间隙的交集