InnoDB存储引擎支持行级锁,那么,对于行级锁的概念,相信大家都能够理解,就是添加在数据行上的锁, 而对于InnoDB来说,是通过怎样的算法来实现行级锁的呢?
A、Record Lock——只在单个行记录上的锁
B、Gap Lock——间隙锁,锁顶一个范围,但是不包括记录本身
C、Next-Key Lock——Gap Lock + Record Lock,锁定一个范围,同时包括记录本身
Record Lock:它总是去锁住索引记录,如果InnoDB存储引擎表在建立时没有设立任何索引,则该存储引擎会使用隐式主键来进行锁定。
Next-Key Lock 采用Next-Key Locking技术,于索引区间进行锁定,是Gap Lock + Record Lock 的一种锁定算法。
举个例子:假设有一个数据表中有索引:1、3、6、8这四个值,使用Next-Key Locking 锁定的区间可能为:
(-NaN,1]、(1,3]、(3,6]、(6,8]、(8,+NaN)
之所以这样设计,是为了解决Phantom Problem 问题(下一篇介绍)
而当查询的索引具有唯一属性时,InnoDB存储引擎会将Next-Key Lock自动降级为Record Lock,锁住索引记录本身,而不是范围,例子如下:
可见,当索引具有唯一属性时,也就是查询的列是唯一索引时,Next-Key Lock 会降级为Record Lock。
下面我们来说说,Next-Key Lock如果对于辅助索引的查询,会不会依旧降级为Record Lock,还是拿例子来说话,我们建立一个测试表z
CREATE TABLE `z` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB
导入相应的测试数据并执行下面的SQL语句:
select * from z where b=3 for update;
分析一下这句指令的执行:
对于行记录 b = 3 的查询还是按照传统技术——Next-Key Locking 加锁,由表结构可知,字段a和字段b是不同的索引,所以需要进行不同的锁定。
对于聚集索引,仅仅只对列a加Record Lock(X Lock),而对于辅助索引,则是采用Next-Key Lock进行范围锁定(1,3)。InnoDB对于辅助索引的下一个键值加上一个Gap Lock,也就是说,还有一个(3,6)的间隙锁。
所以,当在会话中执行insert锁定范围的数据时,都会被阻塞:
select * from z where a=5 lock in share mode;
insert into z select 4,2;
insert into z select 6,5;
Gap Lock的作用是防止在同一个范围内添加过多的数据,从而造成幻读问题的产生,而在InnoDB存户储引擎中,可以通过以下两种方式来显示的关闭Gap Lock:
A、将事务的隔离级别调整为READ COMMITED
B、将参数innodb_locks_unsafe_for_binlog设置为1
需要注意的一点是,对于唯一键的查询,Next-Key Lock 降级为Record Lock的情况只出现在查询所有的唯一索引列。若该索引由多个列组成,还是会通过Next-Key Lock进行锁定。