• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB行锁类型测试(二)

MySQL 彭东稳 8年前 (2016-06-17) 26300次浏览 已收录 0个评论

一、InnoDB锁算法

InnoDB支持到行级别粒度的并发控制,下面我们分析下几种常见的行级锁类型,以及在哪些情况下会使用到这些类型的锁。

1)Record Lock(源码定义名称:LOCK_REC_NOT_GAP)

锁带上这个Flag时,表示这个锁对象只是单纯的锁在记录上,不会锁记录之前的GAP。在RC隔离级别下一般加的都是该类型的记录锁(但唯一二级索引上的duplicate key检查除外,总是加LOCK_ORDINARY类型的锁)。简单说就是单个行记录上加锁,防止事务间修改或删除数据。

2)Gap Lock(源码定义名称:LOCK_GAP)

间隙锁,表示只锁住一段范围,不锁记录本身,通常表示两个索引记录之间,或者索引上的第一条记录之前,或者最后一条记录之后的锁。可以理解为一种间隙锁,一般在RR隔离级别下会使用到GAP锁。你可以通过切换到RC隔离级别,或者开启选项innodb_locks_unsafe_for_binlog来避免GAP锁。这时候只有在检查外键约束或者duplicate key检查时才会使用到GAP LOCK。

3)Next-Key Lock(源码定义名称:LOCK_ORDINARY)

Gap Lock + Record Lock锁定一个范围及锁定记录本身。当前MySQL默认情况下使用RR的隔离级别,而Next-key LOCK正是为了解决RR隔离级别下的不可重复读问题和幻读问题,所谓不可重复读就是一个事务内执行相同的查询,会看到不同的行记录。在RR隔离级别下这是不允许的。

假设索引上有记录1,4,5,8,12,我们执行类似语句SELECT … WHERE col > 10 FOR UPDATE。如果我们不在(8, 12)之间加上Next-key LOCK,另外一个会话就可能向其中插入一条记录9,再执行一次相同的SELECT ... FOR UPDATE,就会看到新插入的记录。这也是为什么MySQL插入一条记录时,需要判断下一条记录上是否加锁了,如果加锁就需要等待。

Record Lock总是会去锁住索引记录,如果InnoDB存储引擎表建立的时候没有设置任何一个索引,这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种算法,在Next-Key Lock算法下,InnoDB对于行的查询都是采用这种锁定算法,对于不同SQL查询语句,可能设置共享的Next-key Lock和排他的Next-Key Lock。InnoDB对于行的查询都是采用这种锁定算法的,例如一个索引有10, 11, 13, 20这四个值,那么该索引可能被Next-key Locking的区间为(负无穷, 10], (10, 11], (11, 13], (12, 20], (20, 正无穷)。需要理解一点,InnoDB中加锁都是给所有记录一条一条加锁,并没有一个直接的范围可以直接锁住,所以会生成多个区间。

InnoDB在RR隔离级别下采用Next-key Lock(在RC隔离级别,对于外键及唯一性检查也会采用Next-key Lock),采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决“不可重复读”问题(RR隔离级别就是解决不可重复读问题)。然而,当查询条件为等值时,且索引有唯一属性时(就是只锁定一条记录),InnoDB存储引擎会对Next-Key Lock进行优化,将其降级为Record Lock,即仅锁住索引本身,而不是一个范围,因为此时不会产生重复读问题。

下面针对这几种锁算法,分别测试一下RR隔离级别+主键、RR隔离级别+普通索引、RC隔离级别+主键、RC隔离级别+普通索引的加锁方式。

RR隔离级别+主键

看下面例子,首先根据如下代码创建测试表t,然后开启两个事务进行操作。

创建完上面的环境之后,下面分别开启两个会话用来运行事务1和事务2。

# 事务1

锁信息:

# 事务2

t共有125、7、9五个值,在上面的例子中,在事务1中首先对“a=5”进行X锁定,而由于a是主键且唯一,因此锁定的仅是5这个值,而不是(2,5)这个范围。从事务1的锁信息也是可以看出的,这样在事务2中插入值4而不会阻塞,可以立即插入并返回。即锁定由Next-key Lock算法降级为Record Lock,从而提高应用的并发性。

