关于mysql:MySQL间隙锁nextkey锁

间隙锁

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最初一个索引记录之后的间隙的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其余事务将 的值插入 15 到列中 t.c1，无论列 中是否曾经存在任何此类值，因为该范畴内所有现有值之间的间隙被锁定。

间隙锁的目标

是为了避免幻读，其次要通过两个方面实现这个目标：
（1）避免间隙内有新数据被插入
（2）避免已存在的数据，更新成间隙内的数据

间隙是怎么划分的

这个表依据 age 列（间隙锁作用在索引上，必须要有索引），间隙能够划分为（-∞，15），（15，18），（18，22），（22，28），（28，+∞）

间隙锁锁定的区域

依据检索条件向左寻找最靠近检索条件的记录值 A，作为左区间，向右寻找最靠近检索条件的记录值 B 作为右区间，即锁定的间隙为（A，B）。

间隙锁作用范畴

1、间隙锁只能作用在 RR 隔离级别

2、能作用在索引上

补充：

1、应用惟一索引锁定行以搜寻惟一行的语句不须要间隙锁定。（这不包含搜寻条件仅蕴含多列惟一索引的某些列的状况；在这种状况下，的确会产生间隙锁定。）

2、不同的事务能够在间隙上持有抵触的锁。例如，事务 A 能够在间隙上持有共享间隙锁（间隙 S 锁），而事务 B 在同一间隙上持有排他间隙锁（间隙 X 锁）。容许抵触间隙锁的起因是，如果从索引中革除记录，则必须合并不同事务在记录上持有的间隙锁。

3、能够明确禁用间隙锁定。将事务隔离级别更改为 READ COMMITTED 即可

筹备数据：

创立表：

CREATE TABLE `user` (`id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8_general_ci',
    `age` INT(10) NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `age` (`age`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

表数据：

一般索引的间隙锁

在一般索引列上，不论是何种查问，只有加锁，都会产生间隙锁，这跟惟一索引不一样；
在一般索引跟惟一索引中，数据间隙的剖析，数据行是优先依据一般索引排序，再依据惟一索引排序。

案例 1（检索单个值）

开启两个会话，设置会话隔离级别为 RR，设置主动提交为 0，开启事物测试：

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age=22 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(3, 'gap lock', 18) #胜利
INSERT INTO user VALUE(6, 'gap lock', 18) #阻塞
INSERT INTO user VALUE(3, 'gap lock', 20) #阻塞
INSERT INTO user VALUE(10, 'gap lock', 22) #阻塞
INSERT INTO user VALUE(14, 'gap lock', 28) #阻塞
INSERT INTO user VALUE(21, 'gap lock', 28) #胜利

会话 1 执行 sql: age=18 会锁定间隙 [15，18）（18，22），会话 2 中 age=[15，22）之间的插入都会失败。2、同样插入 age=22，id=10 的时候能够胜利，id=14 的时候就会失败，这阐明当索引程序雷同时，会依据主键来排序。对 age=18 同理

锁定区域示意图：

案例 2（检索不存在的值）

仍基于原始表数据测试验证：

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age=30 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(13, 'gap lock', 28) #阻塞
INSERT INTO user VALUE(14, 'gap lock', 100) #阻塞
INSERT INTO user VALUE(15, 'gap lock', 27) #胜利

* 会话 1 执行 sql: 条件 age=30
会锁定间隙 [28，正无穷大），会话 2 中 age>=28 的插入都会失败 *

案例 3（检索范畴）

仍基于原始表数据测试验证：

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age>=18 AND age<23 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(11, 'select range', 14) #胜利
INSERT INTO user VALUE(11, 'select range', 15) #阻塞
INSERT INTO user VALUE(12, 'select range', 17) #阻塞
INSERT INTO user VALUE(13, 'select range', 18) #阻塞
INSERT INTO user VALUE(14, 'select range', 20) #阻塞
INSERT INTO user VALUE(17, 'select range', 23) #阻塞
INSERT INTO user VALUE(18, 'select range', 24) #阻塞
INSERT INTO user VALUE(19, 'select range', 28) #胜利

* 会话 1 执行 sql: 条件 age>=18 AND age<23
会锁定 age=[15，28）之间的间隙区间，会话 2 中 age>=15 and age<28 的插入都会失败。因为 23 是 22-28 之间不存在的记录，所以这个间隙区间也被锁定了 *

next-key 锁

next-key 锁是记录锁和间隙锁的组合，mysql 默认应用这个锁。
下面的案例一 session 1 中的 sql 是：SELECT * FROM USER WHERE age=18 FOR UPDATE; next-key 锁锁定的范畴为间隙锁 + 记录锁，首先在（15，18）（18，22）加间隙锁，同时 age=18 的记录加记录锁。