Deep understanding of MySQL isolation levels

Build table Insert test data
A> CREATE TABLE test (id int, num int);
Query OK, 0 rows affected (0.53 sec)

a> INSERT into test values ();
Query OK, 1 row affected (0.01 sec)

a> INSERT into test values (2,2);
Query OK, 1 row Affected (0.00 sec)

a> INSERT into test values (3,3);
Query OK, 1 row affected (0.01 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

A> Show create TABLE test;
+-------+------------------------------------------------------------------------------------------------------ ----------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ ----------------+
| Test | CREATE TABLE ' Test ' (
' id ' int (one) DEFAULT NULL,
' num ' int (one) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8 |
+-------+------------------------------------------------------------------------------------------------------ ----------------+
1 row in Set (0.00 sec)

One. Isolation level READ UNCOMMITTED, thing a can read uncommitted data of thing B, uncommitted data is called dirty data, so it is called dirty read.
A> Set Session transaction ISOLATION level READ UNCOMMITTED;
Query OK, 0 rows Affected (0.00 sec)

a> SELECT @ @tx_isolation;
+------------------+
| @ @tx_isolation |
+------------------+
| read-uncommitted |
+------------------+
1 row in Set (0.00 sec)

a> start transaction;
Query OK, 0 rows Affected (0.00 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

b> start transaction;
Query OK, 0 rows Affected (0.00 sec)

b> Update test set num=10 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0

B> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in Set (0.01 sec)

b> rollback;
Query OK, 0 rows affected (0.01 sec)

B> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

Two. Isolation level Read Committed,
For things B uncommitted DML operations, thing A is not visible
But thing a in the process of its own things (not commit or rollback), can read the submitted data of thing B, which is not repeatable read
Below the test:
A> Set Session transaction isolation level Read Committed;
Query OK, 0 rows Affected (0.00 sec)

a> start transaction;
Query OK, 0 rows Affected (0.00 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

B>start transaction;
Query OK, 0 rows Affected (0.00 sec)

B>update test set num=20 where id=1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

B>rollback;
Query OK, 0 rows affected (0.01 sec)

B>start transaction;
Query OK, 0 rows Affected (0.00 sec)

B>insert into test values (bis);
Query OK, 1 row Affected (0.00 sec)

B>select * from Test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 rows in Set (0.00 sec)

B>rollback;
Query OK, 0 rows affected (0.01 sec)

B>start transaction;
Query OK, 0 rows Affected (0.00 sec)

B>delete from test where id = 1;
Query OK, 1 row affected (0.01 sec)

B>select * from Test;
+------+------+
| ID | num |
+------+------+
| 2 | 2 |
| 3 | 3 |
+------+------+
2 rows in Set (0.00 sec)

B>rollback;
Query OK, 0 rows affected (0.01 sec)

When thing B does these DML operations, the query result of thing a remains the same, when thing a is not over.
A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

To commit an operation of thing B, the query result of the table test in thing a changes, so the query of the test table in thing A is not repeatable, so it is called non-repeatable read

Three. Isolation level REPEATABLE READ
MySQL's default thing isolation level prevents dirty reads and non-repeatable reads, but Phantom reads may occur
This isolation level resolves the non-repetition and ensures that the result of the query is the state (consistency) at the beginning of the transaction.
However, if another transaction commits new data at the same time, when the transaction is updated, the new data is "surprisingly" discovered, as if the previously read data were "ghost" hallucinations.

A> Set Session transaction ISOLATION level repeatable read;
Query OK, 0 rows Affected (0.00 sec)

A> SELECT @ @session. tx_isolation;
+------------------------+
| @ @session. tx_isolation |
+------------------------+
| Repeatable-read |
+------------------------+
1 row in Set (0.00 sec)

a> start transaction;
Query OK, 0 rows Affected (0.00 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

b> start transaction;
Query OK, 0 rows Affected (0.00 sec)

b> Update test set num=10 where id=1;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

B> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

B> commit;
Query OK, 0 rows affected (0.01 sec)

The modification of a here should be num=3, but because of the Phantom reading, the thing B is submitted, but the thing a reads the old record,
Like Phantom, when thing a changes, it finds that the result is 30, not the desired result 3
a> Update test set num=num*3 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 30 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

A> commit;
Query OK, 0 rows affected (0.01 sec)

Four. Isolation level Serializable
The serializable transaction isolation level is the most severe, and when a query is made, a shared lock is added to the table or row, and other transactions are read-only to the table and cannot be written.

A> Set Session transaction isolation level serializable;
Query OK, 0 rows Affected (0.00 sec)

A> SELECT @ @session. tx_isolation;
+------------------------+
| @ @session. tx_isolation |
+------------------------+
| SERIALIZABLE |
+------------------------+
1 row in Set (0.00 sec)

a> start transaction;
Query OK, 0 rows Affected (0.00 sec)

A> select * from test;
+------+------+
| ID | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 Rows in Set (0.00 sec)

b> start transaction;
Query OK, 0 rows Affected (0.00 sec)

b> Update test set num=3 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

Parameters to control timeout
Innodb_lock_wait_timeout | 120

Deep understanding of MySQL isolation levels