mysql 死锁案例及简略分析

准备数据

# 创一个测试表，存储引擎使用 innodb
create table test_lock (id int primary key auto_increment,name varchar(20),age int
)engine = innodb;insert into test_lock (name,age) values ('ionc001',10);
insert into test_lock (name,age) values ('ionc002',12);
insert into test_lock (name,age) values ('ionc003',13);
insert into test_lock (name,age) values ('ionc004',14);
insert into test_lock (name,age) values ('ionc005',15);
insert into test_lock (name,age) values ('ionc006',16);
insert into test_lock (name,age) values ('ionc007',17);
insert into test_lock (name,age) values ('ionc008',18);
insert into test_lock (name,age) values ('ionc009',19);
insert into test_lock (name,age) values ('ionc010',20);

死锁案例

• 执行事务前的数据

 select * from test_lock;

id	name	age
1	ionc001	10
2	ionc002	12
3	ionc003	13
4	ionc004	14
5	ionc005	15
6	ionc006	16
7	ionc007	17
8	ionc008	18
9	ionc009	19
10	ionc010	20

• 事务执行过程

执行次序	事务 1	描述	事务 2	描述
1	begin;	事务 1 开启显式事务
2			begin;	事务 2 开启显式事务
3	update test_lock set age = 100 where id = 1;	事务 1 执行更新id = 1的记录
4			update test_lock set name = ‘william’ where id = 2;	事务 2 执行更新id = 2的记录
5	update test_lock set name = ‘tx_1’ where id = 2;	事务 1 执行更新,此时事务1会被事务2 阻塞
6			update test_lock set age = 200 where id = 1;	此条记录执行时，会报错，终止实物且整个事务回滚
7		由于事务 2 执行导致死锁，接着被 mysal检测到，终止事务 2 的执行，此时事务 1 继续执行
	commit;

• 执行事务后的数据

id	name	age
1	ionc001	100
2	tx_1	12
3	ionc003	13
4	ionc004	14
5	ionc005	15
6	ionc006	16
7	ionc007	17
8	ionc008	18
9	ionc009	19
10	ionc010	20

show engine innodb status;

锁日志分析

=====================================
------------------------
LATEST DETECTED DEADLOCK # 上一次检测到的死锁
------------------------
2025-01-03 23:02:12 0x700010112000 # 时间
*** (1) TRANSACTION: # 事务 1
# 事务 ID，活跃时间
TRANSACTION 16535, ACTIVE 25 sec starting index read
# 表示当前事务使用了一个表，有一个表级锁（意向锁，有事务执行行级产生）
mysql tables in use 1, locked 1
# 表示表链中存在 3 个锁 （一个意向锁)，堆大小，2 个行级锁,undo 日志一条
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
# 当前事务的线程id 及 执行的 SQL 语句，从语句看出来这是事务 1 的执行信息
MySQL thread id 14, OS thread handle 123145582202880, query id 2802 localhost 127.0.0.1 root updating
/* ApplicationName=DataGrip 2024.1.3 */ update test_lock set name = 'tx_1' where id = 2*** (1) HOLDS THE LOCK(S): # 持有的锁信息
# 记录锁，空间 ID，页码，事务 ID，锁模式 X lock（记录锁）且非间隙锁
RECORD LOCKS space id 368 page no 4 n bits 80 index PRIMARY of table `my_demo`.`test_lock` trx id 16535 lock_mode X locks rec but not gap
# 记录锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000001; asc     ;; # 主键索引 11: len 6; hex 000000004097; asc     @ ;; # 事务 ID  十六进制 4097 转 十进制 165352: len 7; hex 010000008f03bd; asc        ;; # 回滚指针3: len 7; hex 696f6e63303031; asc ionc001;; # 本事务要更新的行记录的字段的值  ionc0014: len 4; hex 80000064; asc    d;; # 本事务要更新的行记录的字段的值  十六进制 64 转 十进制 100*** (1) WAITING FOR THIS LOCK TO BE GRANTED:事务 1 等待事务 2 释放锁
RECORD LOCKS space id 368 page no 4 n bits 80 index PRIMARY of table `my_demo`.`test_lock` trx id 16535 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
# 下面信息是事务 2 的执行信息0: len 4; hex 80000002; asc     ;;# 主键索引 21: len 6; hex 000000004098; asc     @ ;;# 事务 ID  十六进制 4098 转 十进制 165362: len 7; hex 02000000cc11ab; asc        ;;# 回滚指针3: len 7; hex 77696c6c69616d; asc william;;# 本事务要更新的行记录的字段的值  william4: len 4; hex 8000000c; asc     ;; # 本事务要更新的行记录的字段的值  十六进制 c 转 十进制 12*** (2) TRANSACTION:
TRANSACTION 16536, ACTIVE 19 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 123145582505984, query id 2811 localhost 127.0.0.1 root updating
/* ApplicationName=DataGrip 2024.1.3 */ update test_lock set age = 200 where id = 1*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 368 page no 4 n bits 80 index PRIMARY of table `my_demo`.`test_lock` trx id 16536 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000002; asc     ;; # 主键索引 21: len 6; hex 000000004098; asc     @ ;; # 事务 ID  十六进制 4098 转 十进制 165362: len 7; hex 02000000cc11ab; asc        ;;# 回滚指针3: len 7; hex 77696c6c69616d; asc william;;# 本事务要更新的行记录的字段的值  william4: len 4; hex 8000000c; asc     ;;# 本事务要更新的行记录的字段的值  十六进制 c 转 十进制 12*** (2) WAITING FOR THIS LOCK TO BE GRANTED: # 事务 2 等待事务 1 释放锁
RECORD LOCKS space id 368 page no 4 n bits 80 index PRIMARY of table `my_demo`.`test_lock` trx id 16536 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
# 下面信息是事务 1 的执行信息，0: len 4; hex 80000001; asc     ;;1: len 6; hex 000000004097; asc     @ ;;2: len 7; hex 010000008f03bd; asc        ;;3: len 7; hex 696f6e63303031; asc ionc001;;4: len 4; hex 80000064; asc    d;;*** WE ROLL BACK TRANSACTION (2) # 决定回滚事务 2
------------
TRANSACTIONS
------------
Trx id counter 16538
Purge done for trx's n:o < 16538 undo n:o < 0 state: running but idle
# ... 省略后面信息
END OF INNODB MONITOR OUTPUT
============================

开启死锁日志的持久化

• 默认情况下，上面显示引擎状态的命令，只会显示最近一次的死锁记录，可以使用下面命令查看死日志的记录参数的值，默认是关闭的

show variables like '%innodb_print_all_deadlocks%'; # off

• 开启死锁的错误日志记录

set global innodb_print_all_deadlocks = 'ON';

避免死锁的建议

避免死锁的方式，归根到底还是减少资源竞争。

• 时间上：耗时事务拆分，减少单个事务的耗时
• 空间上：事务操作的数据行尽可能的小，结果集尽可能小，减少联表操作等

1. 合理使用索引，尽量把区分度高的列放到组合索引的前面(基于最左匹配原则)，使得SQL 尽可能使用索引定位到最小的行（结果集），减少锁竞争。
2. 调整业务逻辑 SQL 的执行顺序，把耗时操作放到后面执行，如 update语句。
3. 事务拆分、避免事务执行逻辑过多，减少持锁周期。
4. 在高并发的系统中不要显示加锁，能不在事务中加锁就不加锁。