需要注意的一点是,对于唯一键值的锁定,Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成,而查询仅是查找多个唯一索引列中的其中一个,那么查询其实是range类型查询,而不是const类型查询,故InnoDB存储引擎依然使用Next-Key Lock进行锁定。

但如果对主键进来范围查询时,锁的范围是怎么样的呢?

# 事务1

锁信息:

# 事务2

t共有125、7、9五个值,在上面的例子中,在事务1中执行“a<=5”范围查询时,根据锁信息可以看出事务1加锁范围是(负无穷, 1]、(1, 2]、(2, 5]、(5, 7],且1、2、5、7这四条记录也都加锁了,也就是说再插入>=7的记录都是会产生锁等待,插入记录8是成功的。

那么InnoDB如何判断是否允许插入数据呢?对于普通索引,insert的加锁策略是:查找小于等于 insert_rec 的第一条记录,然后查看第一条记录的下一个记录是否有GAP锁,有则等待,没有则插入。

比如说我们插入6这条记录,首先定位>=6的记录,也就是5,然后确认5的下一条记录是否锁住了GAP?这里也就是7,当7这条记录有锁时代表锁住的是(5, 7)这个范围,就不允许插入(会申请一把插入意向锁),保证了可重复读。证明了在RR隔离级别下使用了Next-key Lock来保证其“可重复读”的特性。如果没有锁就直接插入即可。

但是如果插入的记录有唯一约束时,只判断下一条记录是否锁住了GAP就不行了,显然会插入重复数据破坏唯一性。这时还会把插入的记录与前一条数据进行比较,如果相同则给插入记录的前一条记录加S Lock(lock in share mode),加锁成功则返回duplicate key,否则等待S Lock。

但这个地方有一个疑问,为什么MySQL在加锁时,不直接加5这条记录本身以及<5的记录呢?为什么还要给(5, 7)加锁呢?因为感觉(5, 7)加不加锁并不会影响RR级别可重复读的特性啊。其实这就跟B+树有关系了,首先MySQL定位到1这条记录并加锁,然后顺着1往后读取数据并加锁,直到读取到第一条不匹配数据才能确定是否停止继续读取数据,而在RR隔离级别下只要被读到的数据都需要进行加锁。如果你查询条件是<5,那么加锁只会加到5这条记录为止。

但如果是在RC隔离级别下,只会对符合条件的记录进行加记录锁,不会对满足条件的下一条记录进行加锁。

RR隔离级别+普通索引

正如上面所介绍的,Next-Key Lock降级为Record Lock仅在查询的列是唯一索引且条件为等值查询的情况下。若是辅助索引,则情况会完全不同,同样,首先根据如下代码创建测试表z

z的列b是辅助索引,若在事务1中执行下面的SQL语句:

锁结构:

很明显,这时SQL语句通过索引列b进行查询,因此其使用传统的Next-key Locking技术加锁,并且由于有两个索引,其需要分别进行锁定。对于聚集索引,其仅对列id等于5的索引加上Record Lock。那么,为什么主键索引上的记录也要加锁呢?因为有可能其他事务会根据主键对 z 表进行更新,试想一下,如果主键索引没有加锁,那么显然会存在并发问题。

而对于辅助索引,其加上的是Next-key Lock,锁定的范围是(1, 3),特别需要注意的是,InnoDB存储引擎还会对辅助索引下一个键值加上Gap lock,即还有一个辅助索引范围为(3, 6)的锁。因此,若在新的事务2中运行下面的SQL语句,都会被阻塞。

可以看出以下结论:

第一个SQL语句,出现锁超时是因为在事务1中执行的SQL语句已经对聚集索引中的列“a=5”的值加上X锁,因此执行会被阻塞。

第二个SQL语句,主键插入2,没有问题,插入的辅助索引值1也不在锁定的范围(1,3)中,因此可以执行成功。

第三个SQL语句,主键插入4,没有问题,插入的辅助索引值2在锁定的范围(1,3)中,因此执行会被阻塞。

第四个SQL语句,插入的主键6没有被锁定,6也不在范围(1,3)之间。但插入的值6在另一个锁定的范围(3,6)中,故同样需要等待。

