MySQL加锁范围分析

场景：

最近，遇到了一个关于mysql 加锁的问题，将当时的情形简化如下，有一个index_test表，表结构如下所示：

mysql> CREATE TABLE `index_test` (

  `priv_id` int(11) NOT NULL DEFAULT '0',

  `index_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`priv_id`),

  KEY `index_id` (`index_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

priv_id为主键，index_id上建有非唯一索引。

初始情况表中有如下记录:

mysql> select * from index_test;

+---------+----------+

| priv_id | index_id |

+---------+----------+

|       1 |        1 |

|       3 |        3 |

|       5 |        5 |

|       7 |        6 |

|       9 |        9 |

+---------+----------+

问题(1)：

开启两个client，首先，两个client分别按时间顺序执行如下命令：

结果client2中（4,4）插不进去，事务被阻塞，为何我只锁住了index_id=5的记录，插入index_id=4的数据也插不进去了，难道是client1锁全表了么？

寻找答案：

带着这样的疑问，先查阅了mysql官方文档关于MySQL锁的章节，InnoDB本身支持3种锁：

• Record Locks：锁住表中的某一条记录
• Gap Locks：锁住某个范围
• Next-key Locks： 相当于Record Locks和Gap Locks的组合

那么这3种锁在哪些场合下使用呢？

这里不得不提令一个概念：隔离级别

事务在并发执行的过程中会导致的几个问题如下：

• 脏读(Drity Read)：当一个事务允许读取另外一个事务修改但未提交的数据时，就可能发生脏读(dirty reads)。
• 不可重复读(Non-repeatable read):当一行数据获取两遍得到不同的结果表示发生了“不可重复读(non-repeatable read)
• 幻读(Phantom Read):当两个完全相同的查询语句执行得到不同的结果集。

为了解决上面几个问题，ANSI/ISO SQL定义的标准隔离级别如下：

1. 可序列化(Serializable)：最高的隔离级别。
2. 可重复读(Repeatable reads)：会产生幻读
3. 授权读(Read committed)：会出现“不可重复读”
4. 未授权读(Read uncommitted)：会产生脏读

不同的DBMS默认隔离级别也不同，一般的DBMS系统，默认都会使用读提交（Read-Comitted，RC）作为默认隔离级别，如Oracle、SQL Server等，而InnoDB默认隔离级别为可重复读（Repeatable reads），但是InnoDB的隔离级别与标准的的隔离级别有少许不同，其在RR隔离级别下通过next-key locks防止幻读的产生。

mysql官方文档上介绍next-key locks一节有这么一段话：

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows。

即默认情况下，InnoDB默认隔离级别为RR，并且不开启binlog，这种情况下会加next-key locks，看了自己机器上的InnoDB隔离级别以及innodb_locks_unsafe_for_binlog果真是默认配置，说明上述问题1中的client1加的就是next-key locks而不是record locks。

那么next-key locks的范围如何确定呢？

让我们继续看官方文档：

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where ( or ) denote exclusion of the interval endpoint and or denote inclusion of the endpoint:(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)

按照这种方法，我们上例中，对于index_id上的索引，可能的next-key locks为

• (negative infinity, 1]
• (1, 3]
• (3, 5]
• (5, 6]
• (6, 9]
• (9, positive infinity)

因为问题1中，client1 上的锁为index_id=5 for update，所以在此，加的是next-key锁，并且锁的范围是(3,5],因为client2插入的数据(priv_id=4,index_id=4)其在(3,5]范围之内，因此插不进去，问题解决。

更进一步，问题(2)：

解决了上述index_id=5时，（4，4）记录插不进去的问题之后，为了验证官方文档上所说的锁范围，我进一步做了如下实验：

mysql> select * from index_test;

+---------+----------+

| priv_id | index_id |

+---------+----------+

|       1 |        1 |

|       3 |        3 |

|       5 |        5 |

|       7 |        6 |

|       9 |        9 |

+---------+----------+

结果出现client2插不进去，事务阻塞的情况。为何我只锁住了index_id=6的记录，插入index_id=5的数据也插不进去了，即使是client1加了next-key锁，根据InnoDB关于next-key locks这一节所述，这里next-key locks的范围也应该是（5,6]才对,即index_id=6的记录插不进去，但是index_id=5的记录应该可以差的进去，为何（priv_id=6，index_id=5)这条记录没有插进去呢？难道官方文档把next-key locks的锁范围说错了么？这不科学啊。

