记一次高并发下导致的数据库死锁解决方案

原创已于 2025-08-26 10:49:51 修改 · 663 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2025-02-27 15:45:31 首次发布

mysql 专栏收录该内容

4 篇文章

订阅专栏

数据库:mysql、引擎:InnoDB，隔离级别为可重复读（repeatable-read）

如果你只是想删除锁住的事务看这里
 了解mysql的锁

你需要提前知道

InnoDB行锁的原理是通过给索引上的索引项加锁来实现的。
InnoDB 默认情况下，对于普通的查询语句（如 SELECT），并不会主动加行锁的记录锁，除非你指定了特定，但是会根据情况决定是否加间隙锁
无论是精确查询还是非精确查询是否加间隙锁，还是取决于InnoDB 判断数据是否唯一

查询类型	命中索引类型	加锁情况	备注
精确查询	唯一索引	不加间隙锁	唯一索引数据一定记录唯一，不会涉及到记录之间的间隙
非精确查询	非唯一索引	加间隙锁	范围查询非唯一索引 InnoDB 认为可能存在幻读的风险一定加间隙锁
精确查询	非唯一索引	取决于查询的数据是否唯一	如果数据唯一，不加间隙锁；如果数据不唯一，可能加间隙锁
非精确查询	唯一索引	取决于查询的数据是否唯一	就算是唯一索引但是查询的范围包含多个可能的插入位置，InnoDB 认为可能存在幻读的风险（如where statistics_date > ‘2022-08-01’ 可能影响多行数据。），除非查询范围内的数据仍然是唯一的（如根据主键in删除）

插入意向锁的兼容性

锁类型	是否兼容	是否等待
插入意向锁	兼容	不需要等待
共享锁（S Lock）	兼容	不需要等待
排他锁（X Lock）	不兼容	需要等待
间隙锁（Gap Lock）	不兼容	需要等待

场景

并发场景下对数据先删除后批量插入形成死锁

某统计表的数据需要按照指定时间范围进行统计和更新。具体流程如下：

按日期分组：将指定时间范围内的数据按日期分组。

多线程处理：为每组日期开启独立线程，并行处理数据。

数据处理逻辑：

删除旧数据：在每组日期的处理中，先删除该日期范围内的旧数据。

新增数据：删除完成后，重新计算并插入该日期范围内的新数据。

错误日志分析

错误1
meal_aid_statistics_org_sale_date_unique_uindex ：涉及日期的唯一联合索引

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-02-07 18:20:27 0x7f997ad3d700
*** (1) TRANSACTION:
TRANSACTION 499271782, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 69 row lock(s), undo log entries 34
MySQL thread id 460250133, OS thread handle 140297208248064, query id 9608085201 192.168.0.44 root Sending data
DELETE t FROM meal_aid_statistics_org_sale_date t
WHERE t.statistics_date >= '2023-01-01'



AND t.statistics_date <= '2023-01-31'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23943 page no 14 n bits 440 index meal_aid_statistics_org_sale_date_unique_uindex_1 of table `db_elderly_care_meal_aid`.`meal_aid_statistics_org_sale_date` trx id 499271782 lock_mode X locks rec but not gap waiting
Record lock, heap no 301 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 3; hex 8fce41; asc A;;
1: len 7; hex 73717a63643038; asc sqzcd08;;
2: len 1; hex 31; asc 1;;
3: len 12; hex 333430323032303030303030; asc 340202000000;;
4: len 8; hex 8000000000001076; asc v;;

*** (2) TRANSACTION:
TRANSACTION 499271783, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 97 row lock(s), undo log entries 49
MySQL thread id 460251036, OS thread handle 140297167427328, query id 9608085259 192.168.0.44 root update
INSERT INTO meal_aid_statistics_org_sale_date (
uuid,
organizational_name,
organizational_region_code,
organizational_tenant_uuid,
meal_record_count,
finish_pay_meal_record_count,
meal_subsidy_count,
older_count,
normal_count,
refund_count,
cancel_count,
region_subsidy_sum,
city_subsidy_sum,
sale_money_sum,
real_money_sum,
receivable_money_sum,
cancel_sum,
refund_sum,
statistics_date,
create_time,
source_type
) VALUES

