Problèmes liés au RR et à la lecture fantôme dans MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement le contenu pertinent sur RR et la lecture fantôme, y compris le principe MVCC, la génération de lecture fantôme RR, la résolution de lecture fantôme RR, etc. Ce qui suit est Jetons un coup d'œil, espérons ça aide tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

1. Introduction

Cet article se concentre sur ces trois sujets Comment RR résout-il la lecture fantôme ?

• Principe MVCC

• Expérience : RR et lecture fantôme

• Cas : blocage

Tout d'abord, passons en revue les quatre types d'isolation de transactions et de transactions simultanées pris en charge par InnoDB dans MySQL Quelques problèmes :

• Lecture non validée : il peut lire le processus intermédiaire d'une transaction, qui viole les caractéristiques ACID et présente le problème de la lecture sale, qui n'est fondamentalement pas utilisée.

• Lire le commit : Indique que si d'autres transactions ont été soumises, vous pouvez le voir. Il n’est pas beaucoup utilisé dans les environnements de production.

• Lecture répétable : niveau par défaut, le plus utilisé. Il dispose du verrouillage Gap.

• Sérialisable : toutes les implémentations sont implémentées via des verrous.

Le traitement des transactions simultanées entraînera également quelques problèmes : lectures sales, lectures non répétables, lectures fantômes

• Lectures sales : une transaction modifie un enregistrement avant que la transaction ne soit terminée et soumise, cet enregistrement Les données sont. dans un état incohérent.

• Lecture non répétable : Une transaction est lue deux fois selon les mêmes conditions de requête, et les données lues sont incohérentes (modification, suppression).

• Lecture fantôme : réinterrogez les données selon les mêmes conditions de requête au sein d'une transaction, mais constatez que d'autres transactions ont inséré de nouvelles données qui satisfont à leurs conditions de requête.

Pour résumer le contexte de cet article : RR introduit MVCC pour une concurrence plus rapide, mais il existe une possibilité de lecture fantôme. Pour résoudre la lecture fantôme, le verrouillage Gap est introduit et Gap peut provoquer une impasse.

2. Principe MVCC

MVCC (Multiple Version Control) : fait référence à la base de données afin d'obtenir un accès simultané élevé aux données, un traitement multi-version des données et, grâce à la visibilité des transactions, de garantir que les transactions peuvent voir ce qu'elles devraient voir Version des données.

Le plus grand avantage de MVCC est qu'il n'y a pas de verrouillage pour la lecture et qu'il n'y a pas de conflit entre la lecture et l'écriture.

Dans les applications OLTP (On-Line Transaction Processing), il est important qu'il n'y ait pas de conflit entre la lecture et l'écriture. Presque tous les SGBDR prennent en charge MVCC.

Remarque : MVCC ne fonctionne que sous deux niveaux d'isolement : RC en lecture-validation et RR en lecture répétable.

Remarque : MVCC ne fonctionne que sous deux niveaux d'isolement : RC en lecture-validation et RR en lecture répétable.

Remarque : MVCC ne fonctionne que sous deux niveaux d'isolement : RC en lecture-validation et RR en lecture répétable.

(1) Implémentation multi-version MVCC

Lorsque MySQL implémente le mécanisme MVCC, il est basé sur la chaîne multi-version d'annulation de journal + le mécanisme ReadView.

