How to solve mysql phantom reading

Transaction isolation level (tx_isolation)

Mysql has four levels of transaction isolation. Each level has a character or numeric number

Level	symbol	Value	Description
Read Uncommitted	READ-UNCOMMITTED	0	There are problems with dirty reads, non-repeatable reads, and phantom reads
Read submitted	READ-COMMITTED	1	Solving the problem of dirty reads, there are problems of non-repeatable reads and phantom reads
Repeatable reads	REPEATABLE-READ	2	mysql default level solves the problems of dirty reads, non-repeatable reads, and phantom reads. Use the MMVC mechanism to implement repeatable reads
Serialization	SERIALIZABLE	3	Solves dirty reads, non-repeatable reads, and phantom reads Read, which can ensure transaction security, but is completely serialized and has the lowest performance

We can view/set the transaction isolation level of global/session through the following command

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

# 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
# SET [GLOABL] config_name = 'foobar';
# SET @@[session.|global.]config_name = 'foobar';
# SELECT @@[global.]config_name;

SET @@gloabl.tx_isolation = 0;
SET @@gloabl.tx_isolation = 'READ-UNCOMMITTED';

SET @@gloabl.tx_isolation = 1;
SET @@gloabl.tx_isolation = 'READ-COMMITTED';

SET @@gloabl.tx_isolation = 2;
SET @@gloabl.tx_isolation = 'REPEATABLE-READ';

SET @@gloabl.tx_isolation = 3;
SET @@gloabl.tx_isolation = 'SERIALIZABLE';

phantom reading

First of all, we have to understand what phantom reading is. There are currently many explanations of phantom reading on the Internet. The blog post personally feels that if you think about it carefully, you can find examples of overturning it. Just like the blog post equated non-blocking IO with asynchronous IO, and then many articles borrowed it. In fact, the two are completely different. Non-blocking IO is a part of synchronous IO. This mode is not asynchronous IO. The public’s misconceptions have been “corrected”, so let’s return to the topic.

Phantom reads will appear at the RU / RC / RR level. SERIALIZABLE eliminates phantom reads. However, dirty reads and non-repeatable reads will still exist under RU / RC, so we study phantoms at the RR level. Read and eliminate other interference.

Note: There is a possibility of phantom reading at RR level, but you can also use the method of manually adding X lock to the record to eliminate phantom reading. SERIALIZABLE prevents phantom reads by adding X locks to all transactions, but in many scenarios our business SQL does not have the risk of phantom reads. Although using SERIALIZABLE can ensure the absolute security of transactions, it will cause a lot of unnecessary losses in performance. Therefore, you can decide whether to lock according to business needs under RR. If there is a risk of phantom reading, we will lock it. If it does not exist, we will not lock it. This has both transaction security and performance. This is also the reason why RR, as the default isolation level of mysql, is a transaction isolation level, so it is necessary Correct understanding of phantom reading.

Understanding of phantom reading errors: It is said that phantom reading is when transaction A performs two select operations to obtain different data sets, that is, select 1 obtains 10 records, and select 2 obtains 11 records. This is actually not a phantom read. This is a type of non-repeatable read that will only occur at the R-U R-C level, but will not occur at the default RR isolation level of mysql.

Here is my colloquial understanding of phantom reading:

Phantom reading does not mean that the result sets obtained by two reads are different. The focus of phantom reading is on a certain time. The data status represented by the result of the select operation cannot support subsequent business operations. To be more specific: select whether a certain record exists. If it does not exist, prepare to insert the record. However, when executing insert, it is found that the record already exists and cannot be inserted. At this time, a phantom read occurs.

Here is a more vivid scenario of mysql phantom reading (borrowing my answer on Zhihu):

table users: id primary key

Transaction T1

Transaction T2

#step1 T1: SELECT * FROM \`users\` WHERE \`id\` = 1;
step2 T2: INSERT INTO \`users\` VALUES (1, 'big cat');
step3 T1: INSERT INTO \`users\` VALUES (1, 'big cat');
step4 T1: SELECT * FROM \`users \` WHERE \`id\` = 1;

T1: Main transaction, detects whether there is a record with id 1 in the table, and inserts it if not. This is the normal business logic we expect.

T2: Interference transaction, the purpose is to disrupt the normal transaction execution of T1.

Under the RR isolation level, step1 and step2 will be executed normally, but step3 will report a primary key conflict error. For the business of T1, the execution fails. Here T1 has occurred phantom reading, because the data status read by T1 in step1 cannot support subsequent business operations, T1: "Damn it, the result I just read should be able to support my operation like this, why can't it be done now?" T1 couldn't believe it and executed step 4 again, and found that the result read by setp1 was the same (MMVC mechanism under RR). At this time, phantom reading has undoubtedly occurred. No matter how many times T1 reads it, it cannot find the record with id = 1, but it cannot insert this record that it determined does not exist by reading (this data has been inserted by T2 ), for T1, it reads phantom.

In fact, RR can also avoid phantom reads by manually adding row You also need to know that even if the current record does not exist, for example, id=1 does not exist, the current transaction will obtain a record lock (because InnoDB's row lock locks the index, it does not matter whether the record entity exists or not, if it exists, add Row X lock, if it does not exist, add next-key lock gap

Under the SERIALIZABLE isolation level, row (X) locks/gap (X) locks will be implicitly added when step1 is executed, so step2 will be blocked and step3 will execute normally. After T1 is submitted, T2 can continue to execute (primary key conflict execution fails). For T1, the business is correct. Successful blocking kills T2 that disrupted the business. For T1, the results of its early reading can support its subsequent business.

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

RR级别下防止幻读

RR级别下只要对 SELECT 操作也手动加行（X）锁即可类似 SERIALIZABLE 级别（它会对 SELECT 隐式加锁），即大家熟知的：

# 这里需要用 X锁， 用 LOCK IN SHARE MODE 拿到 S锁 后我们没办法做 写操作
SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

这里我们就展示下 id = 1 的记录不存在的场景，FOR UPDATE 也会对此 “记录” 加锁，要明白，InnoDB 的行锁（gap锁是范围行锁，一样的）锁定的是记录所对应的索引，且聚簇索引同记录是直接关系在一起的。

id = 1 的记录不存在，开始执行事务：
step1: T1 查询 id = 1 的记录并对其加 X锁
step2: T2 插入 id = 1 的记录，被阻塞
step3: T1 插入 id = 1 的记录，成功执行（T2 依然被阻塞中），T1 提交（T2 唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

SERIALIZABLE级别杜绝幻读

在这个层面上，我们不必对 SELECT 操作进行显式加锁，因为InnoDB会自动加锁以确保事务的安全性，但是这会导致性能较低

step1: T1 查询 id = 2 的记录，InnoDB 会隐式的对齐加 X锁
step2: T2 插入 id = 2 的记录，被阻塞
step3: T1 插入 id = 2 的记录，成功执行（T2 依然被阻塞中）
step4: T1 成功提交（T2 此时唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

The above is the detailed content of How to solve mysql phantom reading. For more information, please follow other related articles on the PHP Chinese website!