MySQL间隙锁/Next-key演示

1. 建表建索引：

CREATE TABLE user( 
id int primary key, 
name varchar(20), 
age int) ENGINE=InnoDB;

CREATE INDEX index_age ON user(age);

INSERT INTO user(id,name,age) VALUES (1,'xiaoming',12);
INSERT INTO user(id,name,age) VALUES (3,'xiaohong',20);
INSERT INTO user(id,name,age) VALUES (5,'laowang',36);

SELECT * FROM user;

单条索引上加锁，record lock 永远锁的是索引，而非数据本身，如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

2. 新建两个session，并取消自动提交事务：

SET AUTOCOMMIT=0;

3. 开始演示：

1)普通索引

#【session_1】查询age=20的记录
mysql> SELECT * FROM user WHERE age=20 FOR UPDATE;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | xiaohong |   20 |
+----+----------+------+
1 row in set (0.00 sec)

#【session-2】（update范围age>=36的操作成功，不会阻塞）
UPDATE user SET name='xiaowang' WHERE age=36;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#（insert范围age>=36的操作成功，不会阻塞，同理age<=12也不会阻塞）
INSERT INTO user(id,name,age) VALUES(7,'laohua',40);
Query OK, 1 row affected (0.00 sec)


INSERT INTO user(name,age) VALUES('xiaoli',30);
（insert范围20<age<36的操作，会受到阻塞）

通过上面的例子可以看出Gap 锁的作用是在20,36的间隙之间加上了锁。而且并不是锁住了表，可以看出锁住的范围是（12,20]U[20,36)。

2）主键索引/唯一索引

【session-1】
SELECT * FROM user WHERE id = 3 FOR UPDATE;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | xiaohong |   20 |
+----+----------+------+

【session-2】
INSERT INTO user(id,name,age) VALUES(4,'xiaoli',30);
Query OK, 1 row affected (0.00 sec)

例子说明的其实就是行锁的原因，我只将id=3的行数据锁住了，用Gap锁的原理来解释的话：因为主键索引和唯一索引的值只有一个，所以满足检索条件的只有一行，故并不会出现幻读，所以并不会加上Gap锁。

3）范围查询

【session-1】
SELECT * FROM user WHERE age > 20 FOR UPDATE;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  5 | laowang |   36 |
+----+---------+------+
1 row in set (0.00 sec)

【session-2】
UPDATE user SET name='daming' WHERE age=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

INSERT INTO user(id,name,age) VALUES(8,'xiaoli',12);
Query OK, 1 row affected (0.00 sec)

UPDATE user SET name='dahong' WHERE age=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

INSERT INTO user(id,name,age) VALUES(7,'xiaoli',20);
#（受到阻塞）

UPDATE user SET name='xiaowang' WHERE age=36;
#（受到阻塞）

可以看到，间隙锁给范围查询加锁时，不允许在边界进行insert操作，而只允许给边界进行update操作。

4）检索条件不存在的情况

• 等值查询

【session-1】
DELETE FROM user WHERE age =20;
commit;
SELECT * FROM user;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   12 |
|  5 | laowang  |   36 |
+----+----------+------+
2 rows in set (0.00 sec)

SELECT * FROM user WHERE age = 20 FOR UPDATE;
Empty set (0.00 sec)

【session-2】
INSERT INTO user(id,name,age) VALUES(6,'xiaohong',30);
(阻塞)
INSERT INTO user(id,name,age) VALUES(7,'xiaohong',20);
（阻塞）
INSERT INTO user(id,name,age) VALUES(8,'xiaohong',10);
（执行成功）

会锁住[12,20]U[20,36）的区间。

• 范围查询

【session-1】
SELECT * FROM user WHERE age>40 FOR UPDATE;

【session-2】
INSERT INTO user(id,name,age) VALUES(6,'hello',90);
（阻塞）
INSERT INTO user(id,name,age) VALUES(6,'hello',36);
（阻塞）

INSERT INTO user(id,name,age) VALUES(6,'hello',35);
Query OK, 1 row affected (0.00 sec)

会锁住[36,+∞]。