• Chaîne multi-version du journal d'annulation : chaque fois que la base de données est modifiée, le numéro de transaction de l'enregistrement de modification actuel et l'adresse de stockage de l'état des données avant la modification (c'est-à-dire ROLL_PTR) seront enregistrés dans le journal d'annulation afin qu'il peut être restauré si nécessaire. Passez à une ancienne version de données.

• Mécanisme ReadView : Basé sur la chaîne multi-version, contrôlez la visibilité de la lecture des transactions. (La principale différence est : RC et RR)

Je ne me concentre pas sur l'exploration des principes ici, mais j'ai besoin d'avoir un concept général : annuler la chaîne multi-version du journal et le mécanisme ReadView.

Pour la chaîne multi-versions d'annulation du journal, voici un exemple :

• Une transaction de lecture interroge l'enregistrement actuel, mais la dernière transaction n'a pas encore été soumise.

• Selon l'atomicité, les transactions de lecture ne peuvent pas voir les dernières données, mais elles peuvent trouver des versions plus anciennes de données dans le segment de restauration, générant ainsi plusieurs versions.

Pour le mécanisme ReadView : basé sur l'implémentation d'une chaîne multi-versions d'annulation de journal, différentes isolations de transactions ont un traitement différent :

• Transactions de niveau RC : la visibilité est relativement élevée, elle peut voir toutes les modifications des transactions soumises.

• Transactions au niveau RR : dans une transaction de lecture, quelles que soient les modifications apportées par d'autres transactions aux données et si elles sont soumises, tant que vous ne les soumettez pas, les résultats des données de la requête ne changeront pas.

Comment ça se fait ?

Soumission de lecture RC : chaque instruction d'opération de lecture obtiendra un ReadView. Après chaque mise à jour, le dernier statut de soumission de transaction dans la base de données sera obtenu et vous pourrez voir la dernière transaction soumise, c'est-à-dire que chaque exécution d'instruction mettra à jour sa visibilité. voir.

Lecture répétable RR : ReadView ne sera pas obtenu lors du démarrage d'une transaction, et ReadView ne sera obtenu que lorsque la première lecture d'instantané est lancée.

Si vous utilisez la lecture actuelle, vous obtiendrez un nouveau ReadView et vous pourrez également voir les données mises à jour.

(2) Lecture instantanée et lecture actuelle

Dans le contrôle de concurrence MVCC, les opérations de lecture peuvent être divisées en deux catégories :

Lecture instantanée : Lire la version visible de l'enregistrement (éventuellement la version historique), non besoin de verrouiller.

Fonctionnement : opération de sélection simple.

Lecture actuelle : la dernière version de l'enregistrement est lue et l'enregistrement renvoyé par la lecture actuelle sera verrouillé pour garantir que d'autres transactions ne modifieront pas cet enregistrement simultanément.

Opérations : opérations de lecture spéciales, opérations d'ajout/mise à jour/suppression.

-- 对应 SQL 如下：
-- 1. 特殊读操作
SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE  -- 共享锁
-- 2. 新增：INSERT 
-- 3. 更新：UPDATE
-- 4. 删除：DELETE

Combiné avec le mécanisme ReadView pour distinguer : lecture d'instantané et lecture en cours :

Lecture d'instantané : dans une transaction, ReadView ne sera obtenu que lorsque la première lecture d'instantané est lancée, et les opérations de lecture suivantes ne l'obtiendront plus.

Lecture actuelle : ReadView sera obtenu pour chaque opération de lecture.

3. Expérience : RR et lecture fantôme

Question d'entretien : sous le niveau d'isolement des transactions RR, la transaction A interroge une donnée et la transaction B ajoute une donnée.

Cette question est relativement vague, mais nous savons que le point d'inspection général est le RR et la lecture fantôme. La question peut être divisée en deux catégories :

Dans quelles circonstances le RR produit-il une lecture fantôme ? (Peut voir les données)

Réponse : Lecture actuelle (SÉLECTIONNER.. POUR LA MISE À JOUR, SÉLECTIONNER... VERROUILLER EN MODE PARTAGE)

Dans quelles circonstances RR résout-il les lectures fantômes ? (Impossible de voir les données)

Réponse : verrouillage, lecture d'instantanés

Remarque : la lecture non répétable se concentre sur UPDATA et DELETE, tandis que la lecture fantôme se concentre sur INSERT.

La plus grande différence entre eux réside dans la manière de résoudre les problèmes qu'ils causent grâce au mécanisme de verrouillage.

Le verrou mentionné ici utilise uniquement le mécanisme de verrouillage pessimiste.

Revoyons : Lecture fantôme

-- 举个栗子：有这样一个查询 SQL
SELECT * FROM user WHERE id < 10;

Dans le cadre d'une même transaction, 4 données sont interrogées à l'instant T1, et 8 données sont interrogées à l'instant T2. Cela crée une lecture fantôme.

Dans le cadre d'une même transaction, 8 données sont interrogées à l'instant T1, et 4 données sont interrogées à l'instant T2. Cela crée une lecture fantôme.

La préparation de l'expérience est la suivante : Pratique pratique

show variables like 'transaction_isolation'; -- 事务隔离级别 RR
select version();                            -- 版本 8.0.16
show variables like '%storage_engine%';      -- 引擎 InnoDB
-- 1. 手动开启事务提交
begin;  -- 开始事务
commit; -- 提交事务
-- 2. 创建表
CREATE TABLE IF NOT EXISTS `student` (
`id` INT NOT NULL COMMENT '主键 id',
`name` VARCHAR(50) NOT NULL COMMENT '名字',
`age` TINYINT NOT NULL COMMENT '年龄',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '学生表';
-- 3. 新增数据用于实验
INSERT INTO student (id, name, age) VALUES (5, 'kunkun', 14);
INSERT INTO student (id, name, age) VALUES (30, 'ikun', 18);

(1) RR génère une lecture fantôme

L'expérience est la suivante : Testez la lecture actuelle

Expérience 1 : SELECT d'abord, puis SELECT. .. POUR LA MISE À JOUR

Expérience 2 : SELECT d'abord, puis UPDATE (aucune lecture fantôme ne se produira)

Expérience 1 : SELECT d'abord, puis SELECT... POUR LA MISE À JOUR

-- 事务A：
BEGIN;
SELECT * FROM student WHERE id < 30;
SELECT * FROM student WHERE id < 30 FOR UPDATE;  -- 等待事务B commit 后再执行
-- SELECT * FROM student WHERE id < 30 LOCK IN SHARE MODE;
COMMIT;
-- 事务B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, &#39;wulikun&#39;, 16);
COMMIT;

Ce qui s'est passé est comme indiqué dans l'image ci-dessous :

L'enregistrement de l'expérience est tel que montré dans l'image ci-dessous :

Conclusion du phénomène : lors de l'utilisation de la lecture actuelle (SELECT ... FOR UPDATE), des lectures fantômes se produiront.

De même, l'utilisation de SELECT ... LOCK IN SHARE MODE produira des lectures fantômes.

Expérience 2 : SÉLECTIONNEZ d'abord, puis MISE À JOUR

-- 事务A：
BEGIN;
SELECT * FROM student WHERE id < 30;
UPDATE student SET name = &#39;zhiyin&#39; WHERE id = 5;  -- 等待事务B commit 后再执行
SELECT * FROM student WHERE id < 30;
COMMIT;
-- 事务B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, &#39;wulikun&#39;, 16);
COMMIT;

Ce qui s'est passé est comme indiqué dans l'image ci-dessous :

L'enregistrement de l'expérience est tel qu'indiqué dans l'image ci-dessous :

Conclusion du phénomène : la lecture actuelle (UPDATE) ne générera pas de lectures fantômes. Ni INSERT / DELETE ne fera la même chose.

(2) RR résout la lecture fantôme

L'expérience est la suivante :

• Expérience 1 : Lecture instantanée enregistrements)

• Expérience 3 : Verrouillage (SELECT... POUR MISE À JOUR)

• Expérience 1 : Lecture d'instantané, SELECT ordinaire

-- 事务A：
BEGIN;
SELECT * FROM student;
SELECT * FROM student;  -- 等待事务B commit 后再执行
COMMIT;
-- 事务B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;

Ce qui se passe est comme indiqué ci-dessous :

L'enregistrement de l'expérience est comme indiqué ci-dessous :

Conclusion du phénomène : Sous le niveau d'isolement des transactions RR, seules les lectures d'instantanés (SELECT) ne provoqueront pas de lectures fantômes. Il n'y a pas de lecture en cours.

Expérience 2 : Verrouillage (mise à jour des enregistrements non existants)

Sous le niveau d'isolement RR, la transaction A utilise UPDATE pour verrouiller, la transaction B ne peut pas insérer de nouvelles données entre les deux, donc la transaction A lit avant et après UPDATE Les données reste cohérent et les lectures fantômes sont évitées.

-- 事务A：
BEGIN;
SELECT * FROM student;
UPDATE student SET name = 'wulikunkun' WHERE id = 18; -- 记录不存在，产生间隙锁 (5, 30)。
COMMIT;
-- 事务B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (10, 'zhiyin', 16); -- 需要等待事务A结束。
COMMIT;
-- 事务C：
BEGIN;
INSERT INTO student (id, name, age) VALUES (40, 'zhiyin你太美', 32);
COMMIT;
-- 查询数据库中当前有哪些锁
SELECT INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

Ce qui s'est passé est comme le montre l'image ci-dessous :

实验记录如下图所示：

现象结论：

一开始先加 临键锁Next-key lock，锁范围为 (5,30]。

因为是唯一索引，且更新的记录不存在，临键锁退化成 间隙锁Gap，最终锁范围为 (5,30)。其余的记录不受影响。

实验三：加锁（SELECT ... FOR UPDATE）

-- 事务A：
BEGIN;
SELECT * FROM student;
SELECT * FROM student WHERE id < 5 FOR UPDATE;
COMMIT;
-- 事务B：
BEGIN;
INSERT INTO student (id, name, age) VALUES (4, &#39;zhiyin&#39;, 4); -- 需要等待事务A结束。
COMMIT;
-- 事务C：
BEGIN;
INSERT INTO student (id, name, age) VALUES (5, &#39;zhiyin你太美&#39;, 32); -- 插入成功
COMMIT;
-- 查询数据库中当前有哪些锁
SELECT INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

发生情况如下图所示：

实验记录如下图所示：

现象结论：

先加 临键锁Next-key lock，锁范围为 (-∞,5]。

所以，id

拓展：Gap 锁（间隙锁）

根据 官方文档 可知：

• 锁是加在索引上的。

• 记录锁： 行锁，只会锁定一条记录。

• 间隙锁 ：是在索引记录之间的间隙上的锁，区间为前开后开 (,)。

• 临键锁（Next-Key Lock）： 由 记录锁 和 间隙锁Gap 组合起来。

• 加锁的基本单位是 临键锁，其加锁区间为前开后闭 (,]。

• 索引上的等值查询，给唯一索引加锁的时候，如果满足条件，临键锁 退化为 行锁。

• 索引上的等值查询，给唯一索引加锁的时候，如果不满足条件，临键锁 退化为 间隙锁。注意，非等值查询是不会优化的。

推荐学习：mysql视频教程

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!