踩坑攀登者:mysql/innodb的锁、隔离与MVCC (下)

1,203 阅读16分钟

注意本文建立在理解mysql的两种reads和行锁的基础上,如果对none-locking reads、gap lock或insert intention这些概念不熟悉请先阅读《上篇》

本文主要有以下几个内容:与locking并驾齐驱的并发处理机制MVCC,sql92对isolation级别的规定以及innodb的实现,innodb不同的隔离级别对两种reads的影响。

MVCC

什么是MVCC

在上一篇中我们讲到了锁,锁是一种在并发场景保证数据一致性的常见手段。这次要讲的MVCC可以说是和锁同一个层次的设计。MVCC的是multi-version concurrency control的缩写也就是多版本并发控制,由于名字太长了大家普遍称之为MVCC。 MVCC本质上是一种通过多版本的机制来管理并发的策略。由于锁会限制了效率,所以MVCC常常作为一种对于锁的补充方案来管理read与write并发请求之间的一致性问题。从这里可以看到并发环境下的一致性管理又多了个MVCC的设计思路。

基本上所有的主流数据库都实现了MVCC,各个数据库对MVCC的实现方案和细节不完全相同,但整体思路都是基于version的读写隔离:一个trx的write操作会导致版本变化,同时保留老的数据版本,供需要老版本数据的其他trx来读取。

innodb对MVCC的实现

如何表示版本verison?

实现MVCC的第一步就是需要一些“工具”来记录version,最好这些“工具”能帮忙判断应该读哪个版本的数据。在innodb的设计里,transactionId事务id是记录verison的核心工具。TransactionId在innodb中是自增的,大的Transaction在小的transaction之后开始(注意这个trxId不能简单的认为是自动开始下一个trx的时间,在个Id是和select语句实行时间相关的)。innodb在每一行增加了一个隐藏的字段DB_TRX_ID来表示最后一次尝试修改该行的Transaction,这个字段叫做DB_TRX_ID。这个字段帮助了select语句来判断当前的row data是否可以读,是不是自己能看到的version版本。

历史数据存在哪儿?

那么历史版本存在哪儿,这个有两种设计方向:一种是还是存在原地,只是打标为delete,重新建一条目前有效的数据;另一种方向是原地更改数据,更改数据的版本号也就是trx_id,历史数据统一放到别处

针对行数据innodb采用的是第二种方法(postgresql采用的是第一种方案):innodb的每一行上新增了DB_ROLL_PTR,回滚指针,指针指向rollback segment中这一行之前的一个数据版本。rollback segment是 undolog里的一部分这里面记录了历史版本的数据情况,之所以称为rollback和undo是因为这部分信息的另一个关键作用适用于事务的回滚。

innodb把dml分为两类,对已有记录的修改的update和delete,新增数据的操作insert。这种设定和我们在上篇讲到的两类锁——针对记录的锁和针对空隙的锁——是一致的。在这种分类思路下undo log分成了两部分,update和insert区域。 innodb认为insert操作在commit之后的rollback信息可以马上清除掉,而update类的信息需要保留到所有MVCC支持的一致读不再需要这部分信息之后才能进行purge(purge是指innodb后台线程对undolog中无用数据的一种gc操作)。

updated in-place & reconstruct 原地更新与历史重建

正如刚刚讲的原地更新的方案,update(update+delete)和insert语句执行时,聚集索引上的row data会直接修改/新增,修改前的行数据会存到undo log中,并在row的pointer中会更新为新的undo log的位置。

当另一个trx的select发现这一行的trxId大于自己的trxId时,就会根据指针找到undolog中该行的历史记录一直找到最大的小于自己trxId的修改记录即可。(这是一种trxId满足要求的判断,在不同的隔离级别下这个判断的方式不同)。

如何识别一行就需要一个rowId的设计,由于pk也可能被更新,而自增不变的rowId是识别一行的可靠id。所以行数据上还有一个rowId的隐藏字段。

这样当需要回滚时只要去找到当前行的历史版本就可以回滚, 当别的trx来select时发现rowId对应的trxId与自己trx的快照snaptshot中的该rowId对应的trxId(在一致性环境下trx会保存自己的select快照里的版本信息)并不一致,也会根据pointer去查undo log来复原历史版本的数据返回给用户。从trx快照这个设计其实能让我们更好的体会到为什么一个trx称为一个unit of work。

