[MySql] 数据库死锁的排查和相关知识

查看数据库最近的一次死锁

执行以下命令：

show engine innodb status;

查询结果

......

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-03-20 15:26:35 7f37cf7bc700
*** (1) TRANSACTION:
TRANSACTION 2392496607, ACTIVE 0.516 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 35 lock struct(s), heap size 6544, 19 row lock(s), undo log entries 12
LOCK BLOCKING MySQL thread id: 2150145 block 2145491
MySQL thread id 2145491, OS thread handle 0x7f37cc6b3700, query id 25419661194 192.168.1.239 dbuser0026 statistics
select 1 from accdata where tid = 627899 and cmpid =1 and accid = 2 limit 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35006 page no 3012 n bits 360 index `PRIMARY` of table `userdb0026`.`accdata` trx id 2392496607 lock_mode X locks rec but not gap waiting
Record lock, heap no 266 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 00000002; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a0993; asc \  wJ  ;;
 5: len 8; hex 8000005306ec1f72; asc    S   r;;
 6: len 11; hex 7ffffffffffff83779e08d; asc        7y  ;;

*** (2) TRANSACTION:
TRANSACTION 2392496387, ACTIVE 1.529 sec starting index read
mysql tables in use 1, locked 1
45 lock struct(s), heap size 6544, 28 row lock(s), undo log entries 22
MySQL thread id 2150145, OS thread handle 0x7f37cf7bc700, query id 25419661218 192.168.1.239 dbuser0026 updating
update customer set lastdate = case when lastdate < 1679241600 then 1679241600 else lastdate end where tid = 627899 and custid = 3
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 35006 page no 3012 n bits 360 index `PRIMARY` of table `userdb0026`.`accdata` trx id 2392496387 lock_mode X locks rec but not gap
Record lock, heap no 226 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 000003ea; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a09fb; asc \  wJ  ;;
 5: len 8; hex 8000000000000000; asc         ;;
 6: len 11; hex 8000000000004d4a691900; asc       MJi  ;;

Record lock, heap no 266 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 00000002; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a0993; asc \  wJ  ;;
 5: len 8; hex 8000005306ec1f72; asc    S   r;;
 6: len 11; hex 7ffffffffffff83779e08d; asc        7y  ;;

Record lock, heap no 275 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 0000000b; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a0aa1; asc \  wJ  ;;
 5: len 8; hex 8000000000000000; asc         ;;
 6: len 11; hex 800000000000a348cd1518; asc        H   ;;

Record lock, heap no 280 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 00000010; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a09c7; asc \  wJ  ;;
 5: len 8; hex 8000000000000000; asc         ;;
 6: len 11; hex 8000000000004aa4750640; asc       J u @;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35097 page no 336 n bits 128 index `PRIMARY` of table `userdb0026`.`customer` trx id 2392496387 lock_mode X locks rec but not gap waiting
Record lock, heap no 45 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000003; asc     ;;
 2: len 6; hex 00008e74b3be; asc    t  ;;
 3: len 7; hex 240000801c2230; asc $    "0;;
 4: len 7; hex 43303030393939; asc C000999;;
 5: len 12; hex e99bb6e594aee5aea2e688b7; asc             ;;
 6: len 4; hex 4c534b48; asc LSKH;;
 7: len 4; hex 00000002; asc     ;;
 8: len 1; hex 01; asc  ;;
 9: len 8; hex 8000000000000000; asc         ;;
 10: len 8; hex 8000000000010000; asc         ;;

......

结果分析

重点在 LATEST DETECTED DEADLOCK 区域。

LOCK BLOCKING MySQL thread id: 2150145 block 2145491 表示线程 2150145 与线程 2145491 形成死锁。这里可以同时看到两个线程当前加锁的SQL语句：

线程 2145491：

  • SQL语句：
select 1 from accdata where tid = 627899 and cmpid =1 and accid = 2 limit 1 for update
  • 锁

