[大数据] MySQL 锁机制系列（一） —

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL 锁机制系列（一） —— Next-Key Lock -> 正文阅读

[大数据]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)