mysql 死锁问题？

mysql 死锁问题

sys_rule_instance 表结构为

数据库隔离级别为

使用两个session, 按照图片上标注的 sql 执行顺序执行。其中 session2执行到 6 时，会因为获取不到锁而被阻塞。这里是问题1: session1 只会锁定 rid = 1359214167466315800 的记录，与 session2 锁定 rid = 1359214167462121472 的记录不会产生冲突，也就不会被阻塞。这里 session2 到底是在获取哪一行的 x 锁被阻塞了呢？
session1 执行 7 时，session 2 由阻塞状态立即转为失败，原因是出现了死锁。这里是问题2: 同样的，session1 执行 7 时，根据 where 条件扫描时应只会到满足 rid = 1359214167466315800 条件的记录加锁，他是如何影响到 session2 导致死锁的呢？

死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-04-09 02:57:24 0x7f53afd72700
*** (1) TRANSACTION:
TRANSACTION 1040049, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 4513, OS thread handle 139998153193216, query id 230453 172.16.0.2 root updating
delete from sys_rule_instance where rid = 1359473330733846560 and type = 10 and state != 2

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 48 page no 5 n bits 368 index PRIMARY of table `trx`.`sys_rule_instance` trx id 1040049 lock_mode X locks rec but not gap
Record lock, heap no 18 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821010; asc         ;;
 1: len 6; hex 0000000fdeb1; asc       ;;
 2: len 7; hex 01000001191c30; asc       0;;
 3: len 8; hex 12ddd1caa0021020; asc         ;;
 4: len 8; hex 12ddd1caa0021021; asc        !;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 5 n bits 368 index PRIMARY of table `trx`.`sys_rule_instance` trx id 1040049 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 0000000fdeb0; asc       ;;
 2: len 7; hex 020000010a21c1; asc      ! ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 1040048, ACTIVE 22 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 4
MySQL thread id 4509, OS thread handle 139996957316864, query id 230455 172.16.0.2 root updating
delete from sys_rule_instance where rid = 1359473330733846528

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 48 page no 5 n bits 368 index PRIMARY of table `trx`.`sys_rule_instance` trx id 1040048 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 0000000fdeb0; asc       ;;
 2: len 7; hex 020000010a21c1; asc      ! ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821001; asc         ;;
 1: len 6; hex 0000000fdeb0; asc       ;;
 2: len 7; hex 020000010a2169; asc      !i;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021002; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821002; asc         ;;
 1: len 6; hex 0000000fdeb0; asc       ;;
 2: len 7; hex 020000010a2195; asc      ! ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021003; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 5 n bits 368 index PRIMARY of table `trx`.`sys_rule_instance` trx id 1040048 lock_mode X locks rec but not gap waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821010; asc         ;;
 1: len 6; hex 0000000fdeb1; asc       ;;
 2: len 7; hex 01000001191c30; asc       0;;
 3: len 8; hex 12ddd1caa0021020; asc         ;;
 4: len 8; hex 12ddd1caa0021021; asc        !;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

就以上问题询问 chatgpt，他的意思是两个session的加锁顺序不一致造成的，创建联合索引能够降低或避免出现死锁。
在我创建
create index idx_rid_state_uid_type on sys_rule_instance(rid, uid, state, type);
联合索引后就没有出现死锁问题了。
对于 chatgpt 描述的两个session加锁顺序不一致导致死锁不太理解：session1 和 session2 锁定的都是不同记录行，即使加锁顺序不一致又有什么影响呢？

复现：

create table if not exists sys_rule_instance(
    id bigint unsigned primary key,
    rid bigint unsigned not null,
    uid bigint unsigned not null,
    state int not null,
    type int not null
);

INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012352,1359473330733846528,1359473330733846529,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012353,1359473330733846528,1359473330733846530,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012354,1359473330733846528,1359473330733846531,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012355,1359473330733846536,1359473330733846537,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012356,1359473330733846540,1359473330733846541,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012357,1359473330733846540,1359473330733846542,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012358,1359473330733846540,1359473330733846543,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012359,1359473330733846544,1359473330733846545,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012360,1359473330733846544,1359473330733846546,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012361,1359473330733846544,1359473330733846547,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012362,1359473330733846544,1359473330733846548,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012363,1359473330733846549,1359473330733846550,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012364,1359473330733846549,1359473330733846551,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012364,1359473330733846549,1359473330733846551,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012365,1359473330733846549,1359473330733846552,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012366,1359473330733846549,1359473330733846553,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012367,1359473330733846549,1359473330733846554,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012368,1359473330733846560,1359473330733846561,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012369,1359473330733846560,1359473330733846562,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012370,1359473330733846560,1359473330733846563,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012371,1359473330733846569,1359473330733846570,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012372,1359473330733846569,1359473330733846571,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012373,1359473330733846569,1359473330733846572,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012374,1359473330733846573,1359473330733846574,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012375,1359473330733846573,1359473330733846575,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012376,1359473330733846573,1359473330733846576,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012377,1359473330733846573,1359473330733846577,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012378,1359473330733846573,1359473330733846578,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012379,1359473330733846582,1359473330733846583,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012380,1359473330733846582,1359473330733846584,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012381,1359473330733846582,1359473330733846585,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012382,1359473330733846582,1359473330733846586,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012383,1359473330733846591,1359473330733846592,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012384,1359473330733846591,1359473330733846593,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012385,1359473330733846591,1359473330733846594,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012386,1359473330733846591,1359473330733846595,1,10);
INSERT INTO `sys_rule_instance` (`id`,`rid`,`uid`,`state`,`type`) VALUES (1359473330759012387,1359473330733846596,1359473330733846597,1,10);

session1:

begin; 

update sys_rule_instance set state = 2 
    where rid = 1359473330733846528 
          and uid = 1359473330733846529 
          and type = 10 
          and state = 1; 

delete from sys_rule_instance 
    where rid = 1359473330733846528 
          and type = 10 
          and state != 2; 

delete from sys_rule_instance 
    where rid = 1359473330733846528; 

commit;

session2:

begin;

update sys_rule_instance set state = 2 
    where rid = 1359473330733846540 
          and uid = 1359473330733846541 
          and type = 10 
          and state = 1;
          
delete from sys_rule_instance 
    where rid = 1359473330733846540 
    and type = 10 
    and state != 2;

delete from sys_rule_instance 
    where rid = 1359473330733846540;

commit;

mysql 死锁

阅读 486

3 个回答

得票最新

开心的大海

726

发布于
4 月 10 日广东

更新于
4 月 10 日

✓ 已被采纳

经过大量百度以及查看官方文档：
在 RC 隔离级别下
对于UPDATE 或 DELETE语句， InnoDB只对它更新或删除的行持有锁。WHERE在 MySQL 评估条件后，释放不匹配行的记录锁。
对于UPDATE语句，如果一行已经被锁定，则InnoDB 执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL判断该行是否符合 WHERE条件 UPDATE。如果该行匹配（必须更新），MySQL 将再次读取该行，这次InnoDB要么锁定它，要么等待锁定它。

以下是我的看法:
首先，where 条件的所有字段均不存在索引，导致使用全表扫描
session1 在执行完 2,3 后, 持有了 rid = 1359473330733846560 的 x 锁，
session2 在执行完 5 时, 尝试给全表加锁,但由于某些行的锁已被 session1 持有,所以采用了半一致读, 得出并不需要对session1持有锁的数据进行更新，所以不会被阻塞。执行完成后,持有成功更新记录的x锁。
session2 在执行 6 时, 还是会对全表进行加锁,但由于无法获取到 session1 持有的记录锁，所以被阻塞。
session1 在此时执行 7, 尝试对全表加锁,发现无法获取session2持有的记录锁,此时存在互斥条件，session1 与 session2 都无法获取对方的记录锁，死锁发生，回滚 session2 的事务，session1执行 7 成功。

解决方式：
如果WHERE条件包含索引列并InnoDB使用索引，则在获取和保留记录锁时仅考虑索引列

考虑对 rid 列加索引，或者如问题中描述的一样对于包含在 where 条件中的所有列加上联合索引。

官网地址

pipiimmortal

121

发布于
4 月 9 日河北

事务1 尝试删除 rid=1359473330733846560 的记录，已持有该行（heap no 18）的锁，但需要等待另一行（heap no 2）的锁。

事务2 尝试删除 rid=1359473330733846528 的记录，已持有该行（heap no 2）及相邻行的锁，但需要等待事务1持有的行（heap no 18）的锁。

结果：两个事务互相等待对方持有的锁，形成循环依赖，触发死锁。

zzMeow

541

发布于
4 月 10 日江苏

更新于
4 月 10 日

session1

update sys_rule_instance set state = 2 
    where rid = 1359473330733846528 
          and uid = 1359473330733846529 
          and type = 10 
          and state = 1;

锁日志

TABLE LOCK table `test`.`sys_rule_instance` trx id 1305148 lock mode IX
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305148 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300bfd; asc 0   0  ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

