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

【MySQL】死锁案例之二

2017-09-17 1543
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 一 前言    死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个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
关键词：
云数据库 RDS MySQL 版死锁
云数据库 RDS MySQL 版案例
云数据库 RDS MySQL 版死锁案例
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
yangyi_dba
目录
相关文章
程序员飞鱼
|
2月前
|
SQL 关系型数据库 MySQL
MySQL死锁及源码分析！
MySQL死锁及源码分析！
程序员飞鱼
53 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
武子康
209 0
威哥爱编程
|
6天前
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好，我是V哥。上周与朋友讨论数据库子查询问题，深受启发。为此，我整理了10个案例，详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例，帮助大家在实际开发中选择更高效的查询方式，提升系统性能。关注V哥，一起探讨技术，欢迎点赞支持！
威哥爱编程
74 5
蓝易云
|
18天前
|
存储 关系型数据库 MySQL
从新手到高手：彻底掌握MySQL表死锁
通过本文的介绍，希望你能深入理解MySQL表死锁的概念、原因、检测方法及解决方案，并在实际开发中灵活应用这些知识，提升系统的稳定性和性能。
蓝易云
112 9
1436047922066202
|
20天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现：1、数据库无法进行查询等操作；2、使用mysqlcheck和myisamchk无法修复数据库。
1436047922066202
75 6
qhhf_地道酬勤
|
1月前
|
SQL 算法 关系型数据库
面试：什么是死锁，如何避免或解决死锁；MySQL中的死锁现象，MySQL死锁如何解决
面试：什么是死锁，死锁产生的四个必要条件，如何避免或解决死锁；数据库锁，锁分类，控制事务；MySQL中的死锁现象，MySQL死锁如何解决
qhhf_地道酬勤
96 1
mzlogin
|
2月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
mzlogin
181 3
小王老师呀
|
2月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
55 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语法校验
    95
  • 3
    【MySQL系列笔记】分库分表
    231
  • 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快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 函数计算X RDS PostgreSQL，基于LLM大语言模型构建AI知识库

    • 推荐镜像

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