数据库使用锁是为了支持更好的并发,提供数据的完整性和一致性。InnoDB
是一个支持行锁的存储引擎,锁的类型有:
共享锁(S)
排他锁(X)
意向共享(IS)
意向排他(IX)
为了提供更好的并发,InnoDB
提供了非锁定读:不需要等待访问行上的锁释放,读取行的一个快照。该方法是通过InnoDB
的一个特性:MVCC
来实现的。
InnoDB有三种行锁的算法:
1、Record Lock
:单个行记录上的锁。
2、Gap Lock
:间隙锁,锁定一个范围,但不包括记录本身。GAP
锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。
3、Next-Key Lock
:1+2,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。
为什么section B上面的插入语句会出现锁等待的情况 ?InnoDB
是行锁,在section A
里面锁住了a=8
的行,其他应该不受影响。why?
因为InnoDB
对于行的查询都是采用了Next-Key Lock
的算法,锁定的不是单个值,而是一个范围(GAP)
。上面索引值有1,3,5,8,11
,其记录的GAP
的区间如下:是一个左开右闭的空间(原因是默认主键的有序自增的特性,结合后面的例子说明)
1 | (-∞,1],(1,3],(3,5],(5,8],(8,11],(11,+∞) |
特别需要注意的是,InnoDB
存储引擎还会对辅助索引下一个键值加上gap lock
。如上面分析,那就可以解释了。
1 | mysql> select * from t where a = 8 for update; |
该SQL
语句锁定的范围是(5,8]
,下个下个键值范围是(8,11]
,所以插入5~11
之间的值的时候都会被锁定,要求等待。即:插入5,6,7,8,9,10
会被锁住。插入非这个范围内的值都正常。
如果是范围的排它,那是非常的影响性能的,是否可以只让它锁一行呢?
分析:因为会话1已经对id=8
的记录加了一个X锁,由于是RR
隔离级别,INNODB
要防止幻读需要加GAP
锁:即id=5
(8的左边),id=11
(8的右边)之间需要加间隙锁(GAP
)。这样[5,e]
和[8,g]
,[8,g]
和[11,j]
之间的数据都要被锁。上面测试已经验证了这一点,根据索引的有序性,数据按照主键(name
)排序,后面写入的[5,cz]
([5,e]
的左边)和[11,ja]
([11,j]
的右边)不属于上面的范围从而可以写入。
超时时间的参数:innodb_lock_wait_timeout
,默认是50秒。
超时是否回滚参数:innodb_rollback_on_timeout
默认是OFF。
经过测试,不会回滚超时引发的异常,当参数innodb_rollback_on_timeout
设置成ON
时,则可以回滚,会把插进去的12回滚掉。
那Record Lock
什么时候用?还是用上面的列子,把辅助索引改成唯一属性的索引。
为什么section B
上面的插入语句可以正常,和测试一不一样?
分析:
因为InnoDB
对于行的查询都是采用了Next-Key Lock
的算法,锁定的不是单个值,而是一个范围,按照这个方法是会和第一次测试结果一样。但是,当查询的索引含有唯一属性的时候,Next-Key Lock
会进行优化,将其降级为Record Lock
,即仅锁住索引本身,不是范围。
注意:通过主键或则唯一索引来锁定不存在的值,也会产生GAP
锁定。
参考地址
如果大家喜欢我的文章,可以关注个人订阅号。欢迎随时留言、交流。如果想加入微信群的话一起讨论的话,请加管理员简栈文化-小助手(lastpass4u),他会拉你们进群。