MySQL(七)关于MySQL不同版本下临键锁锁定范围不同

MySQL InnoDB底层的锁实现算法分为三种 :

记录锁，间隙锁，临键锁。

之前在验证MySQL的临键锁的时候使用docker安装的最新版本的MySQL镜像，发现其临键锁在最新的MySQL的表现和低版本(5.7)不一致，后面又自己验证了一下，并整理成博客

本文使用的高低版本MySQL分别为:
高版本MySQL: 8.0.18
低版本MySQL : 5.7.10

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t2` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');
INSERT INTO `t2` (`id`, `name`) VALUES (9, '9');
INSERT INTO `t2` (`id`, `name`) VALUES (14, '14');

高版本下执行

使用临键锁:

在事务2里试验一下插入和查询操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(8,'8');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values (13,'13');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values (15,'15');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values (9,'9');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t2 values (14,'14');
ERROR 1062 (23000): Duplicate entry '14' for key 'PRIMARY'

mysql> select * from t2 where id = 13 for update;
Empty set (0.00 sec)

mysql> select * from t2 where id = 14 for update;
+----+------+
| id | name |
+----+------+
| 14 | 14   |
+----+------+
1 row in set (0.00 sec)
mysql> select * from t2 where id = 9 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如果命中的刚好的查询范围内最大的记录
#事务1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id > 6 and id < = 9 for update;
+----+------+
| id | name |
+----+------+
|  9 | 9    |
+----+------+
1 row in set (0.00 sec)


#事务2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(8,'8');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t2 values (10,'10');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2 where id = 14 for update;
+----+------+
| id | name |
+----+------+
| 14 | 14   |
+----+------+
1 row in set (0.00 sec)

通过SQL试验结论:
1)select * from t2 where id > 6 and id < 10 for update;// 锁住范围是(5,9],(9,14)
在(5,9],(9,14)区间对于插入是阻塞的，但是对于查询操作，只有查询事务1中SQL命中的记录id=9才会阻塞，查询其他记录均不会阻塞
2)select * from t2 where id > 6 and id <= 9 for update;// 锁住范围是(5,9]
在(5,9]区间对于插入是阻塞的，但是对于查询操作，只有查询事务1中SQL命中的记录id=9才会阻塞，查询其他记录均不会阻塞

 

在低版下执行

#事务1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id > 6 and id < 10 for update;
+----+------+
| id | name |
+----+------+
|  9 | 9    |
+----+------+
1 row in set (0.00 sec)
如果在事务1执行select * from t2 where id > 6 and id < = 9 for update;
其锁定范围和上面的SQL是一致的


#事务2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(8,'8');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t2 values (9,'9');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t2 values (13,'13');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t2 values (14,'14');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t2 values (15,'15');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2 where id = 13 for update;
Empty set (0.00 sec)

mysql> select * from t2 where id = 14 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select * from t2 where id = 9 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结论:在(5,9],(9,14]区间对于插入是阻塞的,但是对于查询操作，只有在查询区间(5,9],(9,14]中存在的记录才会阻塞，查询其他记录均不会阻塞

比较高低版本下的SQL执行结果区别,发现其区别主要是在对id=14的操作上

低版本:

mysql> insert into t2 values (14,'14');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t2 where id = 14 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

高版本:

mysql> insert into t2 values (14,'14');
ERROR 1062 (23000): Duplicate entry '14' for key 'PRIMARY'
mysql> select * from t2 where id = 14 for update;
+----+------+
| id | name |
+----+------+
| 14 | 14   |
+----+------+
1 row in set (0.00 sec)

所以MySQL在8.0对临键锁算法进行了优化
1)对于在SQL执行时没有命中的记录，在同个事务中执行相同SQL的时候也不会查询到，对于插入和查询没必要阻塞
2)对于临键锁的锁定范围做了优化，如果查询的时候SQL可能查询到的最大值刚好是Next-Key的右闭区间的值，那么也就不需要锁住下一个临键区间

简单来说MySQL 8.0中临键锁的锁定范围是命中的Record和包含当前查询范围的最小Gap的并集，这样其实跟我们理解的需要锁定的范围更加一致