MYSQL死锁分析案例一（唯一键冲突）

Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `b` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

mysql> insert into t1 select 1,1,2,3;

mysql> insert into t1 select 5,2,2,3;

 

mysql> select * from t1;
+----+------+------+------+
| id | b | c | d |
+----+------+------+------+
| 1 | 1 | 2 | 3 |
| 5 | 2 | 2 | 3 |
+----+------+------+------+

 

session A:begin; select * from t1 where b=1 and c=2 for update;

session B:begin; select * from t1 where b=1 and c=2 for update;

sessionA :insert into t1 select 3,1,2,3;

 

报错顺序是会话B先报错，会话A再报错，下面会讲。

会话B报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

会话A报错：ERROR 1062 (23000): Duplicate entry '1-2' for key 'b'

 

输出下死锁信息：show engine innodb status\G

 

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-01-21 16:25:00 0x7fd5deeb2700
*** (1) TRANSACTION:
TRANSACTION 15117731, ACTIVE 28 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 233174, OS thread handle 140556482070272, query id 5674894 106.1
1.34.12 root statistics
select * from t1 where b=1 and c=2 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21542 page no 4 n bits 72 index b of table `ceshi`.`t1` tr
x id 15117731 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000002; asc ;;
2: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 15117718, ACTIVE 49 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 208385, OS thread handle 140556544714496, query id 5675592 106.1
1.34.12 root executing

insert into t1 select 3,1,2,3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21542 page no 4 n bits 72 index b of table `ceshi`.`t1` tr
x id 15117718 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000002; asc ;;
2: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21542 page no 4 n bits 72 index b of table `ceshi`.`t1` tr
x id 15117718 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000002; asc ;;
2: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

下面分析下加锁流程：

会话A：对唯一索引（1,2）这条记录加X锁。

会话B：对唯一索引（1,2）这条记录加X锁，会话A持有锁，会话B只能等待。

会话A：写入一条（3,1,2,3）记录，可以看到1,2会造成唯一键冲突，

数据库会在主键冲突的记录上加S锁，这时就会有环路出现， 

B------->A：B在等A的X锁。

A------->B：A要拿记录的S锁，但要等B的X锁。

1、数据库检测到有死锁，会自动KILL掉其中一个事务，根据日志信息，可以看到KILL掉了B会话，

2、B会话KILL掉之后，死锁消失，此时A会继续执行insert into t1 select 3,1,2,3;这条写入记录，会报唯一键冲突报错。