Detailed description of four transaction isolation levels of MySQL and four levels of mysql

Detailed description of four transaction isolation levels of MySQL and four levels of mysql

Test environment of this experiment: Windows 10 + cmd + 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

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:

(5) execute the update statement update account set balance = balance-50 where id = 1 on Client A. The balance of lilei is not changed to 350, but it is 400. Isn't it strange, data Consistency is not a question. If you think so, it would be naive. In the application, we will use 400-50 = 350, and we do not know that other sessions are rolled back, to solve this problem, you can use the Read committed isolation level.

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) transaction commit of client B

(5) Client A executes the same query as the previous step, and the results are inconsistent with the previous step, that is, the problem of non-repeated reading occurs. In the application, assume that we are in the session of Client, the balance of lilei is found to be 450, but other transactions change the balance value of lilei to 400. We do not know if other operations are performed with the value of 450, however, this probability is really small. To avoid this problem, you can adopt a Repeatable read isolation level.

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 to say, mysql's repeatable reads won't lock the rows queried by the transaction. This is beyond my expectation. in SQL standards, when the transaction isolation level is repeatable, read/write operations need to lock the rows, mysql does not have a lock. I went there. In the application, pay attention to apply the row lock. Otherwise, you will take the lilei balance in step (1) as the center value for other operations.

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

(4) in step 1, the balance of lilei is still 400 consistent with the query result of step (1), and there is no problem of repeated reading; then execute update balance = balance-50 where id = 1, balance does not change to 400-50 = 350, and the balance value of lilei is calculated using 350 in step (2, so it's 300. Data Consistency is not broken. It's amazing. Maybe it's a special feature of mysql.

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

(5) 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 |+------+--------+---------+rows in set (0.00 sec)

(6) Start the transaction on client B and add a new piece of data. The balance field value is 600, and submit

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

(7) Calculate the sum of balance on Client A, with A value of 300 + 16000 + 2400 = 18700. The value of client B is not included, and client A submits the value before calculating the sum of balance, this is because 19300 of client B is included. From the customer's point of view, the customer cannot see client B, and it will think it is a pie in the world, with 600 more data blocks, this is phantom reading. From the developer's perspective, data consistency is not broken. However, in the application, the Code may submit 18700 to the user. If you must avoid this situation, then we need to adopt the transaction isolation level "serialize" described below"

mysql> select sum(balance) from account;+--------------+| sum(balance) |+--------------+| 18700 |+--------------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select sum(balance) from account;+--------------+| sum(balance) |+--------------+| 19300 |+--------------+1 row 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 |+------+--------+---------+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, therefore, there will be no phantom read. This isolation-level concurrency is extremely low, and often one transaction occupies a table. Thousands of other transactions can only be used when they are used up and committed, it is rarely used in development.

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. The default transaction isolation level in mysql is that the read row will not be locked during Repeatable read.

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

4. 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.

5. when a transaction is executed in mysql, Data Consistency does not occur in the final result, because in a transaction, mysql may not use the intermediate result of the previous operation to execute an operation, it will be collected and processed based on the actual situation of other concurrent transactions. It looks illogical, but ensures data consistency. However, when a transaction is executed in an application, the result of one operation is used by the next operation and is calculated by other operations. This is because we should be careful that we should lock rows during repeatable reads and lock tables during serialization. Otherwise, data consistency will be damaged.

6. When a transaction is executed in mysql, mysql will comprehensively process it based on the actual situation of each transaction, resulting in Data Consistency not being broken, but the application will play the cards according to the logic routine, without the knowledge of mysql, Data Consistency may inevitably occur.

7. The higher the isolation level, the more data integrity and consistency can be ensured, but the greater the impact on concurrency performance, the fish and the bear's paw cannot have both. 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.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.