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

Hedwig
  • 6

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

回复
阅读 294
2 个回答

EXPLAIN的结果字段type

  • indexfull 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 |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+

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

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

宣传栏