死锁案例分析

wzl
76 阅读7分钟

线上遇到的死锁例子与分析

表结构入下

create table file_sync_task  
(  
id bigint(11) auto_increment comment '自增id'  
primary key,  
project_id bigint(11) null comment '项目id',  
org_id int(20) null comment '组织id',  
file_id int(20) null comment '文件id',  
event_id int(20) null comment '事件类型id',  
status varchar(20) null comment '任务状态 unstart/running/finish/failed',  
create_time datetime default CURRENT_TIMESTAMP null comment '创建时间',  
modify_time datetime default CURRENT_TIMESTAMP null comment '修改时间',  
deleted tinyint(1) default 0 null comment '删除标识0 未删除 1 已删除'  
)  
row_format = DYNAMIC;  
  
create index idx_project_event  
on file_sync_task (project_id, status)  
comment '项目id索引';  
  
create index idx_status  
on file_sync_task (status)  
comment '状态索引';

死锁日志

------------------------
2024-06-17 16:09:35 0x7f7cecffb700
*** (1) TRANSACTION:
TRANSACTION 5608908325, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 27 lock struct(s), heap size 3520, 41 row lock(s)
MySQL thread id 618080, OS thread handle 140175111071488, query id 4855029630 172.xx.xx.xx xxxx updating
UPDATE file_sync_task SET deleted=1  WHERE  deleted=0
AND project_id = 645 AND file_id = 229153472 AND status = 'UnStart'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3918773 n bits 456 index idx_status of table `xxx`.`file_sync_task` trx id 5608908325 lock_mode X locks rec but not gap
Record lock, heap no 250 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b22; asc        ";;

Record lock, heap no 251 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b23; asc        #;;

Record lock, heap no 252 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b24; asc        $;;

Record lock, heap no 253 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b25; asc        %;;

Record lock, heap no 254 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b26; asc        &;;

Record lock, heap no 255 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b27; asc        ';;

Record lock, heap no 256 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b28; asc        (;;

Record lock, heap no 257 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b29; asc        );;

Record lock, heap no 258 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b2a; asc        *;;

Record lock, heap no 259 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b2b; asc        +;;

Record lock, heap no 260 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b2c; asc        ,;;

Record lock, heap no 261 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b2d; asc        -;;

Record lock, heap no 262 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b2e; asc        .;;

Record lock, heap no 263 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b2f; asc        /;;

Record lock, heap no 264 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b30; asc        0;;

Record lock, heap no 265 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b31; asc        1;;

Record lock, heap no 266 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b32; asc        2;;

Record lock, heap no 267 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b33; asc        3;;

Record lock, heap no 268 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b34; asc        4;;

Record lock, heap no 269 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b35; asc        5;;

Record lock, heap no 270 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b36; asc        6;;

Record lock, heap no 271 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b37; asc        7;;

Record lock, heap no 272 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b38; asc        8;;

Record lock, heap no 273 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b39; asc        9;;

Record lock, heap no 274 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 7; hex 556e5374617274; asc UnStart;;
 1: len 8; hex 80000000000e0b3a; asc        :;;

Record lock, heap no 275 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

*** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 3918773 n bits 456 index idx_status of table xxx.file_sync_task trx id 5608908325 lock_mode X locks rec but not gap

指的是事务1持有了 file_sync_task 的 idx_status索引的行锁

使用的腾讯云数据库监控

image.png sql语句如下

事务1
UPDATE file_sync_task SET deleted=1 WHERE deleted=0 AND project_id = 645 AND file_id = 229153472 AND status = 'UnStart' 

事务2
UPDATE file_sync_task SET status='Running',modify_time='2024-06-17 16:09:35.804' WHERE deleted=0 AND id = 920392 AND status = 'UnStart'

分析

  1. 首先代码里是没有加事务注解的，但是还是死锁了
  2. 原因就是加锁是一步步加上的，不是原子性加锁的
  3. 根据二级索引 status 字段修改会锁主键索引，事务1走了 status 索引，然后再锁主键索引
  4. 根据主键索引修改二级索引字段时，加锁步骤是先加主键索引的锁，再锁住二级索引然后修改
  5. 所以在并发情况下就出现死锁问题

解决方法

建立 file_id 字段索引，区分度会比 status 字段高，走 file_id 字段就不会导致死锁了

avatar
文章
阅读
粉丝