MySQL transaction isolation level and problematic reads (dirty read, non-repeatable read, Phantom Read)

1. Some problematic reads in the transaction: dirty read, non-repeatable read, Phantom read

The dirty read (Dirty Read) transaction T1 updated the contents of a row of records, but did not commit the changes. The transaction T2 reads the updated row, and then T1 performs the rollback operation, canceling the modification that was just made. Now the line read by T2 is invalid.

The non-repeatable read (nonrepeatable Read) transaction T1 reads a row of records, and the transaction T2 modifies the row of records that the T1 just read. The T1 then reads the line again and finds that it differs from the result just read. This is referred to as "non-repeatable" reading because the row record that T1 originally read has changed.

The Phantom Read (Phantom Read) transaction T1 reads a result set returned by a specified where clause. The transaction T2 then inserts a new row of records that exactly satisfies the condition of the WHERE clause in the query condition used by T1. Then T1 again uses the same query to retrieve the table again, but at this point you see the new row that the transaction T2 just inserted. This new line is called "Phantom", because for T1 this line is like a sudden appearance.

2. Isolation level of a transaction

From the lowest level to the highest:

Read uncommitted is allowed for Phantom reads, non-repeatable reads, and dirty reads.

Read COMMITTED allows for Phantom reads, non-repeatable reads, but dirty reads are not allowed.

repeatable Read allows Phantom reads, but does not allow non-repeatable reads and dirty reads. InnoDB Default Level

SERIALIZABLE Phantom reads, non-repeatable reads, and dirty reads are not allowed.

However, InnoDB's repeatable read isolation level differs from the repeatable reads of other databases and does not cause Phantom reads (Phantom Read).

The Oracle database supports read COMMITTED and SERIALIZABLE , and does not support READ UNCOMMITTED and repeatable read .

3. Test:

1) Concurrent update , table tab1 a record id=1 num=1, two sessions executed separately

Enter the following statement, respectively

Transaction 1 (Session1):

Start transaction;

Update TAB1 set num=num+1 where id=1;

=========

A record was updated, select found num=2;

Transaction 2 (Session2):

Start transaction;

Update TAB1 set num=num+1 where id=1;

=========

Transaction 2 is blocked and then session1 input commit; COMMIT TRANSACTION 1. The transaction 2 Update succeeds at this time.

Session1 Select will find Num or 2 ( dirty read not allowed ); Session2 Select num is 3 (updatecan get the latest submitted data and then update, but if there is no update, Always a SELECT, the select gets the num always 1 ), then commit;

Session1 Select Found num is 3, session2 select found Num is 3;

Therefore, the update in a transaction has a row write lock (exclusive lock) and does not occur: dirty read and non-repeatable read ( for transactions that have an update of their own, it is possible to read the latest data after the update, is this an exception?). ^_^ ),

2) about Phantom reads , INNODB default transaction isolation level is not present; From the Web, the test passes:

Mysql> set Autocommit=off;
Query OK, 0 rows Affected (0.00 sec)

Session 1 creating a table and inserting test data

Mysql> CREATE TABLE Test (i int) engine=innodb;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO test values (1);
Query OK, 1 row Affected (0.00 sec)

Session 2 query, no data, normal, Session1 not submitted, dirty read not allowed

Mysql> select * from test;
Empty Set (0.00 sec)

Session 1 COMMIT Transaction

Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)

Session 2 query, or no data, no phantom read generated

Mysql> select * from test;
Empty Set (0.00 sec)

4. Finally, it should now be known that the database concurrency control is not complex, to the database of the transaction, MySQL chooses the InnoDB engine, does not appear: "Non-repeatable read" (that is, when the transaction 1 data modification, transaction 2 read the data is not modified before the data, When the transaction 1 commits, the transaction 2 reads again when the data is modified, the data in a single transaction is inconsistent), "dirty read", "Phantom read";

For a detailed explanation of the INNODB lock mechanism, see the MySQL manual-"15.2.10." InnoDB Transaction model and lock ".

MySQL transaction isolation level and problematic reads (dirty read, non-repeatable read, Phantom Read)