MySQL 一致性读深入研究

197次阅读

共计 8807 个字符，预计需要花费 23 分钟才能阅读完成。

MySQL 一致性读，又称为快照读。使用的是 MVCC 机制读取 undo 中的已经提交的数据。所以它的读取是非阻塞的。

相关文档：http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

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.

一致性读肯定是读取在某个时间点已经提交了的数据，有个特例：本事务中修改的数据，即使未提交的数据也可以在本事务的后面部分读取到。

1. RC 隔离和 RR 隔离中一致性读的区别

根据隔离级别的不同，一致性读也是不一样的。不同点在于判断是否提交的“某个时间点”：

1）对于 RR 隔离：

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。实际上实验的结果表明，这里的 the first such read 指的是：对同一个表或者不同表进行的第一次 select 语句建立了该事务中一致性读的 snapshot. 其它 update, delete, insert 语句和一致性读 snapshot 的建立没有关系。在 snapshot 建立之后提交的数据，一致性读就读不到，之前提交的数据就可以读到。

事务的起始点其实是以执行的第一条语句为起始点的，而不是以 begin 作为事务的起始点的。

实验 1：

sesseion A	session B
mysql> set tx_isolation=’repeatable-read’; Query OK, 0 rows affected (0.00 sec)	mysql> set tx_isolation=’repeatable-read’; Query OK, 0 rows affected (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.01 sec)
	mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1(c1,c2) values(1,1); Query OK, 1 row affected (0.01 sec)
mysql> select * from t1; +—-+——+ \| c1 \| c2 \| +—-+——+ \| 1 \| 1 \| +—-+——+ 1 row in set (0.00 sec)

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

实验 2：

session A	session B
mysql> set tx_isolation=’repeatable-read’;	mysql> set tx_isolation=’repeatable-read’;
	mysql> select * from t1; Empty set (0.00 sec)
mysql> begin; mysql> select * from t;
	mysql> insert into t1(c1,c2) values(1,1); Query OK, 1 row affected (0.01 sec)
mysql> select * from t1; Empty set (0.00 sec)

该使用说明：RR 隔离级别下的一致性读，是以第一条 select 语句的执行点作为 snapshot 建立的时间点的，即使是不同表的 select 语句。这里因为 session A 在 insert 之前对 t 表执行了 select，所以建立了 snapshot，所以后面的 select * from t1 不能读取到 insert 的插入的值。

实验 3：

session A	session B
mysql> set tx_isolation=’repeatable-read’;	mysql> set tx_isolation=’repeatable-read’; mysql> select * from t1; Empty set (0.00 sec)
mysql> begin;
mysql> select * from t1; Empty set (0.00 sec)	mysql> select * from t1; Empty set (0.00 sec)
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.01 sec)

该实验中：session A 的第一条语句，发生在 session B 的 insert 语句提交之前，所以 session A 中的第二条 select 还是不能读取到数据。因为 RR 中的一致性读是以事务中第一个 select 语句执行的时间点作为 snapshot 建立的时间点的。而此时，session B 的 insert 语句还没有执行，所以读取不到数据。

实验 4：

session A	session B
mysql> set tx_isolation=’repeatable-read’;	mysql> set tx_isolation=’repeatable-read’; mysql> select * from t1; Empty set (0.00 sec)
mysql> select * from t1; Empty set (0.00 sec)
	mysql> insert into t1(c1,c2) values(1,1),(2,2); mysql> select * from t1; +—-+——+ \| c1 \| c2 \| +—-+——+ \| 1 \| 1 \| \| 2 \| 2 \| +—-+——+ 2 rows in set (0.01 sec)
mysql> select * from t1; Empty set (0.00 sec)
mysql> update t1 set c2=100 where c1=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1; +—-+——+ \| c1 \| c2 \| +—-+——+ \| 1 \| 100 \| +—-+——+ 1 row in set (0.00 sec)

该实验说明：本事务中进行修改的数据，即使没有提交，在本事务中的后面也可以读取到。update 语句因为进行的是“当前读”，所以它可以修改成功。

2）对于 RC 隔离就简单多了：

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

事务中每一次读取都是以当前的时间点作为判断是否提交的实际点，也即是 reads its own fresh snapshot.

RC 是语句级多版本(事务的多条只读语句，创建不同的 ReadView，代价更高)，RR 是事务级多版本(一个 ReadView)；

3. MySQL ��事务开始的时间

一般我们会认为 begin/start transaction 是事务开始的时间点，也就是一旦我们执行了 start transaction，就认为事务已经开始了，其实这是错误的。上面的实验也说明了这一点。事务开始的真正的时间点(LSN)，是 start transaction 之后执行的第一条语句，不管是什么语句，不管成功与否。

但是如果你想要达到将 start transaction 作为事务开始的时间点，那么我们必须使用：

START TRANSACTION WITH consistent snapshot

它的含义是：执行 start transaction 同时建立本事务一致性读的 snapshot . 而不是等到执行第一条语句时，才开始事务，并且建立一致性读的 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.

http://dev.mysql.com/doc/refman/5.6/en/commit.html
效果等价于：start transaction 之后，马上执行一条 select 语句(此时会建立一致性读的 snapshot)。

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. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries. (RR 隔离级别中的一致性读的 snapshot 是第一条 select 语句执行时建立的，其实应该是第一条任何语句执行时建立的)

