MYSQL-INNODB Transaction ISOLATION Level-repeatable Read detailed

http://blog.csdn.net/dong976209075/article/details/8802778

Experience Summary:

After Python uses the MYSQLDB database, such as using multiple threads, each thread creates a DB link, and then each creates a cursor cursor, where the first thread reads a table with NULL data, the second writes the table one piece of data and commits, and the first thread reads the table data still unreadable. Should be related to the transaction level of multiple databases, and you can also insert one before the first read, and you will be able to read the data written and submitted by the second thread.

Reprinted from: http://blog.csdn.net/alifel/article/details/6548075

One, transaction isolation level

The Ansi/iso SQL standard defines the transaction isolation level in 4: uncommitted read (READ UNCOMMITTED), commit read (Read committed), repeat read (repeatable read), serial read (serializable).

For different transactions, different isolation levels are used to separate the results. Different isolation levels have different phenomena. There are 3 main types now:

1. Dirty reads (dirty read): One transaction can read another modified data that has not yet committed a transaction.

2, non-repeating read (nonrepeatable read): In the same transaction, the same query reads a row at T1 time, the data in this row is modified, it may be updated (update), or deleted (delete) when the line is re-read at T2 time.

3, Phantom Read (Phantom Read): In the same transaction, the same query is repeated multiple times, due to the other insert operation (insert) of the transaction commits, resulting in a different result set is returned each time.

Different isolation levels have different phenomena, and there are different locking/concurrency mechanisms, the higher the isolation level, the worse the concurrency of the database, the 4 kinds of transaction isolation levels are shown in the following table:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
READ UNCOMMITTED	Allow	Allow	Allow
Read Committed		Allow	Allow
REPEATABLE READ			Allow
Serializable

Second, the default transaction isolation level in the database

The default transaction isolation level in Oracle is read committed.

The default transaction isolation level for MySQL InnoDB is read repeatedly (repeatable read). You can view it by using the following command:

Mysql> SELECT @ @GLOBAL. tx_isolation, @ @tx_isolation;

+ ——————— –+ ————— –+

| @ @GLOBAL. tx_isolation | @ @tx_isolation |

+ ——————— –+ ————— –+

| Repeatable-read | Repeatable-read |

+ ——————— –+ ————— –+

1 row in Set (0.00 sec)

Here's a test:

Time	Session 1	Session 2
T1	Set autocommit=0;	Set autocommit=0;
T2	Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 1 | +--+ ——— + 1 row in Set (0.00 sec)
T3		mysql> Update tmp_test set version=2 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0 Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 2 | +--+ ——— + 1 row in Set (0.00 sec)
T4	Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 1 | +--+ ——— + 1 row in Set (0.00 sec) Description Session 2 did not commit, see the data unchanged, no dirty read.
T5		Commit
T6	Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 1 | +--+ ——— + 1 row in Set (0.00 sec) Description Session 2 has been submitted, or you see the data unchanged, that is, can be repeated reading.
T7	Commit
T8	Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 2 | +--+ ——— + 1 row in Set (0.00 sec) Description Commit the transaction and see the latest data.
 	Mysql> insert into tmp_test values (2,1);   Query OK, 1 row Affected (0.00 sec) Mysql> select * from Tmp_test; +--+ ——— + | id   | version | +--+ ——— + |    1 |       2 | |    2 |       1 | +--+ ——— + 2 rows in Set (0.00 sec) Mysql> commit; Query OK, 0 rows Affected (0.00 sec)
T10	Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 2 | +--+ ——— + 1 row in Set (0.00 sec) Description The insert transaction for Session 2 has been submitted, and the data you see is the same as when you T8, that is, Phantom reads are not occurring.
T11	Mysql> commit; Query OK, 0 rows Affected (0.00 sec) Mysql> select * from Tmp_test; +--+ ——— + | ID | Version | +--+ ——— + |       1 | 2 | |       2 | 1 | +--+ ——— + 2 rows in Set (0.00 sec) Description Transaction commits, see the latest data.

The above results can be seen InnoDB repeated reads (REPEATABLE read) do not allow dirty reads, non-repeatable reads are not allowed (that is, they can be repeated, InnoDB are implemented using multiple versions of consistent reads), and Phantom reads are not allowed (this differs from the Ansi/iso SQL standard definition).

In addition, the same test:

1, when the session 2 to TRUNCATE TABLE, this time session 1 again query will not see the data.

2, when session 2 to make the ALTER TABLE, this time session 1 again query will not see the data.

The reason for this is that because of the persistent non-lock read of MySQL, the read is persistent non-locked read at the repeatable read level. See below for a related introduction:

Continuous reading means that InnoDB uses its multi-versioning to give a query a snapshot of the database at a point in time. The query sees changes made to the exact transactions that were committed before that point in time, and there are no changes to subsequent transactions or uncommitted transactions. The exception to this rule is that the query sees a change in the transaction itself that publishes the query.

If you run at the default repeatable read isolation level, all persistent read reads within the same transaction are taken by the snapshot established by the first such read in the transaction. You can get a fresher snapshot of your query by committing the current transaction and after the transaction for the new query is published.

Continuous reading is the default mode in which Innodbzai processes SELECT statements at the Read committed and repeatable read isolation levels. Continuous reading does not set the lock on any of the tables it accesses, so other users are free to modify the tables at the same time that they continue to read on a table.

Note that continuous reading does not work on drop table and ALTER TABLE. Continuous reading does not work on drop table because MySQL cannot use a table that has been removed, and InnoDB destroys the table. Continuous reading does not work on ALTER TABLE because it executes within a transaction, the transaction creates a new table, and inserts rows from the old table into the new table. Now, when you re-emit continuous reading, it cannot see any rows in the new table because they are inserted into a transaction that is not visible in a snapshot that reads continuously.

The reason is explained in the multi-version consistency read in the MySQL official documentation:consistent read does not work over certain DDL statements.

MYSQL-INNODB Transaction ISOLATION Level-repeatable Read detailed