然后在网上搜索相关的资料，看看别人有没有遇到过这样的问题，在一篇关于MySQL加锁处理分析的blog中得到了启示，按照blog中组合七：id非唯一索引+RR的理论，gap锁的范围不仅跟被锁定的键有关，还跟主键有关，同时在MySQL官方文档中也得到了验证

Each secondary index record also contains all the primary key fields defined for the clustered index key that are not in the secondary index.

即每一条辅助索引记录同样还包含主键。

按照该blog中的理论：上述client1中gap锁的范围应该如下图所示：

即当执行：

mysql> select * from index_test where index_id=6 for update;，

这条语句时，所有从(priv_id=5,index_id=5)到(priv_id=9,index_id=9)的区间都会被锁住，这也就解释了为什么把(priv_id=6,index_id=5)这条记录却插不进去的原因，因为本身（6,5）就在上述的gap锁区间范围之内。

显然(priv_id=4,index_id=5)是不在上述锁区间的，那么(priv_id=4,index_id=5)理论上来分析是可以插入进去的

验证：执行如下sql语句

结果：client2中(priv_id=4,index_id=5)记录顺利插入成功。

但是：为何gap锁的范围会是上述描述的那样呢，gap锁的范围如何确定呢？其实确定gap锁范围，我们只要把握一点：就是让后续不能插入满足条件的新纪录，然后按照这个点，去考虑哪些地方需要加gap锁。仍然拿下面这个例子来说：

mysql> select * from index_test;

+---------+----------+

| priv_id | index_id |

+---------+----------+

|       1 |        1 |

|       3 |        3 |

|       5 |        5 |

|       7 |        6 |

|       9 |        9 |

+---------+----------+

mysql> begin;

mysql> select * from index_test where index_id=6 for update;，

加了gap锁之后就是不能够再让其他index_id=6的记录被插入，因为InnoDB索引结构都是B+树，索引记录都是按顺序排序的，想要再插入一条index_id=6的记录，其必定只能是在（priv_id>5,index_id=5）~（priv_id<9，index_id=9）记录之间插入，由于为了防止幻读，为了不让满足index_id=6的记录再插入进去，因此那段区间就被加了锁,而不是别的区间。

##最后：

开启binlog（需要重启mysql），或者设置mysql隔离级别为RC：

mysql> set session transaction isolation level read committed;

再执行问题（2）中的例子：

mysql> select * from index_test;

+---------+----------+

| priv_id | index_id |

+---------+----------+

|       1 |        1 |

|       3 |        3 |

|       5 |        5 |

|       7 |        6 |

|       9 |        9 |

+---------+----------+

这一次，client2中的（6,5）顺利插入，这是因为client1仅仅对（7,6）这一条记录加了record lock，而不会对旁边区间加gap锁，这种情况下是会发生幻读现象的。

因此，在我们使用mysql加锁过程中，也首先需要搞清楚，我们的隔离级别是什么，是否开启了binlog等等，然后才能正确分析加锁的范围。

参考文献：

• http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html 幻读的概念
• http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html InnoDB的3种锁
• http://hedengcheng.com/?p=771 大神描述Mysql 加锁分析的blog
• http://hedengcheng.com/?p=577 SQL中的where条件，在数据库中提取与应用浅析
• http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html 关于InnoDB记录的物理结构
• http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog