每个数据库几乎都会实现自己的锁机制,锁机制是数据库区别于文件系统的主要标志之一,用于管理对共享资源的并发访问。
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锁(注意也锁住记录行之后的间隙)。
对辅助索引对应的聚合索引加上索引记录锁。
当辅助索引为间隙锁“最小”和“最大”值时,对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。
上面我们将对辅助索引加锁的情况介绍完了,接下来我们测试一下对聚合索引和唯一索引加锁。