AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL】死锁案例之四

2017-09-18 2591
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
日志服务 SLS，月写入数据量 50GB 1个月
简介： 一 前言   死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
一 前言
  死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。本文介绍一例三个并发insert 导致的死锁，根本原因还是在于insert 唯一键申请插入意向锁这个特殊的GAP锁。其实称呼插入意向锁 为 Insert Intention Gap Lock 更为合理。
二 案例分析
2.1 环境准备 
Percona server 5.6 RR模式
  1. CREATE TABLE `t6` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `a` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`),
  5.   unique KEY `idx_a` (`a`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  7. insert into t6 values(1,2),(2,8),(3,9),(4,11),(5,19)

sess1

sess2

sess3

begin;



insert into t6(id,a) values(6,15);

begin;



insert into t6(id,a) values(7,15);

begin;



insert into t6(id,a) values(8,15);

rollback; 


ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


2.2 死锁日志
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-18 10:03:50 7f78eae30700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB 1
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  9. MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update
  10. insert into t6(id,a) values(7,15)
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308725 lock_mode X insert intention waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB 1
  15. mysql tables in use 1, locked 1
  16. 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  17. MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update
  18. insert into t6(id,a) values(8,15)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock mode S
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock_mode X insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (2)
2.3 死锁分析
首先依然要再次强调insert 插入操作的加锁逻辑。
第一阶段: 唯一性约束检查，先申请LOCK_S + LOCK_ORDINARY
第二阶段: 获取阶段一的锁并且insert成功之后, 插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert唯一键冲突。
                新数据插入:LOCK_X + LOCK_REC_NOT_GAP
对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断.
其次 我们需要了解 锁的兼容性矩阵。

从兼容性矩阵我们可以得到如下结论:
  1. INSERT操作之间不会有冲突。
  2. GAP,Next-Key会阻止Insert。
  3. GAP和Record,Next-Key不会冲突
  4. Record和Record、Next-Key之间相互冲突。
  5. 已有的Insert锁不阻止任何准备加的锁。
这个案例是三个会话并发执行的，我打算一步一步来分析每个步骤执行完之后的事务日志。
第一步 sess1 执行插入操作
insert into t6(id,a) values(6,15);
  1. ---TRANSACTION 462308737, ACTIVE 5 sec
  2. 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
  3. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149440 localhost root init
  4. show engine innodb status
  5. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
因为第一个插入的语句，所以唯一性冲突检查通过，成功插入(6,15). 此时sess1 会话持有(6,15)的LOCK_X|LOCK_REC_NOT_GAP锁。参考"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."

第二步 sess2 执行插入操作
insert into t6(id,a) values(7,15);
  1. ---TRANSACTION 462308738, ACTIVE 4 sec inserting
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  4. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update
  5. insert into t6(id,a) values(7,15)
  6. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  8. ------------------
  9. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  11. ---TRANSACTION 462308737, ACTIVE 66 sec
  12. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  13. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149526 localhost root init
  14. show engine innodb status
  15. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
  16. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap
首先sess2的insert 申请了IX锁，因为sess1 会话已经插入成功并且持有唯一键 a=15的X 行锁 ， 故而sess2 insert 进行唯一性检查，先申请LOCK_S + LOCK_ORDINARY ,事务日志列表中提示lock mode S waiting
第三部 sess3 执行插入操作
insert into t6(id,a) values(8,15);
  1. ---TRANSACTION 462308739, ACTIVE 3 sec inserting
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  4. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root update
  5. insert into t6(id,a) values(8,15)
  6. ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting
  8. ------------------
  9. TABLE LOCK table `test`.`t6` trx id 462308739 lock mode IX
  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting
  11. ---TRANSACTION 462308738, ACTIVE 35 sec inserting
  12. mysql tables in use 1, locked 1
  13. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  14. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update
  15. insert into t6(id,a) values(7,15)
  16. ------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:
  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  18. ------------------
  19. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  21. ---TRANSACTION 462308737, ACTIVE 97 sec
  22. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  23. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149560 localhost root init
  24. show engine innodb status
  25. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
  26. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap
与会话sess2 的加锁申请流程一致，都在等待sess1释放锁资源。
第四步 sess1 执行回滚操作，sess2 不提交
sess1 rollback;
此时sess2 插入成功，sess3出现死锁，此时sess2 insert插入成功,还未提交,事务列表如下:
  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308744
  5. Purge done for trx s n:o < 462308744 undo n:o < 0 state: running but idle
  6. History list length 1866
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308737, not started
  9. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149626 localhost root init
  10. show engine innodb status
  11. ---TRANSACTION 462308739, not started
  12. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root cleaning up
  13. ---TRANSACTION 462308738, ACTIVE 75 sec
  14. 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
  15. MySQL thread id 3825768, OS thread handle 0x7f78eadce700, query id 781149608 localhost root cleaning up
  16. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S
  18. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S
  19. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock_mode X insert intention
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S locks gap before rec
死锁的原因
 sess1 insert成功并针对a=15的唯一键加上X锁。
 sess2 执行insert 插入(6,15), 在插入之前进行唯一性检查发现和sess1的已经插入的记录重复键需要申请LOCK_S|LOCK_ORDINARY, 但与sess1 的(LOCK_X | LOCK_REC_NOT_GAP)冲突,加入等待队列,等待sess1 释放锁。
 sess3 执行insert 插入(7,15), 在插入之前进行唯一性检查发现和sess1的已经插入的记录重复键需要申请LOCK_S|LOCK_ORDINARY, 但与sess1 的(LOCK_X | LOCK_REC_NOT_GAP)冲突,加入等待队列,等待sess1 释放锁。
 sess1 执行rollback, sess1 释放索引a=15 上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP),此后 sess2和sess3 获得S锁(LOCK_S|LOCK_ORDINARY)成功,sess2和sess3都要请求索引a=15上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP),日志中提示 lock_mode X insert intention。由于X锁与S锁互斥，sess2和sess3都等待对方释放S锁，于是出现死锁，MySQL 选择回滚其中之一。

四 总结
    死锁分析是已经很有挑战的事情，尤其对于insert 唯一键冲突,要分多个阶段去申请，也要理解锁的兼容矩阵。对于这块我还有需要在学习了解的知识点，本文算是抛砖引玉，如有分析理解不正确的地方，望大家指正。

推荐文章 《 MySQL insert 锁机制》 《 insert into 加锁机制》
文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
索引
数据库管理
关键词：
云数据库 RDS MySQL 版死锁
云数据库 RDS MySQL 版案例
云数据库 RDS MySQL 版死锁案例
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
yangyi_dba
目录
相关文章
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
MySQL死锁及源码分析！
MySQL死锁及源码分析！
程序员飞鱼
52 0
MySQL死锁及源码分析！
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析：MySQL唯一索引并发插入导致死锁！
案例剖析：MySQL唯一索引并发插入导致死锁！
程序员飞鱼
214 0
案例剖析：MySQL唯一索引并发插入导致死锁！
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析，MySQL共享锁引发的死锁问题！
案例剖析，MySQL共享锁引发的死锁问题！
程序员飞鱼
57 0
武子康
|
2月前
|
消息中间件 关系型数据库 MySQL
大数据-117 - Flink DataStream Sink 案例：写出到MySQL、写出到Kafka
大数据-117 - Flink DataStream Sink 案例：写出到MySQL、写出到Kafka
武子康
207 0
威哥爱编程
|
5天前
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好，我是V哥。上周与朋友讨论数据库子查询问题，深受启发。为此，我整理了10个案例，详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例，帮助大家在实际开发中选择更高效的查询方式，提升系统性能。关注V哥，一起探讨技术，欢迎点赞支持！
威哥爱编程
71 5
蓝易云
|
18天前
|
存储 关系型数据库 MySQL
从新手到高手：彻底掌握MySQL表死锁
通过本文的介绍，希望你能深入理解MySQL表死锁的概念、原因、检测方法及解决方案，并在实际开发中灵活应用这些知识，提升系统的稳定性和性能。
蓝易云
111 9
1436047922066202
|
19天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现：1、数据库无法进行查询等操作；2、使用mysqlcheck和myisamchk无法修复数据库。
1436047922066202
71 6
qhhf_地道酬勤
|
1月前
|
SQL 算法 关系型数据库
面试：什么是死锁，如何避免或解决死锁；MySQL中的死锁现象，MySQL死锁如何解决
面试：什么是死锁，死锁产生的四个必要条件，如何避免或解决死锁；数据库锁，锁分类，控制事务；MySQL中的死锁现象，MySQL死锁如何解决
qhhf_地道酬勤
96 1
mzlogin
|
2月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
mzlogin
178 3
小王老师呀
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
54 4

热门文章

最新文章

  • 1
    MySQL数据库下载安装教程（Windows&Linux）
  • 2
    MySQL常用函数：IF、SUM等用法
  • 3
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 4
    从新手到高手：彻底掌握MySQL表死锁
  • 5
    MySQL事务隔离级别及默认隔离级别的设置
  • 6
    Liunx部署java项目Tomcat、Redis、Mysql教程
  • 7
    Mysql索引：深入理解InnoDb聚集索引与MyisAm非聚集索引
  • 8
    MySQL导入.sql文件后数据库乱码问题
  • 9
    MySQL的插件式认证到底是干什么的？
  • 10
    数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    82
  • 2
    Linux对MySQL语法校验
    94
  • 3
    【MySQL系列笔记】分库分表
    227
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    170
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    221
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    422
  • 7
    golang使用gorm操作mysql3，数据查询
    100
  • 8
    golang使用gorm操作mysql2
    68
  • 9
    golang使用gorm操作mysql1
    118
  • 10
    Hbase与MySQL对比，区别是什么？
    326

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)