mysql5.6.16(polarDB)死锁问题记录

系统管理员 2022-12-22 15:23 63阅读 0赞

### 问题描述: ###

线上环境点击取消任务时, 系统后台报错, 检查日志错误为mysql发生死锁

### 问题排查: ###

*mysql出现死锁的条件:(1) innoDB引擎, (2) 同时开启2个或2个以上的事务, 彼此持有对方的锁*

**线上环境分析:**

**事务A:** 插入call\_task\_info表, call\_task\_info表上有更新task\_info表任务数量的触发器  
**事务B:** 更新task\_info表的状态, 之后更新call\_task\_info表中相关的记录的任务状态

**事务A与事务B影响的任务ID 相同**

查看数据库死锁记录:

show engine innodb status;

查询记录如下:

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2020-11-19 18:50:15 7f06429f7700
    *** (1) TRANSACTION:
    TRANSACTION 674590067, ACTIVE 0.026 sec starting index read
    mysql tables in use 2, locked 2
    LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
    LOCK BLOCKING MySQL thread id: 1760585 block 1760584
    MySQL thread id 1760584, OS thread handle 0x7f064c9e8700, query id 617886804 172.25.1.154 robot updating
    UPDATE task_info SET task_num = task_num + 1 WHERE id = NEW.task_info_id
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1435 page no 5 n bits 160 index `PRIMARY` of table `robot-test`.`task_info` trx id 674590067 lock_mode X locks rec but not gap waiting
    Record lock, heap no 88 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
     0: len 8; hex 0000000000002797; asc       ' ;;
     1: len 6; hex 000028356d71; asc   (5mq;;
     2: len 7; hex 250000002a2d38; asc %   *-8;;
     3: len 15; hex 6c7962e6b58be8af95e4ba8be58aa1; asc lyb            ;;
     4: len 4; hex 80000052; asc    R;;
     5: len 8; hex 8000000000000009; asc         ;;
     6: len 8; hex 80000175df930572; asc    u   r;;
     7: SQL NULL;
     8: SQL NULL;
     9: len 1; hex 83; asc  ;;
     10: len 8; hex 746d702e786c7378; asc tmp.xlsx;;
     11: len 8; hex 8000000000000038; asc        8;;
     12: len 4; hex 31303138; asc 1018;;
     13: len 4; hex 80000001; asc     ;;
     14: len 11; hex 3031303537363234333433; asc 01057624343;;
     15: len 5; hex 5369797565; asc Siyue;;
     16: len 4; hex 80000032; asc    2;;
     17: len 4; hex 80000000; asc     ;;
     18: len 4; hex 80000000; asc     ;;
     19: SQL NULL;
     20: len 4; hex 80000000; asc     ;;
     21: len 4; hex 80000000; asc     ;;
     22: len 4; hex 80000000; asc     ;;
     23: len 1; hex 80; asc  ;;
    
    *** (2) TRANSACTION:
    TRANSACTION 674590065, ACTIVE 1.360 sec fetching rows
    mysql tables in use 1, locked 1
    213 lock struct(s), heap size 30248, 83 row lock(s), undo log entries 82
    MySQL thread id 1760585, OS thread handle 0x7f06429f7700, query id 617886797 172.25.1.154 robot updating
    UPDATE call_task_info
            SET progress = 4
            WHERE
            task_info_id = 10135
            and progress in
             (
                0
             ,
                3
             ,
                5
             )
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 1435 page no 5 n bits 160 index `PRIMARY` of table `robot-test`.`task_info` trx id 674590065 lock_mode X locks rec but not gap
    Record lock, heap no 88 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
     0: len 8; hex 0000000000002797; asc       ' ;;
     1: len 6; hex 000028356d71; asc   (5mq;;
     2: len 7; hex 250000002a2d38; asc %   *-8;;
     3: len 15; hex 6c7962e6b58be8af95e4ba8be58aa1; asc lyb            ;;
     4: len 4; hex 80000052; asc    R;;
     5: len 8; hex 8000000000000009; asc         ;;
     6: len 8; hex 80000175df930572; asc    u   r;;
     7: SQL NULL;
     8: SQL NULL;
     9: len 1; hex 83; asc  ;;
     10: len 8; hex 746d702e786c7378; asc tmp.xlsx;;
     11: len 8; hex 8000000000000038; asc        8;;
     12: len 4; hex 31303138; asc 1018;;
     13: len 4; hex 80000001; asc     ;;
     14: len 11; hex 3031303537363234333433; asc 01057624343;;
     15: len 5; hex 5369797565; asc Siyue;;
     16: len 4; hex 80000032; asc    2;;
     17: len 4; hex 80000000; asc     ;;
     18: len 4; hex 80000000; asc     ;;
     19: SQL NULL;
     20: len 4; hex 80000000; asc     ;;
     21: len 4; hex 80000000; asc     ;;
     22: len 4; hex 80000000; asc     ;;
     23: len 1; hex 80; asc  ;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1430 page no 240 n bits 72 index `PRIMARY` of table `robot-test`.`call_task_info` trx id 674590065 lock_mode X locks rec but not gap waiting
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
     0: len 8; hex 87712a12c9009000; asc  q*     ;;
     1: len 6; hex 000028356d73; asc   (5ms;;
     2: len 7; hex a6000000200110; asc        ;;
     3: len 8; hex 8000000000002797; asc       ' ;;
     4: len 6; hex e5bca0e4b889; asc       ;;
     5: len 11; hex 3138313231333736373835; asc 18121376785;;
     6: len 4; hex 80000000; asc     ;;
     7: len 4; hex 80000000; asc     ;;
     8: len 8; hex 8000000000000009; asc         ;;
     9: len 15; hex 545453e7bb84e59088e8af9de69caf; asc TTS            ;;
     10: len 11; hex 3031303537363234333433; asc 01057624343;;
     11: SQL NULL;
     12: SQL NULL;
     13: len 8; hex 80000175e0204f24; asc    u  O$;;
     14: SQL NULL;
     15: SQL NULL;
     16: len 4; hex 31303138; asc 1018;;
     17: SQL NULL;
     18: len 1; hex 81; asc  ;;
     19: len 4; hex 80000002; asc     ;;
     20: len 8; hex 80000175e0204f24; asc    u  O$;;
     21: len 5; hex 3132333435; asc 12345;;
     22: len 7; hex 636f6e74656e74; asc content;;
     23: SQL NULL;
     24: SQL NULL;
     25: SQL NULL;
     26: SQL NULL;
     27: len 4; hex 80000000; asc     ;;
     28: len 4; hex 80000000; asc     ;;
     29: SQL NULL;
     30: SQL NULL;
     31: SQL NULL;
    
    *** WE ROLL BACK TRANSACTION (1)

**分析日志可知:**

事务A锁定了新插入的一行记录,触发器更新task\_info表的记录数时 再去获取task\_info对应ID的锁, 而此时,task\_info表中对应行已经被事务B锁住了, 需等待事务B释放

而事务B先锁定了task\_info一行记录, 随后锁定了call\_task\_info表83行记录, 包括事务A新插入但还未提交的记录,  从而形成死锁了

### 解决方案: ###

移除取消方法的事务即解决