Innodb间隙锁实战

锁概念

InnoDB存储引擎包含了三种行锁的算法，分别如下所示：

• Record Lock：行锁，针对的是单行记录；
• Gap Lock：间隙锁，锁定的是一个范围，但是不包含记录本身；
• Next-Key Lock：其实就是行锁+间隙锁，包含了记录本身和范围；

为什么需要间隙锁

数据库一般都有四种隔离级别，其中最常用的就是：已提交读(Read committed)和可重复读(Repeatable read)；在已提交读隔离级别下会出现不可重复读的现象，而在可重复读隔离级别下会出现幻读(Phantom Read)的现象；

幻读：同一事务下，连续执行两次同样的SQL可能会导致不同的结果；

Innodb引擎在可重复读隔离级别下并不会出现幻读的现象，这主要是因为Innodb提供了多版本并发控制MVCC和间隙锁；常见的快照读其实就是使用的MVCC，而当前读就使用了间隙锁；

以下实例有两点说明：

• Innodb的可重复读隔离级别下，对当前读使用了间隙锁来解决幻读的问题，所以下面的实例都是基于默认隔离级别RR；
• Innodb的锁机制都依赖索引，所以下面的实例围绕索引来展开；

实战

无索引的情况

首先创建一个无索引的表，并初始化数据：

mysql> create table t1(a int);
mysql> insert into t1 values(1),(3),(5);

启动事务1，执行当前读：

mysql> begin;
mysql> select * from t1 where a=3 for update;

以上事务没有提交，再启动事务2，以下语句都被阻塞：

mysql> select * from t1 where a=3 for update;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(5);
mysql> insert into t1 values(7);

但是这时候去执行快照读还是可以的：

mysql> select * from t1 where a=3;

可以发现在没有索引的情况下，除了快照读什么都干不了，感觉像是表被锁住了，表锁分为读和写锁，在写锁的情况下快照读同样被锁住，而在读锁的情况下可以使用快照读，类似上面无索引的情况；

mysql> lock table t1 read;  ## 读锁
mysql> lock table t1 write;  ## 写锁
mysql> unlock tables;  ## 解锁

那是不是无索引的情况下就使用了表锁那，可以通过如下命令进行查看，首先看一下在表锁的情况下执行插入操作：

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+
| Id  | User | Host            | db   | Command | Time | State                        | Info                     |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+
|  75 | ODBC | localhost:65316 | test | Query   |   98 | Waiting for table level lock | insert into t1 values(7) |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+

可以发现状态一栏显示Waiting for table level lock，表示当前插入语句在等待表锁；再看一下无索引的情况：

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+--------+--------------------------+
| Id  | User | Host            | db   | Command | Time | State  | Info                     |
+-----+------+-----------------+------+---------+------+--------+--------------------------+
|  75 | ODBC | localhost:65316 | test | Query   |    6 | update | insert into t1 values(7) |
+-----+------+-----------------+------+---------+------+--------+--------------------------+

总结：以上状态并不是在等待表锁，其实InnoDB在没有任何索引的情况下，会使用隐式的主键来进行锁定。

普通索引的情况

首先创建一个普通索引的表，并初始化数据：

mysql> create table t2 (a int,key(a));
mysql> insert into t2 values(1),(3),(5);

启动事务1，执行当前读：

mysql> begin;
mysql> select * from t2 where a=3 for update;

启动事务2，以下语句都被阻塞：

mysql> select * from t2 where a=3 for update;
mysql> insert into t2 values(1);
mysql> insert into t2 values(2);
mysql> insert into t2 values(4);

以下语句不会被阻塞：

mysql> insert into t2 values(5);
mysql> insert into t2 values(7);

mysql> select * from t2 where a=1 for update;
mysql> select * from t2 where a=5 for update;

总结：可以发现在使用普通索引的情况下，锁定包含了行锁和间隙锁，行锁就是a=3这行，间隙锁包含了(1,3)和(3,5)，注意这里两边都是开区间，所以当前读a=1和a=5都可以成功；但是在插入数据的时候可以发现a=5可以插入，但是a=1不能插入，这是因为对Insert操作会检查插入的下一条记录是否被锁定，如果锁定则无法执行，否则可以执行。

唯一索引的情况

首先创建一个唯一索引的表，并初始化数据：

mysql> create table t3 (a int,unique key(a));
mysql> insert into t3 values (1),(3),(5);

启动事务1，执行当前读：

mysql> begin;
mysql> select * from t3 where a=3 for update;

启动事务2，以下语句都被阻塞：

mysql> select * from t3 where a=3 for update;

以下语句不会被阻塞：

mysql> select * from t3 where a=1 for update;
mysql> insert into t3 values(2);
mysql> insert into t3 values(4);
mysql> insert into t3 values(7);

总结：可以发现当是唯一索引的时候，这时候会降级为行锁，只会锁定当前记录，其他插入SQL都不会被阻塞；同样主键索引效果也是一样的。

无索引+普通索引的情况

首先创建一个拥有无索引和普通索引的表，并初始化数据：

mysql> create table t4 (a int,b int,key(a));
mysql> insert into t4 values(1,1),(5,5),(9,9);

因为存在多个字段的情况，所以这时候的加锁情况就和查询的条件有关了：

条件为a字段

启动事务1，执行当前读：

