发布于 8 个月前 作者 yuanliandu 191 次浏览 来自 服务端


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. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.





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.


Untitled Diagram.png


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


Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

一致性读是Innodb在读提交和可重复读可隔离级别下,处理select语句,所采取的默认模式。 一致性读不会对其访问的表设置任何锁,因此其它会话可以自由修改这些表,与此同时这些表也正在进行一致性读。

Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.


Note: The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

Note: 数据库状态的快照适用于事务中的select语句,不一定适用于DML语句。如果有一个事务插入或者更新了一些行并提交,另一个同时发生的可重复读的事务发出的删除或者更新语句可能会影响刚刚提交的行,即使会话无法查询。如果一个事务确实删除或更新了其他事务提交的行,这些更改对当前事务是可见的。例如,你可能会遇见下面情况

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
-- 没有行匹配
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.
-- 删除其他事务刚提交的几行数据

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
-- 没有行匹配
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
-- 影响10行数据,另一个事务提交的带有'abc'值的10行数据
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.
-- 返回10行:这个事务可以看见刚更新的行。

You can advance your timepoint by committing your transaction and then doing another SELECT or START TRANSACTION WITH CONSISTENT SNAPSHOT.


This is called multi-versioned concurrency control.


In the following example, session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.


就是sessionB和sessionA提交后,sessionA commit 之后的select读取到了结果集。因为都设置了自动提交为0,sessionA先执行了select语句,所以sessionA的时间点比sessionB早。所以在sessionA结束前,无法读取到sessionB的内容==

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
v          SELECT * FROM t;
           empty set

           SELECT * FROM t;
           empty set


           SELECT * FROM t;
           |    1    |    2    |

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:


select * from t for share;

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. With FOR SHARE, a locking read occurs instead: A SELECT blocks until the transaction containing the freshest rows ends (see Section, “Locking Reads”).

在读提交隔离级别下,事务中每一次一致性读,都会设置并读取最新的快照。使用for share时,将发生锁定读取:直到包含最新行的事务结束时,才执行选择块。(详见15.7.2.4,锁定读取

Consistent read does not work over certain DDL statements:

  • 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: ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.


  • 一致性读不适用drop table,因为mysql不会使用已经移除的表,并且innodb会删除该表。
  • 一致性读不适用于alter table,因为该语句生成原始表的临时副本,并在生成临时副本时删除原始表。在事务中重新发出一致读取时,新表中的行不可见,因为在获取事务快照时这些行不存在。这种情况,事务会报错 ER_TABLE_DEF_CHANGED;意思是表定义已经更改,请重试事务。

The type of read varies for selects in clauses like INSERT INTO … SELECT, UPDATE … (SELECT), and CREATE TABLE … SELECT that do not specify FOR UPDATE or FOR SHARE:

  • By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
  • To use a consistent read in such cases, set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ (that is, anything other than SERIALIZABLE). In this case, no locks are set on rows read from the selected table.

对于select子句,读的类型有所不同,比如insert into ....select, UPDATE ... (SELECT), CREATE TABLE ... SELECT。 这些语句并不指定 FOR UPDATEFOR SHARE:

  • 默认情况下,innodb使用更强的锁,select部分的行为类似于read committed,其中每个一致性读,即使在同一事务中,也会设置和读取自己的新快照。
  • 要在这种情况下使用一致读取,请将事务的隔离级别设置为“读取未提交的”、“读取已提交的”或“可重复读取”(即除可序列化之外的任何内容)。在这种情况下,不会对从所选表中读取的行设置锁。