Translation Analysis of the mechanism of consistency reading for MySQL without lock

Original train: Consistent nonlocking Reads

MySQL's consistent read mechanism is implemented as follows: The InnoDB engine provides a snapshot of the time T1 for a transactional TX (T1 is the

The point in time at which the query statement was first executed in the transaction. The data submitted before the point-in-time T1 can be queried in the transaction TX, and the data submitted after the point-in-time T1

It is not visible in TX. The only exception is that the data submitted in this transaction can be seen in transaction TX (that is, data that has not been committed at T1 Point in time).

First build a table, side theory side practice, specifically see how MySQL works.

Mysql> Create TableMVCC ( -Idint Primary Key,     -Namevarchar( -),     -Cityvarchar( -)     -) Engine InnoDBdefaultCharSet UTF8 - ; Query OK,0Rows Affected (0.01Sec

Create a new database connection, assuming Session1, do the following in Session1:

Mysql> SetSessionTransaction Isolation  Level Repeatable Read; Query OK,0Rows Affected (0.00sec) MySQL> SetAutocommit=off; Query OK,0Rows Affected (0.00sec) MySQL> Insert  intoMVCC (id,name,city)Values(1,'name1','city1'); Query OK,1Row affected (0.00sec) MySQL> Select *  fromMVCC; T1 +----+-------+-------+|Id|Name|City|+----+-------+-------+|  1 |Name1|City1|+----+-------+-------+1Rowinch Set(0.00Sec

As you can see in the above experiment, the uncommitted data that was inserted before T1 time in the transaction TX is visible in the new snapshot of T1 time.

This is said to be a snapshot, in fact, is not a strict snapshot (in fact, the T1 point to make a mark, T1 after the data submitted by other transactions, using the Undo log rollback, the old data)

This time, in fact, the transaction TX, and other transactions, can update the snapshot data at T1 point in time.

This exception causes the following exceptions: If you update the table data in TX, the SELECT statement in TX will see the data for these updated data, but at this point the select may also see

Some rows of older data (T1 have other transactions after the update of the data, but in the transaction TX is not visible to these updates, even if the other transaction commits, all above are in the case of repeatable read isolation level)

This is an unusual smell. The state of the database is not the state that the select of the transaction TX sees.

If the isolation level of the database is repeatable read isolation level (the default level for MySQL), all of the SELECT statements in the transaction TX are seen in the same snapshot as the first select in TX

The data you see is the same. If you first get a new snapshot in TX, you can only commit the transaction first, and in the newly opened transaction

If the isolation level of the database is the Read committed level, each select operation in TX will be re-updated with a new snapshot.

Consistency reads the default behavior of the InnoDB engine when executing a SELECT statement under the Read committed and repeatable read isolation levels. Consistent read does not add on the table you read

Lock, so other sessions can also modify the table's data at the same time.

If your MySQL database is running at the default repeatable read isolation level. When you issue a consistent read (that is, the normal SELECT statement). InnoDB will give you the business

Given a point-in-time t, this point-in-time t is the point in time when you execute a SELECT statement in a transaction. If the database server has given you a point in time for your current transaction and other transactions have deleted the data, it is not visible in your transaction that the data was deleted. Other transaction execution insert your current transaction also does not see the new data, other transactions do update your current transaction also sees the updated data.

Attention:

These database snapshots are typically used to do data read operations (select) in a single transaction. Not used in DML (INSERT,UPDATE,DELETE) statements.

Consider the following scenario:

If you modify or insert a record in a transaction and commit the transaction. Another repeatable read transaction TX2 is not visible to them. Although these changes or new data are not visible, TX2

You can delete the data you just added (unbeliveable), and the experiment explains everything.

Experiment with the following:

Snapshot (Execute SELECT statement) in transaction of Session1 at REPEATABLE read isolation level

Step 1

Mysql> Select @ @session. tx_isolation;+------------------------+| @ @session. tx_isolation|+------------------------+| Repeatable-READ        |+------------------------+1Rowinch Set(0.00sec) MySQL>StartTransaction; Query OK,0Rows Affected (0.00sec) MySQL> Select *  fromMVCC;+----+-----------------+-------+|Id|Name|City|+----+-----------------+-------+|  1 |Name1-not-New-1 |City1||  2 |Name2-not-New|City2||  3 |Name3|City3|+----+-----------------+-------+3Rowsinch Set(0.00Sec

Add a new piece of data to the transaction of another Session2

Step 2

Mysql> Select *  fromMVCC;+----+-----------------+-------+|Id|Name|City|+----+-----------------+-------+|  1 |Name1-not-New-1 |City1||  2 |Name2-not-New|City2||  3 |Name3|City3|+----+-----------------+-------+3Rowsinch Set(0.00sec) MySQL> Insert  intoMVCC (id,name,city)Values(4,'Name4','City4'); Query OK,1Row affected (0.00sec) MySQL> Commit; Query OK,0Rows Affected (0.00sec) MySQL> Select *  fromMVCC;+----+-----------------+-------+|Id|Name|City|+----+-----------------+-------+|  1 |Name1-not-New-1 |City1||  2 |Name2-not-New|City2||  3 |Name3|City3||  4 |Name4|City4|+----+-----------------+-------+4Rowsinch Set(0.00Sec

View the data just now in Session1 and find that it is not visible, but the deletion will succeed.

Step 3

Mysql> Select *  fromMVCC;+----+-----------------+-------+|Id|Name|City|+----+-----------------+-------+|  1 |Name1-not-New-1 |City1||  2 |Name2-not-New|City2||  3 |Name3|City3|+----+-----------------+-------+3Rowsinch Set(0.00sec) MySQL> Delete  fromMvccwhereId=4; Query OK,1Row affected (0.00Sec

This is multi-versioned concurrency control (abbreviated MVCC)

In the following example, session a can only see the data submitted by B after Sessionb commit and session a commit. Sessionb submitted with Sessiona in this interval Sessiona "missed" sessionb data

Session A Session BSETAutocommit=0;SETAutocommit=0; time|          SELECT *  fromT;|EmptySet|                                 INSERT  intoTVALUES(1,2);|vSELECT *  fromT; EmptySet                                  COMMIT; SELECT *  fromT; EmptySet           COMMIT; SELECT *  fromT; ---------------------           |    1    |    2    |           ---------------------           1Rowinch Set

If you want to see the data in Sessionb commit to session a commit during Sessiona, you can use the following syntax

SELECT * FROM T-LOCK in SHARE MODE;