GreatSQL 死鎖案例分析

GreatSQL發表於2024-04-19

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-100-1-1.html

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

死鎖案例分析
2021-01-16
故障分析 | MySQL死鎖案例分析
2023-01-05
MySql
MySQL批量更新死鎖案例分析
2018-09-21
MySql
死鎖案例二
2018-07-08
死鎖案例三
2018-07-15
MySQL死鎖案例分析一(先delete，再insert，導致死鎖)
2021-09-09
MySqldelete
GreatSQL執行Update失敗案例分析
2024-09-04
SQL
【MySQL】死鎖案例之六
2018-03-27
MySql
【MySQL】死鎖案例之七
2018-03-27
MySql
【MySQL】死鎖案例之八
2018-03-28
MySql
MySQL：一個死鎖分析 (未分析出來的死鎖)
2018-10-16
MySql
MySQL:Innodb 一個死鎖案例
2018-09-25
MySql
MySQL死鎖案例一（回滾導致死鎖）
2020-08-21
MySql
MySQL死鎖案例二（自增列導致死鎖）
2020-08-21
MySql
剖析6個MySQL死鎖案例的原因以及死鎖預防策略
2021-04-22
MySql
SQLServer的死鎖分析（1）：頁鎖
2020-09-27
SQLServer
SQL SERVER死鎖查詢，死鎖分析，解鎖，查詢佔用
2024-12-06
SQLServer
MySQL 死鎖問題分析
2020-04-09
MySql
MySQL鎖等待與死鎖問題分析
2021-03-30
MySql
GreatSQL 的重新整理鎖
2024-07-29
SQL
MySQL死鎖案例 – Learn. Write. Repeat.
2019-04-26
MySql
MySQL死鎖系列-常見加鎖場景分析
2020-05-28
MySql
MySQL：RR分析死鎖一列
2019-03-14
MySql
MySQL死鎖分析與解決之路
2022-02-16
MySql
死鎖
2024-08-15
線上BUG：MySQL死鎖分析實戰
2021-07-04
MySql
故障分析 | 從 Insert 併發死鎖分析 Insert 加鎖原始碼邏輯
2023-04-26
原始碼
什麼是死鎖？如何解決死鎖？
2020-08-11
面試官：什麼是死鎖？怎麼排查死鎖？怎麼避免死鎖？
2021-04-01
面試
死鎖概述
2020-10-13
作業系統(5) 死鎖的概念死鎖產生的必要條件死鎖的處理策略預防死鎖避免死鎖死鎖的檢測和解除銀行家演算法
2020-12-27
作業系統演算法
檢視oracle死鎖程式並結束死鎖
2018-09-10
Oracle
例項詳解 Java 死鎖與破解死鎖
2022-02-28
Java
死鎖和可重入鎖
2018-05-25
MySQL 死鎖和鎖等待
2020-04-13
MySql
面試：什麼是死鎖，如何避免或解決死鎖；MySQL中的死鎖現象，MySQL死鎖如何解決
2024-11-10
面試MySql
一次 MySQL 線上死鎖分析實戰
2021-02-23
MySql
MySQL死鎖系列-線上死鎖問題排查思路
2020-10-19
MySql

GreatSQL 死鎖案例分析

1.背景概述

2.問題復現

2.1 建立測試表

2.2 事務執行順序

2.3 當delete的資料存在時

2.4 當delete的資料不存在時

3.總結

關於 GreatSQL

GreatSQL社群：

技術交流群：

相關文章