Four types of transaction isolation levels for MySQL

I. Basic elements of a transaction (ACID)

1, atomicity (atomicity): All operations after the start of the transaction, or all done, or all do not, it is impossible to stagnate in the middle. An error occurred during the execution of the transaction, and it was rolled back to the state before the transaction began, and all operations were as if they had not occurred. This means that the transaction is an indivisible whole, like the atoms that the chemistry has learned, and is the basic unit of the material composition.

2. Consistency (consistency): The integrity constraints of the database are not compromised before and after the transaction begins. For example, a to B transfer, not a deduction of money, B but did not receive.

3, Isolation (isolation): At the same time, only one transaction is allowed to request the same data, and different transactions do not interfere with each other. For example, a is taking money from a bank card, B cannot transfer to this card until the end of the process of taking money.

4. Persistence (Durability): After the transaction is complete, all updates to the database are saved to the database and cannot be rolled back.

Second, the concurrency problem of the transaction

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

2. Non-repeatable READ: Transaction a reads the same data multiple times, transaction B updates and commits the data during transaction a multiple reads, resulting in inconsistent results when transaction a reads the same data multiple times.

3, Magic read: System administrator A to the database of all students from the specific score to the ABCDE level, but system Administrator B at this time to insert a specific score record, when the system administrator a changed the end of a record has not changed, as if an illusion, this is called Phantom Reading.

Summary: Non-repeatable Read and Phantom reading is easy to confuse, non-repeatable reading focuses on modification, and Phantom reading focuses on new or deleted. Solve the problem of non-repeatable reading just lock the line that satisfies the condition, and solve the Phantom read need lock table

Third, MySQL transaction isolation level

Transaction ISOLATION level Dirty read non-repeatable read Phantom read
READ UNCOMMITTED (read-uncommitted) Yes Yes Yes
Non-repeatable READ (read-committed) No Yes Yes
Repeatable Read (Repeatable-read) No no Yes
Serialization (serializable) No no No

MySQL default transaction isolation level is Repeatable-read

Iv. using examples to illustrate the situation of each isolation level

1, read not submitted:

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

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

(3) At this point, although client B's transaction has not yet been committed, client a can query the data that has been updated by B:

(4) Once client B's transaction is rolled back for some reason, all operations are revoked, and the data that client a queries to is actually dirty data:

(5) The UPDATE statement in client a updates account set balance = balance-50 where ID =1,lilei balance did not become 350, incredibly is 400, is not very strange, data inconsistency ah, if you think so naïve , in the application, we will use 400-50=350, do not know other session rollback, to solve this problem can take the Read Committed isolation level

2, read has been submitted

(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 commits, open another client B and update the table account:

(3) At this point, client B's transaction has not been committed, client a cannot query to B has updated data, resolved the dirty read problem:

(4) Transaction submission for Client B

(5) Client a executes the same query as the previous step, which is inconsistent with the previous step, resulting in a non-repeatable read problem

3. Repeatable READING

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

(2) Before client A's transaction commits, open another client B, update the table account and submit

(3) In client A, perform step (1) of the query:

(4) The Execution Step (1), Lilei balance is still 400 and step (1) Query results consistent, no non-repeatable reading problem; then update balance = balance-50 WHERE id = 1,balance not become 400-50=350,lilei balance value is the step (2) in the 350来 calculation, so is 300, the data consistency is not destroyed, this is a bit magical, perhaps MySQL features it

Copy Code
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:0

Mysql> select * from account;
+------+--------+---------+
| ID | name | Balance |
+------+--------+---------+
| 1 | Lilei | 300 |
| 2 | Hanmei | 16000 |
| 3 | Lucy | 2400 |
+------+--------+---------+
3 Rows in Set (0.00 sec)
Copy Code
(5) Commit a transaction at client A, querying the initial value of the table account

Copy Code
Mysql> commit;
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)
Copy Code
(6) Open transaction in Client B, add a new data, where the Balance field value is 600, and submit

Copy Code
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)
Copy Code
(7) In client A to calculate the sum of balance, the value of 300+16000+2400=18700, not the value of client B, client a after submission and then calculate the sum of balance, actually become 19300, this is because the client B's 600 is counted in the

, standing in the customer's point of view, the customer is not see the client B, it will feel that the world dropped pie, more than 600, this is the Phantom reading, standing in the developer's perspective, the data consistency and no damage. But in the application, we have the code may submit 18700 to the user, if you must avoid this situation of small probability situation, then you should take the following to describe the transaction isolation level "serialization"

Copy Code
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)

Copy Code
　　

4. Serialization

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

Copy Code
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)
Copy Code
(2) Open a client B, and set the current transaction mode to serializable, insert a record error, the table is locked insert failure, MySQL lock table when the transaction isolation level is serializable, so there is no phantom read, this isolation level is very low concurrency, Rarely used in development.

Copy Code
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
Copy Code

Add:

1, the standard of the SQL specification, different database specific implementation may be some differences

2, the default transaction isolation level in MySQL is repeatable read and will not lock the read to the row

3. When the transaction isolation level is read Committed, the write data will only lock the corresponding row

4, when the transaction isolation level is repeatable read, if there is an index (including the primary key index), the index as a condition to update the data, there will be a gap lock gap lock, Row lock, the next key lock problem, thereby locking some rows, if there is no index, the update data will lock the entire table.

5. When the transaction isolation level is serialized, the read/write data will lock the entire table

6, the higher the isolation level, the more you can ensure the integrity and consistency of data, but the impact on concurrency is also greater, fish and bear paw can not be both. For most applications, it is preferable to set the isolation level of the database system to read Committed, which avoids dirty reads and has good concurrency performance. Although it leads to non-repeatable reads, Phantom reads, these concurrency problems can be controlled by applications using pessimistic or optimistic locks on individual occasions where such problems may occur.

Four types of transaction isolation levels for MySQL