MySQL死锁浅析-每日运维

文章顶部.png

微风.png

说明

不同版本、不同存储引擎下的锁有所不同，本文将在 MySQL-8.1.0 版本，InnoDB 存储引擎的前提下进行介绍；初始化表结构和数据的语句如下所示，文章中出现的案例都是基于下表数据进行操作。

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`id` int NOT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_b` (`b`) USING BTREE,
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
// 插入数据
insert into t values(0,0,0,0),(5,5,5,5),(10,10,10,10),(15,15,15,15),(20,20,20,20),(25,25,25,25);

MySQL中的锁

要想搞清 MySQL 中的死锁问题，那必然得先了解下 MySQL 锁知识！

MySQL中存在着许多的锁，按照锁的作用范围可以分为全局锁、表级锁和行级锁，每种锁级别下又可划分更细粒度的锁。文章不会涉及锁的具体实现细节，主要介绍的是碰到锁时的现象和其背后原理。由于日常开发阶段主要打交道的是行级锁，所以你可以重点关注行级锁的特性！

读写锁

针对 MySQL 中一系列行为操作划分为可以同时执行和必须互斥执行两种方式，将一把互斥锁设计为读（共享）锁和写（互斥）锁。不同事务中，读读兼容，读写互斥，写写互斥，写读互斥；同一事务中，都兼容。读写锁目的是提高 MySQL 读读场景并发访问能力。

需要注意的是，不是只能读语句加读锁，写语句加写锁，比如：增删改查 DML 语句会自动加元数据读锁、查询语句也可以主动加写锁，具体加什么类型的锁 MySQL 会根据操作性质是共享还是互斥决定。

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供加全局读锁的方法，命令是 Flush tables with read lock，释放锁的命令是 unlock tables 或者当前连接关闭。当需要让整个库处于只读状态的时候，可以使用这个命令。添加全局锁后以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

表级锁

表锁

MySQL 提供了对整个表范围加读/写锁，加锁命令为 lock tables … read/write，释放锁命令为 unlock tables 或者是连接关闭。

加读锁：当前连接中只能读，写操作将报错；其他连接中只能读，写操作阻塞等待。
加写锁：当前连接中能读写，其他连接中读写都将阻塞等待。

演示

元数据锁

MDL（metadata lock）不需要显式使用，在访问一个表的时候会被自动加上。MDL 作用是保证读写正确性。可以想象一下，如果线程正在遍历表 t 中的数据，同一时刻另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构就会匹配不上。

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

意向锁

如果想对表 t 加读锁，需要判断表中每个索引记录上是否存在写锁。这个过程效率低下，如果有个标识可以表示这张表下存在行读/写锁，那么判断操作就可以达到 O(1) 的时间复杂度。

获取行读锁之前，MySQL 会自动对表加意向读锁（IR）；获取行写锁之前，MySQL 会自动对表加意向写锁（IX）。

演示

开启事物，执行 UPDATE t set d = 5 WHERE id = 5 语句；
通过 SELECT * FROM information_schema.INNODB_LOCKS 命令查看加锁情况
下图中 LOCK_MODE：IX 就代表着意向写锁，LOCK_TYPE：TABLE 代表着表级锁。

行级锁

MySQL 的行级锁是在引擎层由各个引擎实现。但并不是所有的引擎都支持行级锁，比如 MyISAM 引擎就不支持行级锁，InnoDB 支持行级锁，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

针对索引记录区间范围不同可以划分为记录锁（REC_NOT_GAP_LOCK）、间隙锁（GAP_LOCK）和临键锁（NEXT-KEY_LOCK）。

记录锁是针对索引行记录的读/写锁，图中存在'5'、'10'、'15'、'20'、'25'共5个记录。
间隙锁是针对索引之间的间隙、头索引之前或者尾索引之后的区域加的读写锁，左开右开，图中存在(-∞,5)、(5,10)、(10,15)、(15,20)、(20,25)、(25,+∞)共6个间隙。用于防止间隙内插入数据。
临键锁 = 间隙锁 + 行锁，左开右闭，图中存在(-∞,5]、(5,10]、(10,15]、(15,20]、(20,25]、(25,+∞]共6个临键。

演示

RR 隔离级别下开启事物，执行 select id from t where c = 10 for update 语句后，查看当前加锁情况。如下图所示： 'IX' 代表表级意向写锁，'X' 代表临键写锁，'X,REC_NOT_GAP' 代表记录写锁，'X,GAP' 代表间隙写锁。

MySQL如何加锁

锁的兼容互斥性

意向锁之间都互相兼容；
间隙锁之间都相互兼容，间隙锁和插入意向锁互斥，间隙锁目的是保护该间隙不被插入新数据；
行级读锁和行级读锁兼容，行级读锁和行级写锁互斥，行级写锁和行级写锁互斥。

加锁规则

RR 隔离级别下：

查询过程中访问到的对象才会加锁（回表操作也算），update 语句、delete 语句和 select 语句一样，需要先定位到数据；而加锁的基本单位是 next-key lock（左开右闭），锁并非不需要就立刻释放，而是要等到事务结束才释放；
等值查询 MySQL 的优化：索引上的等值查询，如果是唯一索引，next-key lock 会退化为记录锁；如果不是唯一索引，需要访问到第一个不满足条件的值， next-key lock 会退化为间隙锁；
范围查询：范围查询需要访问到不满足条件的第一个值为止；
insert语句：
- 普通insert情况，只加IX表锁和插入意向锁；
- 若其他线程并发插入同一索引位置时，若当前位置是普通索引，在该记录上加一把X锁；若当前位置是唯一索引，则会给冲突的索引记录添加S锁。

RC 隔离级别下：

和 RR 类似，没有间隙锁和临键锁，查询过程中访问到的对象才会加锁，加锁的基本单位为记录锁，语句执行完就释放“不满足条件的行”的记录锁，“满足条件的行”的记录锁才在事务结束时才释放。

死锁

何为死锁

MySQL 中不同的锁之间存在兼容互斥关系，如果线程 1 中需要的锁资源 C 和线程 2 中拥有的锁资源 B 互斥，线程 1 就会阻塞等待线程 2 释放锁 B ；线程 2 需要的锁资源 D 又和线程 1 拥有的锁资源 A 互斥，线程 2 会阻塞等待线程 1 释放锁 A ，导致互相等待对方锁资源释放，这个现象就是死锁。

MySQL 提供了两种策略解决死锁问题：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置，默认为 50 秒；

另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认是开启的。

线上死锁问题如何排查

通过 deadlock 关键词条搜索线上日志，相关日志如上图所示，其中会打印出相关的sql语句，可以很容易定位到程序中的代码位置。偶尔的死锁并不用担心，可以使用 SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks" 命令查看数据库死锁发生的次数，如果出现次数特别多，就需要排查下是否是程序代码的问题；

在 MySQL 管理台上执行 SHOW ENGINE INNODB STATUS 命令可以查看最后一次发生死锁时的日志，Status 字段中就是日志。查看死锁日志命令只能看到最近一次死锁日志，你想看的死锁日志可能被其他业务死锁覆盖，你可以打开innodb_print_all_deadlocks 配置，会记录所有死锁日志，排查好后再关闭该配置。

下面是截取的一段死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-11-08 21:08:08 0x16c56f000
*** (1) TRANSACTION:
TRANSACTION 3466, ACTIVE 341 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 59, OS thread handle 6131134464, query id 5744 localhost 127.0.0.1 root updating
UPDATE t set status=5 where id = 5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 88 index PRIMARY of table `test`.`t` trx id 3466 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 13; compact format; info bits 64
 0: len 4; hex 80000000; asc     ;;
 1: len 6; hex 000000000d8a; asc       ;;
 2: len 7; hex 02000001160511; asc        ;;
 3: len 4; hex 80000000; asc     ;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 7: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 8: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 9: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 10: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 11: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 12: len 4; hex 80000005; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 88 index PRIMARY of table `test`.`t` trx id 3466 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 13; compact format; info bits 64
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000d80; asc       ;;
 2: len 7; hex 010000011908ba; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000005; asc     ;;
 6: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 7: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 8: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 9: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 10: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 11: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 12: len 4; hex 80000005; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 3467, ACTIVE 28 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 60, OS thread handle 6127792128, query id 5748 localhost 127.0.0.1 root updating
UPDATE t set status=5 where id = 0

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 88 index PRIMARY of table `test`.`t` trx id 3467 lock_mode X locks rec but not gap
Record lock, heap no 11 PHYSICAL RECORD: n_fields 13; compact format; info bits 64
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000d80; asc       ;;
 2: len 7; hex 010000011908ba; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 4; hex 80000005; asc     ;;
 6: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 7: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 8: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 9: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 10: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 11: len 30; hex 80000005000d40006000288000000a000000000d82020000017201518000; asc       @ ` (              r Q  ; (total 4294967291 bytes);
 12: len 4; hex 80000005; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 88 index PRIMARY of table `test`.`t` trx id 3467 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 13; compact format; info bits 64
 0: len 4; hex 80000000; asc     ;;
 1: len 6; hex 000000000d8a; asc       ;;
 2: len 7; hex 02000001160511; asc        ;;
 3: len 4; hex 80000000; asc     ;;
 4: len 4; hex 80000000; asc     ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 7: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 8: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 9: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 10: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 11: len 30; hex 80000005000d400058002880000005000000000d80010000011908ba8000; asc       @ X (                   ; (total 4294967291 bytes);
 12: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

分析下主要内容：

这个结果分成三部分：

(1) TRANSACTION，是第一个事务的信息；
(2) TRANSACTION，是第二个事务的信息；
WE ROLL BACK TRANSACTION (2)，是最终的处理结果，表示回滚了第一个事务。

第一个事务的信息中：

HOLDS THE LOCK(S) 用来显示这个事务持有哪些锁；
- index PRIMARY of table test.t 表示锁是在表t的主键索引上；
- lock_mode X locks rec but not gap 表示持有的是一把记录写锁；
- hex 80000000 表示 16 进制的 0，代表主键 ID = 0 的记录；
WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
- index PRIMARY of table test.t，说明在等的是表 t 的主键索引上面的锁；
- lock_mode X locks rec but not gap waiting 表示这个语句要自己加一个记录写锁，当前的状态是等待中；
- hex 80000005 表示 16 进制的 5，代表主键 ID = 5 的记录；
第一个事务信息就显示了持有着主键 ID = 0 的记录写锁，想在主键 ID = 5 上加记录写锁时被阻塞等待；

第二个事务的信息中：

同理可以看出持有着主键 ID = 5 的记录写锁，想在主键 ID = 0 上加记录写锁时被阻塞等待；

从上面这些信息中，可以得出以下结论：

事务 1中持有主键 ID = 0 的记录写锁，主键 ID = 5 上加记录写锁时阻塞等待；
事务 2 中持有主键 ID = 5 的记录写锁，主键 ID = 0 上加记录写锁时阻塞等待；
因为记录写锁和记录写锁是互斥的，需要等待对方释放写锁，出现了互相依赖，死锁检测到后，回滚了其中一个事物；

可以发现是因为不同线程中更新语句顺序不同导致，所以可以通过修改代码，使其按照相同顺序执行即可解决。

总结

数据库锁设计的初衷是处理并发问题。作为共享资源，当出现并发访问的时候，数据库需要合理地控制共享资源的访问。锁就是用来实现这些访问规则的重要数据结构。

然而，不合理地使用锁也带来了其他问题。本文介绍了MySQL 中常见的锁，希望大家可以通过本文的锁知识解决线上相关锁的问题。

最后，总结几点日常开发中需要注意的地方：

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放；
更新操作最好根据主键索引去更新，因为更新走非聚簇索引，还会回表锁主键索引，锁的范围更多；
批量更新前，可以对其进行排序；
事务中存在更新多表时，保证多个业务场景下的更新表的相对顺序；
唯一键插入冲突时，会给冲突的索引记录加上 S 锁；

参考链接

MySQL45讲 MySQL官方参考手册

招贤纳士

政采云技术团队（Zero），Base 杭州，一个富有激情和技术匠心精神的成长型团队。规模 500 人左右，在日常业务开发之外，还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践，推动并落地了一系列的内部技术产品，持续探索技术的新边界。此外，团队还纷纷投身社区建设，目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。

如果你想改变一直被事折腾，希望开始折腾事；如果你想改变一直被告诫需要多些想法，却无从破局；如果你想改变你有能力去做成那个结果，却不需要你；如果你想改变你想做成的事需要一个团队去支撑，但没你带人的位置；如果你想改变本来悟性不错，但总是有那一层窗户纸的模糊……如果你相信相信的力量，相信平凡人能成就非凡事，相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程，亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程，我觉得我们该聊聊。任何时间，等着你写点什么，发给 zcy-tc@cai-inc.com

微信公众号

文章同步发布，政采云技术团队公众号，欢迎关注文章顶部.png

MySQL死锁浅析

读写锁

全局锁

表级锁

行级锁

锁的兼容互斥性

加锁规则

何为死锁

线上死锁问题如何排查

推荐阅读

招贤纳士

微信公众号

相关文章

发布评论取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

MySQL死锁浅析

读写锁

全局锁

表级锁

行级锁

锁的兼容互斥性

加锁规则

何为死锁

线上死锁问题如何排查

推荐阅读

招贤纳士

微信公众号

相关文章

发布评论 取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

发布评论取消回复