MySQL transaction isolation level example tutorial

The test environment for this article’s experiment: Windows 10+cmd+MySQL5.6.36+InnoDB

1. Basic elements of transactions (ACID)

##　 1. Atomicity: After the transaction starts, all operations are either completed or not completed, and it is impossible to stagnate in the middle. If an error occurs during transaction execution, it will be rolled back to the state before the transaction started, and all operations will be as if they did not happen. That is to say, affairs are an indivisible whole, just like atoms learned in chemistry, which are the basic units of matter.

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

## 3. Isolation: At the same time, only one transaction is allowed to request the same transaction. Data, different transactions do not interfere with each other. For example, A is withdrawing money from a bank card. B cannot transfer money to this card before A's withdrawal process is completed.

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.

Summary: Atomicity is the basis of transaction isolation, isolation and durability are the means, and the ultimate goal This is to maintain data consistency.

2. Transaction concurrency issues

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

2. Non-repeatable reading: Transaction A reads the same data multiple times, and transaction B reads the same data multiple times in transaction A. The data was updated and submitted, resulting in inconsistent results when transaction A read the same data multiple times.

## 3. Phantom reading: System administrator A changes the scores of all students in the database from specific scores It is ABCDE level, but system administrator B inserted a record with a specific score at this time. When system administrator A completed the change, he found that there was still one record that had not been changed. It was like an hallucination. This is called phantom reading. .

## Summary: Non-repeatable reading and phantom reading are easily confused, and non-repeatable reading focuses on

Modify

, phantom reading focuses on adding or deleting. To solve the problem of non-repeatable reading, you only need tolock the rows that meet the conditions. To solve the problem of phantom reading, you only need tolock the table 3. MySQL transaction isolation level

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

The default transaction isolation level of mysql is repeatable-read

IV. Use examples to illustrate each isolation level

1. Read uncommitted ：

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　A Client A, and set the current transaction mode to read uncommitted (uncommitted read), query the initial value of table account:

　　　 (2) Before client A’s transaction is submitted, open another client B and update the table account:

　(3) At this time, although client B's transaction has not yet been submitted, client A can query B's updated data:

(4) Once client B's transaction is rolled back for some reason, all operations will be undone, 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. lilei's balance did not become 350, but actually 400. Isn't it strange? The consistency of the data was not asked. , If you think so, you are too naive. In the application, we will use 400-50=350, and we do not know that other sessions have been rolled back. To solve this problem, you can use the read-committed isolation level

2. Read committed

(1) Open a client A and set the current transaction mode to read Committed (uncommitted read), query the initial value of the table account:

　　　　 (2) Before client A's transaction is committed, open another client B and update the table account:

　 (3) At this time, client B’s transaction has not yet been submitted, and client A cannot query the updated data of B, which solves the dirty read problem:

　　　(4) Client B’s transaction submission

　　　　 (5) Client A performs the same as the previous step The result of the query is inconsistent with the previous step, which causes a non-repeatable read problem.

In the application, assuming that we are in the session of client A, the query finds that lilei's balance is 450, but other transactions will change lilei's balance. The value is changed to 400. We don’t know that if we use the value 450 to do other operations, there will be a problem, but the probability is really small. To avoid this problem, you can use the repeatable read isolation level

3. Repeatable read

(1) Open a client A and set the current transaction mode to repeatable read , query the initial value of the table account:

##　 (2) Before client A’s transaction is submitted, open another client B, update the table account and submit,

Client B's transaction can actually modify the rows queried by client A's transaction. That is, mysql's repeatable read will not lock the rows queried by the transaction. This is beyond my expectation. The transaction isolation level in the SQL standard is During repeatable reading, rows are required to be locked for read and write operations, but mysql does not have any locks. I am confused. Pay attention to locking rows in the application, otherwise you will use lilei's balance of 400 in step (1) as the intermediate value to do other operations

(3) Execute the query of step (1) on client A:

　(4) Execute step (1), lilei's balance is still 400, which is consistent with the query result of step (1), and there is no non-repeatable read problem; Then execute update balance = balance - 50 where id = 1, balance has not become 400-50=350, lilei's balance value is calculated using 350 in step (2), so it is 300, and the consistency of the data has not been destroyed. , this is a bit magical, maybe it is a feature of mysql

mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     400 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 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 |+------+--------+---------+3 rows in set (0.00 sec)

　(5) Start a transaction on client A and query the initial value of 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)

　　　　　(6) Start a transaction on client B, add a new piece of data, in which 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) 　　　　　　　　　　　　　　　　　　　　　　When calculating the sum of balance, the value is 300+16000+2400=18700. The value of client B is not included. After client A submits, the sum of balance is calculated and it turns out to be 19300. This is because the value of client B is included. The 600 is included in the calculation. From the customer's perspective, the customer cannot see client B. It will feel that the world has lost its pie. There are 600 more yuan. This is phantom reading. From the development perspective, From the user's perspective, the consistency of the data is not destroyed. But in the application, our code may submit 18700 to the user. If you must avoid this small probability situation, then you must adopt the transaction isolation level "serialization" introduced 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, and query the initial value 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)

　　　　　　　　　　　　　　　　　　　nelcess in client B' and set the current transaction mode to serializable. Query the initial value 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> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

, an error is reported when inserting a record. The table is locked and the insertion fails. The table will be locked when the transaction isolation level in mysql is serializable, so phantom reads will not occur. The concurrency of this isolation level is extremely low, and one transaction often occupies it. Once a table is created, thousands of other transactions can only stand idle and wait until it is finished and submitted before it can be used. This is rarely used in development.

###rrreee###

Replenish:

　1. The standards stipulated in the SQL specification, the specific implementation of different databases may have some differences

##　2 . The default transaction isolation level in mysql is repeatable read and the read rows will not be locked

　3. Transaction isolation When the level is serialization, reading data will lock the entire table

　4. When reading this article, if you stand on From a developer's perspective, there may be no logical problems with non-repeatable reads and phantom reads, and the final data is still consistent. However, from a user's perspective, they usually can only see one transaction (only one transaction can be seen). Client A does not know the existence of the undercover client B) and does not consider the phenomenon of concurrent execution of transactions. Once the same data is read multiple times with different results, or new records appear out of thin air, they may have doubts. This is User experience issues.

5. When a transaction is executed in mysql, the final result will not have data consistency problems, because in a transaction In MySQL, performing an operation may not necessarily use the intermediate results of the previous operation. It will be processed based on the actual situation of other concurrent transactions. It seems illogical, but it ensures the consistency of the data; but the transaction is executed in the application , the result of one operation is used by the next operation and other calculations are performed. This is why we have to be careful. We should lock rows during repeatable reading and lock tables during serialization, otherwise the consistency of the data will be destroyed.

6. When a transaction is executed in mysql, mysql will comprehensively process it according to the actual situation of each transaction, resulting in no consistency of data. Destroyed, but the application plays cards according to logical routines, which is not as smart as MySQL, and data consistency problems will inevitably occur.

##　7. The higher the isolation level, the more complete and consistent the data can be guaranteed, but it will affect the concurrency performance. The greater the impact, you can’t have your cake and eat it too. For most applications, you can give priority to setting the isolation level of the database system to Read Committed, which can avoid dirty reads and has better concurrency performance. Although it will lead to concurrency problems such as non-repeatable reads and phantom reads, in individual situations where such problems may occur, the application can use pessimistic locks or optimistic locks to control them.

The above is the detailed content of MySQL transaction isolation level example tutorial. For more information, please follow other related articles on the PHP Chinese website!