发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

GreatSQL 死锁案例分析

天马行空笑傲一生

3

主题

3

帖子

9

积分

新手上路

Rank: 1

积分
9
显示全部楼层
1.背景概述

客户业务发生死锁的报错，根据业务程序日志及业务流程，发现造成死锁的原因是：事务1 delete + insert ，事务2 delete + insert  2个事务交替执行导致的死锁；由于GAP锁阻塞了插入意向锁，并且当delete的数据存在时死锁不会发生，当delete的数据不存在时，会发生死锁。
2.问题复现

本次测试基于 GreatSQL-8.0.32-24，隔离级别为 RR
2.1 创建测试表
  1. greatsql> create database test;
  3. greatsql> create table test(c1 int unique key, c2 int, c3 int, c4 int);
  5. greatsql> insert into test values (1,1,1,1),(3,3,3,3),(5,5,5,5),(9,9,9,9);
  7. greatsql> select * from test;
  8. +------+------+------+------+
  9. | c1  | c2  | c3  | c4  |
  10. +------+------+------+------+
  11. |   1 |   1 |   1 |   1 |
  12. |   3 |   3 |   3 |   3 |
  13. |   5 |   5 |   5 |   5 |
  14. |   9 |   9 |   9 |   9 |
  15. +------+------+------+------+
  16. 4 rows in set (0.01 sec)
复制代码
2.2 事务执行顺序

按以下事务执行顺序，如果要删除的数据存在，则不会发生死锁；如果要删除的数据不存，并且要删除的数据在同一个GAP锁的区间内则会发生死锁；
时间事务1事务2T1BEGIN;BEGIN;T2delete from test where c1=?;T3delete from test where c1=?;T4insert into test value(?,?,?,?);T5insert into test value(?,?,?,?);2.3 当delete的数据存在时

事务1：delete
  1. greatsql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  4. greatsql> delete from test where c1=3;
  5. Query OK, 1 row affected (0.00 sec)
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  4. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA     |
  5. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  6. |     59 | test      | test     | NULL     | TABLE   | IX       | GRANTED   | NULL        |
  7. |     59 | test      | test     | c1       | RECORD   | X,REC_NOT_GAP | GRANTED   | 3, 0x000000000201 |
  8. |     59 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000201   |
  9. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  10. 3 rows in set (0.00 sec)
复制代码
此时事务1给 3, 0x000000000201 这条数据加了 记录锁 X,REC_NOT_GAP
事务2：delete
  1. greatsql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  4. greatsql> delete from test where c1=5;
  5. Query OK, 1 row affected (0.00 sec)
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  4. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA     |
  5. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  6. |     57 | test      | test     | NULL       | TABLE   | IX       | GRANTED   | NULL        |
  7. |     57 | test      | test     | c1        | RECORD   | X,REC_NOT_GAP | GRANTED   | 5, 0x000000000202 |
  8. |     57 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000202   |
  9. |     59 | test      | test     | NULL       | TABLE   | IX       | GRANTED   | NULL        |
  10. |     59 | test      | test     | c1        | RECORD   | X,REC_NOT_GAP | GRANTED   | 3, 0x000000000201 |
  11. |     59 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000201   |
  12. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  13. 6 rows in set (0.00 sec)
复制代码
此时事务2给 5, 0x000000000202 这条数据加了 记录锁 X,REC_NOT_GAP
事务1：insert
  1. greatsql> insert into test value(3,3,3,3);
  2. Query OK, 1 row affected (0.00 sec)
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  5. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA     |
  7. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  9. |     57 | test      | test     | NULL       | TABLE   | IX       | GRANTED   | NULL        |
  11. |     57 | test      | test     | c1        | RECORD   | X,REC_NOT_GAP | GRANTED   | 5, 0x000000000202 |
  13. |     57 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000202   |
  15. |     59 | test      | test     | NULL       | TABLE   | IX       | GRANTED   | NULL        |
  17. |     59 | test      | test     | c1        | RECORD   | X,REC_NOT_GAP | GRANTED   | 3, 0x000000000201 |
  19. |     59 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000201   |
  21. |     59 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 3, 0x000000000201 |
  23. |     59 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 5, 0x000000000202 |
  25. |     59 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 3, 0x000000000206 |
  27. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  28. 9 rows in set (0.00 sec)
