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

【MySQL】死锁案例之二

2017-09-17 1748
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 一 前言    死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
一 前言
    死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。 本文源于我们的生产案例:并发申请gap锁导致的死锁案例,与之前的 死锁案例一不同，本案例是因为RR模式下两个事务中的sql可以获取同一个gap锁，导致对方事务的insert 相互等待，导致死锁的。
二 案例分析
测试环境准备
Percona server 5.6.24  事务隔离级别为RR
  1. CREATE TABLE `t4` (
  2.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
  3.   `kdt_id` int(11) unsigned NOT NULL ,
  4.   `admin_id` int(11) unsigned NOT NULL ,
  5.   `biz` varchar(20) NOT NULL DEFAULT '1' ,
  6.   `role_id` int(11) unsigned NOT NULL ,
  7.   `shop_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  8.   `operator` varchar(20) NOT NULL DEFAULT '0' ,
  9.   `operator_id` int(11) NOT NULL DEFAULT '0' ,
  10.   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  11.   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  12.   PRIMARY KEY (`id`),
  13.   UNIQUE KEY `uniq_kid_aid_biz_rid` (`kdt_id`,`admin_id`,`role_id`,`biz`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

  16. INSERT INTO `t4` (`id`, `kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  17. VALUES
  18.  (1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),
  19.  (2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),
  20.  (3,30,1,'retail',1,0,'0',0,'2017-05-09 15:55:55','2017-05-09 15:55:55'),
  21.  (4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),
  22.  (5,50,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');
2.2  测试用例
 本测试案例场景是两个事务删除不存的行，然后在insert记录。
T2 T1
test [RW] 02:50:27 >begin;
Query OK, 0 rows affected (0.00 sec) 		test [RW] 02:50:27 >begin;
Query OK, 0 rows affected (0.00 sec)
test [RW] 02:50:34 >delete from t4 where kdt_id = 15 and admin_id = 1 
and biz = 'retail' and role_id = '1';
test [RW] 02:50:41 >delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';
test [RW] 02:50:43 >insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
    -> VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
test [RW] 02:51:02 >INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
    -> VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.3 死锁日志
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-11 14:51:03 7f78eaf25700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308535, ACTIVE 20 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  9. MySQL thread id 3584515, OS thread handle 0x7f78ea5f5700, query id 780258123 localhost root update
  10. insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  11. VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  12. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  13. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308535 lock_mode X locks gap before rec insert intention waiting
  14. *** (2) TRANSACTION:
  15. TRANSACTION 462308534, ACTIVE 29 sec inserting, thread declared inside InnoDB 5000
  16. mysql tables in use 1, locked 1
  17. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  18. MySQL thread id 3584572, OS thread handle 0x7f78eaf25700, query id 780258153 localhost root update
  19. INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  20. VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  21. *** (2) HOLDS THE LOCK(S):
  22. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec
  23. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  24. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec insert intention waiting
  25. *** WE ROLL BACK TRANSACTION (2)
2.4 死锁日志分析
   首先根据《 死锁案例一 》 和《 一个最不可思议的MySQL死锁分析 》中强调 delete 不存在的记录是要加上GAP锁，事务日志中显示Lock_mode X wait .
a T2  delete from t4 where kdt_id = 15 and admin_id = 1   and biz = 'retail' and role_id = '1';  符合条件的记录不存在，导致T2 先持有了(lock_mode X locks gap before rec) 锁住[ ( 2 , 20 , 1 , 'retail' , 1 , 0)-(3,30,1,'retail',1,0) ]的区间 ，防止符合条件的记录插入。
b T1的delete 于T2的delete一样 同样申请了  ( lock_mode X locks gap before rec ) 锁住[ ( 2 , 20 , 1 , 'retail' , 1 , 0)-(3,30,1,'retail',1,0) ]的区间 。

  1. It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.
c T1 的insert 语句申请插入意向锁，但是插入意向锁和T2持有的X GAP ( lock_mode X locks gap before rec ) 冲突，故等待T2中的GAP 锁释放。
  1. Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.
d T2  的insert 语句申请插入 意向锁，但是插入意向锁和T1持有 X GAP ( lock_mode X locks gap before rec ) 冲突，故等待T1中的GAP 锁释放。
T1（INSERT  ）等待T2(DELETE ),T2(INSERT )等待T1(DELETE ) 故而循环等待，出现死锁。
有兴趣的读者朋友可以测试一下 delete 存在记录的场景。


2.6 如何解决呢？
   a 先select 检查一下看看是否存在，然后在删除。这里也存在两个或者多个会话并发执行同一个select where条件的，这里需要开发同学做处理。
   b insert into on deuplicate key .
三 小结
    RR事务隔离级别和GAP锁是导致死锁的常见原因，但是业务逻辑设计不合理也会出发死锁,本文的案例通过修改业务逻辑最终将死锁解决。
如果您觉得能从本文收益，可以请北在南方一瓶饮料 ^_^


文章标签：
云数据库 RDS MySQL 版
关系型数据库
测试技术
索引
MySQL
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
yangyi_dba
目录
相关文章
1243767626601764
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器，操作系统为windows server。服务器上部署mysql单实例，innodb引擎，独立表空间。未进行数据库备份，未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句，导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份，也未开启binlog日志，无法直接还原数据库。
1243767626601764
1061 2
技术自由圈/原疯狂创客圈
|
关系型数据库 MySQL Java
字节面试： MySQL 百万级 导入发生的 “死锁” 难题如何解决？“2序4拆”，彻底攻克
字节面试： MySQL 百万级 导入发生的 “死锁” 难题如何解决？“2序4拆”，彻底攻克
技术自由圈/原疯狂创客圈
924 0
字节面试： MySQL 百万级 导入发生的 “死锁” 难题如何解决？“2序4拆”，彻底攻克
程序员飞鱼
|
SQL 关系型数据库 MySQL
案例剖析，MySQL共享锁引发的死锁问题！
案例剖析，MySQL共享锁引发的死锁问题！
程序员飞鱼
355 0
青云交（Java大数据AI云原生Python）
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计：MySQL 数据库 SQL 语句调优的进阶策略与实际案例（2-2）
本文延续前篇，深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用，介绍多种索引类型及避免索引失效等；调整数据库参数，如缓冲池、连接数和日志参数；还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章，强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导，确保数据库高效运行。
青云交（Java大数据AI云原生Python）
540 5
1436047922066202
|
存储 SQL 关系型数据库
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器，linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时，操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行，导致生产库上部分表被truncate，还有部分表中少量数据被delete。
1436047922066202
458 25
1436047922066202
|
SQL 关系型数据库 MySQL
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障： 本地服务器，安装的windows server操作系统。 操作系统上部署MySQL单实例，引擎类型为innodb，表空间类型为独立表空间。该MySQL数据库没有备份，未开启binlog。 人为误操作，在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除，删除后未对该表进行任何操作。
1436047922066202
605 2
蓝易云
|
存储 关系型数据库 MySQL
从新手到高手：彻底掌握MySQL表死锁
通过本文的介绍，希望你能深入理解MySQL表死锁的概念、原因、检测方法及解决方案，并在实际开发中灵活应用这些知识，提升系统的稳定性和性能。
蓝易云
1301 9
威哥爱编程
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好，我是V哥。上周与朋友讨论数据库子查询问题，深受启发。为此，我整理了10个案例，详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例，帮助大家在实际开发中选择更高效的查询方式，提升系统性能。关注V哥，一起探讨技术，欢迎点赞支持！
威哥爱编程
824 5
1436047922066202
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现：1、数据库无法进行查询等操作；2、使用mysqlcheck和myisamchk无法修复数据库。
1436047922066202
734 6
qhhf_地道酬勤
|
SQL 算法 关系型数据库
面试：什么是死锁，如何避免或解决死锁；MySQL中的死锁现象，MySQL死锁如何解决
面试：什么是死锁，死锁产生的四个必要条件，如何避免或解决死锁；数据库锁，锁分类，控制事务；MySQL中的死锁现象，MySQL死锁如何解决
qhhf_地道酬勤
1378 1

热门文章

最新文章

  • 1
    PostgreSQL 聚合函数讲解 - 6 分组排序聚合
  • 2
    MySql加密存储的数据，如何模糊搜索？
  • 3
    基于 MySQL + Tablestore 分层存储架构的大规模订单系统实践-数据处理ETL篇
  • 4
    再议mysql 主从配置
  • 5
    基于Springboot+MYSQL+Maven实现的宠物医院管理系统（源码+数据库+运行指导文档+项目运行指导视频）
  • 6
    2022云栖精选—AnalyticDB MySQL 湖仓版在B站的最佳实践
  • 7
    源码编译安装mysql，DDL数据定义语言的使用。
  • 8
    连接rds设置网络权限
  • 9
    MySQL故障排查与性能分析方法汇总
  • 10
    apache+mysql+php编译参数
  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
    1451
  • 2
    MySQL中日期和时间函数的使用指南
    670
  • 3
    MySQL 与 Redis 如何保证双写一致性？
    1344
  • 4
    【赵渝强老师】MySQL的事务隔离级别
    607
  • 5
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
    1143
  • 6
    MySQL源码编译安装
    2760
  • 7
    MySQL二进制包安装
    2114
  • 8
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
    1135
  • 9
    MySQL包安装 -- SUSE系列（SUSE资源库安装MySQL）
    1151
  • 10
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
    4214

    • 相关课程

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

    • 相关电子书

    更多
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！