mysql> begin;
mysql>  select * from t4 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

同时查出了a=5和b=5这条记录，所以会根据无索引和普通索引的情况加锁；启动事务2，以下语句都被阻塞：

mysql> select * from t4 where a=5 for update;
mysql> insert into t4 values(1,5);
mysql> insert into t4 values(2,5);
mysql> insert into t4 values(8,5);

mysql> select * from t4 where b=1 for update;
mysql> select * from t4 where b=2 for update;
mysql> select * from t4 where b=9 for update;
mysql> select * from t4 where b=11 for update;

以下语句不会被阻塞：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

这里a上有普通索引，所以效果和直接使用普通索引类似；但是同时查出来的结果还有b，因为b上是无索引的，所以关于b的一系列操作都被阻塞了；

条件为b字段

启动事务1，执行当前读：

mysql> begin;
mysql> select * from t4 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务2，以下语句都被阻塞：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

mysql> select * from t4 where a=1 for update;
mysql> select * from t4 where a=9 for update;

可以发现条件改变后，原来a=5条件下可以插入的数据，这b=5条件下不能插入；并且a条件下的两个开区间1和9也无法查询了；应该是使用了隐式的主键来进行锁定，整个表被锁住了；

条件为a+b字段

启动事务1，执行当前读：

mysql> begin;
mysql> select * from t4 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务2，阻塞的SQL同条件为a的情况，Mysql执行计划会优先使用有索引的字段；以下SQL都可以执行：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

通过上面的实例，我们大概可以下面两件事：

• 具体使用哪些锁和我们使用的查询有很大关系；
• 查出来记录中的多个字段都会启动锁机制，就算查询条件里面没有这个字段；

唯一索引+普通索引的情况

首先创建一个拥有唯一索引和普通索引的表，并初始化数据：

mysql> create table t5 (a int,b int,unique key(a),key(b));
mysql> insert into t5 values(1,1),(5,5),(9,9);

条件为a字段

mysql> begin;
mysql> select * from t5 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

这种情况基本和使用唯一索引一样，以下SQL都可以执行：

mysql> insert into t5 values(2,2);
mysql> insert into t5 values(10,2);
mysql> select * from t5 where a=1 for update;

阻塞的SQL主要包含如下：

mysql> select * from t5 where a=5 for update;
mysql> select * from t5 where b=5 for update;

条件为b字段

mysql> begin;
mysql>  select * from t5 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

这种情况基本和使用普通索引一样，以下SQL会阻塞：

mysql> insert into t5 values(2,3);
mysql> insert into t5 values(2,7);
mysql> insert into t5 values(2,1);

以下插入SQL不会阻塞：

mysql> insert into t5 values(2,9);

条件为a+b字段

mysql> begin;
mysql> select * from t5 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

此种情况基本和使用条件a一样，优先使用唯一索引，可以查看执行计划：

mysql> explain select * from t5 where b=5 and a=5 for update;
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t5    | const | a,b           | a    | 5       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+

以上实例我们发现在多索引的情况下，要看执行计划具体使用的什么索引，才会使用什么类型的锁；

组合索引的情况

首先创建拥有组合索引的表，并初始化数据：

mysql> create table t6 (a int,b int,key(a,b));
mysql> insert into t6 values(1,1),(5,5),(9,9);

条件为a字段

首先开启事务一，执行如下SQL：

mysql> begin;
mysql> select * from t6 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务2，以下语句都被阻塞：

mysql> insert into t6 values(1,2);
mysql> insert into t6 values(2,2);
mysql> insert into t6 values(7,2);
mysql> insert into t6 values(9,2);

除了(9,2)这条记录其他和使用普通索引没有差别，这里(9,2)被阻塞的原因是因为组合索引的存在，导致(9,9)记录在(9,2)记录的后面，索引会被锁住，同理如果是(9,10)是不会被锁住的；

mysql> insert into t6 values(9,10);
mysql> insert into t6 values(10,2);

条件为b字段

首先开启事务一，执行如下SQL：

mysql> begin;
mysql> select * from t6 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务2，以下语句都被阻塞：

mysql> insert into t6 values(9,2);
mysql> insert into t6 values(9,10);
mysql> insert into t6 values(10,2);

这个其实也好理解，因为使用的是组合索引，会按照最左匹配原则，这时候条件b=5其实是无法使用索引的，所以这时候和无索引的情况是一样的，以上语句都会被锁住；

条件为a+b字段

首先开启事务一，执行如下SQL：

mysql> begin;
mysql> select * from t6 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务2，阻塞和不阻塞的情况基本一致，执行计划会走组合索引；

总结

经过以上的实例测试大致做了以下几点总结：

• 无索引的情况会使用隐式的主键来进行锁定，效果就是整个表被锁住了；
• 普通索引的情况下会使用间隙锁，对当前值前后添加开区间的间隙锁，并且对当前值添加行锁；对Insert操作会检查插入的下一条记录是否被锁定；
• 唯一索引的情况，直接使用行锁，只对当前行加锁；
• 具体使用哪些锁和我们使用的查询有很大关系；
• 查出来记录中的多个字段都会启动锁机制，就算查询条件里面没有这个字段；
• 对于查询条件有多个的情况，会根据执行计划选择索引，然后选择对应的锁机制。