MySQL consistency read in-depth study digdeep blog Learning

Http://www.cnblogs.com/digdeep/p/4947694.html

Consistent reads, also known as snapshot reads. The MVCC mechanism is used to read the data that has been committed in undo. So its read is non-blocking.

Related documents: http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in Ti Me. The query sees the changes made by transactions this committed before that point of time, and no changes made by later or Uncommitted transactions. The exception to this rule is, the query sees the changes made by earlier statements within the same transaction.

Consistent reads must read data that has been committed at some point in time, with a special exception: data that is modified in this transaction, even if uncommitted data can be read later in the transaction.

1. Differences in consistency reading in RC isolation and RR isolation

Consistent reads are different depending on the isolation level. The difference is in deciding whether to commit "a point in time":

1) for RR isolation:

If The transaction isolation Level REPEATABLE READ was (the default level), all consistent reads within the same transaction read the Snapshot established by the first such read on that transaction.

The document says: the first such read in that transaction. In fact, the results of the experiment show that the first such read here refers to the snapshot of consistent reads in the same table or a different table. Other update, DELETE, insert statement and consistent read snapshot are not related to the establishment. Data submitted after the snapshot is established, the consistent reading is not available, the data submitted before can be read.

The starting point of a transaction is actually the starting point of the first statement executed, not the starting point of the transaction with begin.

Lab 1:

Sesseion A	Session B
Mysql> set tx_isolation= ' Repeatable-read '; Query OK, 0 rows Affected (0.00 sec)	Mysql> set tx_isolation= ' Repeatable-read '; Query OK, 0 rows Affected (0.00 sec)
Mysql> begin; Query OK, 0 rows affected (0.01 sec)
	Mysql> select * from T1; Empty Set (0.00 sec) mysql> insert into T1 (C1,C2) values (n); Query OK, 1 row affected (0.01 sec)
Mysql> SELECT * FROM t1;+----+------+| C1 |  C2 |+----+------+|    1 | 1 |+----+------+1 row in Set (0.00 sec)

The above experiment shows that consistent reads under the RR isolation level do not establish a point in time as snapshot at the beginning of begin, but rather as the point in time of the first SELECT statement as snapshot.

Lab 2:

session A	Session B
mysql> set tx_isolation= ' repeatable-read ';	mysql> set tx_isolation= ' repeatable-read ';
 	mysql> select * from t1; empty set  (0.00 sec)
mysql> begin;mysql> select * from t ;	 
 	mysql> insert into t1 (C1,C2)  values ( ); query ok, 1 row affected  (0.01 sec)
mysql>  select * from t1; empty set  (0.00 sec)	 

The usage Note: Consistent read under RR isolation level is the point at which the execution point of the first SELECT statement is established as snapshot, even if it is a SELECT statement for a different table. Here, because session a performs a select on the T table before the insert, snapshot is established, so the subsequent select * from T1 cannot read the inserted value of the insert.

Lab 3:

session A	session B
mysql> ;  set tx_isolation= ' Repeatable-read ';	mysql> set tx_isolation= ' repeatable-read '; mysql> select * from  t1; empty set  (0.00 sec)
mysql> begin;	 
mysql> select * from t1; empty set  (0.00 sec)	mysql> select * from t1; empty set  (0.00 sec)
 	mysql>  INSERT INTO T1 (C1,C2)  values ();
mysql> select * from t1; empty set  (0.01 sec)	 

In this experiment: the first statement of session a occurs before the INSERT statement of Session B, so the second select in session a cannot read the data. Because a consistent read in a RR is a point in time established by the snapshot as the first SELECT statement in a transaction. At this point, the INSERT statement for session B has not been executed, so the data is not read.

Lab 4:

Session A	Session B
Mysql> set tx_isolation= ' Repeatable-read ';	Mysql> set tx_isolation= ' repeatable-read ';mysql> select * from T1; Empty Set (0.00 sec)
Mysql> select * from T1; Empty Set (0.00 sec)
	mysql> INSERT INTO T1 (C1,C2) values (UP), (2,2);mysql> select * FROM t1;+----+------+| C1 |  C2 |+----+------+|    1 |  1 | |    2 | 2 |+----+------+2 rows in Set (0.01 sec)
