目录

MySQL死锁场景【官网案例】

https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html

背景介绍

场景模拟

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 ROWS affected (0.16 sec)
 
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 ROW affected (0.05 sec)
 
mysql> SELECT * FROM t;
+------+
| i    |
+------+
|    1 |
+------+
1 ROW IN SET (0.01 sec)
session 1 session 2
step-1 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.09 sec)
step-2 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;
step-3
mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.05 sec)


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

问题分析

1、查看死锁日志

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-24 19:56:49 0x7fc46c0e0700
*** (1) TRANSACTION:
TRANSACTION 2257678, ACTIVE 44 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 432542, OS thread handle 140481399330560, query id 9320959 10.10.133.115 root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7953 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`t` trx id 2257678 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000009458d; asc     E ;;
 1: len 6; hex 00000022730c; asc    "s ;;
 2: len 7; hex bb0000040e0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2257679, ACTIVE 53 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 432541, OS thread handle 140481603176192, query id 9321165 10.10.133.115 root updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7953 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`t` trx id 2257679 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000009458d; asc     E ;;
 1: len 6; hex 00000022730c; asc    "s ;;
 2: len 7; hex bb0000040e0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7953 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`t` trx id 2257679 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 00000009458d; asc     E ;;
 1: len 6; hex 00000022730c; asc    "s ;;
 2: len 7; hex bb0000040e0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

2、分析死锁日志

事务1执行的SQL：DELETE FROM t WHERE i = 1
事务1等待的锁：（Next-key Lock）RECORD LOCKS space id 7953 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`t` trx id 2257678 lock_mode X waiting

事务2执行的SQL：DELETE FROM t WHERE i = 1
事务2持有的锁：（共享Next-Key Lock）RECORD LOCKS space id 7953 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`t` trx id 2257679 lock mode S
事务2等待的锁：（Next-Key Lock）RECORD LOCKS space id 7953 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`t` trx id 2257679 lock_mode X waiting

事务1要获得锁需要等待事务2释放S next-key锁。 事务2要释放S锁需要等待事务1获得并释放Next-key锁。形成僵局（死锁）

3、复盘加锁过程

1、sesison1 执行 SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; 对记录添加了共享锁
2、session2 执行了 DELETE FROM t WHERE i = 1; 需要获得排他锁，此时记录上已经有了共享锁，排他锁和共享锁冲突。所以会话进入等待状态
3、session1 执行了 DELETE FROM t WHERE i = 1; 需要获得排他锁。需要等待session2 先获得排他锁并释放。但是此时session2正在等待session1释放共享锁
由此形成了死锁