[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，也称为写锁

锁模式

• 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

参考文章

• 我永远吹着泡泡 - 【工作笔记】数据库死锁的排查和数据库锁知识记录

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据
• Django客户端应用1向服务端应用2发送POST请求并接收解析数据
• 数据可视化是如何帮助我们高效了解数据的？

数据库 知识 数据 MySql数据库 知识 数据mysql mysql知识点 知识 数据 理论 数据库 知识 数据 基础知识 数据库 基础 知识 知识点 数据库 知识 数据 基本知识 数据库 知识 数据 知识 数据09 数据库 数据 知识点 小结 向量 数据 数据库 知识库 图谱 数据库 知识 数据