等待 X 锁 (排他锁，但不是间隙锁）于表 accdata 的主键。

RECORD LOCKS space id 35006 page no 3012 n bits 360 index `PRIMARY` of table `userdb0026`.`accdata` trx id 2392496607 lock_mode X locks rec but not gap waiting
  • 锁住的行或记录信息：
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 00000002; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a0993; asc \  wJ  ;;
 5: len 8; hex 8000005306ec1f72; asc    S   r;;
 6: len 11; hex 7ffffffffffff83779e08d; asc        7y  ;;

线程 2150145：

  • SQL语句：
update customer set lastdate = case when lastdate < 1679241600 then 1679241600 else lastdate end where tid = 627899 and custid = 3
  • 锁

已经加了 X 锁 (排他锁，但不是间隙锁）于表 accdata 的主键。

RECORD LOCKS space id 35006 page no 3012 n bits 360 index `PRIMARY` of table `userdb0026`.`accdata` trx id 2392496387 lock_mode X locks rec but not gap
  • 锁住的行或记录信息：
 0: len 4; hex 000994bb; asc     ;;
 1: len 4; hex 00000001; asc     ;;
 2: len 4; hex 000003ea; asc     ;;
 3: len 6; hex 00008e9a9903; asc       ;;
 4: len 7; hex 5c0000774a09fb; asc \  wJ  ;;
 5: len 8; hex 8000000000000000; asc         ;;
 6: len 11; hex 8000000000004d4a691900; asc       MJi  ;;
  • 正在等锁添加的锁

等待 X 锁 (排他锁，但不是间隙锁）于表 customer 的主键。

RECORD LOCKS space id 35097 page no 336 n bits 128 index `PRIMARY` of table `userdb0026`.`customer` trx id 2392496387 lock_mode X locks rec but not gap waiting

可以看出，2145491 需要锁的表 accdata 的记录区域 0: len 4; hex 000994bb; asc ;; 已被 线程 2150145 锁定，且 线程 2150145 正等待锁另一张表 customer。

解决方案

尽可能的使用 where 条件减少锁的范围。包括将 join 表中的条件在可能的情况下添加到 where 中。
索引优化，尽量避免重复，避免在遍历索引的时候加上行级锁。

数据库锁相关介绍

锁粒度

锁可以分为：表锁、页锁、行锁

  • 行锁之共享锁（S lock）
    允许事务读一行数据，一般记为 S，即读锁
  • 行锁之排他锁（X lock）
    允许事务删除或更新一行数据，一般记为 X，也称为写锁
兼容性 X S
X 不兼容 不兼容
S 不兼容 兼容

锁模式

  • Record Lock (记录锁）
    锁直接加在索引记录上，而不是行数据

  • Gap Lock（间隙锁）
    这里需要明白的是，锁加在了索引记录间隙（记住是间隙不是记录本身！），确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别

    • 什么是间隙？
      假如有一个索引 key 目前有 (1, 3, 5, 7, 9) 五个 key。你要是更新 key=7 时。间隙锁就会锁定 (5,7) 和 (7,9) 这两个范围的数据，然后找到 key=7 的数据行的主键索引和非唯一索引，对 key 加上锁

  • Next-Key Lock
    行锁和间隙锁组合起来就叫 Next-Key Lock，以此防止幻读的发生
    默认情况下，InnoDB 中，更新非唯一索引对应的记录，会加上 Next-Key Lock。如果更新记录为空，就不能加记录锁，只能加间隙锁

锁选择

  • 更新条件没有走索引：
    • 此时所有记录都会加 X 锁和 Gap 锁，相当于进行了表锁
  • 更新条件为索引字段，但是并非唯一索引（包括主键索引）
    • 使用 Next-Key Lock，此时匹配的数据会加 X 锁，记录间隙会加 Gap 锁
  • 更新条件为唯一索引（包括主键索引）
    • 因为唯一索引和主键索引是等值查询，则加 Record Lock（记录锁）
    • 唯一索引需要锁住唯一索引和主键索引，主键索引只需要锁住主键即可
  • 间隙锁是在可重复读隔离级别下才会生效的

如何尽可能避免死锁

  • 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争
  • 调整业务逻辑 SQL 执行顺序， 避免 update / delete 长时间持有锁的 SQL 在事务前面
  • 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小
  • 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁
  • 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了 autocommit 等于0）,那么就会锁定所查找到的记录
  • 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 select … where … order by rand(); 这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住
  • 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL

参考文章

posted @ 2023-03-21 11:02  我爱我家喵喵  阅读(288)  评论(0)  编辑  收藏  举报