Detailed introduction to MySql's transaction isolation level (with code)

This article brings you a detailed introduction to the transaction isolation level of MySql (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. Helps.

1. The four characteristics of transactions (ACID)

The four characteristics of transactions that you must understand before understanding the transaction isolation level.

1. Atomicity

After the transaction starts, all operations are either completed or not completed. A transaction is an indivisible whole. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started to ensure the integrity of the transaction. Similar to the physical explanation of atoms: it refers to basic particles that cannot be further divided in chemical reactions. Atoms are indivisible in chemical reactions.

2. Consistency

After the transaction starts and ends, it can ensure the correctness of the database integrity constraints, that is, the integrity of the data. For example, in the classic transfer case, when A transfers money to B, we must ensure that A deducts the money and B will definitely receive the money. My personal understanding is similar to the conservation of energy in physics.

3. Isolation

Complete isolation between transactions. For example, A transfers money to a bank card to avoid too many operations at the same time causing a loss of account amount. Therefore, no other operations on this card are allowed before A's transfer is completed.

4. Durability

The impact of transactions on data is permanent. The popular explanation is that after the transaction is completed, the data operations must be put to disk (persistence). Once a transaction is completed, it is irreversible. In terms of database operations, once a transaction is completed, it cannot be rolled back.

2. Transaction Concurrency Issues

In the tide of the Internet, the value of programs is no longer to help people solve some complex business logic in traditional industries. In the Internet era where user experience is paramount, code is like the steps of coders at Xierqi subway station, speed, speed, speed. Of course, you can’t sit in the wrong direction. I originally wanted to go to Xizhimen and ended up at Dongzhimen (let’s take this as correct for now). Compared with the complex business logic of traditional industries, the Internet pays more attention to the speed and passion that concurrency brings to programs. Of course speeding comes with a price. In concurrent transactions, the poor coder will run away if he is not careful.

1. Dirty reading

is also called invalid data reading. A transaction reading data that has not yet been committed by another transaction is called a dirty read.

For example: Transaction T1 modified a row of data, but it has not been submitted. At this time, transaction T2 read the data modified by transaction T1. Later, transaction T1 rolled back for some reason, then transaction T2 read is dirty data.

2. Non-repeatable reading

In the same transaction, the same data read multiple times is inconsistent.

For example: transaction T1 reads certain data, transaction T2 reads and modifies the data, and T1 reads the data again in order to verify the read value, and obtains different results.

3. Illusion reading

It’s hard to explain, let’s just give an example:

In warehouse management, the administrator has to give a batch of food to the newly arrived batch. When goods enter warehouse management, of course, before entering the warehouse, you must check whether there is any previous entry record to ensure accuracy. Administrator A ensures that the product does not exist in the warehouse and then puts the product into storage. If administrator B has already put the product into storage because of his quick hands. At this time, administrator A discovered that the product was already in the inventory. It was like a phantom reading had just happened, something that didn't exist before, suddenly he had it.

Note: The three types of problems seem difficult to understand. Dirty reading focuses on the correctness of the data. Non-repeatability focuses on the modification of data, while phantom reading focuses on the addition and deletion of data.

3. MySql’s Four Transaction Isolation Levels

In the previous chapter, we learned about the impact on transactions under high concurrency. The four isolation levels of transactions are solutions to the above three problems.

## Non-repeatable read (read-committed )Repeatable-readSerializable

4. SQL demonstration of four isolation levels

mysql version: 5.6

Storage engine: InnoDB

Tool: navicat

Table statement:

CREATE TABLE `tb_bank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) COLLATE utf8_bin DEFAULT NULL,
  `account` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (1, '小明', 1000);

1. Demonstrate through sql ------read-uncommitted dirty reading

(2)Dirty reading caused by read-uncommit

The so-called dirty reading is Say, two transactions, one transaction can read the uncommitted data of the other transaction.
Scenario: Session1 wants to transfer 200 yuan, and session2 transfers 100 yuan. The base is 1000. The correct result for successful completion should be 900 yuan. But we assume that session2 is transferred to a transaction rollback for some reason. The correct result at this time should be 800 yuan.

Demonstration steps:
① Create two new sessions (sessions appear as two query windows in navicat, and are also two windows in the mysql command line), and execute

 select @@tx_isolation;//查询当前事务隔离级别
 set session transaction isolation level read uncommitted;//将事务隔离级别设置为 读未提交

respectively. ② Both sessions open transactions

 start transaction;//开启事务

③ Session1 and session2: Prove that the account balance before the two operations are executed is 1000

 select * from tb_bank where id=1;//查询结果为1000

④ Session2: At this time, it is assumed that the update of session2 is executed first.

update tb_bank set account = account + 100 where id=1;

⑤ session1: session1 starts execution before session2 commit.

 select * from tb_bank where id=1;//查询结果：1100

⑥ session2: For some reason, the transfer failed and the transaction was rolled back.

 rollback;//事务回滚
 commit;//提交事务

⑦ At this time, session1 begins to transfer out, and session1 feels that the query result 1100 in ⑤ is the correct data.

 update tb_bank set account=1100-200 where id=1;
 commit;

⑧Session1 and session2 query results

 select * from tb_bank where id=1;//查询结果：900

At this time we found that the final data was inconsistent due to dirty reading of session1. The correct result should be 800;
How can we avoid dirty reading at this point and increase the isolation level of the transaction to read-commit

(2) read-commit solves dirty reading

Reset the data and restore the data to account=1000

① Create two new sessions and set them respectively

 set session transaction isolation level read committed;//将隔离级别设置为 不可重复读

Repeat steps ②③④ in (1)

⑤ session1 executes the query

 select * from tb_bank where id=1;//查询结果为1000，这说明 不可重复读 隔离级别有效的隔离了两个会话的事务。

At this time, we found that after upgrading the transaction isolation to read-committed;, the two transactions were effectively isolated, making it impossible for the transaction in session1 to query the data changes made by the transaction in session2. Dirty reads are effectively avoided.

2. Demonstrate through sql ----- read-committed non-repeatable reading

(1) read-commit non-repeatable reading

Reset the data so that Data is restored to account=1000

The so-called non-repeatable read means that a transaction cannot read the data of another uncommitted transaction, but it can read the submitted data. At this time, the results of the two readings are inconsistent. So it is non-repeatable reading.
READ COMMITTED Under the isolation level, each read will regenerate a snapshot, so each snapshot is the latest, and therefore each SELECT in the transaction can also see the changes made by other committed transactions
Scenario : Session1 performs account query, and session2 performs account transfer of 100.
Session1 opens a transaction to query and update the account. At this time, session2 also opens a transaction for the account and updates it. The correct result should be that the results read by the query after session1 starts the transaction should be the same.

① Create two new sessions and set up

 set session transaction isolation level read committed;

respectively. ② Session1 and session2 open transactions respectively.

 start transaction;

③ The first query of session1:

 select * from tb_bank where id=1;//查询结果：1000

④ Session2 updates:

 update tb_bank set account = account+100 where id=1;
 select * from tb_bank where id=1;//查询结果：1100

⑤ Second query of session1:

 select * from tb_bank where id=1;//查询结果：1100。和③中查询结果对比，session1两次查询结果不一致。

Looking at the query results, it can be seen that the repeated read results of session1 during the opening of the transaction are inconsistent, so you can see the read commit transaction isolation level It cannot be read repeatedly. Obviously this result is not what we want.

(2)repeatable-read can be read repeatedly

Reset the data to restore the data to account=1000

① Create two new sessions and set

 set session transaction isolation level repeatable read;

respectively

Repeat ②③④
⑤ session1 second query in (1):

 select * from tb_bank where id=1;//查询结果为：1000

From the results, it can be seen that under the isolation level of repeatable-read, the results of multiple reads are not affected by other transactions of. It can be read repeatedly. A question arises here. The result read by session1 is still the result before the update of session2. Can the correct result of 1200 be obtained by continuing to transfer 100 in session1?
Continue operation:
⑥ Session1 transfers to 100:

update tb_bank set account=account+100 where id=1;

I feel like I have been cheated here, lock, lock, lock. The update statement of session1 is blocked. Only after the update statement in session2 is committed, execution in session1 can continue. The execution result of session is 1200. At this time, it is discovered that session 1 is not calculated using 1000 100, because the MVCC mechanism is used under the isolation level of repeatable read, and the select operation will not update the version number, but is a snapshot read (historical version). Insert, update and delete will update the version number, which is the current read (current version).

3. Demonstrate through sql-----phantom reading of repeatable-read

在业务逻辑中，通常我们先获取数据库中的数据，然后在业务中判断该条件是否符合自己的业务逻辑，如果是的话，那么就可以插入一部分数据。但是mysql的快照读可能在这个过程中会产生意想不到的结果。
场景模拟：
session1开启事务，先查询有没有小张的账户信息，没有的话就插入一条。这是session2也执行和session1同样的操作。

准备工作：插入两条数据

 INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (2, '小红', 800);
 INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (3, '小磊', 6000);

（1）repeatable-read的幻读

① 新建两个session都执行

 set session transaction isolation level repeatable read;
 start transaction;
 select * from tb_bank;//查询结果：（这一步很重要，直接决定了快照生成的时间）

结果都是：

② session2插入数据

 INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (4, '小张', 8000);
 select * from tb_bank;

结果数据插入成功。此时session2提交事务

 commit;

③ session1进行插入
插入之前我们先看一下当前session1是否有id=4的数据

 select * from tb_bank;

结果session1中没有该条记录，这时按照我们通常的业务逻辑，此时应该是能成功插入id=4的数据。继续执行：

 INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (4, '小张', 8000);

结果插入失败，提示该条已经存在，但是我们查询里面并没有这一条数据啊。为什么会插入失败呢？

因为①中的select语句生成了快照，之后的读操作（未加读锁）都是进行的快照读，即在当前事务结束前，所有的读操作的结果都是第一次快照读产生的快照版本。疑问又来了，为什么②步骤中的select语句读到的不是快照版本呢？因为update语句会更新当前事务的快照版本。具体参阅第五章节。

（2）repeatable-read利用当前读解决幻读

重复（1）中的①②
③ session1进行插入
插入之前我们先看一下当前session1是否有id=4的数据

select * from tb_bank;

结果session1中没有该条记录，这时按照我们通常的业务逻辑，此时应该是能成功插入id=4的数据。

 select * from tb_bank lock in share mode;//采用当前读

结果：发现当前结果中已经有小张的账户信息了，按照业务逻辑，我们就不在继续执行插入操作了。
这时我们发现用当前读避免了repeatable-read隔离级别下的幻读现象。

4、serializable隔离级别

在此级别下我们就不再做serializable的避免幻读的sql演示了，毕竟是给整张表都加锁的。

五、当前读和快照读

本想把当前读和快照读单开一片博客，但是为了把幻读总结明白，暂且在本章节先简单解释下快照读和当前读。后期再追加一篇MVCC，next-key的博客吧。。。

1、快照读：即一致非锁定读。

① InnoDB存储引擎下，查询语句默认执行快照读。

② RR隔离级别下一个事务中的第一次读操作会产生数据的快照。

③ update，insert，delete操作会更新快照。

四种事务隔离级别下的快照读区别：

① read-uncommitted和read-committed级别：每次读都会产生一个新的快照，每次读取的都是最新的，因此RC级别下select结果能看到其他事务对当前数据的修改，RU级别甚至能读取到其他未提交事务的数据。也因此这两个级别下数据是不可重复读的。

② repeatable-read级别：基于MVCC的并发控制，并发性能极高。第一次读会产生读数据快照，之后在当前事务中未发生快照更新的情况下，读操作都会和第一次读结果保持一致。快照产生于事务中，不同事务中的快照是完全隔离的。

③ serializable级别：从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降。（锁表，不建议使用）

2、当前读：即一致锁定读。

如何产生当前读

① select ... lock in share mode

② select ... for update

③ update，insert，delete操作都是当前读。

读取之后，还需要保证当前记录不能被其他并发事务修改，需要对当前记录加锁。①中对读取记录加S锁 (共享锁)，②③X锁 (排它锁)。

3、疑问总结

① update，insert，delete操作为什么都是当前读？

简单来说，不执行当前读，数据的完整性约束就有可能遭到破坏。尤其在高并发的环境下。

分析update语句的执行步骤：update table set ... where ...;

The InnoDB engine first performs a where query. The queried result set starts reading from the first item, then performs the update operation, then reads the second piece of data, and performs the update operation...so every time Executing updates is accompanied by the current read. The same goes for delete. After all, the data must be found first before it can be deleted. Insert is a little different. A unique key check needs to be performed before the insert operation is executed. [Related recommendations: MySQL Tutorial]

##Isolation level	Dirty read	Non-repeatability	Phantom reading
read-uncommitted	Yes	is	is
##No	Yes	Yes
No	No	Yes
No	No	No

The above is the detailed content of Detailed introduction to MySql's transaction isolation level (with code). For more information, please follow other related articles on the PHP Chinese website!