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

Question

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会将全表都锁住，求助各位大佬能给解释一下

Answer 1

EXPLAIN的结果字段type

• index，full index scan扫描全索引，所以锁住全表
• range，区间查询，所以区域锁定

EXPLAIN select * from t_lock_1 where a>=13;
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_lock_1 | index | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+


EXPLAIN select * from t_lock_1 where a>13;
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_lock_1 | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+

Answer 2

应该是a没有索引,所以导致锁全表了吧