👆 这是第 405 篇不掺水的原创,想要了解更多,请戳下方卡片关注我们吧~
不同版本、不同存储引擎下的锁有所不同,本文将在 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 读读场景并发访问能力。
需要注意的是,不是只能读语句加读锁,写语句加写锁,比如:增删改查 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' 代表间隙写锁。
RR 隔离级别下:
RC 隔离级别下:
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)
分析下主要内容:
这个结果分成三部分:
test
.t
表示锁是在表t的主键索引上;test
.t
,说明在等的是表 t 的主键索引上面的锁;从上面这些信息中,可以得出以下结论:
可以发现是因为不同线程中更新语句顺序不同导致,所以可以通过修改代码,使其按照相同顺序执行即可解决。
数据库锁设计的初衷是处理并发问题。作为共享资源,当出现并发访问的时候,数据库需要合理地控制共享资源的访问。锁就是用来实现这些访问规则的重要数据结构。
然而,不合理地使用锁也带来了其他问题。本文介绍了MySQL 中常见的锁,希望大家可以通过本文的锁知识解决线上相关锁的问题。
最后,总结几点日常开发中需要注意的地方:
如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放;
更新操作最好根据主键索引去更新,因为更新走非聚簇索引,还会回表锁主键索引,锁的范围更多;
批量更新前,可以对其进行排序;
事务中存在更新多表时,保证多个业务场景下的更新表的相对顺序;
唯一键插入冲突时,会给冲突的索引记录加上 S 锁;
MySQL45讲 MySQL官方参考手册
如果你觉得这篇内容对你挺有启发,我想邀请你帮我两件小事
1.点个「在看」,让更多人也能看到这篇内容(点了「在看」,bug -1 😊)
招贤纳士
政采云技术团队(Zero),Base 杭州,一个富有激情和技术匠心精神的成长型团队。规模 500 人左右,在日常业务开发之外,还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践,推动并落地了一系列的内部技术产品,持续探索技术的新边界。此外,团队还纷纷投身社区建设,目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。
如果你想改变一直被事折腾,希望开始折腾事;如果你想改变一直被告诫需要多些想法,却无从破局;如果你想改变你有能力去做成那个结果,却不需要你;如果你想改变你想做成的事需要一个团队去支撑,但没你带人的位置;如果你想改变本来悟性不错,但总是有那一层窗户纸的模糊……如果你相信相信的力量,相信平凡人能成就非凡事,相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程,亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程,我觉得我们该聊聊。任何时间,等着你写点什么,发给 zcy-tc@cai-inc.com