The transaction isolation level of the MySQL InnoDB storage engine

As we know, there are four transaction isolation levels in the relational database standard:

Uncommitted read (READ UNCOMMITTED): Allow dirty reads, that is, data that may be read to uncommitted transaction modifications in other sessions

Commit read (Read Committed): Only data that has been submitted can be read. Most databases, such as Oracle, default to this level

Repeatable read (repeated read): Repeatable read. Queries within the same transaction are always at the beginning of the transaction, InnoDB the default level. In the SQL standard, the isolation level eliminates the need for repeatable reads, but there are also phantom reads

Serial Read (Serializable): Fully serialized read, each read need to obtain table-level shared lock, read and write each other will block

To view the transaction isolation level at the INNODB system level:

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ　　　|
+-----------------------+
1 row in set (0.00 sec)

To view the transaction isolation level at the InnoDB session level:

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation　|
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

To modify the transaction isolation level:

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)