Transaction isolation level for MySQL

The SQL standard defines 4 isolation levels, including specific rules that define which changes within and outside a transaction are visible and which are not.

Low-level isolation levels generally support higher concurrency processing and have lower system overhead.

First, we use the test database, create a new TX table, and open two windows to manipulate the same database:

Level 1th: READ UNCOMMITTED (reads uncommitted content)
(1) All transactions can see the execution results of other uncommitted transactions
(2) This isolation level is rarely used in real-world applications because it has no better performance than other levels
(3) The problem raised by this level is-dirty read (Dirty Read): READ UNCOMMITTED data

#首先, modify the isolation level
Set tx_isolation= ' read-uncommitted '; select @ @tx_isolation; +------------------+| @ @tx_isolation |+------------------+| read-uncommitted |+------------------+ #事务A: Start a transaction start Transaction;select * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |    1 | |    2 |    2 | |    3 | 3 |+------+------+ #事务B: Also initiates a transaction (then two transactions crossed)
Execute UPDATE statement in transaction B without committing start transaction;update TX set num=10 where Id=1;select * from tx;+------+------+| ID |    Num |+------+------+|   1 |    10 | |    2 |    2 | |    3 | 3 |+------+------+ #事务A: So at this point does transaction a see the updated data? SELECT * FROM tx;+------+------+| ID | Num |+------+------+| 1 |   10 | ---> Can see! That means we've read the data that transaction B hasn't yet committed.|    2 |    2 | |    3 | 3 |+------+------+ #事务B: Transaction b rollback, still uncommitted rollback;select * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |    1 | |    2 |    2 | |    3 | 3 |+------+------+ #事务A: What you see in transaction A is also B data not submitted SELECT * FROM tx;+------+------+| ID |    Num |+------+------+|    1 | 1 |---> Dirty reads mean I am in this transaction (a), transaction B Although not committed, but it any data changes, I can see! |    2 |    2 | |    3 | 3 |+------+------+

Level 2nd: Read Committed (reading submissions)

(1) This is the default isolation level for most database systems (but not MySQL default)
(2) It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm
(3) The problem with this isolation level is-non-repeatable read (nonrepeatable Read): Non-repeatable reading means that we may see a different result when we execute the exact same SELECT statement in the same transaction.
|--> This can be caused by: (1) There is a new commit for a cross transaction, which results in a change in the data; (2) When a database is manipulated by multiple instances, other instances of the same transaction may have a new commit during the instance processing

#首先修改隔离级别
Set tx_isolation= ' read-committed '; select @ @tx_isolation; +----------------+| @ @tx_isolation |+----------------+| read-committed |+----------------+ #事务A: Start a transaction start Transaction;select * FROM tx;+------+------+| ID |    Num |+------+------+|    1 |    1 | |    2 |    2 | |    3 | 3 |+------+------+ #事务B: Also initiates a transaction (then two transactions crossed)
The data is updated in this transaction, and the start transaction;update TX set num=10 where Id=1;select * from tx;+------+------+| is not committed ID |    Num |+------+------+|   1 |    10 | |    2 |    2 | |    3 | 3 |+------+------+ #事务A: Can we see the data change in transaction A at this time? SELECT * FROM TX; --------------->+------+------+ | | ID |                num |    |+------+------+                ||    1 | 1 |---> Can't see! ||    2 |                2 |    ||    3 |                3 | |+------+------+ |-->the same SELECT statement, but the result is different| #事务B: What if transaction B was committed?                           |commit;                                  | | #事务A: |select * from TX; --------------->+------+------+| ID |    Num |+------+------+|   1 | 10 |---> Because transaction B has already been submitted, we see a change in the data in a|    2 |    2 | |    3 | 3 |+------+------+

Level 3rd: Repeatable Read (can be reread)
(1) This is the default transaction isolation level for MySQL
(2) It ensures that multiple instances of the same transaction will see the same data row when concurrently reading the data
(3) Possible problems with this level-phantom read: When a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new "phantom" row is found.
(4) The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism

#首先, change the isolation level
Set tx_isolation= ' Repeatable-read '; select @ @tx_isolation; +-----------------+| @ @tx_isolation  |+-----------------+| Repeatable-read |+-----------------+ #事务A: Start a transaction start Transaction;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | |    2 |    2 | |    3 |    3 |+------+------+ #事务B: Open a new transaction (then the two transactions intersect)
Update data in transaction B and submit start Transaction;update TX Set num=10 where Id=1;select * from tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+commit; #事务A: Even if transaction B is already committed, can a see the data change? SELECT * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | ---> Can not be seen! (This level 2 is different, also indicates that level 3 resolves non-repeatable read issues) |    2 |    2 | |    3 |    3 |+------+------+ #事务A: Only if transaction A is committed, can it see data changes Commit;select * from tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+

Level 4th: Serializable (Serializable)
(1) This is the highest isolation level
(2) It solves the Phantom reading problem by forcing the transaction to sort and make it impossible to collide with each other. In short, it is a shared lock on every data row read.
(3) At this level, a large number of timeouts and lock competitions can result

#首先修改隔离界别
Set tx_isolation= ' serializable '; select @ @tx_isolation; +----------------+| @ @tx_isolation |+----------------+| SERIALIZABLE   |+----------------+ #事务A: Starts a new transaction start transaction; #事务B: This cross-transaction is the start of the data that cannot be changed before a commit Transaction;insert TX VALUES (' 4 ', ' 4 '); ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting Transactionupdate TX set num=10 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

Reference article:

Transaction isolation level for MySQL