secondary index 次级索引的MVCC

刚刚讲到的在record上原地更新其实是基于聚集索引的设计,因为record是存放在聚集索引的叶子节点的。而次级索引的叶子节点记录只是记录了聚集索引的id,并没有一行数据,所以MVCC针对次级索引的实现方案与上文所述的并不相同。

次级索引上没有新增字段也没有原地更新,而是采用上述讲到的第一个思路:不在原来数据上做更改,而是生成新的数据,原来的数据entry会被标记成为deleted,新的index索引所在的page上会记录更新的trxId。当select trx发现了delete或者page上更大的trxId,select会回表查询聚集索引,并通过聚集索引的信息去查undo log。从这个过程可以发现mvcc保证一致性读的过程中会导致索引覆盖失效。

Isolation

先讲讲什么是隔离级别Isolation,Isolation就是数据库ACID中的I。隔离级别讲的是不同的事务transaction在使用相同数据时之间的隔离程度。讲到隔离级别其实有两个层次,一个是SQL标准中规定的隔离级别,一个是数据库对于隔离级别的实现。

sql-92定义

第三版本sql修订也就是sql-92中定义了4中隔离级别。

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

sql-92中是通过是否会产生一些不一致问题来定义了隔离级别,这些不一致问题有:dirty read(脏读),none-repeatable read(不可重复读)和 phantom read(幻读)

Dirty,None-Repetable, Phantom

Dirty Read脏读定义最简单是只读到了其他trx尚未commit的insert或者update

不可重复读和幻读在笔者第一次看到时总觉得这两个是一回事,不知道大家是不是有过和我一样的疑惑。但是如果看了第一篇讲锁的情况大家就知道对已经存在的行记录records和还没存在的行记录(间隙)加锁的情况是完全不一样的。而不可重复度和幻读也是根据records是否存在来区分的

不可重复度是指已经一个trx中读到的已存在的records被其他trx修改或者删除并体提交的记录,也就是第一次读到了数据再读这个数据变化了或者没了。幻读是指一个trx中读到了其他trx insert并commit的记录,也就是第一次读没有这个数据,再读出现了。

隔离级别的定义

在SQL-92中,隔离级别就根据是否能防止上述的三种问题来划分(注意这个和innodb的实现不完全一致)。Serializable定义成为按照一定顺序并发开始执行的事务与按同样顺序一个个顺序执行的结果完全相同,如果并行和串行完全一致那么就不会出现任何并发下的不一致问题。

这里其实一个很重要的注意点就是这些定义更多的是针对none-locking reads也就是普通的select来讲的,试想一下如果涉及到的行已经全都加上锁了那么就是最弱的隔离级别也不会出现脏读了;为什么说是更多呢,不同的隔离级别对锁的实现也不同,所以这3种不一致读的现象在locking reads的情况下也可以参与讨论。 sql-92定义的隔离级别可以参考下图:

innodb声称支持4种隔离级别但实现上的方案和效果与标准sql-92的定义不完全一致,一个最大的差别就是innodb在RepetableRead的阶段就防止了幻读的出现。innodb实现的隔离级别可以参考下图,serializable并不常用其与repeatable相比就是把所有的select都变成了select for share从而把sql的并行化降低了。

隔离级别与两种reading

在上一部分抛出的观点是隔离级别所指的几种不一致读是在none-locking reads下的情况,但隔离级别同样影响locking reads的加锁情况,看过上篇的同学应该有了解当我们上一篇讲insert intention lock、gap lock和next key lock这些对间隙才加锁的。

consistent none-locking reads

由于innodb下serializable隔离级别如果auto_commit = false会把none-locking的select都变成locking的select for share,如果auto_commit = true那么每个select都构成一个transaction并且马上结束。所以这个隔离级别下没有必要再讨论none-locking reads。

在另一个隔离级别read-uncommitted下,未提交的更改也能被别的trx看到,这个隔离级别下trx之间没有相互consistent reading。

consistent none-locking reads的重点在于Read-committed(RC)和Repeatable-read(RR),这两种隔离级别也是最常用的。RC和RR这两种隔离级别下,none-locking reads使用上文所讲的MVCC的机制来保证事务内部读的一致性。只不过两者对于consistent的要求不同,RC认为只要提交的读都可以读到所以RC的每次read其snaptshot都会更新,RR认为在第一次读读到的snaptshot数据要坚持到trx结束。

