MySQL transaction isolation level

Posted by qrt123 on Wed, 05 Jan 2022 20:06:18 +0100

MySQL transactions must meet the A (atomicity) C (consistency) I (isolation) D (persistence) principle. Among them, isolation is to minimize the impact of concurrent transactions on each other. The highest isolation level can ensure that concurrent transactions do not affect each other.

in the actual application process, in order to adapt to different application scenarios, the isolation level can not be set to the highest level. This leads to more or less interaction between concurrent transactions, which leads to various read operation exceptions.

1. Four kinds of read operations are abnormal

Dirty reading

transactions T1 and T2 are executed simultaneously. T1 reads the data written by T2 but not committed. If T2 finally executes the rollback of the transaction, T1 finally returns the wrong result set.

Non repeatable reading

transactions T1 and T2 are executed at the same time. After the select statement in T1 is executed, T2 modifies and submits a specific data. When the select operation is executed in T1 again, the result of the same record is different from the previous one.

Unreal reading

phantom reading is similar to non repeatable reading, except that phantom reading is used to query multiple records. After the select statement is executed in T1, the insert/update/delete operation is executed in T2, resulting in a different result set from the previous one.

Serialization exception

multiple transactions modify and commit the same data at the same time without resolving the conflict, resulting in unpredictable final results.

2. Four isolation levels

read uncommitted

records written by other transactions but not committed can be seen in the transaction

read committed

records written and committed by other transactions can be seen in transactions

repeatable read

ensure that the same result set is obtained by multiple executions of the select in the same transaction, regardless of whether other transactions have modified the data during this period

serializable

the highest transaction isolation level. Concurrent transactions are executed serially in a certain order and do not overlap each other

3. Correspondence between read operation exception and transaction isolation level

	Dirty reading	Non repeatable reading	Unreal reading	Serialization exception
Read uncommitted	Y	Y	Y	Y
Read commit	N	Y	Y	Y
Repeatable reading	N	N	N	Y
Serialization	N	N	N	N

4. Verification

Transaction isolation levels of MySQL include global and current sessions. View them in the following ways:

# View the transaction isolation level of the current session
select @@transaction_isolation；

# View global transaction isolation levels
select @@global.transaction_isolation;
Copy code

similarly, modifying the transaction isolation level of MySQL can only modify the current session and modify the global:

# Modify only the current session
set session transaction isolation level repeatable read;

# Modify global
set  global transaction isolation level repeatable read;
Copy code

tables 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 'title of account',
    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 code

Read uncommitted

first modify 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 code

execute transactions in two session s respectively, 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 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)
Copy code

check the corresponding records in T1 before T 2 submission:

# T1
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-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 code

it can be seen that under the read uncommitted transaction isolation level, the data modification in T2 has been reflected in T1 before T2 is committed, resulting in dirty reads in T1.

Read commit

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 code

view 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 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy code

at this time, update 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 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy code

therefore, under the transaction isolation level of read commit, the modifications in T2 will not be reflected in T1 before T2 commit. Now, submit T2, and then execute the query statement just now 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 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
Copy code

check the record with ID 1 again and find that the balance has changed, resulting in non readability. When the balance is filtered under the same conditions, one record in the result set is missing, resulting in unreal reading.

Repeatable reading

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 code

view the data of the current table in T1 and T2 respectively, and then update the record with ID 1 in T2 and submit:

# 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 | 2021-07-24 16:21:58 | 2021-07-24 17:13:02 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:13:02 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:35:15 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Copy code

view the data in T1 at this time:

# T1
mysql> select * from accounts where id = 1;
+----+-------+---------+---------------------+---------------------+
| id | owner | balance | created_at          | updated_at          |
+----+-------+---------+---------------------+---------------------+
|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:38:22 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy code

it can be seen that the repeatable transaction isolation level successfully avoids non repeatable reading and unreal reading. But what happens if you update the balance of the record with ID 1 in T1?

# 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 | 2021-07-24 16:21:58 | 2021-07-24 17:47:14 |
+----+-------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
Copy code

it can be seen that the readable isolation level ensures the correctness of the updated results, but it is easy to give people an illusion.

Serialization

first modify 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 code

query the data of the current table in T1 and T2 respectively, and update the record with ID 1 in T2:

# 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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 code

the update in T2 will be blocked first and will eventually fail because of the wait timeout. The reason for this result is that in the serialized transaction isolation level, MySQL will implicitly convert the select statement to select for share. At this time, the transaction that obtains the select for share lock only allows other transactions to read these data, not write. Therefore, the update in T2 is blocked by the lock in T1 and finally times out.

roll back T2 and restart. Still perform the previous operation, but T2 will not timeout this time. Before T2 timeout, perform the same operation 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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 code

at this time, the update operation in T2 is blocked by select in T1, and the uodate operation in T1 is also blocked by select in T2, resulting in deadlock. At this time, MySQL will roll back T1, release the lock and restart. After T1 releases the lock, the uodate operation in T2 can be executed successfully.

roll back T1 and T2 and restart:

# 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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
+----+-------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)
Copy code

execute update in T1 and quickly submit T2. At this time, you can see that the update in T1 is 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 code

5. MySQL handling of serialization exceptions

MySQL uses the lock 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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  4 | sum   |  270.00 | 2021-07-24 18:29:02 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  4 | sum   |  270.00 | 2021-07-24 18:29:02 | 2021-07-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 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |
|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |
|  4 | sum   |  270.00 | 2021-07-24 18:29:02 | 2021-07-24 18:29:02 |
|  5 | sum   |  540.00 | 2021-07-24 18:32:05 | 2021-07-24 18:32:05 |
+----+-------+---------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
Copy code

the locking mechanism ensures the uniqueness of sum(balance) queried in T1 and T2. Therefore, when writing data to the data table, it ensures that sum records with the same balance will not be written.

Topics: Java MySQL Programmer

Programmer Think