SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。 Mysql数据库InnoDB引擎支持行级锁，也就是说我们可以对表中某些行数据执行锁定操作，锁定操作的影响是：如果一个事物对表中某行执行了锁定操作，而另一个事务也需要对同样的行执行锁定操作，这样第二个事务的锁定操作有可能被阻塞，一旦被阻塞第二个事务只能等到第一个事务执行完毕（提交或回滚）或超时。本文主要介绍InnoDB中的行锁相关概念，重点介绍行锁的锁定范围： 什么样的SQL语句会加锁？ 加什么样的锁？ 加锁语句会锁定哪些行？
背景知识上面我们简单的介绍了InnoDB的行级锁，为了理解后面的验证部分，需要补充一下背景知识。如果对相应知识非常了解，可以直接跳转到验证部分内容。 1. InnoDB锁的类型InnoDB引擎使用了七种类型的锁，他们分别是： 本文主要涉及Shared and Exclusive Locks，Record Locks，Gap Locks，Next-Key Locks这几种锁，其他类型锁如果大家感兴趣可以自己深入了解，在此不在详述。 1.1 Shared and Exclusive Locks共享锁（S锁）和排他锁（X锁）的概念在许多编程语言中都出现过。先来描述一下这两种锁在MySQL中的影响结果： 用一张经典的矩阵表格继续说明共享锁和排他锁的互斥关系： 图中S表示共享锁X表示独占锁，0表示锁兼容1表示锁冲突，兼容不被阻塞，冲突被阻塞。由表可知一旦一个事务加了排他锁，其他个事务加任何锁都需要等待。多个共享锁不会相互阻塞。 1.2 Record Locks、Gap Locks、Next-Key Locks这三种类型的锁都描述了锁定的范围，故放在一起说明。 以下定义摘自MySQL官方文档 记录锁（Record Locks）:记录锁锁定索引中一条记录。 间隙锁（Gap Locks）:间隙锁要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。 Next-Key Locks:Next-Key锁是索引记录上的记录锁和在索引记录之前的间隙锁的组合。
定义中都提到了索引记录（index record）。为什么？行锁和索引有什么关系呢？其实，InnoDB是通过搜索或者扫描表中索引来完成加锁操作，InnoDB会为他遇到的每一个索引数据加上共享锁或排他锁。所以我们可以称行级锁（row-level locks）为索引记录锁（index-record locks），因为行级锁是添加到行对应的索引上的。 三种类型锁的锁定范围不同，且逐渐扩大。我们来举一个例子来简要说明各种锁的锁定范围，假设表t中索引列有3、5、8、9四个数字值，根据官方文档的确定三种锁的锁定范围如下： 记录锁的锁定范围是单独的索引记录，就是3、5、8、9这四行数据。 间隙锁的锁定为行中间隙，用集合表示为(-∞,3)、(3,5)、(5,8)、(8,9)、(9,+∞)。 Next-Key锁是有索引记录锁加上索引记录锁之前的间隙锁组合而成，用集合的方式表示为(-∞,3]、(3,5]、(5,8]、(8,9]、(9,+∞)。
最后对于间隙锁还需要补充三点： 间隙锁阻止其他事务对间隙数据的并发插入，这样可有有效的解决幻读问题(Phantom Problem)。正因为如此，并不是所有事务隔离级别都使用间隙锁，MySQL InnoDB引擎只有在Repeatable Read（默认）隔离级别才使用间隙锁。 间隙锁的作用只是用来阻止其他事务在间隙中插入数据，他不会阻止其他事务拥有同样的的间隙锁。这就意味着，除了insert语句，允许其他SQL语句可以对同样的行加间隙锁而不会被阻塞。 对于唯一索引的加锁行为，间隙锁就会失效，此时只有记录锁起作用。
2. 加锁语句前面我们已经介绍了InnoDB的是在SQL语句的执行过程中通过扫描索引记录的方式来实现加锁行为的。那哪些些语句会加锁？加什么样的锁？接下来我们逐一描述： select ... from语句：InnoDB引擎采用多版本并发控制（MVCC）的方式实现了非阻塞读，所以对于普通的select读语句，InnoDB并不会加锁【注1】。 select ... from lock in share mode语句：这条语句和普通select语句的区别就是后面加了lock in share mode，通过字面意思我们可以猜到这是一条加锁的读语句，并且锁类型为共享锁（读锁）。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描的唯一索引的唯一行，next-key降级为索引记录锁。 select ... from for update语句：和上面的语句一样，这条语句加的是排他锁（写锁）。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。 update ... where ...语句：。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。【注2】 delete ... where ...语句：。InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。 insert语句：InnoDB只会在将要插入的那一行上设置一个排他的索引记录锁。
最后补充两点： 如果一个查询使用了辅助索引并且在索引记录加上了排他锁，InnoDB会在相对应的聚合索引记录上加锁。 如果你的SQL语句无法使用索引，这样MySQL必须扫描整个表以处理该语句，导致的结果就是表的每一行都会被锁定，并且阻止其他用户对该表的所有插入。
SQL语句验证闲言少叙，接下来我们进入本文重点SQL语句验证部分。 1.测试环境数据库：MySQL 5.6.35
事务隔离级别：Repeatable read
数据库访问终端：mysql client 2.验证场景2.1 场景一建表： CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 插入数据： INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i'); 首先我们执行SQL语句的模板： | 步骤 | client 1 | client 2 |
|---|
| 1 | begin; | -- | | 2 | SELECT * FROM user where name='e' for update; | -- | | 3 | -- | begin; | | 4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (10, #{name}); | | 5 | rollback; | -- | | 6 | -- | rollback; |
替换步骤5中name的值，观察结果： | name的值 | 执行结果 |
|---|
| a | 不阻塞 | | b | 不阻塞 | | d | 阻塞 | | e | 阻塞 | | f | 阻塞 | | h | 不阻塞 | | i | 不阻塞 |
观察结果，我们发现SQL语句
SELECT * FROM user where name='e' for update
一共锁住索引name中三行记录，(c,e]区间应该是next-key锁而(e,h)区间是索引记录e后面的间隙。 接下来我们确定next-key锁中哪部分是索引记录锁哪部分是间隙锁。 执行SQL语句的模板： | 步骤 | client 1 | client 2 |
|---|
| 1 | begin; | -- | | 2 | SELECT * FROM user where name='e' for update; | -- | | 3 | -- | SELECT * FROM user where name=#{name} for update; | | 5 | rollback; | -- | | 6 | -- | rollback; |
替换步骤5中name的值，观察结果： 因为间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。 观察执行结果可知，d和f为间隙锁，e为索引记录锁。 结论：通过两条SQL，我们确定了对于辅助索引name在查询条件为 where name='e' 时的加锁范围为（c,e],(e,g),其中： 说的这里细心的读者可能已经发现我们的测试数据中没有间隙的边界数据c和g。接下来我们就对间隙边界值进行测试。 执行SQL语句的模板： | 步骤 | client 1 | client 2 |
|---|
| 1 | begin; | -- | | 2 | SELECT * FROM user where name='e' for update; | -- | | 3 | -- | begin; | | 4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (#{id}, #{name}); | | 5 | rollback; | -- | | 6 | -- | rollback; |
替换步骤5中id，name的值，观察结果： | id的值 | name=c | 执行结果 | id的值 | name=g | 执行结果 |
|---|
| -- | -- | -- | -3 | g | 组塞 | | -- | -- | -- | -2 | g | 阻塞 | | -1 | c | 不阻塞 | -1 | g | 阻塞 | | 1 | c | 不阻塞 | 1 | g | 不阻塞 | | 2 | c | 不阻塞 | 2 | g | 阻塞 | | 3 | c | 不阻塞 | 3 | g | 不阻塞 | | 4 | c | 阻塞 | 4 | g | 阻塞 | | 5 | c | 阻塞 | 5 | g | 阻塞 | | 6 | c | 阻塞 | 6 | g | 阻塞 | | 7 | c | 不阻塞 | 7 | g | 不阻塞 | | 8 | c | 阻塞 | 8 | g | 不阻塞 | | 9 | c | 不阻塞 | 9 | g | 不阻塞 | | 10 | c | 阻塞 | 10 | g | 不阻塞 | | 11 | c | 阻塞 | - | - | - | | 12 | c | 阻塞 | - | - | - |
通过观察以上执行结果，我们发现，name等于c和e时insert语句的结果随着id值得不同一会儿锁定，一会儿不锁定。那一定是id列加了锁才会造成这样的结果。 如果先不看id=5这一行数据的结果，我们发现一个规律： 当name=c时，name=c对应的id=3的id聚合索引数据记录之后的间隙(3,5)，(5,7)，(7,9)，(9,∞)都被加上了锁。 当name=e时，name=e对应的id=7的id聚合索引数据记录之前的间隙(5,7)，(3,5)，(1,3)，(-∞,1)都被加上了锁。 我们可用select * from user where id = x for update;语句判断出以上间隙上加的锁都为间隙锁。
接下来我们解释一下id=5的锁定情况 执行SQL语句的模板： | 步骤 | client 1 | client 2 |
|---|
| 1 | begin; | -- | | 2 | SELECT * FROM user where name='e' for update; | -- | | 3 | -- | SELECT * FROM user where id=#{id} for update; | | 5 | rollback; | -- | | 6 | -- | rollback; |
替换步骤5中id的值，观察结果： | id的值 | 执行结果 |
|---|
| 3 | 不阻塞 | | 4 | 不阻塞 | | 5 | 阻塞 | | 6 | 不阻塞 | | 7 | 不阻塞 |
通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。 最终结论：
对于SELECT * FROM user where name='e' for update;一共有三种锁定行为: 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。 对辅助索引对应的聚合索引加上索引记录锁。 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。
上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。 2.2 场景二建表： CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 注意与场景一表user不同的是name列为唯一索引。 插入数据： INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i'); 首先我们执行SQL语句的模板： | 步骤 | client 1 | client 2 |
|---|
| 1 | begin; | -- | | 2 | SELECT * FROM user where name='e' for update; | | 3 | -- | begin; | | 4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (10, #{name}); | | 5 | rollback; | -- | | 6 | -- | rollback; |
替换步骤5中name的值，观察结果： | name的值 | 执行结果 |
|---|
| a | 不阻塞 | | b | 不阻塞 | | c | 不阻塞 | | d | 不阻塞 | | e | 阻塞 | | f | 不阻塞 | | g | 不阻塞 | | h | 不阻塞 | | i | 不阻塞 |
由测试结果可知，只有name='e'这行数据被锁定。 通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效， 2.3 场景三场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。 借用场景一的表和数据。 建表： CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 插入数据： INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i'); 执行SQL语句的模板： | 步骤 | client 1 | client 2 |
|---|
| 1 | begin; | -- | | 2 | SELECT * FROM user where name>'e' for update; | -- | | 3 | -- | begin; | | 4 | -- | INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name}); | | 5 | rollback; | -- | | 6 | -- | rollback; |
替换步骤5中name的值，观察结果： | name的值 | 执行结果 |
|---|
| a | 阻塞 | | b | 阻塞 | | c | 阻塞 | | d | 阻塞 | | e | 阻塞 | | f | 阻塞 | | g | 阻塞 | | h | 阻塞 | | i | 阻塞 |
这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？ 我们执行以下的SQL语句执行计划： explain select * from user where name>'e' for update; 执行结果： +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|