Mysql> select * from T1; Empty Set (0.00 sec)
mysql> Update T1 set c2=100 where c1=1; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 mysql> select * FROM t1;+----+------+| C1 |  C2 |+----+------+|  1 | |+----+------+1 row in Set (0.00 sec)

The experiment illustrates that the data that is modified in this transaction can be read later in this transaction, even if it is not committed. The UPDATE statement can be modified successfully because it is "currently read."

2) for RC isolation is much simpler:

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

Every read in a transaction is the actual point at which the current point of time is used to determine whether to commit, or reads its own fresh snapshot.

RC is a statement-level multi-version (transaction of multiple read-only statements, create different readview, the cost is higher), RR is a transaction-level multi-version (a readview);

3. When the transaction started in MySQL

In general, we would think that Begin/start transaction is the point at which a transaction begins, that is, once we execute the start transaction, we think that the transaction has started, which is actually wrong. The above experiment also illustrates this point. The real point in time (LSN) at which a transaction begins is the first statement executed after start transaction, regardless of the statement, whether successful or not.

But if you want to reach the point where start transaction starts as a transaction, then we have to use:

START TRANSACTION with consistent snapshot

It means: Execute start transaction while establishing the snapshot of this transactional consistent read. Instead of waiting until the first statement is executed, the transaction is started, and the snapshot of the consistent read is established.

The WITH CONSISTENT SNAPSHOT modifier starts a consistent readFor storage engines that is capable of it. This appliesInnoDB. The effect is the same as issuing aSTART TRANSACTIONFollowed by aSELECTFrom anyInnoDBTable. See section 14.2.2.2, "Consistent nonlocking Reads". TheWITH CONSISTENT SNAPSHOTModifier does the current transactionisolation level, so it provides a consistent snapshot only if the current Isolation level was one that permits a consistent read. The only isolation level, permits a consistent read isREPEATABLE READ. For all other isolation levels, theWITH CONSISTENT SNAPSHOTclause is ignored. As of MySQL 5.7.2, a warning is generated when theWITH CONSISTENT SNAPSHOTclause is ignored.

Http://dev.mysql.com/doc/refman/5.6/en/commit.html
The effect is equivalent to: After start transaction, a SELECT statement is executed immediately (a consistent read snapshot is established).

If The transaction isolation Level REPEATABLE READ was (the default level), all consistent reads within the same transaction read the Snapshot established by the first such read on that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries. (the snapshot of a consistent read in the RR isolation level is established when the first SELECT statement is executed, which should actually be established when any statement executes)

Http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

This is the statement we used in mysqldump--single-transaction :

SET session TRANSACTION Isolation level repeatable Readstart TRANSACTION/*!40100 with consistent snapshot */

So the start point of a transaction is divided into two situations:

1) Start TRANSACTION, is the execution time point of the first statement, that is, the point at which the transaction begins, and the first SELECT statement establishes a consistent read snapshot;

2) Start TRANSACTION with consistent snapshot, it is immediately established the consistency of the transaction read snapshot, of course, also started the business;

Lab 1:

Session A	Session B
Mysql> set tx_isolation= ' Repeatable-read ';	Mysql> set tx_isolation= ' Repeatable-read ';
	Mysql> select * from T1; Empty Set (0.01 sec)
mysql> start transaction;
	mysql> INSERT INTO T1 (C1,C2) values (+);
Mysql> SELECT * FROM t1;+----+------+| C1 |  C2 |+----+------+|    1 | 1 |+----+------+1 row in Set (0.00 sec)

Lab 2:

mysql> set tx_ isolation= ' Repeatable-read ';	mysql> set tx_isolation= ' repeatable-read ';
 	mysql> select * from t1; empty set  (0.01 sec)
mysql> start transaction with con Sistent snapshot;	 
 	mysql> insert into t1 (C1,C2)  values ( );
mysql> select * from t1; empty set  (0.00 sec)	 

