mysql 死锁记录

测试环境： mysql 5.7.18     RR隔离级别

创建表，插入部分测试数据

CREATE TABLE yhtest (
 a INT (11) NOT NULL AUTO_INCREMENT,
 b INT (11) DEFAULT NULL,
 c INT (11) DEFAULT NULL,
 PRIMARY KEY (a),
 unique key(b)
) ENGINE = INNODB ;

INSERT INTO yhtest VALUE (1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 6, 6),
(7, 7, 7),
(8, 8, 8),
(9, 9, 9),
(10, 10, 10),
(11, 11, 11) ;

操作：

事物1：begin;

事物2：begin;

事物1：delete from yhtest where a=2;    可以执行

事物2：delete from yhtest where a=3;    可以执行

事物1：delete from yhtest where a=4;     可以执行

事物2：delete from yhtest where a=5;    可以执行

事物1：insert into yhtest value(2,2,2);     锁等待，待事物2回滚后，可以执行

事物2： insert into yhtest value(3,3,3);    报出死锁，回滚，如下：

死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-10-27 19:38:00 0x7f4b67932700
*** (1) TRANSACTION:
TRANSACTION 3914, ACTIVE 117 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 3
MySQL thread id 4, OS thread handle 139961837504256, query id 53 localhost root update
insert into yhtest value(2,2,2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3914 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 3919, ACTIVE 93 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3
MySQL thread id 5, OS thread handle 139961836971776, query id 54 localhost root update
insert into yhtest value(3,3,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3919 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000003; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3919 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000004; asc     ;;
1: len 4; hex 80000004; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

通过innodb日志可以看出，

事物1执行  insert into yhtest value(2,2,2);  时，会等待b列索引b=3 上的S锁被添加，b=3 这一行因为被事物2执行了 delete from yhtest where a=3;  排他锁锁住， S锁等待正常。

事物2执行   insert into yhtest value(3,3,3); 时，可以看出其持有b=3 的X锁， 等待 b=4 上的S锁被添加，b=4 这一行 因为被事物1执行了delete from yhtest where a=4; 排他锁锁住，S锁等待， 这时候，事物1和事物2互相等待对方持有的锁资源，形成回环，死锁出现

这里事物1 插入（2,2,2） 和事物2插入（3,3,3） 因为b列的唯一键存在，需要进行唯一键校验，而由于在之前已经进行了该列删除，需要通过锁定下一列判断b=2 的唯一性，b=3的唯一性，而这两列的锁已经被对方持有，故出现死锁。

这种死锁情况，在RC隔离级别下同样会出现！