MySQL死锁浅析

见贤思齐 · 发表于 2024-10-12 21:24:56

MySQL死锁浅析 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 中一系列行为操作划分为可以同时执行和必须互斥执行两种方式，将一把互斥锁设计为读（共享）锁和写（互斥）锁。不同事务中，读读兼容，读写互斥，写写互斥，写读互斥；同一事务中，都兼容。读写锁目的是提高 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 readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1MySQL thread id 59, OS thread handle 6131134464, query id 5744 localhost 127.0.0.1 root updatingUPDATE t set status=5 where id = 5*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 18 page no 4 n bits 88 index

RIMARY of table `test`.`t` trx id 3466 lock_mode X locks rec but not gapRecord lock, heap no 10

HYSICAL 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

RIMARY of table `test`.`t` trx id 3466 lock_mode X locks rec but not gap waitingRecord lock, heap no 11

HYSICAL 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 readmysql tables in use 1, locked 1LOCK 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 updatingUPDATE t set status=5 where id = 0*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 18 page no 4 n bits 88 index

RIMARY of table `test`.`t` trx id 3467 lock_mode X locks rec but not gapRecord lock, heap no 11

HYSICAL 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

RIMARY of table `test`.`t` trx id 3467 lock_mode X locks rec but not gap waitingRecord lock, heap no 10

HYSICAL 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官方参考手册

		自动登录	找回密码
密码			会员注册