InnoDB非唯一索引导致死锁

时间：2024-03-19 21:10:16

死锁日志

SHOW ENGINE INNODB STATUS; 获取最近发生的deadlock

配置：innodb_print_all_deadlocks并在error log查看

InnoDB非唯一索引导致死锁

翻译

  • 行号：“1: len 8; hex 000000000000B75; asc”：B75（16进制） = 2933（10进制）。
  • （1）WAITING FOR THIS LOCK TO BE GRANTED：事务（1）等待获取锁
  • （2）HOLDS THIS LOCK(S)：事务（2）持有该锁

过程

  • TRANSACTION（1）通过update语句1获取2934行记录锁，等待2933行记录锁释放；
  • TRANSACTION（2）持有2933行记录锁，等待2934行记录锁释放。
  • MYSQL发现死锁：WE ROLL BACK TRANSACTION（1）。

分析

表T结构：

  • mNo：非唯一索引
  • id：主键

简化语句：

  • select * from T where mNo = 123：mNo为非唯一索引，分别返回id = 2933和2934行两条记录
  • update语句1（id = 2933）：update T set flag = 0 where mNo = 123 and f = 1;
  • update语句2（id = 2934）：update T set flag = 0 where mNo = 123 and f = 2;
  • explain update T set flag = 0 where mNo = 123 and f = 1;
    • possible_keys：mNo_index（计划用到的索引）
    • rows：2（计划查询的行数）

即使只查询f=1的记录，仍会查询2行

由于MySql是在索引上行锁，两个事务同时用一个key–mNo_index索引，两个事务都需要同时对mNo=123的两条记录上行锁，当两个记录上锁顺序不一样（事务1锁2933行，事务2锁2934行）就有几率发生死锁。

解决方案

  • 固定上锁顺序

    • 先select所有的行，然后按照主键id排序，每个事务都按顺序上锁。
      • 每个事务都先上锁2933行，如果没抢到2933行就阻塞等待，不会去抢2934行
      • 优化点：仅select主键可以在当前索引树直接拿到主键id，减少一次回表
    • 缺点：因为每个事务都增加了查询和排序，增加了性能损耗，

  • 重试机制

    • 死锁发生需要一定的巧合，在非唯一索引导致的死锁问题重试在大多数时候不会有问题
    • 缺点：个别事务会发生失败，影响用户体验

  • 避免长时间持有锁，减少死锁概率

    • 避免长事务
    • 优化业务逻辑，在事务尽量接近结束再上锁，而不是事务刚开始的时候
    • 尽早commit

具体需要根据业务量和死锁发生的概率权衡用哪种方案

InnoDB如何发现死锁

配置：innodb_deadlock_detect（默认开）

事务等待图wait-for-graph（有向图）

InnoDB非唯一索引导致死锁

一旦有向图形成了环，表示造成死锁，InnoDB报错死锁并回滚相应事务

References

How to Minimize and Handle Deadlocks：https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html

相关文章