数据库锁机制

每个数据库几乎都会实现自己的锁机制，锁机制是数据库区别于文件系统的主要标志之一，用于管理对共享资源的并发访问。

Mysql数据库InnoDB引擎支持行级锁，也就是说我们可以对表中某些行数据执行锁定操作，锁定操作的影响是：如果一个事物对表中某行执行了锁定操作，而另一个事务也需要对同样的行执行锁定操作，这样第二个事务的锁定操作有可能被阻塞，一旦被阻塞第二个事务只能等到第一个事务执行完毕（提交或回滚）或超时。

本文主要介绍InnoDB中的行锁相关概念，重点介绍行锁的锁定范围：
InnoDB引擎使用了七种类型的锁，他们分别是：

共享排他锁（Shared and Exclusive Locks）
意向锁（Intention Locks）
记录锁（Record Locks）
间隙锁（Gap Locks）
Next-Key Locks
插入意图锁（Insert Intention Locks）
自增锁（AUTO-INC Locks）

共享排他锁：

共享锁（S锁）和排他锁（X锁）的概念在许多编程语言中都出现过。先来描述一下这两种锁在MySQL中的影响结果：

如果一个事务对某一行数据加了S锁，另一个事务还可以对相应的行加S锁，但是不能对相应的行加X锁。
如果一个事务对某一行数据加了X锁，另一个事务既不能对相应的行加S锁也不能加X锁。
用一张经典的矩阵表格继续说明共享锁和排他锁的互斥关系：

--	S	X
S	0	1
X	1	1

图中S表示共享锁X表示独占锁，0表示锁兼容1表示锁冲突，兼容不被阻塞，冲突被阻塞。由表可知一旦一个事务加了排他锁，其他个事务加任何锁都需要等待。多个共享锁不会相互阻塞。

 Record Locks、Gap Locks、Next-Key Locks

记录锁（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语句可以对同样的行加间隙锁而不会被阻塞。
对于唯一索引的加锁行为，间隙锁就会失效，此时只有记录锁起作用。

那哪些些语句会加锁？加什么样的锁？接下来我们逐一描述：

select ... from语句：InnoDB引擎采用多版本并发控制（MVCC）的方式实现了非阻塞读，所以对于普通的select读语句，InnoDB并不会加锁
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降级为索引记录锁
delete ... where ...语句：InnoDB会对搜索的所有索引记录加next-key锁，但是如果扫描唯一索引的唯一行，next-key降级为索引记录锁。
insert语句：InnoDB只会在将要插入的那一行上设置一个排他的索引记录锁。
最后补充两点：

如果一个查询使用了辅助索引并且在索引记录加上了排他锁，InnoDB会在相对应的聚合索引记录上加锁。
如果你的SQL语句无法使用索引，这样MySQL必须扫描整个表以处理该语句，导致的结果就是表的每一行都会被锁定，并且阻止其他用户对该表的所有插入。

实验：

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');

 

步骤	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;

结果

替换步骤4中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锁中哪部分是索引记录锁哪部分是间隙锁。

步骤	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的值，观察结果：

name的值	执行结果
d	不阻塞
e	阻塞
f	不阻塞

 

因为间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。

观察执行结果可知，d和f为间隙锁，e为索引记录锁。

结论：通过两条SQL，我们确定了对于辅助索引name在查询条件为 where name='e' 时的加锁范围为（c,e],(e,g),其中：

对SQL语句扫描的索引记录e加索引记录锁[e]。
锁定了e前面的间隙，c到e之间的数据(c,e)加了间隙锁
前两个构成了next-key锁(c,e]。
值得注意的是还锁定了e后面的间隙(e,g)。

接下来我们就对间隙边界值进行测试。

步骤	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;

替换步骤4中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;

替换步骤3中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锁（注意也锁住记录行之后的间隙）。
对辅助索引对应的聚合索引加上索引记录锁。
当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。
上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。