[MySQL] 一致性读分析

MySQL MVCC

MySQL InnoDB存储引起实现的是基于多版本的并发控制协议---MVCC(Multi-Version Concurrency Control)，基于锁的并发控制，Lock-Based Concurrency Control。

一致性读，又称快照读，读取的是undo中已提交的数据，可能是数据的历史版本，no-locking，所以是非阻塞的读取操作。

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction.

那么一致性读(快照读)和当前读有什么区别呢?

• snapshot read:

  读取记录的可见版本(有可能是历史版本), no-locking

  no-locking reads: 简单的select语句

  • select..

• current read:

  读取的是记录的最新版本, 加锁保证事务隔离性

  locking-reads: 特殊操作, 插入/更新/删除操作

  • select .. for update
  • select ... in share mode
  • insert
  • update
  • delete

RC隔离和RR隔离的中的一致性读

Case1：RR隔离

对于RR的事务隔离级别，同一个事务所有的一致性读建立在第一次read操作开始时。

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

深入理解下"the first such read in that transaction"这句话，事务开始后，对表的第一次select语句建立了该事务中一致性读的snapshot，而并非指事务的begin或者start transaction。

上面的实验说明：RR隔离级别下的一致性读，不是以begin为开始建立的snapshot，而是第一条select语句为timepoint建立的snapshot。

上面的实验说明：RR隔离级别下的一致性读，是以第一条select语句为timepoint建立的snapshot，即使是针对不同的表做检查。

上面的实验说明：RR隔离级别下，其他事务插入的数据，即使没有提交，在本事务中也可以update成功，因为update语句执行的是“当前读”，并且修改后的数据在本事务中可见。

最后，在RR隔离级别下，locking reads的加锁信息依赖于具体的SQL条件，如果基于UK的唯一搜索条件，那么会锁定二级uk index记录以及聚簇索引表的记录(no-gap锁)；如果基于范围区域的扫描条件，那么会锁定记录本身外，还会使用gap locks/next-key lock来避免幻读。具体可参考"MySQL 加锁处理分析"

Case2：RC隔离

对于RC事务隔离级别，每一个一致性读都是最新的快照数据。

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

MySQL中事务开始时间

一般的begin/start transaction是事务开始的时间点，这里有误区，正如上面的实验看到的。事务开始的真正时间点(LSN)是start transaction之后执行的第一条语句。

START TRANSACTION WITH consistent snapshot指执行start transaction同事建立事务的一致性读snapshot。

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.2.2.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. As of MySQL 5.7.2, a warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

一致性读问题

一致性读在遇到特定的DDL语句的问题

Consistent read does not work over certain DDL statements

• drop table语句，因为MySQL是无法使用一个已经被drop的表。
• alter table语句，因为alter table会通过表copy的方式实现，在一致性读开始后，alter table过程中的临时表数据是无法看到的，这种情况下，事务会返回：ER_TABLE_DEF_CHANGED(Table definition has changed, please retry transaction)。

这个问题经常发生在mysqldump一致性备份期间。

一致性mysqldump问题

mysqldump是MySQL逻辑数据导出的有力工具，其中参数：--single-transaction参数支持数据的一致性导出。原理是：设置事务为RR模式，然后获取LSN快照，来获得一致性的数据。

--single-transaction

Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE 这些DDL语句的执行，会导致无法使用undo构造出正确的一致性读，一致性读和它们是无法隔离的。

另外在mysqldump中还可能遇到大查询带来的"waiting for table flush"问题， 假设有3个会话，会话A执行大查询，访问t表；然后一个备份会话B正处于关闭表阶段，需要关闭表t；随后会话C也请求访问t表。三个会话按照这个顺序执行，我们会发现备份会话B和会话C访问t表的线程都处于“waiting for table flush”状态。这就是关闭表引起的，这个问题很严重，因为此时普通的select查询也被堵住了。下面简单解释下原因：

1. 会话A打开表t，执行中……
2. 备份会话B需要清理表t的cache，更新版本号(refresh_version++)
3. 会话B发现表t存在旧版本(version != refresh_version)，表示还有会话正在访问表t，
   等待,加入share对象的等待队列

4. 后续会话C同样发现存在旧版本(version != refresh_version)，
   等待，加入share对象的等待队列
   ......

5. 大查询执行完毕，调用free_table_share,唤醒所有等待线程。

free_table_share //逐一唤醒所有等待的线程。

{

while ((ticket= it++))

ticket->get_ctx()->m_wait.set_status(MDL_wait::GRANTED);

}

第4步与第5步之间，所有的访问该表的会话都处于“waiting for table flush”状态，唯有大查询结束后，等待状态才能解除。

参考:

MySQL 5.7官方文档：http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

MySQL 加锁处理分析：http://hedengcheng.com/?p=771

FTWRL详解：http://www.cnblogs.com/cchust/p/4603599.html