从一个案例深入剖析InnoDB隐式锁和可见性判断(3)-阿里云开发者社区

从一个案例深入剖析InnoDB隐式锁和可见性判断(3)

2022-08-09 54

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： 从一个案例深入剖析InnoDB隐式锁和可见性判断

4.3 delete语句通过二级索引删除数据

TIME	S1	S2
T1	begin；delete from testimp4 where b=9999;（不提交）
T2		select * from testimp4 where d='a' for update

#T1时刻S1锁状态：
---TRANSACTION 94501, ACTIVE 109 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
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 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 6; hex 000000017125; asc     q%;;
2: len 7; hex 5a0000002518ea; asc Z   %  ;;
3: len 4; hex 8000270f; asc   ' ;;
4: len 4; hex 8000270f; asc   ' ;;
5: len 1; hex 61; asc a;;

# T2时刻S1锁状态：
---TRANSACTION 94501, ACTIVE 119 sec
4 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
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 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 6; hex 000000017125; asc     q%;;
2: len 7; hex 5a0000002518ea; asc Z   %  ;;
3: len 4; hex 8000270f; asc   ' ;;
4: len 4; hex 8000270f; asc   ' ;;
5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 61; asc a;;
1: len 4; hex 8000270f; asc   ' ;;

实际上我们看到这里delete语句后，显示二级索引加了显示锁，然后主键加了显示锁，这是因为数据查找阶段先查找的二级索引然后回表查的主键，但是对于二级索引d来讲是由于维护而加的是隐式锁，我们通过S2将其转换为了显示锁。

4.4 update语句通过主键修改数据

这里要特别注意一下，对于二级索引的更新通常是进行了删除和插入，因此这里有2行数据都有隐式锁

TIME	S1	S2	S3
T1	begin;update testimp4 set b=10000 where id=9999;（不提交）
T2		select * from testimp4 where b=9999 for update
T3			select * from testimp4 where b=10000 for update

# T1时刻S1锁状态

---TRANSACTION 94553, ACTIVE 7 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000270f; asc ' ;;
1: len 6; hex 000000017159; asc qY;;
2: len 7; hex 770000002a187f; asc w * ;;
3: len 4; hex 8000270f; asc ' ;;
4: len 4; hex 80002710; asc ' ;;
5: len 1; hex 61; asc a;;

# T2时刻S1锁状态
---TRANSACTION 94553, ACTIVE 62 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000270f; asc ' ;;
1: len 6; hex 000000017159; asc qY;;
2: len 7; hex 770000002a187f; asc w * ;;
3: len 4; hex 8000270f; asc ' ;;
4: len 4; hex 80002710; asc ' ;;
5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000270f; asc ' ;;
1: len 4; hex 8000270f; asc ' ;;

# T3时刻S1锁状态
---TRANSACTION 94553, ACTIVE 128 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000270f; asc ' ;;
1: len 6; hex 000000017159; asc qY;;
2: len 7; hex 770000002a187f; asc w * ;;
3: len 4; hex 8000270f; asc ' ;;
4: len 4; hex 80002710; asc ' ;;
5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80002710; asc ' ;;
1: len 4; hex 8000270f; asc ' ;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000270f; asc ' ;;
1: len 4; hex 8000270f; asc ' ;;

这里由于对表的二级索引b通过主键进行了修改，那么二级索引包含了2条数据，一条标记为del flag，另外一条为插入如下：

(11) normal record offset:266 heapno:12 n_owned 0,delflag:Y minflag:0 rectype:0
(12) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(13) SUPREMUM record offset:112 heapno:1 n_owned 8,delflag:N minflag:0 rectype:3

因此这两行都上了隐式锁，这是由于二级索引维护而上的，值得注意的是二级索引d不会上隐式锁，因为update语句的修改不会涉及到d列索引，因此不会维护。如果查询d列上的值（for update），会获取d列上的锁成功，然后会堵塞在主键id上如下：

---TRANSACTION 94565, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 16, OS thread handle 140737086228224, query id 748 localhost root Sending data
select * from testimp4 where d='a' for update
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

------------------
TABLE LOCK table `test`.`testimp4` trx id 94565 lock mode IX
RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94565 lock_mode X
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;