一致性非锁定读
发布于 5 个月前 作者 yuanliandu 90 次浏览 来自 服务端

原文-一致的非锁定读

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.

一致性读意味着,innodb使用多版本为一个sql查询语句在某个时刻提供数据库的快照。

这个查询可以看见那个时刻之前所有事务提交的更改,但是不能看见之后或者还没有提交的事务的更改。

此规则的例外情况是,查询可以看到同一事务中靠前的语句所做的更改。这种例外导致了以下异常,如果你在表中更新一些行,select可以看到更新行的最新版本,但是它可能看到任何行的旧版本。如果其它会话同时更新同一张表,异常意味着你可能看到该表在数据库中从未出现过的状态。

不是很懂

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

sessionB事务中两次读取的内容一致,都是name=aaa;即第一次读建立的快照;即使期间,sessionA更新了数据,也无法看到。

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.

假设你正在运行默认的可重复读隔离级别。当发出一致性读时(也就是,通常的select语句),innodb为你的事务提供一个时间点,根据时间点,你的查询可以看见数据库。如果另一个事务删除一行,在你的时间点之后提交,你将不会看见该行已经被删除。插入、更新语句与此类似。

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.

您可以通过提交事务,然后使用select或者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.

在下面的示例中,会话A仅在B提交了插入,并且A也提交了插入操作时,才看到B插入的行,这样时间点就提前超过了B的提交操作。

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

             Session A              Session B

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

           SELECT * FROM t;
           empty set

           COMMIT;

           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 15.7.2.4, “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”.

一致性读不适用于一些DDL语句:

  • 一致性读不适用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,其中每个一致性读,即使在同一事务中,也会设置和读取自己的新快照。
  • 要在这种情况下使用一致读取,请将事务的隔离级别设置为“读取未提交的”、“读取已提交的”或“可重复读取”(即除可序列化之外的任何内容)。在这种情况下,不会对从所选表中读取的行设置锁。
回到顶部