MySQL transactions must comply with A (atomicity) C (consistency) I (isolation) D (persistence). Where isolation is to minimize the impact of concurrent transactions on each other, the highest isolation level ensures that concurrent transactions do not affect each other.
In practice, the isolation level cannot be set to the highest level to adapt to different application scenarios. This results in concurrent transactions affecting each other more or less, and these effects cause various read operation exceptions.
Manner The four read operations are abnormal
- Dirty read
Transaction T1 and T2 execute simultaneously, T1 read the data written by T2 but not committed yet. If T2 eventually performs a rollback of the transaction, T1 eventually returns an incorrect result set.
- Unrepeatable read
Transactions T1 and T2 are executed at the same time. After the select statement in T1 is executed, T2 modifies and commits a specific data. When the select operation is performed in T1 again, the result of the same record is different from the previous one.
- Phantom read
The phantom read is similar to the non-repeatable read, except that the phantom read applies to the scenario where multiple records are queried. After the SELECT statement was executed in T1, the INSERT/Update/DELETE operation was performed in T2, resulting in a different result set for the second select in T1.
- Serialization exception
Multiple transactions modify the same data at the same time and commit at the same time without resolving the conflict, resulting in unpredictable results.
⒉ Four isolation levels
- Read uncommitted
Records written but not committed by other transactions can be seen in a transaction
- Read committed
Records written and committed by other transactions can be seen in a transaction
- Repeatable read (repeatable read)
Ensure that select in the same transaction results in the same result set multiple times, regardless of whether other transactions have modified the data during that time
- Serializable
The highest transaction isolation level, where concurrent transactions execute sequentially in some order and do not overlap
(a) Mapping of read operation exceptions to transaction isolation levels
| Dirty read | Unrepeatable read | Phantom read | Serialization exception | |
|---|---|---|---|---|
| Read uncommitted | Y | Y | Y | Y |
| Read the submission | N | Y | Y | Y |
| Repeatable read | N | N | N | Y |
| serialization | N | N | N | N |
4. Verify
MySQL’s transaction isolation levels can be global or specific to the current session. View them in the following ways:
View the transaction isolation level of the current sessionselect @@transaction_isolation; View the global transaction isolation levelselect @@global.transaction_isolation;
Copy the codeIf you change the transaction isolation level of MySQL, you can change only the current session and change the global transaction isolation level.
Change only the current sessionsetsession transaction isolation level repeatable read; # Modify globalset global transaction isolation level repeatable read;
Copy the codeTable and data used for validation:
drop database if exists bank;
create database bank;
use bank;
drop table if exists accounts;
create table accounts(
id bigint unsigned auto_increment primary key comment 'primary key',
owner char(32) not null default ' ' comment 'Account name',
balance decimal(10.2) not null comment 'Account balance',
created_at datetime not null default current_timestamp comment 'Creation time',
updated_at datetime not null default current_timestamp on update current_timestamp comment 'Update time'
)engine=innodb charset=utf8 collate=utf8_general_ci;
insert into accounts(owner, balance) values
('A'.100),
('B'.100),
('C'.100);
Copy the code- Read uncommitted
First change the transaction isolation level of the session:
# T1
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.00 sec)
# T2
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.00 sec)
Copy the codeExecute the transaction in two sessions, and modify the record with ID 1 in T2:
# T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:53:07 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
# T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:53:07 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 90.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:59:23 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
Copy the codeCheck the status of the corresponding record in T1 before T 2 commits:
# T1
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 90.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:59:23 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# T2
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
Copy the codeAs you can see, at the read uncommitted transaction isolation level, changes to data in T2 are reflected in T1 before T2 commits, resulting in dirty reads in T1.
- Read the submission
First change the transaction isolation level in the session to read Commit:
# T1 and T2
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 row in set (0.00 sec)
Copy the codeView the data in the current table in T1 and T2 respectively:
# T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 90.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:59:23 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
# T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 90.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:59:23 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy the codeUpdate the record with ID 1 in T2 and view the record with ID 1 in T1 and T2 respectively:
# T2
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 80.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:13:02 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
# T1
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 90.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:59:23 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from accounts where balance > = 90;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 90.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:59:23 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy the codeThus, below the transaction isolation level of a read commit, changes in T2 are not reflected in T1 until T2 commits. Now, commit T2 and execute the query in T1:
# T2
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# T1
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 80.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:13:02 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from accounts where balance > = 90;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
Copy the codeWhen you check record 1 again, it is found that the balance has changed, causing the unrepeatable read. However, if the balance is filtered with the same condition, one record in the result set is missing, resulting in phantom reading.
- Repeatable read
First update the transaction isolation level of the session:
# T1 and T2
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
Copy the codeSelect * from T1 where ID = 1 and select * from T2 where ID = 1
# T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 80.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:13:02 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
# T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 80.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:13:02 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:35:15 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Copy the codeAt this point, view data in T1:
# T1
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 80.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:38:22 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from accounts where balance > = 80;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 80.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:38:22 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy the codeThus, the repeatable read transaction isolation level successfully avoids the non-repeatable read and phantom read situations. But what happens if the balance of the record with ID 1 is updated in T1 at this point?
# T1
mysql> update accounts set balance = balance - 10;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 60.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:47:14 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
Copy the codeAs you can see, the repeatable read isolation level ensures that the updated results are correct, but it is misleading.
- serialization
First change the transaction isolation level of the session:
# T1 and T2
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE |
+-------------------------+
1 row in set (0.00 sec)
Copy the codeSelect * from T1 where ID = 1; select * from T2 where ID = 1;
# T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
# T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> update accounts set balance = balance - 10 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the codeThe UPDATE in T2 will first block and eventually fail because of the wait timeout. This result is due to the fact that MySQL implicitly converts select statements to SELECT for share at the serialized transaction isolation level. At this point, the transaction that acquires the SELECT for Share lock will only allow other transactions to read the data, not write it. So, the UPDATE in T2 is blocked by the lock in T1 and eventually times out.
Roll back T2 and start again, doing the same thing as before, but this time without T2 time-out. Before T2 times out, do the same in T1:
# T2
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> update accounts set balance = balance - 10 where id = 1;
# T1
mysql> update accounts set balance = balance - 10 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
# T2
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (3.51 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Copy the codeAt this point, the UPDATE operation in T2 is blocked by the SELECT in T1, and the UoDate operation in T1 is also blocked by the SELECT in T2, resulting in a deadlock. MySQL will roll back T1 to release the lock and restart. After T1 releases the lock, the Uodate operation in T2 will be executed successfully.
Roll back T1 and T2 and start again:
# T1 and T2
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy the codePerform the update in T1 and promptly commit T2, at which point you can see that the UPDATE in T1 has executed successfully.
# T1
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (3.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# T2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# T1
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
Copy the code5. Handle serialization exceptions by MySQL
MySQL uses the locking mechanism to handle serialization exceptions.
# T1 and T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# T1
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
| 270.00 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into accounts (owner, balance) values ('sum'.270);
Query OK, 1 row affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 4 | sum | 270.00 | 202107 -- 24 18:29:02 | 202107 -- 24 18:29:02 |
+----+-------+---------+---------------------+---------------------+
4 rows in set (0.00 sec)
# T2
mysql> select * from accounts;
# T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# T2
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 4 | sum | 270.00 | 202107 -- 24 18:29:02 | 202107 -- 24 18:29:02 |
+----+-------+---------+---------------------+---------------------+
4 rows in set (21.19 sec)
mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
| 540.00 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into accounts (owner, balance) values ('sum'.540);
Query OK, 1 row affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at | updated_at |
+----+-------+---------+---------------------+---------------------+
| 1 | A | 70.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:39:37 |
| 2 | B | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 3 | C | 100.00 | 202107 -- 24 16:21:58 | 202107 -- 24 17:32:16 |
| 4 | sum | 270.00 | 202107 -- 24 18:29:02 | 202107 -- 24 18:29:02 |
| 5 | sum | 540.00 | 202107 -- 24 18:32:05 | 202107 -- 24 18:32:05 |
+----+-------+---------+---------------------+---------------------+
5 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
Copy the codeThe lock mechanism guarantees the uniqueness of sum(balance) obtained in T1 and T2. Therefore, data is written to the data table to ensure that the sum record with the same balance is not written.