这也就是为什么RC情况下会产生幻读和不可重复度而RR状态下并不会的原因:只要udpate和insert被commit,在RC情况下都认为可以被读到,RC的select会更新snapshot把这些update和insert的trx_id刷新进来。而由于RR中的trx不会再更改第一次读到的snapshot,所以update和insert就算成功了,由于他们的trx_id并不在RR保存快照的范围内,所以不会被读到。

locking reads

locking reads在不同的I级别下的加锁状态也不相同。RC和RR可以在两个方面对比学习。

record和gap

RC级别下最大的特点是只会加锁record lock,并不会对间隙加锁所以RC情况下不能防止insert操作, 所以RC情况下就算加了锁可以防止None-Repeatable Read,但是不能防止Phantom Read

RR级别会给命中的record向前加next key lock,向后加gap lock到下一个record。从而RR级别可以有效的防止Phantom Read。实际上根据innodb的实现一直防止insert到上一个record记录和下一个record记录,如果table中的表是十分稀疏的,被锁定的区间会相当大。这也从另一个角度说明了为什么使用transaction要尽可能short and fast

select * from tb_user_complaint where user_id = 222 for update;
+----+----------------------------+---------+------------------+------------+----------------------------+-----------+
| id | created_at                 | user_id | contents         | is_archive | last_updated_at            | user_name |
+----+----------------------------+---------+------------------+------------+----------------------------+-----------+
| 2  | 2020-02-12 15:12:19.214543 | 222     | complaint-test-1 | ^@          | 2020-02-12 15:12:19.214543 | macavity  |
+----+----------------------------+---------+------------------+------------+----------------------------+-----------+

// RR级别下: (222,2)的X表示向前加了next-key lock,(500,1042)表示向后加了gap lock到这里;
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                     | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME       | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME  | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4724256128:1079:140387850342152    | 6118                  | 1068      | 134      | test          | tb_user_complaint | <null>         | <null>            | <null>      | 140387850342152       | TABLE     | IX            | GRANTED     | <null>    |
| INNODB | 4724256128:22:5:3:140387876554776  | 6118                  | 1068      | 134      | test          | tb_user_complaint | <null>         | <null>            | idx_user_id | 140387876554776       | RECORD    | X             | GRANTED     | 222, 2    |
| INNODB | 4724256128:22:4:17:140387876555120 | 6118                  | 1068      | 134      | test          | tb_user_complaint | <null>         | <null>            | PRIMARY     | 140387876555120       | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
| INNODB | 4724256128:22:5:8:140387876555464  | 6118                  | 1068      | 134      | test          | tb_user_complaint | <null>         | <null>            | idx_user_id | 140387876555464       | RECORD    | X,GAP         | GRANTED     | 500, 1042 |
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+

// RC级别下: 只有两个index records lock
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                     | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME       | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME  | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4724256128:1079:140387850342152    | 6117                  | 1068      | 129      | test          | tb_user_complaint | <null>         | <null>            | <null>      | 140387850342152       | TABLE     | IX            | GRANTED     | <null>    |
| INNODB | 4724256128:22:5:3:140387876554776  | 6117                  | 1068      | 129      | test          | tb_user_complaint | <null>         | <null>            | idx_user_id | 140387876554776       | RECORD    | X,REC_NOT_GAP | GRANTED     | 222, 2    |
| INNODB | 4724256128:22:4:17:140387876555120 | 6117                  | 1068      | 129      | test          | tb_user_complaint | <null>         | <null>            | PRIMARY     | 140387876555120       | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+

RR级别加间隙锁的一个大坑是与1)执行计划中使用的index是否unique,2)且对应的record是否能找到相关的:

  • 如果使用的index是unique的,且搜索区间是等值,且有命中的records;在这种情况下,由于unqiue锁就能防止insert,所以innodb不会再使用gap lock或者next-key lock;
  • 如果不满足上面的要求,比如unique key搜索的是一个范围,比如unique key搜索的record不存在,或者使用的key不是unique的,那么innodb就会使用next-key lock或者gap lock(也就是上面例子里的情况)。