( '4b98208fe4a74e37ab5f7c5bab034bf1',
'sqzcd08',
'340202000000',
'sqzcd08',
16,
16,
2,
2,

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23943 page no 14 n bits 440 index meal_aid_statistics_org_sale_date_unique_uindex_1 of table `db_elderly_care_meal_aid`.`meal_aid_statistics_org_sale_date` trx id 499271783 lock_mode X locks rec but not gap
Record lock, heap no 301 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 3; hex 8fce41; asc A;;
1: len 7; hex 73717a63643038; asc sqzcd08;;
2: len 1; hex 31; asc 1;;
3: len 12; hex 333430323032303030303030; asc 340202000000;;
4: len 8; hex 8000000000001076; asc v;;

看日志得知删除后增涉及到了记录锁的死锁，锁定对象：只锁定记录本身，不锁定记录之间的间隙（lock_mode X locks rec but not gap waiting
Record lock）

新增事务 持有heap no 301 的 X锁，删除事务想要完成需要heap no 301 的X锁释放

删除事务 锁定 statistics_date 在 2023-01-01 到 2023-01-31 之间的所有记录,这些记录被锁又导致新增事务等待，导致了死锁。

错误2
index2 涉及日期的非唯一联合索引（不要在意索引名称）

LATEST DETECTED DEADLOCK

2025-02-08 10:09:08 0x7f85f8915700
*** (1) TRANSACTION:
TRANSACTION 453615400, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 6
MySQL thread id 234297810, OS thread handle 140213256140544, query id 12289016158 192.168.0.48 saas update
INSERT INTO meal_aid_statistics_older_meal_record_month (
uuid,
older_uuid,
older_name,
organizational_tenant_uuid,
organizational_name,
region_code,
address,
older_number,
meal_record_count,
refund_count,
cancel_count,
region_subsidy_sum,
city_subsidy_sum,
sale_money_sum,
real_money_sum,
cancel_sum,
refund_sum,
statistics_date,
create_time,
source_type
) VALUES

( ‘da3dedb8e81a4b39aba5cf2052b0349e’,
‘2a3dc86811dd435688a14a71ccc5afc1’,
‘杜鹏’,
‘zcjg02’,
‘助餐机构02’,
null,
‘’,
null,
3,
0,

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10206 page no 6 n bits 480 index index2 of table db_elderly_care_meal_aid.meal_aid_statistics_older_meal_record_month trx id 453615400 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 323032322d3038; asc 2022-08;;
1: len 6; hex e5b08fe9bb84; asc ;;
2: len 8; hex 8000000000000e35; asc 5;;

*** (2) TRANSACTION:
TRANSACTION 453615401, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 234298028, OS thread handle 140213377652480, query id 12289016176 192.168.0.48 saas update
INSERT INTO meal_aid_statistics_older_meal_record_month (
uuid,
older_uuid,
older_name,
organizational_tenant_uuid,
organizational_name,
region_code,
address,
older_number,
meal_record_count,
refund_count,
cancel_count,
region_subsidy_sum,
city_subsidy_sum,
sale_money_sum,
real_money_sum,
cancel_sum,
refund_sum,
statistics_date,
create_time,
source_type
) VALUES

( ‘8f3910a7a26845f29b28bc8ac3203d53’,
‘0e3089d00bf848ffb64e87db3ce91c73’,
‘小新’,
‘zcjg08’,
‘助餐机构08’,
null,
‘’,
null,
3,
0,

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10206 page no 6 n bits 480 index index2 of table db_elderly_care_meal_aid.meal_aid_statistics_older_meal_record_month trx id 453615401 lock_mode X locks gap before rec
Record lock, heap no 15 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 7; hex 323032322d3038; asc 2022-08;;
1: len 6; hex e5b08fe9bb84; asc ;;
2: len 8; hex 8000000000000e35; asc 5;;

看日志得知两次新增涉及到了记录锁的死锁，锁定对象： “间隙锁（Gap Lock）”与“插入意向锁（Insert Intention Lock）”冲突导致的死锁场景

在删除操作中，InnoDB 会对删除的范围加间隙锁,
当多个事务同时操作相同范围的间隙时，可能会发生锁冲突：
事务 453615401 持有间隙锁，阻塞事务 453615400 的插入意向锁。
事务 453615400 持有其他锁，阻塞事务 453615401 的操作。
最终形成死锁。

疑问都是先删除再新增为什么两个错误一个涉及间隙锁一个不涉及间隙锁呢？

因为InnoDB行锁的原理是通过给索引上的索引项加锁来实现的。如果查询无法通过索引快速定位到具体记录（例如，使用了全表扫描），InnoDB会锁定所有扫描过的行，而不是整个表。在RR事务隔离级别下且索引为非唯一索引，不仅会对数据表中的每一行加上LOCK_REC_NOT_GAP（lock_mode X）的行锁，而且还会两数据行的间隙加上LOCK_GAP间隙锁。

如何通过代码解决此类死锁问题

由上次问题分析看出
错误1 错误2的根本原因还是删除操作时大量数据被锁住，如果我能尝试缩小被锁数据的范围是不是就可以解决此类问题

那我的方案是根据statistics_date先查询数据然后根据查询数据的id主键进行删除解决了此问题，新方案之所以能够有效避免死锁，主要因为它减少了操作中的锁竞争、并且通过分离查询与删除操作来避免长时间的锁等待。虽然根据id删除阶段仍然可能加间隙锁，但由于锁定的数据量和锁持有时间的减少，死锁发生的概率大大降低了。

 新方案的锁机制

先 SELECT id FROM table WHERE statistics_date BETWEEN '2022-08' AND '2022-09' 查询符合条件的 id。

再 DELETE FROM table WHERE id IN (...) 删除数据。

锁的变化
查询阶段：查询操作记录锁不指定用 FOR UPDATE 或 LOCK IN SHARE MODE的不会加记录锁（读、写锁），但是可能也会加间隙锁,但是锁的范围还是会显著减小

删除阶段：根据 id 删除时，InnoDB 只会对具体的记录加 记录锁（根据主键删除时 除非id 连续 不然不会加间隙锁）。

不会加间隙锁，因此不会阻塞其他事务的插入操作。

其他解决方案
1.你也可以调整隔离级别到READ COMMITTED
2.仍保持先根据statistics_date范围删除，但是加入循环每次只删除1000条
3.再新方案的基础上再加循环逻辑：分页查询 + 分批删除