Transaction isolation level of the MySQL InnoDB Storage Engine

We know that there are four transaction isolation levels in the relational database standard:

Uncommitted read (read uncommitted): Dirty read is allowed, that is, data that may be read from other sessions that have not committed transaction modifications.

Read committed: only committed data can be read. Most databases such as Oracle are at this level by default.

Repeated read. The queries in the same transaction are consistent at the start time of the transaction, and the InnoDB default level. In the SQL standard, this isolation level eliminates non-repeated reads, but Phantom reads still exist.

Serializable: Fully serializable read. A table-level shared lock is required for each read, which blocks read/write operations.

View the transaction isolation level at the InnoDB system level:

Reference content is as follows:
Mysql> select @ Global. tx_isolation;
+ ----------------------- +
| @ Global. tx_isolation |
+ ----------------------- +
| REPEATABLE-READ |
+ ----------------------- +
1 row in SET (0.00 Sec)
 

View the transaction isolation level at the InnoDB session level:

Reference content is as follows:
Mysql> select @ tx_isolation;
+ ----------------- +
| @ Tx_isolation |
+ ----------------- +
| REPEATABLE-READ |
+ ----------------- +
1 row in SET (0.00 Sec)
 

Modify the transaction isolation level:

Reference content is as follows:
Mysql> set global transaction isolation level read committed;

Query OK, 0 rows affected (0.00 Sec)

Mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 Sec)
 

The Repeatable read isolation level of InnoDB is different from that of other databases, and does not result in phantom read. The so-called phantom read refers to multiple select statements in the same transaction, you can read data that has been inserted into other sessions and committed. The following is a small test that proves that InnoDB's Repeatable read isolation level will not cause phantom read. The test involves two sessions: session 1 and Session 2. The isolation level is repeateable read and autocommit is disabled.

Reference content is as follows:
Mysql> select @ tx_isolation;
+ ----------------- +
| @ Tx_isolation |
+ ----------------- +
| REPEATABLE-READ |
+ ----------------- +
1 row in SET (0.00 Sec)

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)

The above test version:

Mysql> select version ();
+ ------------------------- +
| Version () |
+ ------------------------- +
| 5.0.37-Community-NT-log |
+ ------------------------- +
1 row in SET (0.00 Sec)