// 使用pk搜索并且命中一条记录,查看datalocks只有X,rec_not_gap锁
select * from tb_user_complaint where id = 2 for update;
+----+----------------------------+---------+------------------+------------+----------------------------+-----------+
| id | created_at                 | user_id | contents         | is_archive | last_updated_at            | user_name |
+----+----------------------------+---------+------------------+------------+----------------------------+-----------+
| 2  | 2020-02-12 15:12:19.214543 | 222     | complaint-test-1 | ^@          | 2020-02-12 15:12:19.214543 | macavity  |
+----+----------------------------+---------+------------------+------------+----------------------------+-----------+
Time: 0.013s
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                     | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME       | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4724256128:1079:140387850342152    | 6121                  | 1068      | 145      | test          | tb_user_complaint | <null>         | <null>            | <null>     | 140387850342152       | TABLE     | IX            | GRANTED     | <null>    |
| INNODB | 4724256128:22:4:17:140387876554776 | 6121                  | 1068      | 145      | test          | tb_user_complaint | <null>         | <null>            | PRIMARY    | 140387876554776       | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+

不满足where条件的index record是否会释放

这个特点感觉很容易被忽略。

  • 第一阶段: 次级索引只有索引本身和叶子节点primary key的信息,如果update和delete的where条件里有其他筛选条件的话次级索引并没法精确的筛选出命中行,这个时候innodb会选择把所有粗糙命中的行加上record lock

  • 第二阶段: 之后inndb会把命中的pk返回给mysql层,mysql层需要回表clustered index来获取数据进行过滤。这里不同的隔离级别对粗糙命中行的处理就不一样了。RC级别会把通过回表过滤掉的record locks 释放掉(注意第一阶段就算是RC也会先申请锁),RR级别会继续保留所有的锁直到trx结束。-0-

下边我们看个例子,我们把where条件限制成 user_id和user_name,userId=555,userId被选为执行计划中的key。命中userId=555的有3行但是name符合条件的只有两行。这个时候再RR和RC的加锁的结果就完全不同。

select * from tb_user_complaint where user_id = 555 and user_name = "macavity" for update;
+-----+----------------------------+---------+------------------+------------+----------------------------+-----------+
| id  | created_at                 | user_id | contents         | is_archive | last_updated_at            | user_name |
+-----+----------------------------+---------+------------------+------------+----------------------------+-----------+
| 17  | 2020-02-15 16:08:00.184710 | 555     | complaint-test-1 | ^@          | 2020-02-15 16:08:00.184710 | macavity  |
| 123 | 2020-02-12 15:12:11.922491 | 555     | complaint-test-1 | ^@          | 2020-02-14 13:40:04.517506 | macavity  |
+-----+----------------------------+---------+------------------+------------+----------------------------+-----------+

RR情况下会对所有userId命中的条件加锁,同时注意锁类型是X锁也就是nextKey lock加锁范围很大。

RC情况下,在回表进行where查询之后会把之前错加的锁释放掉,只会加真正命中的行锁(注意没有gap也没有next-key lock)。

这里仔细想一下,在RC条件下第一个阶段如果需要加所有疑似的行锁的话,那么是不是两个where语句即使在第二阶段不冲突,第一阶段的冲突也可能导致block?

在RC级别下面执行两个语句,答案是trx-2是否会block么?其实是否冲突是和两个执行计划使用的key是强相关的,如果两个sql都使用user_id作为key那么就会发生冲突,因为trx-1虽然在第二阶段释放了user_name=midofinos行,但是trx-2第一阶段的粗糙加锁回去请求macavity行的锁。如果其中一个使用了user_name作为key,那么就不会block。

// 在RC条件下都指定使用userId作为index,发现第一个trx没有锁住midofinos,但是第二个trx还是会被block;
// 去掉index hint时innodb使用user_name作为index,两个trx不再干扰彼此;
// trx-1
select * from tb_user_complaint use index (idx_user_id) where user_id = 555 and user_name = "macavity";

// trx-2
select * from tb_user_complaint use index (idx_user_id) where user_id = 555 and user_name = "midofinos";

RU与SER

至于uncommitted和serializable对于locking-reads的影响,可以理解为RU对于locking-reads和RC情况相同,而serializable和RR情况相同。这两个级别是对于RC和RR更多的是在none-locking reads上一致性的放松/加强。

结语

DB一直是笔者比较喜欢的课题,关于隔离级别和锁就写完了。希望大家喜欢:)