mysql死锁分析
本帖最后由 zj5220924 于 2025-1-3 17:45 编辑2024-12-01T11:46:53.974324+08:00 0 Transactions deadlock detected, dumping detailed information.
2024-12-01T11:46:53.974369+08:00 0 *** (1) TRANSACTION:
TRANSACTION 1648345460, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 305143, OS thread handle 140540401534720, query id 128699445 172.16.0.64 test Searching rows for update
UPDATE test01SET batch_id=923613,
original_image_id=1176453577275671055,
corrected_image_id=0,
correct_status=0,
failed_times=0,
last_correct_time='2024-12-01 11:46:54.0',
next_retry_time='2024-12-01 11:44:56.0',
create_time='2024-12-01 11:44:56.0',update_time='2024-12-01 11:46:53.972',
is_del=0
WHEREis_del=0
AND (batch_id = 923613 AND original_image_id = 1176453577275671055 AND correct_status IN (1) AND is_del = 0)
2024-12-01T11:46:53.974447+08:00 0 *** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1752 page no 8021 n bits 192 index PRIMARY of table `test`.`test01` trx id 1648345460 lock_mode X locks rec but not gap
Record lock, heap no 88 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 8; hex 00000000000f868a; asc ;;
1: len 6; hex 0000623fc170; asc b? p;;
2: len 7; hex 01000006af1e2b; asc +;;
3: len 8; hex 00000000000e17dd; asc ;;
4: len 8; hex 10539a4ba041020f; ascS K A;;
5: len 8; hex 0000000000000000; asc ;;
6: len 1; hex 81; asc;;
7: len 4; hex 00000000; asc ;;
8: len 4; hex 674bdc2e; asc gK .;;
9: len 4; hex 674bdbb8; asc gK;;
10: len 4; hex 674bdbb8; asc gK;;
11: len 4; hex 674bdc2e; asc gK .;;
12: len 1; hex 00; asc;;
2024-12-01T11:46:53.975148+08:00 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1752 page no 6497 n bits 1168 index idx_status of table `test`.`test01` trx id 1648345460 lock_mode X waiting
Record lock, heap no 1101 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f866e; asc n;;
2024-12-01T11:46:53.975327+08:00 0 *** (2) TRANSACTION:
TRANSACTION 1648345461, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 5 lock struct(s), heap size 1128, 32 row lock(s)
MySQL thread id 305142, OS thread handle 140539107182336, query id 128699444 172.16.0.64 test Searching rows for update
UPDATE test01SET batch_id=923615,
original_image_id=1176453577276195334,
corrected_image_id=0,
correct_status=0,
failed_times=0,
last_correct_time='2024-12-01 11:46:54.0',
next_retry_time='2024-12-01 11:44:56.0',
create_time='2024-12-01 11:44:56.0',update_time='2024-12-01 11:46:53.972',
is_del=0
WHEREis_del=0
AND (batch_id = 923615 AND original_image_id = 1176453577276195334 AND correct_status IN (1) AND is_del = 0)
2024-12-01T11:46:53.975391+08:00 0 *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1752 page no 6497 n bits 1168 index idx_status of table `test`.`test01` trx id 1648345461 lock_mode X
Record lock, heap no 1015 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f867d; asc };;
Record lock, heap no 1031 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8689; asc ;;
Record lock, heap no 1034 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8678; asc x;;
Record lock, heap no 1041 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8680; asc ;;
Record lock, heap no 1057 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f868a; asc ;;
Record lock, heap no 1065 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8681; asc ;;
Record lock, heap no 1070 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f867f; asc ;;
Record lock, heap no 1073 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8687; asc ;;
Record lock, heap no 1075 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8686; asc ;;
Record lock, heap no 1076 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8688; asc ;;
Record lock, heap no 1078 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8683; asc ;;
Record lock, heap no 1079 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f867e; asc ~;;
Record lock, heap no 1087 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f867a; asc z;;
Record lock, heap no 1088 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8672; asc r;;
Record lock, heap no 1101 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f866e; asc n;;
2024-12-01T11:46:53.977131+08:00 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1752 page no 8021 n bits 192 index PRIMARY of table `test`.`test01` trx id 1648345461 lock_mode X locks rec but not gap waiting
Record lock, heap no 88 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 8; hex 00000000000f868a; asc ;;
1: len 6; hex 0000623fc170; asc b? p;;
2: len 7; hex 01000006af1e2b; asc +;;
3: len 8; hex 00000000000e17dd; asc ;;
4: len 8; hex 10539a4ba041020f; ascS K A;;
5: len 8; hex 0000000000000000; asc ;;
6: len 1; hex 81; asc;;
7: len 4; hex 00000000; asc ;;
8: len 4; hex 674bdc2e; asc gK .;;
9: len 4; hex 674bdbb8; asc gK;;
10: len 4; hex 674bdbb8; asc gK;;
11: len 4; hex 674bdc2e; asc gK .;;
12: len 1; hex 00; asc;;
2024-12-01T11:46:53.977771+08:00 0 *** WE ROLL BACK TRANSACTION (1)
mysql版本：8.0.32
隔离级别：repeatable read
表：test01
二级索引：idx_status
根据加锁规则，事务一事务二都是先对idx_status加锁后再对主键进行加锁，事务结束前二级索引和主键索引对应的锁都不会释放，那上面的日志中主键为00000000000f868a的记录如何在事务一已经获取了二级索引&主键索引的锁后依然能够被事务二获取到二级索引的锁呢？
请提供表DDL和事务过程 事务2是等待被授予锁，状态是 "WAITING FOR THIS LOCK TO BE GRANTED" yejr 发表于 2025-1-3 17:35
请提供表DDL和事务过程
DDL:
CREATE TABLE `test01` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`batch_id` bigint unsigned NOT NULL DEFAULT '0',
`original_image_id` bigint unsigned NOT NULL DEFAULT '0',
`corrected_image_id` bigint unsigned NOT NULL DEFAULT '0',
`correct_status` tinyint NOT NULL DEFAULT '0',
`failed_times` int unsigned NOT NULL DEFAULT '0',
`last_correct_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`next_retry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`is_del` tinyint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`uidpk`),
KEY `idx_correcttime_status` (`last_correct_time`,`correct_status`),
KEY `idx_status` (`correct_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1193449 DEFAULT CHARSET=utf8mb3
事务过程：
事务一、事务二分别同时执行日志中的sql即可 yejr 发表于 2025-1-3 17:42
事务2是等待被授予锁，状态是 "WAITING FOR THIS LOCK TO BE GRANTED"
事务二的HOLDS THE LOCK中有对idx_status持有锁
2024-12-01T11:46:53.975391+08:00 0 *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1752 page no 6497 n bits 1168 index idx_status of table `as_main`.`correct_batch_task` trx id 1648345461 lock_mode X
Record lock, heap no 1015 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f867d; asc };;
Record lock, heap no 1031 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8689; asc ;;
Record lock, heap no 1034 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8678; asc x;;
Record lock, heap no 1041 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f8680; asc ;;
Record lock, heap no 1057 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f868a; asc ;; zj5220924 发表于 2025-1-3 17:48
事务二的HOLDS THE LOCK中有对idx_status持有锁
2024-12-01T11:46:53.975391+08:00 0 ...
事务2持有的是对 idx_status 索引上的锁，对应的锁记录是
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f868a; asc ;;
等待的锁记录是 PRIMARY 上的
0: len 8; hex 00000000000f868a; asc ;;
二者并不是同一条记录。 yejr 发表于 2025-1-3 17:59
事务2持有的是对 idx_status 索引上的锁，对应的锁记录是
0: len 1; hex 81; asc;;
1: len 8; hex 0000 ...
虽然一个是idx_status索引上面的锁，一个是primary上面的锁，但是他们都指向同一条数据，
比如下面这个指向的就是主键为00000000000f868a的记录：
0: len 1; hex 81; asc;;
1: len 8; hex 00000000000f868a; asc ;;
既然事务一已经拿到这条记录的主键索引锁，那idx_status肯定已经获取了，事务还未结束，索引锁都还未释放，那么事务二是如何获取到并持有dix_status对应该条数据的锁的呢 zj5220924 发表于 2025-1-3 18:34
虽然一个是idx_status索引上面的锁，一个是primary上面的锁，但是他们都指向同一条数据，
比如下面这个指 ...
对innodb的索引结构你理解有误，可以先看看这篇文章 https://mp.weixin.qq.com/s/sFaQ2dFIvZk7ujUuC6x70A zj5220924 发表于 2025-1-3 18:34
虽然一个是idx_status索引上面的锁，一个是primary上面的锁，但是他们都指向同一条数据，
比如下面这个指 ...
从应用上查一下,是不是事务前面还执行了什么SQL.
页:
[1]