http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

我们在 mysqldump –single-transaction 中使用的就是该语句：

SET session TRANSACTION isolation LEVEL REPEATABLE read
START TRANSACTION /*!40100 WITH consistent snapshot */

所以事务开始时间点，分为两种情况：

1）START TRANSACTION 时，是第一条语句的执行时间点，就是事务开始的时间点，第一条 select 语句建立一致性读的 snapshot；

2）START TRANSACTION WITH consistent snapshot 时，则是立即建立本事务的一致性读 snapshot，当然也开始事务了；

实验 1：

session A	session B
mysql> set tx_isolation=’repeatable-read’;	mysql> set tx_isolation=’repeatable-read’;
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction;
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; +—-+——+ \| c1 \| c2 \| +—-+——+ \| 1 \| 1 \| +—-+——+ 1 row in set (0.00 sec)

实验 2：

mysql> set tx_isolation=’repeatable-read’;	mysql> set tx_isolation=’repeatable-read’;
	mysql> select * from t1; Empty set (0.01 sec)
mysql> start transaction with consistent snapshot;
	mysql> insert into t1(c1,c2) values(1,1);
mysql> select * from t1; Empty set (0.00 sec)

上面两个实验很好的说明了 start transaction 和 start tansaction with consistent snapshot 的区别。第一个实验说明，start transaction 执行之后，事务并没有开始，所以 insert 发生在 session A 的事务开始之前，所以可以读到 session B 插入的值。第二个实验说明，start transaction with consistent snapshot 已经开始了事务，所以 insert 语句发生在事务开始之后，所以读不到 insert 的数据。

3. Oracle 中的一致性读

Oracle读一致性是指一个查询所获得的数据 来自同一时间点。

Oracle读一致性分为 语句级读一致性 和事务级读一致性。

语句级读一致性 ：Oracle 强制实现语句级读一致性。一个查询语句只读取语句开始之前提交的数据。

事务级读一致性 ：隔离级别为 SERIALIZABLE 和read only的事务才支持事务级读一致性。事务中的所有查询语句只读取事务开始之前提交的数据。

Oracle 只实现了 RC 和 serializable，没有实现 Read uncommitted 和 RR。其实 Oracle 的 serializable 级别才实现了可重复读。

4. 当前读(current read) 和一致性读

一致性读是指普通的 select 语句，不带 for update, in share mode 等等子句。使用的是 undo 中的提交的数据，不需要使用锁 (MDL 除外)。 而当前读，是指 update, delete, select for update, select in share mode 等等语句进行的读，它们读取的是数据库中的最新的数据，并且会锁住读取的行和 gap(RR 隔离时)。如果不能获得锁，则会一直等待，直到获得或者超时。RC 隔离级别的当前读没有 gap lock，RC 的 update 语句进行的是“半一致性读”，和 RR 的 update 语句的当前读不一样。

5. 一致性读与 mysqldump –single-transaction

我们知道 mysqldump –single-transaction 的原理是：设置事务为 RR 模式，然后利用事务的特性，来获得一致性的数据，但是：

–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.

在 mysqldump 运行期间，不能执行 alter table, drop table, rename table, truncate table 等等的 DDL 语句，因为一致性读和这些语句时无法隔离的。

那么在 mysqldump –single-transaction 执行期间，执行了上面那些 DDL，会发生什么呢？

mysqldump –single-transaction 的执行过程是：设置 RR，然后开始事务，对应了一个 LSN，然后对所有选中的表，一个一个的执行下面的过程：

save point sp; –> select * from t1 –> rollback to sp;

save point sp; –> select * from t2 –> rollback to sp;

… …

1> 那么如果对 t2 表的 DDL 发生在 save point sp 之前，那么当 mysqldump 处理到 t2 表时，mysqldump 会立马报错：表结构已经改变 ……

2> 如果对 t2 表的 DDL 发生在 save point sp 之后，rollback to sp 之前，那么要么 DDL 被阻塞，要么 mysqldump 被阻塞，具体谁被阻塞，看谁先执行了。

被阻塞额原因是：DDL 需要 t2 表的 MDL 的互斥锁，而 select * from t1 需要 MDL 的共享锁，所以阻塞发生。

3> 如果对 t2 表的 DDL 发生在 rollback to sp 之后，那么因为对 t2 表的 dump 已经完成，不会发生错误或者阻塞。

那么为什么：对 t2 表的 DDL 发生在 save point sp 之前，那么当 mysqldump 开始处理 t2 表时，mysqldump 立马报错呢？

其原因就是一致性读的胳膊拗不过 DDL 的大腿：

Consistent read does not work over certain DDL statements:(一致性读的胳膊拗不过 DDL 的大腿)

Consistent read does not work over DROP TABLE, because MySQL cannot use a table that has been dropped and InnoDB destroys the table.
Consistent read does not work over ALTER TABLE, because that statement makes a temporary copy of the original table and deletes the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction’s snapshot was taken. In this case, the transaction returns an error as of MySQL 5.6.6: ER_TABLE_DEF_CHANGED,“Table definition has changed, please retry transaction”.