您现在的位置是：首页 > 文章详情
GreatSQL 死锁案例分析
日期：2024-04-19点击：270收藏
1.背景概述
客户业务发生死锁的报错，根据业务程序日志及业务流程，发现造成死锁的原因是：事务1 delete + insert ，事务2 delete + insert 2个事务交替执行导致的死锁；由于GAP锁阻塞了插入意向锁，并且当delete的数据存在时死锁不会发生，当delete的数据不存在时，会发生死锁。
2.问题复现
本次测试基于 GreatSQL-8.0.32-24，隔离级别为 RR
2.1 创建测试表
greatsql> create database test;
greatsql> create table test(c1 int unique key, c2 int, c3 int, c4 int);
greatsql> insert into test values (1,1,1,1),(3,3,3,3),(5,5,5,5),(9,9,9,9);
greatsql> select * from test;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 3 | 3 | 3 | 3 |
| 5 | 5 | 5 | 5 |
| 9 | 9 | 9 | 9 |
+------+------+------+------+
4 rows in set (0.01 sec)
2.2 事务执行顺序
按以下事务执行顺序，如果要删除的数据存在，则不会发生死锁；如果要删除的数据不存，并且要删除的数据在同一个GAP锁的区间内则会发生死锁；
| 时间 | 事务1 | 事务2 |
|---|---|---|
| T1 | BEGIN; | BEGIN; |
| T2 | delete from test where c1=?; | |
| T3 | delete from test where c1=?; | |
| T4 | insert into test value(?,?,?,?); | |
| T5 | insert into test value(?,?,?,?); |
2.3 当delete的数据存在时
事务1：delete
greatsql> begin;
Query OK, 0 rows affected (0.00 sec)
greatsql> delete from test where c1=3;
Query OK, 1 row affected (0.00 sec)
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| 59 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 59 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 3, 0x000000000201 |
| 59 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000201 |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
3 rows in set (0.00 sec)
此时事务1给 3, 0x000000000201 这条数据加了 记录锁 X,REC_NOT_GAP
事务2：delete
greatsql> begin;
Query OK, 0 rows affected (0.00 sec)
greatsql> delete from test where c1=5;
Query OK, 1 row affected (0.00 sec)
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| 57 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 57 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 5, 0x000000000202 |
| 57 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000202 |
| 59 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 59 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 3, 0x000000000201 |
| 59 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000201 |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
6 rows in set (0.00 sec)
此时事务2给 5, 0x000000000202 这条数据加了 记录锁 X,REC_NOT_GAP
事务1：insert
greatsql> insert into test value(3,3,3,3);
Query OK, 1 row affected (0.00 sec)
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| 57 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 57 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 5, 0x000000000202 |
| 57 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000202 |
| 59 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 59 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 3, 0x000000000201 |
| 59 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000201 |
| 59 | test | test | c1 | RECORD | S,GAP | GRANTED | 3, 0x000000000201 |
| 59 | test | test | c1 | RECORD | S,GAP | GRANTED | 5, 0x000000000202 |
| 59 | test | test | c1 | RECORD | S,GAP | GRANTED | 3, 0x000000000206 |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
9 rows in set (0.00 sec)
此时事务1给 被delete删除的数据 3, 0x000000000201 ，插入的数据 3, 0x000000000202，以及相邻的下一条数据 5, 0x000000000202 加了 间隙锁 S,GAP
事务2：insert
greatsql> insert into test value(5,5,5,5);
Query OK, 1 row affected (0.01 sec)
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
| 57 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 57 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 5, 0x000000000202 |
| 57 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000202 |
| 57 | test | test | c1 | RECORD | S,GAP | GRANTED | 5, 0x000000000202 |
| 57 | test | test | c1 | RECORD | S,GAP | GRANTED | 9, 0x000000000203 |
| 57 | test | test | c1 | RECORD | S,GAP | GRANTED | 5, 0x000000000207 |
| 59 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 59 | test | test | c1 | RECORD | X,REC_NOT_GAP | GRANTED | 3, 0x000000000201 |
| 59 | test | test | GEN_CLUST_INDEX | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000201 |
| 59 | test | test | c1 | RECORD | S,GAP | GRANTED | 3, 0x000000000201 |
| 59 | test | test | c1 | RECORD | S,GAP | GRANTED | 5, 0x000000000202 |
| 59 | test | test | c1 | RECORD | S,GAP | GRANTED | 3, 0x000000000206 |
+-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
12 rows in set (0.00 sec)
此时事务2给 被delete删除的数据 5, 0x000000000202 ，插入的数据 5, 0x000000000207，以及相邻的下一条数据 9, 0x000000000203 加了 间隙锁 S,GAP
由于GAP锁之间是相互兼容的，所以没有发生锁等待及死锁，此时事务1，事务2都执行完成，可以正常提交。
2.4 当delete的数据不存在时
事务1：delete
greatsql> begin;
Query OK, 0 rows affected (0.01 sec)
greatsql> delete from test where c1=6;
Query OK, 0 rows affected (0.00 sec)
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
| 61 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 61 | test | test | c1 | RECORD | X,GAP | GRANTED | 9, 0x000000000203 |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
2 rows in set (0.00 sec)
此时事务1给 9, 0x000000000203 这条数据加了 间隙锁 X,GAP
事务2：delete
greatsql> begin;
Query OK, 0 rows affected (0.00 sec)
greatsql> delete from test where c1=7;
Query OK, 0 rows affected (0.00 sec)
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
| 62 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 62 | test | test | c1 | RECORD | X,GAP | GRANTED | 9, 0x000000000203 |
| 61 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 61 | test | test | c1 | RECORD | X,GAP | GRANTED | 9, 0x000000000203 |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
4 rows in set (0.00 sec)
此时事务2给 9, 0x000000000203 这条数据加了 间隙锁 X,GAP 间隙锁可以相互兼容，因此没有报错
事务1：insert
greatsql> insert into test value(6,6,6,6);
---hang住，处于锁等待
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-------------------+
| 62 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 62 | test | test | c1 | RECORD | X,GAP | GRANTED | 9, 0x000000000203 |
| 61 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 61 | test | test | c1 | RECORD | X,GAP | GRANTED | 9, 0x000000000203 |
| 61 | test | test | c1 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 9, 0x000000000203 |
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-------------------+
5 rows in set (0.00 sec)
此时事务1，要申请给 9, 0x000000000203 这条数据加 间隙锁，插入意向锁 X,GAP,INSERT_INTENTION；由于事务2已经加了 间隙锁 X,GAP 间隙锁与插入意向锁并不兼容，因此事务1的insert处于锁等待状态
事务2：insert
greatsql> insert into test value(7,7,7,7);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查看锁信息：
greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+-------------+-----------------+-----------+------------------------+-------------+------------------------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+-----------------+-----------+------------------------+-------------+------------------------+
| 61 | test | test | NULL | TABLE | IX | GRANTED | NULL |
| 61 | test | test | c1 | RECORD | X,GAP | GRANTED | 9, 0x000000000203 |
| 61 | test | test | c1 | RECORD | X,GAP | GRANTED | 6, 0x000000000213 |
| 61 | test | test | GEN_CLUST_INDEX | RECORD | X | GRANTED | supremum pseudo-record |
| 61 | test | test | GEN_CLUST_INDEX | RECORD | X,GAP | GRANTED | 0x000000000213 |
| 61 | test | test | c1 | RECORD | X,GAP,INSERT_INTENTION | GRANTED | 9, 0x000000000203 |
+-----------+---------------+-------------+-----------------+-----------+------------------------+-------------+------------------------+
6 rows in set (0.01 sec)
事务2回滚，只有事务1的加锁信息。
由于此时事务2，要申请给 9, 0x000000000203 这条数据加 间隙锁，插入意向锁由于事务1已经加了 间隙锁 X,GAP 间隙锁与插入意向锁并不兼容，因此事务2的insert处于锁等待状态，2个事务相互等待锁导致死锁，此时事务2回滚。
3.总结
此次死锁的发生主要是GAP 锁 和 插入意向锁的冲突，建议让业务修改一下逻辑，先判断数据是否存在 select 一下，存在的话 delete 后在 insert ； 不存在的话直接 insert 不用delete了。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html
（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）
技术交流群：
微信&QQ群：
QQ群：533341697
微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。
关注公众号
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有，本站原创内容转载请注明来源。
-
上一篇
【项目实战经验】DataKit迁移MySQL到openGauss（下）
上一篇我们分享了安装、设置、链接、启动等步骤，本篇我们将继续分享迁移、启动~ 目录 9. 离线迁移 9.1. 迁移插件安装 中断安装，比如 kill 掉java进程（安装失败也要等待300s） 下载安装包准备上传 缺少mysqlclient lib包 mysql如果是二进制安装的话，我这个版本是没有18这个lib包的 安装成功后的截图 主机上有对应的进程 10. 全量迁移 10.1. 选中主机，启动迁移 10.2. 迁移中 10.3. 迁移结束 10.4. 日志所在目录 11. 增量迁移 11.1. PG里面创建第二个库 11.2. 创建在线迁移任务 11.3. 启动 11.4. 在mysql端进行DDL和DML mysql 端进行了5个事务 第6次增量 停止增量 12. 反向迁移 12.1. 在PG端进行增删改 12.2. PG端DDL 9. 离线迁移 9.1. 迁移插件安装 中断安装，比如 kill 掉java进程（安装失败也要等待300s） update tb_migration_host_portal_install set install_status=10; 下...
-
下一篇
DjangoAdmin 敏捷开发框架 Flask+EleVue 版本 v2.4.0 发布
v2.4.0 更新内容：1、新增支持原生 SQL 语句查询；2、新增验证码大小写校验规则；3、修复近期用户反馈的问题； 一款 Python 语言基于 Flask、Vue、ElementUI、MySQL 等框架精心打造的一款模块化、高性能、企业级的敏捷开发框架，本着简化开发、提升开发效率的初衷触发，框架自研了一套个性化的组件，实现了可插拔的组件式开发方式：单图上传、多图上传、等一系列个性化、轻量级的组件，是一款真正意义上实现组件化开发的敏捷开发框架。 软件信息 软件名称：DjangoAdmin 敏捷开发框架 Flask+EleVue 版本 官网网址：https://www.djangoadmin.cn 文档网址：http://docs.flask.elevue.djangoadmin.cn 演示地址：http://manage.flask.elevue.djangoadmin.cn 版本说明 版本名称 版本说明 版本地址 Django+Layui 混编版 采用 Django、Layui 等框架研发 https://gitee.com/djangoadmin/DjangoAdmin_Dja...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
扫描即可查看该文章
微信收款码
支付宝收款码