mysql数据一致性校验sql mysql一致性读当前读

转载

mob64ca1417eedd 2023-12-11 12:45:46

文章标签 mysql数据一致性校验sql mysql事务快照读一致性读 mysql事务隔离级别 文章分类 MySQL 数据库

数据库读，是数据库操作中很常见的一个操作，在数据库事务中也经常出现读取数据的操作，比如先读取是否存在，然后不存在就插入等，想要了解数据库事务，理解“读”这个操作必不可少。

数据库读分为：一致非锁定读、锁定读。这里是mysql官方文档对于一致性读的讲解，翻译一下。

首先，概括一下，我理解的这篇官方文档主要表达的意思：

一致非锁定读，也可以称为快照读，其实就是普通的读取即普通SELECT语句。
既然是快照读，故 SELECT 的时候，会生成一个快照。
生成快照的时机：事务中第一次调用SELECT语句的时候才会生成快照，在此之前事务中执行的update、insert、delete操作都不会生成快照。
不同事务隔离级别下，快照读的区别： READ COMMITTED 隔离级别下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次SELECT也可以看到其它已commit事务所作的更改；REPEATED READ 隔离级别下，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，因此，只有第一次SELECT之前其它已提交事务所作的更改你可以看到，但是如果已执行了SELECT，那么其它事务commit数据，你SELECT是看不到的。

下面是翻译正文(原文地址：https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html)

Consistent Nonlocking Reads 一致非锁定读(一致读、快照读)

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引擎使用多版本展示某个时间点的数据库的查询快照(一致性读是通过 MVCC(多版本并发控制) 为查询提供了一个基于时间的点的快照)。查询可以看到自己之前已提的所有事务所做的更改，看不到在查询开始之后的事务提交的更改(ps:可能会有疑问，READ COMMITTED隔离级别下是可以看到事务提交的更改的，这个疑问文档下面会有一句话解析，其实这是因为：READ COMMITTED 隔离级别下每一次查询都会提前更新数据库快照！)或者未提交的事务所做的更改。这个规则的例外情况是查询可以看到同一个事务中早期语句所作的更改。这个例外导致了一下异常现象：
如果更新表中的一些行，SELECT 语句看到更新行的最新版本，但是也可能看到任何行的旧版本(ps:这后半句是什么意思？)
如果有其它会话同时更新了同一张表，这个异常意味着你可能看到数据库中从未存在过的表的脏数据。

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.

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.

使用 READ COMMITTED 隔离级别，事务中每次一致性读都会设置并读取当前最新的快照。

在 READ COMMITTED 和 REPEATED READ 隔离级别下，一致性读是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.

设想一下事务正在默认的 REPEATABLE READ 隔离级别下运行。当你进行一个一致性读(也就是说，一个普通的 SELECT 语句)，InnoDB 会根据这次查询看到的数据库内容设置一个时间点。如果另外一个事务在这个时间点之后删除了其中一行并且提交，你不会看到这样被删除了。插入和更新也是被同样对待。
(ps:就是说你先在事务1中select，然后事务2中进行delete、insert、update操作，然后再次再事务1中进行select 你是看不到delete、insert、update的结果的，因为第一次select已经形成了快照)

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:

数据库状态的快照适用于事务中 SELECT 语句，对 DML(data manipulation language) 语句没有作用。如果你插入或者修改一些行然后提交事务，另一个 REPEATABLE READ 隔离级别的事务中之行一个 DELETE 或者 UPDATE 语句会影响到已提交的那些行，即使那个会话中不能查询到这些行。如果一个事务对另一个事务提交的行进行更新或者删除操作，那些改变在当前事务中是可以看到的。举个例子，你可能会遇到如下的情形：

例子1:
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.

例子2:
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.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.
例子1:
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.

例子2:
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.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

ps：上面这个例子可能没看太明白，解释一下，其实有两个事务作对比就比较好理解了，不过文档上这里只贴出来了事务1，其它事务(比如事务2、事务3等)并没有贴出来作对比。上面第一个例子的意思就是说：查询xyz的时候没有查询到，但是delete的时候却删除了一些行，这是因为有其它事务修改了数据；第二个例子是说：查询abc的时候没有查询到，但是update的时候却更新到了，然后由于是本事务进行的更新，故而后续的查询都可以看到本事务所作的更改。总结下这两个例子想要表达的就是：REPEATED READ 隔离级别下，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，因此，只有第一次SELECT之前其它已提交事务所作的更改你可以看到，但是如果已执行了SELECT，那么其它事务commit数据，你SELECT是看不到的。

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.

这被称为多版本并发控制 (MVCC)。

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插入的数据，只有B已经提交了插入的数据并且A也已经提交了事务的时候，这是由于快照生成的时间点在B提交之前。

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    |
           ---------------------
             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:

如果想要看到数据库最新的状态，需要使用 READ COMMITTED 隔离级别或者锁定读:

SELECT * FROM t FOR SHARE;
SELECT * FROM t FOR SHARE;

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

READ COMMITTED 隔离级别下，事务中每次一致性读都会设置并读取最新的快照。使用 LOCK IN SHARE MODE 模式，会进行锁定读：SELECT 会阻塞直到事务读取到最新的行为止。

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(Data definition language)语句无效：

一致性读对 DROP TABLE 无效，因为MySQL不能使用已经被删除的表，InnoDB会销毁表。
一致性读对 ALTER TABLE 无效，这是因为这条语句会根据源表产生一个临时副本并且会删除源表当副本被创建时。当你在事务中再次进行一致读的时候，新表中的行对你是不可见的，因为当事务快照创建的时候这些行还不存在。因此，事务会返回错误： ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

--------以下不知道咋翻译了，也没看太明白啥意思，求高手伸出援手～
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 LOCK IN SHARE MODE:
读类型因字句中的select而异，如 INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT ，并不特指 FOR UPDATE 或者 LOCK IN SHARE MODE:
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, enable the innodb_locks_unsafe_for_binlog option and 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.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。