space id 1450 page no 3 n bits 80：这个锁在表空间 ID 1450 的第 3 页上，锁的 bit 数是 80，代表该页中存在多个被锁的记录。
index PRIMARY of table test.sys_rule_instance：锁发生在该表的主键索引上。
lock_mode X locks rec but not gap：
- lock_mode X：排它锁（Exclusive Lock），只能被一个事务持有。
- locks rec but not gap：锁住的是一条记录，不包括前后“间隙”，排除“间隙锁”（gap lock），说明是精准锁定某行。

执行顺序 3

delete from sys_rule_instance 
    where rid = 1359473330733846528 
          and type = 10 
          and state != 2;

锁日志

TABLE LOCK table `test`.`sys_rule_instance` trx id 1305148 lock mode IX
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305148 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300bfd; asc 0   0  ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821001; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300c23; asc 0   0 #;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021002; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821002; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300c4e; asc 0   0 N;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021003; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

session 2

update sys_rule_instance set state = 2 
    where rid = 1359473330733846540 
          and uid = 1359473330733846541 
          and type = 10 
          and state = 1;

锁日志

TABLE LOCK table `test`.`sys_rule_instance` trx id 1305153 lock mode IX
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305153 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821004; asc         ;;
 1: len 6; hex 00000013ea41; asc      A;;
 2: len 7; hex 330000013e0110; asc 3   >  ;;
 3: len 8; hex 12ddd1caa002100c; asc         ;;
 4: len 8; hex 12ddd1caa002100d; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

---TRANSACTION 1305148, ACTIVE 1471 sec
2 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 9, OS thread handle 11360, query id 129 localhost ::1 root
TABLE LOCK table `test`.`sys_rule_instance` trx id 1305148 lock mode IX
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305148 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300bfd; asc 0   0  ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821001; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300c23; asc 0   0 #;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021002; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821002; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300c4e; asc 0   0 N;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021003; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

执行顺序 6


delete from sys_rule_instance 
    where rid = 1359473330733846528 
          and type = 10 
          and state != 2;

锁日志

LOCK WAIT 3 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 14268, query id 134 localhost ::1 root updating
delete from sys_rule_instance
    where rid = 1359473330733846540
    and type = 10
    and state != 2
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305153 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300bfd; asc 0   0  ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

------------------
TABLE LOCK table `test`.`sys_rule_instance` trx id 1305153 lock mode IX
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305153 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821004; asc         ;;
 1: len 6; hex 00000013ea41; asc      A;;
 2: len 7; hex 330000013e0110; asc 3   >  ;;
 3: len 8; hex 12ddd1caa002100c; asc         ;;
 4: len 8; hex 12ddd1caa002100d; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305153 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300bfd; asc 0   0  ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

---TRANSACTION 1305148, ACTIVE 1628 sec
2 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 9, OS thread handle 11360, query id 129 localhost ::1 root
TABLE LOCK table `test`.`sys_rule_instance` trx id 1305148 lock mode IX
RECORD LOCKS space id 1450 page no 3 n bits 80 index PRIMARY of table `test`.`sys_rule_instance` trx id 1305148 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 12ddd1caa1821000; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300bfd; asc 0   0  ;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021001; asc         ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821001; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300c23; asc 0   0 #;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021002; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 8; hex 12ddd1caa1821002; asc         ;;
 1: len 6; hex 00000013ea3c; asc      <;;
 2: len 7; hex 30000001300c4e; asc 0   0 N;;
 3: len 8; hex 12ddd1caa0021000; asc         ;;
 4: len 8; hex 12ddd1caa0021003; asc         ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 8000000a; asc     ;;

解释

session1 语句2 只锁1条记录
session1 语句3 锁了2条记录
一共锁了 3条记录

session2 语句5 只锁1条记录
session2 语句6 想要加锁，等待3条锁的释放，进入锁等待状态

撰写回答

你尚未登录，登录后可以

和开发者交流问题的细节
关注并接收问题和回答的更新提醒
参与内容的编辑和改进，让解决方法与时俱进

推荐问题

mysql 死锁问题？

session1

锁日志

执行顺序 3

锁日志

session 2

锁日志

执行顺序 6

锁日志

解释

你尚未登录，登录后可以

如何在SpringBoot/MySQL事务中并行执行多条SQL？

上面的sql通过login_time排序但是结果不对？

mysql中存放uuid类型问题？

vue3+node.js如何开发系统中不同的账号有页面权限？

超过1万条数据更新导致Canal异常，选什么解决方案？

MySQL可重复读隔离级别下，为什么普通索引范围查询临键锁不退化为间隙锁？