深入理解MySQL8中死锁及线上故障解决 偏执的太偏执、 2021-11-09 19:14 1071阅读 0赞 ## 深入理解MySQL8中死锁及线上故障解决 ## ### 一、什么是死锁 ### **死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。 若无外力作用,事务都将无法推进下去。** 解决死锁问题最简单的方式是不要有等待, 将任何的等待都转化为回滚,并且事务重新开始。毫无疑问,这的确可以避免死锁问题的产生。 然而在线上环境中,这可能导致并发性能的下降,甚至任何一个事务都不能进行。 而这所带来的问题远比死锁问题更为严重,因为这很难被发现并且浪费资源。 ### 二、死锁解决方案 ### **超时** 解决死锁问题最简单的一种方法是超时,即当两个事务互相等待时,当一个等待时间超过设置的某一阂值时, 其中一个事务进行回滚,另一个等待的事务就能继续进行。 在 InnoDB 存储引擎中,参数 innodb\_lock\_wait\_timeout 用来设置超时的时间。 超时机制虽然简单,但是其仅通过超时后对事务进行回滚的方式来处理,或者说其是根据 FIFO 的顺序选择回滚对象。 但若超时的事务所占权重比较大,如事务操作更新了很多行,占用了较多的undo log。 这时采用 FIFO 的方式,就显得不合适了,因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。 因此,除了超时机制,当前数据库还都普遍采用 wait\_for\_graph(等待图)的方式来进行死锁检测。 **wait\_for\_graph** 它是一种主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。 * 锁的信息链 * 事务等待链 通过上述链表可以构造出一张图,而在这个图中若存在回路,就代表存在死锁,因此资源间相互发生等待。 在 wait\_for\_graph中,事务为图中的节点。而在图中,事务 Tl 指向 T2 边的定义为: * 事务 Tl 等待事务 T2 所占用的资源 * 事务 Tl 最终等待 T2 所占用的资源,也就是事务之间在等待相同的资源,而事务 Tl 发生在事务 T2 的后面 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70] 下面来看一个例子,当前事务和锁的状态如下图所示。 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 1] 在 TransactionWaitLists 中可以看到共有 4 个事务 tl 、 t2 、 t3 、 t4 ,故在 wait 一 for graph 中应有 4 个节点。 而事务 t2 对 rowl 占用 X 锁,事务 tl 对 row2 占用 s 锁。事务 tl 需要等待事务 t2 中 rowl 的资源, 因此在 wait\_for\_graph 中有条边从节点 tl 指向节点 t2 。事务 t2 需要等待事务 tl 、 t4 所占用的 row2 对象, 故而存在节点 t2 到节点 tl 、 t4 的边。同样,存在节点 t3 到节点 tl 、 t2 、 t4 的边,因此最终的 wait\_for\_graph 如下图所示 ![在这里插入图片描述][20190801172407299.png] 根据图形可以,t1和t2之间存在环路(了解图论算法的同学看到不陌生),所以检测到时存在死锁的。 wait\_for\_graph是一种较为主动的死锁检测机制,在每个事务请求锁并发生等待时都会判断是否存在回路, 若存在则有死锁,通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。 wait\_for\_graph 的死锁检测通常采用深度优先的算法实现, 在 InnoDB 1.2 版本之前,都是采用递归方式实现。而从 1.2 版本开始,对 wait\_for graph 的死锁检测进行了优化,将递归用非递归方式 进行了实现,进一步提高了InnoDB的性能。 ### 三、死锁产生的几种情况示例 ### #### 3.1 不同表相同行冲突 #### 事务A和事务B操作两张表,但出现循环等待锁情况。A等B释放资源,B等待A释放资源。 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 2] #### 3.2 相同表记录行锁冲突 #### 这种情况比较常见,也是在业务上最常见到的。之前遇到两个job在执行数据批量更新时,jobA处理的的id列表为\[1,2,3,4\],而jobB处理的id列表为\[8,9,10,4,2\],这样就造成了死锁。 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 3] #### 3.3 不同索引锁冲突 #### 这种情况比较隐晦,事务A在执行时,除了在二级索引加锁外,还会在聚簇索引上加锁,在聚簇索引上加锁的顺序是\[1,4,2,3,5\], 而事务B执行时,只在聚簇索引上加锁,加锁顺序是\[1,2,3,4,5\],这样就造成了死锁的可能性。 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 4] #### 3.4 gap锁冲突 #### InnoDB在RR级别下,如下的情况也会产生死锁,比较隐晦。 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 5] ### 四、死锁定位 ### 1. 通过应用业务日志定位到问题代码,找到相应的事务对应的sql 形如以下 start transaction; delete from tableA where id = 1; update tableA set name = 'xxx' where id = 2; commit; 1. 确定数据库隔离级别 执行`select @@global.tx_isolation`确定数据库的隔离级别,我们数据库的隔离级别是RC,这样可以很大概率排除gap锁造成死锁的原因。 1. 执行下show InnoDB STATUS查看最近死锁的日志 这个步骤非常关键。通过DBA的帮忙,我们可以有更为详细的死锁信息。通过此详细日志一看就能发现,与之前事务相冲突的事务结构如下: start transaction; update tableA set name = 'xxx' where id = 2; delete from tableA where id = 1; commit; 这样就很清晰的看出来是`相同表记录行锁冲突`造成的冲突。 ### 五、其他 ### #### 5.1 死锁异常究竟是哪个事务抛出 #### **测试表数据结构及一些测试数据** DROP TABLE IF EXISTS `person_info`; CREATE TABLE `person_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `birthday` date NOT NULL, `phone_number` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `country` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_name_birthday_phone_number`(`name`, `birthday`, `phone_number`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of person_info -- ---------------------------- INSERT INTO `person_info` VALUES (1, '张三', '2019-07-31', '13000000000', '中国'); INSERT INTO `person_info` VALUES (2, '李四', '2017-02-01', '15600000000', '中国'); INSERT INTO `person_info` VALUES (3, 'tom', '2019-08-01', '18888888888', '美国'); **事务隔离级别是RR** **实验1** -- 事务A begin; select * from person_info where id = 1 for update; -- 事务B begin; update person_info set name = 'tom3333' where id = 3; -- 事务A update person_info set name = 'tomaaaaa' where id = 3; -- 此时事务A处理等待状态... -- 事务B update person_info set name = '张三bbbb' where id = 1; -- 当事务B一执行上面sql,事务A立马抛出死锁异常 -- 事务A此时被检测到死锁,被重启事务了。此时事务B还是有效的。 1213 - Deadlock found when trying to get lock; try restarting transaction **实验2** -- 事务A begin; select * from person_info where id = 1 for update; -- 事务B begin; select * from person_info where id = 3 for update; -- 事务A update person_info set name = 'tomaaaaa' where id = 3; -- 此时事务A处理等待状态... -- 事务B update person_info set name = '张三bbbbbb' where id = 1; -- 事务B一执行上面sql,客户端B立马抛出死锁异常 1213 - Deadlock found when trying to get lock; try restarting transaction 以上两个实验,mysql都检测到了死锁,为什么实验1中由事务2触发死锁,重启的是事务1? 但是实验2 中,事务2触发死锁,重启的却是事务2? 所以,mysql在检测到死锁以后,重启的事务的依据是什么呢? 总的依据就是重启**undo量最小的事务**。 看哪个事务的权重最小,事务权重的计算方法:事务加的锁最少;事务写的日志最少;事务开启的时间最晚。 实验1,事务B写了日志,事务A没有,回滚事务A。实验2,都没写日志,但是事务A开始的早,回滚事务B。 #### 5.2 参考 #### * 《MySQL技术内幕:InnoDB存储引擎》第二版 * [由一次线上问题带来的MySQL死锁问题分析][MySQL] [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70]: /images/20211109/0e12e339a8174ef791c46a79455719b4.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 1]: /images/20211109/31c3a3673a874411af882e7b45417ee8.png [20190801172407299.png]: /images/20211109/fc7900ad96d6410ea7d9859c626757ec.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 2]: /images/20211109/06501fb1c997402c9fcccde3ab7cff67.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 3]: /images/20211109/86759f4574cb4d3bb6caf27f3336183c.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 4]: /images/20211109/7031c3adf4d24c1cb72f2087024ccafb.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTcyMzU0NA_size_16_color_FFFFFF_t_70 5]: /images/20211109/483463684e05421ea73736d4fa71e1ac.png [MySQL]: http://benjaminwhx.com/2018/02/28/%E7%94%B1%E4%B8%80%E6%AC%A1%E7%BA%BF%E4%B8%8A%E9%97%AE%E9%A2%98%E5%B8%A6%E6%9D%A5%E7%9A%84MySQL%E6%AD%BB%E9%94%81%E9%97%AE%E9%A2%98%E5%88%86%E6%9E%90/
相关 Java多线程理解:死锁现象及解决方案 在Java多线程编程中,死锁是一种严重问题,它发生在两个或更多的线程相互等待对方释放资源时。 例如,有两个线程A和B: 1. 线程A持有资源R1,并请求资源R2。 2. 线程 迈不过友情╰/ 2024年09月20日 08:54/ 0 赞/ 8 阅读
相关 深入理解Java多线程:死锁的预防与解决策略实例 在Java多线程编程中,死锁是一种常见的问题。下面我们将详细介绍死锁的预防和解决策略,并通过一个实际的例子来说明。 1. **定义**: - 死锁:当两个或更多的线程相 向右看齐/ 2024年09月10日 15:33/ 0 赞/ 22 阅读
相关 【线程安全】死锁问题及解决方案 1. 什么是死锁 比如上一次讲到 synchronized 的时候,一个线程,对同一个对象连续加锁两次,如果出现阻塞等待,代表这个锁是不可重入锁,这样的线程,也就称为死锁 柔情只为你懂/ 2024年03月26日 16:02/ 0 赞/ 15 阅读
相关 深入理解 “死锁“ 死锁理解 一、什么是死锁 二、产生死锁的条件 三、如何避免死锁 1、第一种方式 2、第二种方式(拓展) 一、什么是死锁 本是古典 何须时尚/ 2022年11月03日 15:25/ 0 赞/ 135 阅读
相关 线上BUG:MySQL死锁分析实战 1 线上告警 我们不需要关注截图中得其他信息,只要能看到打印得`org.springframework.dao.DeadlockLoserDataAccessExcept 旧城等待,/ 2022年10月12日 12:17/ 0 赞/ 179 阅读
相关 深入理解MySQL中的锁机制 深入理解MySQL中的锁 一、什么是锁 1.1 为什么需要锁 开发多用户、数据库驱动的应用系统,最大的一个难点:一方面就是要最大程度的利用数据库的并发访问,另 悠悠/ 2022年10月02日 07:45/ 0 赞/ 196 阅读
相关 MySQL数据库死锁原因及解决 数据库和操作系统一样,是一个多用户使用的共享资源。当多个用户并发地存取数据 时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和 痛定思痛。/ 2022年04月18日 00:52/ 0 赞/ 279 阅读
相关 MySQL常见死锁及解决方案 批量更新/删除,使用`in`导致的死锁 批量更新数据时,我猜你会使用`in`关键字,这种批量更新,可能会导致`MySQL`死锁,为什么?因为间隙锁的问题,导致 小灰灰/ 2021年12月11日 12:53/ 0 赞/ 412 阅读
相关 深入理解MySQL8中死锁及线上故障解决 深入理解MySQL8中死锁及线上故障解决 一、什么是死锁 死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。 若无外力作用,事务 偏执的太偏执、/ 2021年11月09日 19:14/ 0 赞/ 1072 阅读
还没有评论,来说两句吧...