MySQL transaction isolation level and problematic reading (dirty reading, repeatable reading, phantom reading)

1. Problematic reads in transactions: dirty reads, non-repeated reads, Phantom reads

The dirty read transaction T1 updates the content of a row of records, but does not commit the modifications. Transaction T2 reads the updated row, and T1 executes the rollback operation to cancel the modification. The row read by T2.

Nonrepeatable read transaction T1 reads a row of records, and then transaction T2 modifies the row of records that T1 just read. Then T1 reads the record again and finds that it is different from the result just read. This is called "repeatable" reading, because the row record originally read by T1 has changed.

Phantom read transaction T1 reads the result set returned by a specified where clause. Then, transaction T2 inserts a new row of records. This row of records exactly meets the WHERE clause condition in the query conditions used by T1. Then T1 uses the same query to retrieve the table again, but now the new row inserted by transaction T2 is displayed. This new line is called "phantom", because for T1, this line appears suddenly.

2. transaction isolation level

From level to height:

Read uncommitted Phantom reads, non-repeated reads, and dirty reads are allowed.

Read committed allows the phantom to be read and cannot be read repeatedly, but dirty reading is not allowed.

Repeatable read allows phantom reading, but does not allow repeated and dirty reading. Default InnoDB level

Serializable Phantom reads, non-repeated reads, and dirty reads are not allowed.

However, the Repeatable read isolation level of InnoDB is different from that of other databases and does not cause phantom read ).

The Oracle Database supports Read committed and serializable, but does not support read uncommitted and Repeatable read.

3. test:

1) For concurrent updates, the ID of a record in Table tab1 is 1 num = 1, and the two sessions execute transaction 1 and 2 respectively.

Enter the following statements respectively.

Transaction 1 (session1 ):

Start transaction;

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

==========

When a record is updated, select finds num = 2;

Transaction 2 (session2 ):

Start transaction;

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

==========

Transaction 2 will be blocked, and session1 will input commit; Commit transaction 1. At this time, transaction 2 is updated successfully.

Session1 select will find num or 2 (dirty read is not allowed); session2 select num is 3 (update can get the latest submitted data and then update, but if there is no update, if it is always select, The num obtained by select is always 1), and then commit;

Session1 select finds that num is 3, session2 select finds that num is 3;

Therefore, update in a transaction has a row write lock (exclusive lock) and does not occur: dirty reads and non-repeated reads (for transactions with update itself, after update, you can read the latest data. Is this an exception? ^_^ ),

2) InnoDB does not display the default transaction isolation level for Phantom reads. from the Internet, the test passes:

Mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 Sec)

Session 1: Create a table and insert 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 a transaction

Mysql> commit;
Query OK, 0 rows affected (0.00 Sec)
Session 2 query, no data, no phantom read

Mysql> select * from test;
Empty set (0.00 Sec)
4. finally, we should know that the concurrency control of the database is not complex, and only the transactions handed over to the database can be done. MySQL selects the InnoDB engine and does not display the following: "repeatable reading" (that is, when transaction 1 modifies data, the data read by transaction 2 is the data before the modification. When transaction 1 is committed, when transaction 2 reads the modified data again, the data read in a single transaction is inconsistent.), "dirty read", "Phantom read ";

For a detailed explanation of the InnoDB Lock Mechanism, see MySQL manual-"15.2.10. InnoDB Transaction Model and locking ".