Comment résoudre la lecture fantôme MySQL

Niveau d'isolement des transactions (tx_isolation)

mysql a quatre niveaux d'isolement des transactions. Chaque niveau a un caractère ou un numéro numérique

Nous pouvons utiliser la commande suivante pour afficher/définir le niveau d'isolement des transactions de global/session

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';

lecture fantôme

Tout d'abord, nous devons comprendre ce qu'est la lecture fantôme. J'ai l'impression qu'il existe de nombreux articles de blog sur Internet. expliquant la lecture fantôme.Je pense que vous pouvez le découvrir si vous y réfléchissez attentivement.Un exemple de renversement est comme le billet de blog qui assimile les E/S non bloquantes aux E/S asynchrones, et de nombreux articles l'ont emprunté. sont complètement différents. Les IO non bloquantes sont un mode d’IO synchrone, pas d’IO asynchrone. Les idées fausses du public ont été « corrigées », revenons donc au sujet.

Les lectures fantômes apparaîtront au niveau RU/RC/RR. SERIALIZABLE élimine les lectures fantômes. Cependant, les lectures sales et non répétables existeront toujours sous RU/RC, nous étudierons donc les lectures fantômes au niveau RR et en exclurons les autres. . interférence.

Remarque : Il existe une possibilité de lecture fantôme au niveau RR, mais vous pouvez également utiliser la méthode d'ajout manuel d'un verrou X à l'enregistrement pour éliminer la lecture fantôme. SERIALIZABLE empêche les lectures fantômes en ajoutant des verrous X à toutes les transactions, mais dans de nombreux scénarios, notre SQL métier ne présente pas de risque de lectures fantômes. Bien que l'utilisation de SERIALIZABLE puisse garantir la sécurité absolue des transactions, elle entraînera de nombreuses pertes de performances inutiles. Par conséquent, vous pouvez décider de verrouiller en fonction des besoins de l'entreprise sous RR. S'il y a un risque de lecture fantôme, nous le verrouillerons s'il n'existe pas. Cela a à la fois la sécurité et les performances des transactions. C'est pourquoi RR, en tant que niveau d'isolation par défaut de MySQL, est un niveau d'isolation de transaction, il est donc nécessaire d'avoir une bonne compréhension de la lecture fantôme.

Compréhension des erreurs de lecture fantôme : on dit que la lecture fantôme se produit lorsque la transaction A effectue deux opérations de sélection pour obtenir différents ensembles de données, c'est-à-dire que la sélection 1 obtient 10 enregistrements et la sélection 2 obtient 11 enregistrements. Il ne s'agit en fait pas d'une lecture fantôme. Il s'agit d'un type de lecture non répétable, qui se produira uniquement au niveau R-U R-C, mais ne se produira pas au niveau d'isolation RR par défaut de MySQL.

Voici ma compréhension plus vernaculaire de la lecture fantôme :

La lecture fantôme ne signifie pas que les ensembles de résultats obtenus par deux lectures sont différents. L'objectif de la lecture fantôme est représenté par le résultat d'une certaine opération de sélection. L'état des données ne peut pas le faire. soutenir les opérations commerciales ultérieures. Pour être plus précis : sélectionnez si un certain enregistrement existe. S'il n'existe pas, préparez-vous à insérer l'enregistrement. Cependant, lors de l'exécution de l'insertion, il s'avère que l'enregistrement existe déjà et ne peut pas être inséré à ce moment. se produit.

Voici un scénario plus vivant de lecture fantôme MySQL (emprunté à ma réponse sur Zhihu) :

table users: id primary key

Transaction T1

Transaction T2

étape 1 T1 : SELECT * FROM `users ` WHERE ` id` = 1;
étape 2 T2 : INSÉRER DANS LES VALEURS `utilisateurs` (1, 'gros chat');
étape 3 T1 : INSÉRER DANS LES VALEURS `utilisateurs` (1, 'gros chat');
étape 4 T1 : SELECT * FROM `users` WHERE `id` = 1;

T1 : transaction principale, détecte s'il existe un enregistrement avec l'identifiant 1 dans la table et l'insère sinon. C'est la logique métier normale à laquelle nous nous attendons.

T2 : Transaction d'interférence, le but est de perturber l'exécution normale de la transaction de T1.

Sous le niveau d'isolement RR, les étapes 1 et 2 seront exécutées normalement, mais l'étape 3 signalera une erreur de conflit de clé primaire. Pour l'activité de T1, l'exécution échoue Ici, T1 a une lecture fantôme, car T1 est à l'étape 1. L'état des données lues ne peut pas prendre en charge les opérations commerciales ultérieures. T1 : « Bon sang, le résultat que je viens de lire devrait pouvoir prendre en charge mon opération comme celle-ci. Pourquoi cela ne peut-il pas être fait maintenant ? T1 n'en revenait pas et a exécuté à nouveau l'étape 4 et a constaté que le résultat lu par setp1 était le même (mécanisme MMVC sous RR). À ce stade, une lecture fantôme s'est sans aucun doute produite. Peu importe le nombre de fois que T1 le lit, il ne peut pas trouver l'enregistrement avec l'identifiant = 1, mais il ne peut pas insérer cet enregistrement dont il a déterminé qu'il n'existait pas en lisant (ces données ont été insérées par). T2 ), pour T1, il lit fantôme.

En fait, RR peut également éviter les lectures fantômes en ajoutant manuellement une ligne. Même si l'enregistrement actuel n'existe pas, par exemple, id=1 n'existe pas, la transaction en cours obtiendra un verrouillage d'enregistrement (car le verrouillage de ligne d'InnoDB verrouille l'index , peu importe que l'entité d'enregistrement existe ou non, si elle existe, ajoutez un verrou de ligne X. S'il n'existe pas, ajoutez le verrou X de l'espace de verrouillage de la clé suivante), les autres transactions ne pourront pas y insérer d'enregistrements. index, donc les lectures fantômes sont éliminées.

Sous le niveau d'isolement SERIALIZABLE, les verrous de ligne (X)/d'espace (X) seront implicitement ajoutés lorsque l'étape 1 est exécutée, donc l'étape 2 sera bloquée, l'étape 3 s'exécutera normalement et T2 pourra continuer à s'exécuter après la soumission de T1. (L'exécution du conflit de clé primaire a échoué.) Pour T1, l'activité est correcte. Le blocage réussi a tué T2, ce qui a perturbé l'activité. Pour T1, les résultats de sa lecture anticipée peuvent soutenir ses activités ultérieures.

所以 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失败。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!