• MySQL中锁的分类及死锁的概念
  • 发布于 2个月前
  • 112 热度
    0 评论
说明
 不同版本、不同存储引擎下的锁有所不同,本文将在 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;
// 堆代码 duidaima.com
// 插入数据
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 或者当前连接关闭。当需要让整个库处于只读状态的时候,可以使用这个命令。添加全局锁后以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

表级锁
1.表锁
MySQL 提供了对整个表范围加读/写锁,加锁命令为 lock tables … read/write,释放锁命令为 unlock tables 或者是连接关闭。
加读锁:当前连接中只能读,写操作将报错;其他连接中只能读,写操作阻塞等待。
加写锁:当前连接中能读写,其他连接中读写都将阻塞等待。

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

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

3.意向锁
如果想对表 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 中常见的锁,希望大家可以通过本文的锁知识解决线上相关锁的问题。

最后,总结几点日常开发中需要注意的地方:
1.如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放;
2.更新操作最好根据主键索引去更新,因为更新走非聚簇索引,还会回表锁主键索引,锁的范围更多;
3.批量更新前,可以对其进行排序;
4.事务中存在更新多表时,保证多个业务场景下的更新表的相对顺序;
5.唯一键插入冲突时,会给冲突的索引记录加上 S 锁;
用户评论