MySQL supports four transaction isolation levels and mysql supports four types of transactions.

MySQL supports four transaction isolation levels and mysql supports four types of transactions.

Test environment of this experiment: Windows 10 + MySQL5.6.36 + InnoDB

I. Basic Elements of transactions (ACID)

　　1. Atomicity: All operations after the transaction starts, either completed or not done, cannot be stuck in the intermediate stage. If an error occurs during the transaction execution, it is rolled back to the state before the start of the transaction. All the operations are the same as none. That is to say, a transaction is an inseparable whole, just like an atom learned by chemistry, which is the basic unit of material composition.

2. Consistency: The integrity constraints of the database are not damaged before and after the transaction starts and ends. For example, if A transfers money to B, it is impossible for A to deduct the money, but B does not receive the money.

3. Isolation: At the same time, only one transaction can request the same data. Different transactions have no interference with each other. For example, if A is taking money from A bank card, B cannot transfer money to the card before A's withdrawal.

4. Durability: after the transaction is completed, all updates to the database by the transaction will be saved to the database and cannot be rolled back.

Conclusion: Atomicity is the foundation of transaction isolation, isolation and persistence are the means, and the ultimate goal is to maintain data consistency.

 

Ii. Transaction concurrency

1. Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back, then the data read by transaction A is dirty data.

2. Non-repeated read: Transaction A reads the same data multiple times, and transaction B updates and submits the data while transaction A reads the data multiple times, as A result, transaction A reads the same data multiple times and the results are inconsistent.

3. Phantom read: System Administrator A changes the score of all the students in the database from the specific score to the ABCDE grade, but System Administrator B inserts A specific score record at this time, after System Administrator A completes the change, he finds that another record has not been changed, just like an illusion. This is called phantom read.

Summary: Non-repeated reads and Phantom reads are easy to confuse. Non-repeated reads focus on modifications, and Phantom reads focus on addition or deletion. To solve the problem of non-repeated read, you only need to lock the rows that meet the conditions and solve the phantom read need to lock the table.

 

Iii. MySQL transaction isolation level

Transaction isolation level	Dirty read	Non-repeated read	Phantom read
Read-uncommitted)	Yes	Yes	Yes
Read-committed)	No	Yes	Yes
Repeatable-read)	No	No	Yes
Serializable)	No	No	No

 

 

 

 

 

The default transaction isolation level of mysql is repeatable-read.

 

4. Use examples to illustrate the isolation levels

1. Read not submitted:

(1) Open A Client A and set the current transaction mode to read uncommitted (uncommitted read). query the initial values of the Table account:

 

 

(2) Before the transaction commit of Client A, open another client B and update the table account:

 

 

(3) at this time, although the transaction of client B has not been committed, client A can query the data updated by client B:

 

 

(4) Once the transaction of client B is rolled back for some reason, all operations will be canceled, and the data queried by Client A is actually dirty data:

 

 

2. Read committed

(1) Open A Client A, set the current transaction mode to read committed (uncommitted read), and query the initial values of the Table account:

 

(2) Before the transaction commit of Client A, open another client B and update the table account:

 

(3) At this time, the transaction of client B has not been committed, and client A cannot query the data updated by client B, solving the dirty read problem:

 

(4) At this time, client B's transaction is committed, and client A executes the same query as the previous one. The results are inconsistent with the previous one, which leads to the problem of non-repeated reading:

 

3. Repeatable read

(1) Open A Client A and set the current transaction mode to repeatable read. query the initial values of the Table account:

(2) Before the transaction commit of Client A, open another client B, update the table account and submit the transaction. The transaction of client B can actually modify the row queried by client A transaction, that is, mysql's Repeatable read won't lock the rows queried by the transaction, which is unexpected:

(3) perform the query in step (1) on Client:

(4) Submit the transaction at Client A and execute the query in step (1). the query results are consistent with those in step (2:

　　　　(5) For the Repeatable read of mysql, only the same query results can be executed from the start to the end of the transaction. Once the transaction ends, the updates committed by other transactions will still be read.

(6) Start A transaction on Client A and query the initial values of the Table account:

mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from account;+------+--------+---------+| id | name | balance |+------+--------+---------+| 1 | lilei | 300 || 2 | hanmei | 16000 || 3 | lucy | 2400 |+------+--------+---------+3 rows in set (0.00 sec)

(7) Start the transaction on client B and add a new piece of data, where the balance field value is 0, and submit

mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into account values(4,'lily',0);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.01 sec)

(8) There are three records in the account query table at Client A. The balance value is updated to 10000. There are four records in the account query again.

mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     300 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 rows in set (0.00 sec)mysql> update account set balance = 10000;Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |   10000 ||    2 | hanmei |   10000 ||    3 | lucy   |   10000 ||    4 | lily   |   10000 |+------+--------+---------+4 rows in set (0.00 sec)

　　

4. serialization

(1) Open A Client A and set the current transaction mode to serializable. query the initial values of the Table account:

mysql> set session transaction isolation level serializable;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |   10000 ||    2 | hanmei |   10000 ||    3 | lucy   |   10000 ||    4 | lily   |   10000 |+------+--------+---------+4 rows in set (0.00 sec)

(2) open a client B, set the current transaction mode to serializable, insert a record, and report an error. If the table is locked, insertion fails. When the transaction isolation level in mysql is serializable, the table will be locked, so there will be no phantom read.

mysql> set session transaction isolation level serializable;Query OK, 0 rows affected (0.00 sec)mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into account values(5,'tom',0);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

 

　　Supplement:

1. The specific implementation of different databases may vary according to the standards specified by SQL specifications.

2. When mysql uses the InnoDB engine, the default transaction isolation level is repeatable, but it does not lock the read rows and does not generate Phantom reads.

3. When the transaction isolation level is serialized, reading data locks the entire table

　　 4. Dirty reads directly generate wrong data, but non-repeated and Phantom reads do not. The latter two are visually misleading when reading data.

5. The higher the isolation level, the more data integrity and consistency can be ensured, but the greater the impact on concurrency performance. For most applications, the isolation level of the database system can be set to Read Committed, which can avoid dirty reading and has good concurrency performance. Although it can cause non-repeated read and phantom read concurrency problems, in some cases where such problems may occur, the application can adopt pessimistic locks or optimistic locks to control

6. When reading this article, from the developer's point of view, you may feel that repeated and Phantom reads are not allowed. Logically, there is no problem, and the final data is still consistent, but from the user's point of view, they can only see one transaction (only client A can be seen, and client B is unknown), without considering the phenomenon of concurrent transaction execution, once the same data is read differently for multiple times, or a new record appears out of thin air, they may have doubts, which is a problem of user experience.