The above two experiments well illustrate the difference between start transaction and start tansaction with consistent snapshot. The first experiment shows that after start transaction executes, the transaction does not start, so insert occurs before session A's transaction begins, so you can read the value inserted in session B. The second experiment shows that start transaction with consistent snapshot has started the transaction, so the INSERT statement occurs after the transaction begins, so the insert data is not read.

3. Consistent read in Oracle

Oracle read consistency refers to the data that a query obtains from the same point in time .

Oracle read consistency is divided into statement-level read consistency and transactional-level read consistency.

statement-level Read consistency : Oracle enforces statement-level read consistency. A query statement reads only the data that was committed before the start of the statement.

Transactional-level read consistency : transaction-level read consistency is supported for transactions with isolation levels of serializable and read only. All query statements in a transaction read only the data that was committed before the transaction started.

Oracle implements only RC and serializable, and does not implement read uncommitted and RRs. In fact, the serializable level of Oracle achieves repeatable reading.

4. Current read and consistent read

Consistent read refers to a normal SELECT statement, without a for update, in share mode, and so on. Using the data submitted in Undo, no locks are required (except MDL). while the current read, refers to the update, delete, select for Update, the select in Share mode and other statements to read , they read the database of the latest data, and will lock the read line and Gap (RR isolation). If a lock cannot be obtained, it waits until it obtains or times out. The current read of the RC isolation level does not have a "semi-consistent read" in the UPDATE statement for Gap LOCK,RC, and the current read of the RR's UPDATE statement is not the same.

5. Consistent reading and mysqldump--single-transaction

We know that the principle of mysqldump--single-transaction is to set the transaction to RR mode, and then take advantage of the transactional characteristics to obtain consistent data, but:

--single-transaction
Creates a consistent snapshot by dumping all tables in a
Single transaction. Works Tables stored in
Storage engines which support multiversioning (currently
only InnoDB does); The dump is not a guaranteed to be
Consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
Valid dump file (correct table contents and binary log
Position),No other connection should use the following
statements:alter table, DROP table, RENAME table ,
TRUNCATE TABLE, as consistent snapshot is not isolated
From them.Option automatically turns off--lock-tables.

The DDL statements for ALTER TABLE, DROP table, rename table, truncate TABLE, and so on cannot be executed during the mysqldump run, because consistent reads and these statements cannot be isolated.

So what happens when the above DDL is executed during the execution of the mysqldump--single-transaction?

The mysqldump--single-transaction is executed by setting the RR, then starting the transaction, corresponding to an LSN, and then performing the following procedure on all of the selected tables, one by one:

Save Point SP; --select * FROM T1--and rollback to SP;

Save Point SP; --select * FROM T2--and rollback to SP;

... ...

1> Then if the DDL on the T2 table occurs before the save Point SP, mysqldump will immediately error when the mysqldump is processed to the T2 table: The table structure has changed ...

2> If the DDL on the T2 table occurs after the save point SP, before the rollback to SP, either the DDL is blocked or the mysqldump is blocked, and the specific person is blocked to see who executed it first.

The blocked amount is due to the fact that the DDL requires the T2 of the MDL of the table, while the SELECT * from T1 requires the shared lock of the MDL, so the blocking occurs.

3> If the DDL on the T2 table occurs after the rollback to SP, there will be no error or blocking because the dump of the T2 table is complete.

So why: The DDL on the T2 table precedes the save point SP, so mysqldump immediately error when mysqldump starts processing the T2 table?

The reason is consistent reading of the arm AO however DDL of the thighs:

Consistent read does not work over certain DDL statements: (Consistency read arm AO however DDL thigh)

• Consistent read does not work over  DROP TABLE , because MySQL cannot use a TABLE this has been En 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 A Re not visible because those rows did not exist when the transaction ' s snapshot were taken. In this case, the transaction returns an error as of MySQL 5.6.6:  er_table _def_changed , "Table definition has CHANGED, please retry transaction".

Reason:ALTER table, DROP table, RENAME table, TRUNCATE table The execution of these DDL statements causes the correct consistency read to be constructed using undo, consistent reads, and they cannot be isolated.

MySQL consistency read in-depth study digdeep blog Learning