首页 新闻 搜索 专区 学院

Mysql REPEATABLE-READ隔离级别下加锁区间的问题

0
悬赏园豆:50 [待解决问题]

Mysql的版本是8.0.23
首先设置事务隔离级别set transaction_isolation="REPEATABLE-READ";
创建测试表,并插入测试数据

create table t_lock_1 (a int primary key);
insert into t_lock_1 values(10),(11),(13),(20);

开启一个事务,查询a>=11的数据

begin;
select * from t_lock_1 where a>=11 for update;

另外新建一个链接查看锁的情况,查看锁定的区间是11,(11,13],(13,20],(20,+∞),被锁的区间是没有问题的

3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 11, OS thread handle 139629588756224, query id 111 localhost root
TABLE LOCK table `dev`.`t_lock_1` trx id 24377 lock mode IX
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24377 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 8200000102011d; asc        ;;

RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24377 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 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 8200000102012a; asc       *;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 82000001020137; asc       7;;

回滚上一个事务,开启新事务,这次查询a>=13的数据

begin;
select * from t_lock_1 where a>=13 for update;

按照刚才的逻辑,这次被锁的区间应该是13,(13,20],(20,+∞),但是查看锁信息时,发现全表都被锁住了

2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 11, OS thread handle 139629588756224, query id 115 localhost root
TABLE LOCK table `dev`.`t_lock_1` trx id 24378 lock mode IX
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24378 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 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 82000001020110; asc        ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 8200000102011d; asc        ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 8200000102012a; asc       *;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 82000001020137; asc       7;;

再次回滚事务并开启新事务,这次查询a>13的数据

begin;
select * from t_lock_1 where a>13 for update;

再次查看锁信息,发现被锁的区间是(13,20),(20,+∞),这次的被锁区间也是没有问题的

2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 139629588756224, query id 119 localhost root
TABLE LOCK table `dev`.`t_lock_1` trx id 24379 lock mode IX
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24379 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 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000005f2c; asc     _,;;
 2: len 7; hex 82000001020137; asc       7;;

现在的疑问就是为什么查询a>=13时,innodb会将全表都锁住,求助各位大佬能给解释一下

Hedwiglzy的主页 Hedwiglzy | 初学一级 | 园豆:147
提问于:2021-03-03 15:43
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册