点击参与 GreatSQL 8.0.32-26 文档捉虫~~~

GreatSQL社区

搜索

[已解决] 为什么死锁日志中事务2持有的锁中有多条唯一键相同的数据

834 3 2023-7-19 14:39
死锁日志如下所示，其中显示被锁定的索引是唯一键


------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-07-14 14:31:35 0x7f8715b7e700
*** (1) TRANSACTION:
TRANSACTION 15544132541, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 24 row lock(s), undo log entries 1
MySQL thread id 953607, OS thread handle 140218332845824, query id 2532709874 x.x.x.x. sigma_rw update
  INTO sigma_lock  ( lock_key,
ts,

owner )  VALUES  ( 'async_lock',
1689316895549,

'KafkaOuterSender' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139 page no 4 n bits 96 index ukey_lock_key_owner of table `sigma`.`sigma_lock` trx id 15544132541 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 15544132542, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 24 row lock(s), undo log entries 1
MySQL thread id 953519, OS thread handle 140218161686272, query id 2532709875 x.x.x.x sigma_rw update
INSERT INTO sigma_lock  ( lock_key,
ts,

owner )  VALUES  ( 'async_lock',
1689316895548,

'KafkaOuterSender' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 139 page no 4 n bits 96 index ukey_lock_key_owner of table `sigma`.`sigma_lock` trx id 15544132542 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb325; asc     -K %;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb326; asc     -K &;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb327; asc     -K ';;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb328; asc     -K (;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb329; asc     -K );;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb32a; asc     -K *;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb32b; asc     -K +;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb32c; asc     -K ,;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb32d; asc     -K -;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb32e; asc     -K .;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb32f; asc     -K /;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb330; asc     -K 0;;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb331; asc     -K 1;;

Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb332; asc     -K 2;;

Record lock, heap no 16 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb333; asc     -K 3;;

Record lock, heap no 17 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb334; asc     -K 4;;

Record lock, heap no 18 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb335; asc     -K 5;;

Record lock, heap no 19 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb336; asc     -K 6;;

Record lock, heap no 20 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb337; asc     -K 7;;

Record lock, heap no 21 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb338; asc     -K 8;;

Record lock, heap no 22 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb33a; asc     -K :;;

Record lock, heap no 23 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 10; hex 6173796e635f6c6f636b; asc async_lock;;
1: len 8; hex 000000002d4bb33b; asc     -K ;;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139 page no 4 n bits 96 index ukey_lock_key_owner of table `sigma`.`sigma_lock` trx id 15544132542 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)


表结构如下所示：


mysql> show create table sigma_lock   \G
*************************** 1. row ***************************
       Table: sigma_lock
Create Table: CREATE TABLE `sigma_lock` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lock_key` varchar(64) DEFAULT NULL,
  `owner` char(36) DEFAULT NULL,
  `ts` bigint(20) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ukey_lock_key_owner` (`lock_key`)
) ENGINE=InnoDB AUTO_INCREMENT=431525470 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)



为什么事务2持有的锁中有多条唯一键相同的数据？





MySQL, InnoDB
全部回复(3)
只看楼主
KAiTO 2023-7-19 19:06:31
在一个事务中，可能会有多个操作需要锁定同一条记录。这种情况下，同一事务可能会在日志中多次出现相同的锁 , 具体的原因还是取决于具体的数据库和事务的操作,请您附带完整的信息补充在帖子里
回复 采纳

举报

鸟山明 2023-7-20 20:22:52
KAiTO 发表于 2023-7-19 19:06
在一个事务中，可能会有多个操作需要锁定同一条记录。这种情况下，同一事务可能会在日志中多次出现相同的锁 ...

好的，谢谢，我查下binlog
回复 采纳

举报

yejr 2023-7-21 09:35:09
鸟山明 发表于 2023-7-20 20:22
好的，谢谢，我查下binlog

贴表DDL，并发执行的事务SQL，以及死锁日志
回复 采纳

举报

鸟山明

3

主题

0

博客

10

贡献

新手上路

Rank: 1

积分
17
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-12-4 01:15 , Processed in 0.056406 second(s), 17 queries , Redis On.
快速回复 返回顶部 返回列表