在RR隔离级别下,对于INSERT的操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许操作,从而避免不可重复读问题。而下面的SQL语句,不会被阻塞,可以立即执行。

从上面的例子中可以看到,Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内,解决“不可重复读”问题的产生。例如在上面的例子中,事务1中用户已经锁定了b=3的记录,若此时没有Gap Lock锁定(3, 6),那么用户可以插入索引b列为3的记录,这会导致事务1中的用户再次执行同样的查询时会返回不同的记录,即产生不可重复读问题。

这里有一个问题值得思考一下,为什么插入(6, 6)不允许,而插入(8, 6)是允许的。这跟InnoDB索引结构有关系,我们知道二级索引是指向主键,所以结构如:(1, 1), (1, 3), (3, 5), (6, 7), (8, 10)。真正的Gap锁锁住的也是((1, 1), (1, 3))这样的结构,所以当我们插入(6, 6)时,需要插入到(3, 5), (6, 7)之间,这区间被锁,所以无法插入;而我们插入(8, 6)是需要插入到(6, 7), (8, 10)之间,没有锁存在,所以可以插入成功。

另外,在RR隔离级别下,我们访问条件为二级索引的情况下,就算访问一条不存在的记录同样需要加Next-key Lokcs,比如我们查询

锁结构如下:

从锁结构可以看到对于(6, 8)这个区间加了Gap Lock,也就是说插入这个区间的数据都会被阻塞。

虽然在RR隔离级别默认使用Gap Lock,但用户可以通过以下两种方式来显式地关闭Gap Lock:

1)将事务的隔离级别设置为READ COMMITTED;

2)将参数innodb_locks_unsafe_for_binlog设置为1;

当设置了上述参数或隔离级别调整到READ COMMITTED时,除了外键约束和唯一性检查(duplicate key)依然需要Gap Lock,其余情况仅使用Record Lock进行锁定。但需要知道的是,上述设置破坏了事务的隔离性,并且对于MySQL复制来说,可能会导致主从数据的不一致。虽然MySQL目前默认隔离级别是RR,但是基本生产环境标配基本都是RC隔离级别+ROW格式。

RC隔离级别+主键

# 事务1

# 事务2

从两个事务的执行结果可以看出,当我在事务1执行主键范围for update时,事务2对这个范围扔可以申请写锁。证明RC隔离级别没有使用NEXT-KEY Lock,而是使用行级锁锁住对应的记录,具体可以show engine innodb status。

RC隔离级别+普通索引

# 事务1

# 事务2

在事务2中,分别执行了三种不同的情况,第一种插入数据到事务1的for update范围内是可以的,因为这里事务1在RC模式下没有加NEXT-KEY LOCK锁,所以可以插入数据。但是第二种操作出现锁等待了,我们选择普通索引作为条件,此时MySQL给普通索引b>3的记录都会加行锁。同时,这些记录对应主键索引上的记录也都加上了锁。第三种操作成功了,说明b<3的记录都无锁。想看锁详细信息可以参考“MySQL InnoDB锁信息分析

在RC隔离级别下,普通索引跟唯一索引的加锁基本没有什么区别,有的也就是在做等值查询时,唯一索引只会有一条记录,而普通索引可能会有多条。

对于不同隔离级别下的不同索引的组合,其各自加锁方式分析,详情请看何登成大神的文章“MySQL加锁处理分析

二、InnoDB锁与索引

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQLOracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将会给所有记录加锁!在RR隔离级别下,由于查询条件没有索引,那么InnoDB需要扫描所有数据来查找数据,对于扫描过的数据InnoDB都会加上锁,并且是加Next-key lock。注意,InnoDB表无索引加锁在RR和RC隔离级别下是不同的。

在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能,下面通过一些实际例子来加以说明。

在下所示的例子中,开始tab_no_index表没有索引:

RR + 无索引

# 事务A

查看事务信息

可以看到这个事务中有此参数6 row lock(s),所有记录全部锁住了。另外,由于我们没有显式主键,InnoDB自行创建了一个ROW_ID,索引名称为GEN_CLUST_INDEX。并且加了Next-key lock,锁模式为X。

