MySQL's four transaction isolation levels

The test environment for this experiment:

mysql> select version() from dual;
+-----------+
| version() |
+-----------+
| 5.7.31    |
+-----------+
1 row in set (0.00 sec)

Isolation level of query transaction:

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

1. The basic elements of business (ACID)

1 原子性(Atomicity).: All operations after the start of the transaction 要么全部做完,要么全部不做, it is impossible to stop in the middle link. If an error occurs during the execution of the transaction, it will roll back to the state before the transaction started, and all operations will appear as if they did not happen. In other words 事务是一个不可分割的整体, just like the atoms learned in chemistry, they are the basic unit of matter.

2 一致性(Consistency).: Before and after the transaction, the database 完整性约束没有被破坏. For example, if A transfers money to B, it is impossible for A to deduct the money, but B does not receive it.

3. 隔离性(Isolation): 同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰. For example, A is withdrawing money from a bank card, and B cannot transfer money to this card before the process of A withdrawing money is finished.

4 持久性(Durability).: After the transaction is completed, all updates to the database by the transaction will be performed 保存到数据库,不能回滚.

Second, the concurrency of transactions

1 脏读(Dirty Reads).: Transaction A reads the uncommitted data of transaction B, and then B rolls back the operation, then the data read by A is dirty data

2 不可重复读(Non-Repeatable Reads).: Transaction A reads the same data multiple times, and transaction B updates and commits the data during the process of multiple reads of transaction A. As a result, when transaction A reads the same data multiple times, the results are inconsistent.

3 幻读(Phantom Reads).: System administrator A changes the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record of specific scores at this time. When system administrator A finishes the change, it is found that there are still If a record is not changed, it is as if an illusion has occurred, which is called a phantom reading.

4. 更新丢失(Lost Update)When two or more transactions select the same row, and then update the row based on the initially selected value, because each transaction does not know the existence of other transactions, a lost update problem will occur, and the last update is overwritten Updates made by other firms.

For example, two programmers modify the same java file. Each programmer independently changes its copy, and then saves the changed copy, thus overwriting the original document. The editor who last saved a copy of his changes overwrites the changes made by the previous programmer.

Summary: It is easy to confuse non-repeatable reading and phantom reading 不可重复读侧重于修改,幻读侧重于新增或删除. To solve the problem of non-repeatable reading, just lock the rows that meet the conditions 解决幻读需要锁表.

Three, MySQL transaction isolation level

Transaction isolation levelDirty readNon-repeatablePhantom reading
Read uncommitted (read-uncommitted)YesYesYes
Non-repeatable read (read-committed)noYesYes
Repeatable-readnonoYes
Serializablenonono

Four, case

-- 设置当前客户端的事务隔离级别为读未提交
set session transaction isolation level read uncommitted;

-- 设置当前客户端的事务隔离级别为不可重复读
set session transaction isolation level read committed;

// 设置当前客户端的事务隔离级别为读未提交
set session transaction isolation level read uncommitted;

// 设置当前客户端的事务隔离级别为可重复读
set session transaction isolation level repeatable read;

// 设置当前客户端的事务隔离级别为串行化
set session transaction isolation level serializable;

start transaction;

commit;

1. Read uncommitted

Transaction B performed the insert operation, but did not commit the transaction.

// 窗口B的代码
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into course (cid,cname,tid) values (5,'Rust',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

A transaction reads the uncommitted task of B transaction

// 窗口A的代码
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
+-----+--------+-----+
4 rows in set (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

2. Read submitted (not repeatable)

If I modify the isolation level of the A client at this time read commit, the A transaction cannot read the uncommitted data of the B transaction:

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
+-----+--------+-----+
4 rows in set (0.00 sec)

At this time transaction B commits the transaction

mysql> commit;
Query OK, 0 rows affected (0.13 sec)

Transaction A executes the query operation, and the data submitted by transaction B can be found

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

3. Repeatable reading

A client modify the isolation level to repeatable read. Transaction B executes a complete transaction operation in time, and transaction A cannot query the submitted data of transaction B at this time.

mysql> set session transaction isolation level repeatable read ;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

// B事务提交事务以后在A事务内执行的查询操作
mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

Transaction B performed a complete transaction operation,

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into course (cid,cname,tid) values (6,'flink',3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

4. Phenomenon of phantom reading

Operation performed by client A:

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
Empty set (0.00 sec)

-- 在客户端B完成了事务提交以后仍然查询不到相关的数据
mysql> select * from course;
Empty set (0.00 sec)

-- 但是你执行插入操作,其唯一键指定为和客户端B返回的唯一键相等,会报错,幻读现象
mysql>  insert into course (cid,cname,tid) values (7,"sql",1);
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'

Operation performed by client B:

Database changed
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into course (cname,tid) values ("sql",1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-----+-------+-----+
| cid | cname | tid |
+-----+-------+-----+
|   7 | sql   |   1 |
+-----+-------+-----+
1 row in set (0.00 sec)

mysql> commit;

The MVCCmechanism is used under the repeatable read isolation level . The select operation will not update the version number, yes 快照读(historical version); insert, update, and delete will update the version number, yes 当前读(current version).

4. Serialization

A transaction modification isolation level is serializable.

mysql> set session transaction isolation level serializable ;
Query OK, 0 rows affected (0.00 sec)

Transaction B performs an insert operation, inserts a record, reports an error, the table is locked, and the insert fails. When the transaction isolation level in mysql is serializable, the table will be locked, so there will be no phantom reading 这种隔离级别并发性极低,开发中很少会用到.

mysql> insert into course (cid,cname,tid) values (6,'flink',3);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

Five, summary

  1. Transaction isolation level 读提交, the write data only 锁住相应的行.
  2. Transaction isolation level 可重复读when, 默认隔离界别if the retrieval conditions index (including primary key index) when the lock mode is the default next-key 锁; will lock the entire table if the conditions are not indexed retrieval, update the data. A gap is locked by the transaction, and other transactions cannot insert records in this gap, which can prevent phantom reads.
  3. read commitAnd repeatable readthe difference between is that the former is as long as other transactions data submitted, the transaction can read data submitted by other transactions, resulting transaction may result in multiple queries inconsistencies, while the latter is that no matter whether the transaction other matters When the data is submitted, the transaction cannot read other transaction data, and the results of multiple queries within the transaction are always consistent.
  4. It is easy to confuse non-repeatable and phantom reading 不可重复读侧重于修改,幻读侧重于新增或删除. To solve the problem of non-repeatable read, you only need to lock the rows that meet the conditions, and to solve the phantom read, you need to lock the table.
  5. Through the above two cases, it is learned that the phantom read problem in the MySQL repeatable read isolation level is not completely solved, but only the phantom read problem under the snapshot read. For the current read operation, there is still a problem of phantom reading, which means that MVCC's solution to phantom reading is incomplete.
A consistent data snapshot (Snapshot) at the point in time of the data request is generated through a certain mechanism, and this snapshot is used to provide a certain level (statement level or transaction level) of consistent reads. From the user's point of view, it seems that the database can provide multiple versions of the same data. Therefore, this technology is called MultiVersion Concurrency Control (MVCC or MCC for short), and is often referred to as a multi-version database.
  1. Transaction isolation level 串行化, the read and write data will be锁住整张表
  2. The higher the isolation level, the more the integrity and consistency of the data can be guaranteed, but the greater the impact on concurrent performance.