复制代码
此时事务1给 被delete删除的数据 3, 0x000000000201 ，插入的数据 3, 0x000000000202，以及相邻的下一条数据 5, 0x000000000202 加了 间隙锁 S,GAP
事务2：insert
  1. greatsql> insert into test value(5,5,5,5);
  2. Query OK, 1 row affected (0.01 sec)
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  4. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA     |
  5. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  6. |     57 | test      | test     | NULL       | TABLE   | IX       | GRANTED   | NULL        |
  7. |     57 | test      | test     | c1        | RECORD   | X,REC_NOT_GAP | GRANTED   | 5, 0x000000000202 |
  8. |     57 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000202   |
  9. |     57 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 5, 0x000000000202 |
  10. |     57 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 9, 0x000000000203 |
  11. |     57 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 5, 0x000000000207 |
  12. |     59 | test      | test     | NULL       | TABLE   | IX       | GRANTED   | NULL        |
  13. |     59 | test      | test     | c1        | RECORD   | X,REC_NOT_GAP | GRANTED   | 3, 0x000000000201 |
  14. |     59 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,REC_NOT_GAP | GRANTED   | 0x000000000201   |
  15. |     59 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 3, 0x000000000201 |
  16. |     59 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 5, 0x000000000202 |
  17. |     59 | test      | test     | c1        | RECORD   | S,GAP     | GRANTED   | 3, 0x000000000206 |
  18. +-----------+---------------+-------------+-----------------+-----------+---------------+-------------+-------------------+
  19. 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
  1. greatsql> begin;
  2. Query OK, 0 rows affected (0.01 sec)
  4. greatsql> delete from test where c1=6;
  5. Query OK, 0 rows affected (0.00 sec)
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
  4. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA     |
  5. +-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
  6. |     61 | test      | test     | NULL    | TABLE   | IX     | GRANTED   | NULL        |
  7. |     61 | test      | test     | c1     | RECORD   | X,GAP   | GRANTED   | 9, 0x000000000203 |
  8. +-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
  9. 2 rows in set (0.00 sec)
复制代码
此时事务1给 9, 0x000000000203 这条数据加了 间隙锁 X,GAP
事务2：delete
  1. greatsql> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  4. greatsql> delete from test where c1=7;
  5. Query OK, 0 rows affected (0.00 sec)
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  2. +-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
  3. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA     |
  4. +-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
  5. |     62 | test      | test     | NULL    | TABLE   | IX     | GRANTED   | NULL        |
  6. |     62 | test      | test     | c1     | RECORD   | X,GAP   | GRANTED   | 9, 0x000000000203 |
  7. |     61 | test      | test     | NULL    | TABLE   | IX     | GRANTED   | NULL        |
  8. |     61 | test      | test     | c1     | RECORD   | X,GAP   | GRANTED   | 9, 0x000000000203 |
  9. +-----------+---------------+-------------+------------+-----------+-----------+-------------+-------------------+
  10. 4 rows in set (0.00 sec)
复制代码
此时事务2给 9, 0x000000000203 这条数据加了 间隙锁 X,GAP 间隙锁可以相互兼容，因此没有报错
事务1：insert
  1. greatsql> insert into test value(6,6,6,6);
  2. ---hang住，处于锁等待
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+------------+-----------+------------------------+-------------+-------------------+
  4. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE        | LOCK_STATUS | LOCK_DATA     |
  5. +-----------+---------------+-------------+------------+-----------+------------------------+-------------+-------------------+
  6. |     62 | test      | test     | NULL    | TABLE   | IX           | GRANTED   | NULL        |
  7. |     62 | test      | test     | c1     | RECORD   | X,GAP          | GRANTED   | 9, 0x000000000203 |
  8. |     61 | test      | test     | NULL    | TABLE   | IX           | GRANTED   | NULL        |
  9. |     61 | test      | test     | c1     | RECORD   | X,GAP          | GRANTED   | 9, 0x000000000203 |
  10. |     61 | test      | test     | c1     | RECORD   | X,GAP,INSERT_INTENTION | WAITING   | 9, 0x000000000203 |
  11. +-----------+---------------+-------------+------------+-----------+------------------------+-------------+-------------------+
  12. 5 rows in set (0.00 sec)
复制代码
此时事务1，要申请给 9, 0x000000000203 这条数据加 间隙锁，插入意向锁 X,GAP,INSERT_INTENTION；由于事务2已经加了 间隙锁 X,GAP 间隙锁与插入意向锁并不兼容，因此事务1的insert处于锁等待状态
事务2：insert
  1. greatsql> insert into test value(7,7,7,7);
  2. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
复制代码
查看锁信息：
  1. greatsql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
  3. +-----------+---------------+-------------+-----------------+-----------+------------------------+-------------+------------------------+
  4. | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME    | LOCK_TYPE | LOCK_MODE        | LOCK_STATUS | LOCK_DATA        |
  5. +-----------+---------------+-------------+-----------------+-----------+------------------------+-------------+------------------------+
  6. |     61 | test      | test     | NULL       | TABLE   | IX           | GRANTED   | NULL          |
  7. |     61 | test      | test     | c1        | RECORD   | X,GAP          | GRANTED   | 9, 0x000000000203    |
  8. |     61 | test      | test     | c1        | RECORD   | X,GAP          | GRANTED   | 6, 0x000000000213    |
  9. |     61 | test      | test     | GEN_CLUST_INDEX | RECORD   | X            | GRANTED   | supremum pseudo-record |
  10. |     61 | test      | test     | GEN_CLUST_INDEX | RECORD   | X,GAP          | GRANTED   | 0x000000000213     |
  11. |     61 | test      | test     | c1        | RECORD   | X,GAP,INSERT_INTENTION | GRANTED   | 9, 0x000000000203    |
  12. +-----------+---------------+-------------+-----------------+-----------+------------------------+-------------+------------------------+
  13. 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
来源:https://www.cnblogs.com/greatsql/p/18145183
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: docker安装mysql8.0.20并远程连接

下一篇: MySQL 8.0 Reference Manual（读书笔记64节--InnoDB Locking）

发表于 2024-4-19 10:08:28

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4