MySQL 锁机制系列（一） —— Next-Key Lock

背景故事

事务A	事务B
begin;
select count(*) from t_user where uid > 20; // 返回1
	insert into t_user values(30,‘AA’,20);
update t_user set age = 30 where uid > 20; // 显示有两条记录修改成功

上面两个事务，首先先开启事务A，查询id大于20的记录数，结果返回1，说明只有1条记录uid大于20，随后事务B插入了一条uid为30的记录并提交，此时事务A开始执行修改操作，将所有uid大于20的记录的age字段修改为30，执行后显示有两条记录修改成功。这就是常说的幻读形象。

而常见的解决幻读的方法有两种：

将隔离级别升级到SERIALIZABLE；
加锁。

一般不会将数据库隔离级别设置为SERIALIZABLE，那么加锁的话，加什么锁呢？Next-Key Lock。

Next-Key Lock是什么？Next-Key Lock是如何解决幻读的？

下面就来仔细看看。

InnoDB下有三种锁算法：

Record Lock ：单个行记录加锁
Gap Lock ：间隙锁，锁定一个范围，但不含记录本身
Next-Key Lock ：Record Lock + Gap Lock

MySQL InnoDB存储引擎中默认使用的是Next-Key Lock，先使用Gap Lock，当通过对应唯一索引查询时，会将Gap Lock降级为Record Lock。

假设有如下数据：
在这里插入图片描述

节点结构可以简单看作如下形式：

在这里插入图片描述

在上面数据中，Recored Lock对应的就是这三个节点，也就是对应uid为1，10，30的记录，而Gap Lock就是这几个节点之间的范围。整个锁范围可以分为(-∞,1) U 1 U (1,10) U 10 U (10,30) U 30 U (30,+∞)。

下面通过特定的几个场景来看下实际的效果：

表结构

CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uname` varchar(30) DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`uid`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

以下示例都是在默认隔离级别REPEATABLE-READ下，不同隔离级别效果不同。
PS ：每次修改数据后，都会将数据恢复到最初的状态。

1. 主键对应记录存在

事务A	事务B
begin
select * from t_user where uid = 10 for update; 对UID为10的记录加锁
	insert into t_user values(7,‘DD’,‘111111’); 执行成功
	update t_user set uname = ‘bbb’ where uid = 10; 对UID为10的记录进行修改
	阻塞直到锁超时
	update t_user set uname = ‘bbb’ where uid = 10; 对UID为10的记录再次进行修改
commit; 此时是释放锁	阻塞
	Query OK, 1 row affected (7.14 sec) Rows matched: 1 Changed: 1 Warnings: 0 成功执行修改操作

当对应主键记录存在时，只会对对应记录行加Recored Lock。

2. 主键对应记录不存在

事务A	事务B	事务C
begin;
select * from t_user where uid = 8 for update; UID为8的记录不存在
	update t_user set uname = ‘DDD’ where uid = 10; 直接执行成功
	insert into t_user values(7,‘DD’,‘111111’);	insert into t_user values(8,‘DD’,‘111111’);
	阻塞	阻塞
commit;
	Query OK, 1 row affected (3.69 sec)	Query OK, 1 row affected (3.69 sec)

对应主键不存在的记录，会加对应的间隙锁，因为uid8的值大于1小于10，所以为在两者之间加间隙锁，范围也就是(1,10)。

3. 通过主键进行范围查询

事务A	事务B	事务C
begin;
select * from t_user where uid > 12 for update;
	insert into t_user values(8,‘DD’,‘111111’); 执行成功
	update t_user set uname = ‘DDD’ where uid = 10; 执行成功
	insert into t_user values(11,‘DD’,‘111111’);	update t_user set uname = ‘DDD’ where uid = 30;
	阻塞	阻塞
commit;
	Query OK, 1 row affected (15.71 sec)	Query OK, 1 row affected (12.78 sec)

当对范围查询加锁时，会将对应的记录行加行锁以及间隙锁，上面加锁范围为 (10,30) U 30 U (30,+∞)，合起来就是(10,+∞)。

4. 对应记录存在 —— 普通索引

事务A	事务B	事务C
begin;
select * from t_user where age = 30 for update;
	insert into t_user values(7,‘DD’,66); 执行成功
	update t_user set uname = ‘EE’ where uid = 10;	insert into t_user values(8,‘DD’,20);
	阻塞	阻塞
commit;
	Query OK, 1 row affected (14.83 sec)	Query OK, 1 row affected (14.83 sec)

通过普通索引查询加锁时，除了会将对应的记录行加Record Lock，还会对对应的普通索引字段内容加锁，上面加锁范围为age 在10U(10,30) U 30 U (30,50) U 50 之间，也就是[10,50]，再加上UID为10的记录。

5. 对应记录不存在 —— 普通索引

事务A	事务B
begin;
select * from t_user where age = 40 for update;
	insert into t_user values(7,‘DD’,66); 执行成功
	insert into t_user values(8,‘DD’,44);
	阻塞
commit;
	Query OK, 1 row affected (2.86 sec)

当不存在对应的行记录时，只会对普通索引字段加锁，age范围为(30,50) U 50，也就是(30,50]。

6. 普通索引范围查询

事务A	事务B
begin;
select * from t_user where age > 40 for update;
	insert into t_user values(7,‘DD’,30); 执行成功
	insert into t_user values(8,‘DD’,33);
	阻塞
commit;
	Query OK, 1 row affected (47.57 sec)

锁范围为(30, 50) U 50 U (50,+∞)，合并后就是(30,+∞)。

7. 非索引字段

事务A	事务B
begin;
select * from t_user where uname = ‘BB’ for update;
	insert into t_user values(6,‘DD’,51);
	阻塞
commit;
	Query OK, 1 row affected (4.01 sec)

SERIALIZABLE事务都是串行的，一般也不会使用该隔离级别。REPEATABLE_READ和READ_COMMITTED一样都会使用Next-Key Lock。但是READ_UNCOMMITTED不存在Gap Lock，只会对对应的记录行加锁。

附录：

1. 查询当前数据库中锁情况:

select * from information_schema.INNODB_LOCKS;

在这里插入图片描述

lock_id ：锁Id，事务ID+space id+lock_page+lock_rec
lock_trx_id ：事务ID
lock_mode ：锁模式
lock_type ：锁类型，行锁还是表锁
lock_table ：加锁表
lock_index ：加锁索引
lock_space ：锁对象的space id
lock_page ：事务锁定页的数量，若为表锁则为null
lock_rec ：事务锁定行的数量，若为表锁则为null
lock_data ：事务锁定的主键值，若为表锁则为null

为什么单个事务中加锁查询无结果，当同一个锁存在其他事务等待时，才会出现。

2. 查询当前数据库锁等待情况

select * from information_schema.INNODB_LOCK_WAITS;

在这里插入图片描述

requesting_trx_id ：申请锁资源事务ID
requested_lock_id ：申请锁ID
blocking_trx_id ：当前锁资源事务ID
blocking_lock_id ：当前锁ID

3. 查询当前数据库事务信息

select * from information_schema.INNODB_TRX;

原文链接：https://blog.csdn.net/ljcgit/article/details/120772413