# 事务B

如上所示的例子中,看起来事务A只给一行加了排他锁,但事务B在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能扫描所有记录(锁住所有记录)。当我们给其增加一个唯一索引后,InnoDB就只锁定了符合条件的行。

InnoDB存储引擎的表在使用主键索引时使用行锁例子。

# 事务A

# 事务B

由这个例子可以看出,对于id是主键索引的情况下,只锁了id=1这一行记录。其余的行都是可以进行DML操作的,但前提条件是以id为条件。如果是以b字段为条件,那么还是会锁的。

RC + 无索引

上面演示了在RR隔离级别下,对于where条件是非索引的情况下,InnoDB是对所有记录加Next-key Locks。

但是在RC隔离级别下,对于where条件是非索引的情况下,其没有对所有记录加锁,而是只对命中的数据的聚簇索引加X锁。根据MySQL官方手册,对于update和delete操作,RC只会锁住真正执行了写操作的记录,这是因为尽管InnoDB会锁住所有记录,MySQL Server层会进行过滤并把不符合条件的锁当即释放掉(违背了2PL的约束)。同时对于UPDATE语句,如果出现了锁冲突(要加锁的记录上已经有锁),InnoDB不会立即锁等待,而是执行semi-consistent read:返回改数据上一次提交的快照版本,供MySQL Server层判断是否命中,如果命中了才会交给InnoDB锁等待。

如果是RR隔离级别,一般情况下MySQL是不能这样优化的,除非设置了innodb_locks_unsafe_for_binlog参数,这时也会提前释放锁,并且不加GAP锁,这就是所谓的semi-consistent read,关于semi-consistent read可以参考这里

# 事务A

查看锁结构:

2个锁结构,其中一个意向锁,一个行锁。同样在GEN_CLUST_INDEX索引上锁,但是显示说不是一个gap锁,证明没有加Next-key lock,与在RR隔离级别下是不一样的。

# 事务B

然后在另一个事务执行delete无法执行,而update和insert却可以。具体delete操作产生锁等待的原因还不太清楚。

三、InnoDB中锁的实现机制

1. 页锁对象 + 位图的实现方式

InnoDB中锁是根据页的组织形式进行管理的,行锁在InnoDB中的定义如下:

其中space/page_no可以唯一决定一个页,nbits是一个位图。因此要查看某行记录是否上锁,只需要根据space/page_no找到对应的页,然后根据位图中对应位置是否是1来决定此行记录是否上锁。而给某条记录上锁,首先查看记录所在页是否已经有锁对象,如果锁对象已经存在,则将位图上对应位置置1。如果不存在,则生成一个锁对象,然后将位图对应位置置1。

这种锁的实现机制可以最大程度地重用锁对象,节省系统资源,且不存在锁升级的问题。可想而知,如果每个行锁都生成一个锁对象,将会导致严重的性能损耗,比如接近于全表扫描的查询就会生成大量的锁对象,内存开销将会很大。位图的方式很好地避免了这个问题。

2. 通过事务或(space, page_no)再Hash的方式组织页锁对象

InnoDB提供了两种方式对行锁进行访问:

通过事务中的trx_t变量访问。一个事务可能在不同页上有多个行锁,因此需要变量trx_locks将一个事务中的所有行锁信息进行链接,这样就可以很快地查看一个事务中的所有锁对象。

通过space/page_no访问。InnoDB提供了一个全局变量lock_sys_struct来方便查询行锁信息,lock_sys_struct包含一个HashTable,Hash的key是space/page_no,value是锁对象lock_rec_struct。

<延伸>

MySQL加锁处理分析

MySQL常见 SQL 语句的加锁分析

MySQL · 引擎特性 · InnoDB事务锁简介

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁

目前为止,MySQL官方默认隔离级别还是RR,但生产中基本都是RC+ROW标配。那么RR与RC到底哪种隔离级别更好,及各自性能又如何呢?其实官方也有一篇针对隔离级别的比较,可以看一看:MySQL Performance : Impact of InnoDB Transaction Isolation Modes in MySQL 5.7


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (1)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!