一 前言
死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 环境说明
MySQL 5.7.12事务隔离级别为RR
create table tx (
id int not null primary key auto_increment ,
c1 int not null default 0,
c2 int not null default 0,
key idx_c1(c1)
) engine=innodb ;
insert into tx values(24,3,4),(25,3,4),
(26,3,4),(30,5,8);
2.2 测试用例
sess1 | sess2 | |
begin; | begin | |
T1 | select * from tx where id=30 for update; | |
T2 | update tx set c2=8 where c1=5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | |
T3 | delete from tx where id=30; |
2.3 死锁日志
LATEST DETECTED DEADLOCK
------------------------
2024-03-15 20:07:10 0x1a18
*** (1) TRANSACTION:
TRANSACTION 489578, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 8600, query id 2473 localhost ::1 root updating
update tx set c2=8 where c1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1092 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 489578 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000077869; asc xi;;
2: len 7; hex 67000001c7138f; asc g ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000008; asc ;;
*** (2) TRANSACTION:
TRANSACTION 489577, ACTIVE 38 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 6680, query id 2478 localhost ::1 root updating
delete from tx where id=30
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1092 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 489577 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000077869; asc xi;;
2: len 7; hex 67000001c7138f; asc g ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000008; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1092 page no 4 n bits 72 index idx_c1 of table `test`.`tx` trx id 489577 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 8000001e; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
2.4 分析死锁日志
首先要理解的是 对同一个字段申请加锁是需要排队的。
其次表ty中索引idx_c1为非唯一普通索引,我们根据事务执行的时间顺序来解释,这样比较好理解。
- T1: sess2 执行select for update 操作持有记录id=30的主键行锁:PRIMARY of table test.tx lock_mode X locks rec but not gap|
- T2: sess1 语句update通过普通索引idx_c1更新c2,先获取idx_c1 c1=5的X锁lock_mode X locks rec but not gap,然后去申请对应主键id=30的行锁,但是sess2 已经持有主键的行锁,于是sess1 等待。
- T3: sess2 执行根据主键id=30删除记录,需要申请id=30的行锁以及c1=5的索引行锁。但是sess1 以及持有该锁,故会出现index idx_c1 of table test.tx trx id 1849 lock_mode X locks rec but not gap waiting
sess2(delete)等待sess1(update),sess1(update)等待sess2(select for update) 循环等待,造成死锁。
对于RDBMS系统出现死锁的根本原因都可以概括为:不同的事务加锁的顺序不一样导致循环等待,进而导致死锁。
2.5 解决方法
修改sess1 的update 为根据主键来更新 也即 update tx set c2=x where id=30,把加锁方式改为顺序加锁,申请主键id的锁,避免通过交叉加锁,相互申请对方持有的锁。
三 小结
上面的案例中出现死锁是由于不同会话对普通索引idx_c1和主键相互竞争导致循环等待而出现死锁的。生产过程中遇到高并发更新同一行的时候,可以考虑避免通过不同的索引进行更新,进而避免死锁。