MySQL Isolation Level table

Isolation Level	Dirty Reads (Dirty read)	non-repeatable read (nonrepeatable Read)	Phantom Read (Phantom Read)
Uncommitted read (READ UNCOMMITTED)	possible	possible	possible
Read Committed	No way	possible	possible
REPEATABLE READ (Repeatable Read)	No way	No way	possible
Serializable (Serializable)	No way	No way	No way

• Dirty reads: One transaction reads data that is not committed by another transaction;
  For example: Transaction T1 updated the contents of a row of records, but did not commit the changes. The transaction T2 reads the T1 updated row, and T1 performs a rollback operation, canceling the modification that was just made. Now the line read by T2 is invalid;

• Non-repeatable READ: In the same transaction, two reads the same data, gets the content different;
  For example: Transaction T1 reads a row of records, and the transaction T2 modifies the line of records that 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;

• Phantom reading: In the same transaction, the same operation is read two times, the number of records obtained is not the same;
  For example: 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.

The lower the isolation level, the less lock the transaction requests or the less time it takes to hold the lock. The default support isolation level for the INNODB storage engine is repeatable READ, which, under this default transaction isolation level, fully guarantees the isolation of the transaction, which is serializable level isolation to the SQL standard.

We can use the SET TRANSACTION statement to change the isolation level of a single session or all new incoming connections. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION Isolation Level {READ UNCOMMITTED | READ COMMITTED | Repeatable READ | SERIALIZABLE}

Note: The default behavior (without session and global) is to set the isolation level for the next (not started) transaction. If you use the Global keyword, the statement sets the default transaction level globally for all new connections created from that point, except for connections that do not exist. You need super privilege to do this. Use the session keyword to set the default transaction level for future transactions performed on the current connection. Any client can freely change the session isolation level (even in the middle of a transaction) or set the isolation level for the next transaction.

Mysql> Set Session transaction ISOLATION level repeatable read; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT @ @tx_isolation; +-----------------+| @ @tx_isolation |+-----------------+| Repeatable-read |+-----------------+1 row in Set (0.00 sec)

MySQL Isolation Level table