Consistent read
Consistent read refers to that InnoDB uses multiple versions to query the snapshot of the database at a certain point in time. This query can see the changes made by the transactions submitted before this time point and will not be affected by the later changes or uncommitted transactions. However, this rule does not apply to the modification of earlier statements in the same transaction, which will cause the following exceptions: if you update some rows in the table, you may see the latest version of the updated row or the old version of any row in one SELECT; if other sessions update to the same table at the same time, you may see that the table is in a state that has never existed in the database.
Multi version concurrency control
- When the transaction isolation level is REPEATABLE READ, the consistent reads in the same transaction are all the snapshots created by the first query of the transaction.
We can go through
- When the transaction isolation level is READ COMMITTED, consistent reads under the same transaction will establish and read the latest snapshot of the query itself
Consistent read is the default mode for InnoDB to process SELECT statements in REPEATABLE READ and READ COMMITTED transaction isolation. Consistent read does not set any lock on the table, so other sessions can read and write to the table.
A snapshot of the database state applies to a SELECT statement in a transaction, not necessarily to a DML statement. If some rows of INSERT or UPDATE are executed and then the transaction is committed, the DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction will affect those data rows that have just been committed. The following example shows this scenario:
Session A Session B
begin; begin;
-------------------------------------------------------------------------------------
SELECT * FROM t; SELECT * FROM t;
> empty set > empty set
-------------------------------------------------------------------------------------
INSERT INTO t (id) VALUES (1);
> 1 row affected
SELECT * FROM t;
-----------
| id |
-----------
| 1 |
-----------
-------------------------------------------------------------------------------------
SELECT * FROM t;
> empty set
-------------------------------------------------------------------------------------
COMMIT;
-------------------------------------------------------------------------------------
SELECT * FROM t;
> empty set
-------------------------------------------------------------------------------------
DELETE FROM t WAERE id = 1;
> 1 row affected
-------------------------------------------------------------------------------------
SELECT * FROM t;
-----------
| id |
-----------
| 1 |
-----------
-------------------------------------------------------------------------------------
COMMIT;
-------------------------------------------------------------------------------------
SELECT * FROM t;
> empty set
-------------------------------------------------------------------------------------
SELECT * FROM t;
-----------
| id |
-----------
| 1 |
-----------
-------------------------------------------------------------------------------------- Consistent reads are not available for DROP TABLE because the table has been destroyed by InnoDB
- Consistent reads do not apply to ALTER TABLE because ALTER TABLE is actually a temporary table that generates an original table and deletes the original table after the build is complete. When a consistent read occurs in a transaction, the rows in the new table are not visible. In this case, the transaction will return an Er? Table? Def? Changed error (the table definition has changed, please retry the transaction)
INSERT INTO without specifying FOR UPDATE or LOCK IN SHARE MODE SELECT，UPDATE … (select) and CREATE TABLE There are the following differences in reads in statements such as:
- By default, just like READ COMMITTED, each consistent read creates and reads its own snapshot, even in the same transaction
- If InnoDB ﹐ locks ﹐ unsafe ﹐ for ﹐ binlog is set to enable and the transaction isolation level is not SERIALIZABLE, the read operation will not be locked again