MySQL résume le principe MVCC d'InnoDB-tutoriel mysql-php.cn

Cet article vous apporte des connaissances pertinentes sur mysql, qui introduit principalement les problèmes liés au principe MVCC d'InnoDB MVCC est un contrôle de concurrence multi-version, principalement pour améliorer les performances de concurrence de la base de données. j'espère que cela aide tout le monde.

MySQL résume le principe MVCC d'InnoDB

Apprentissage recommandé : Tutoriel vidéo mysql

MVCC signifie Multi-Version Concurrency Control, qui est un contrôle de concurrence multi-version, principalement destiné à améliorer les performances de concurrence de la base de données. Lorsqu'une demande de lecture ou d'écriture se produit pour la même ligne de données, elle sera verrouillée et bloquée. Mais MVCC utilise une meilleure façon de gérer les requêtes de lecture-écriture, de sorte qu'aucun verrouillage ne se produise lorsqu'un conflit de requêtes de lecture-écriture se produit. Cette lecture fait référence à la lecture de l'instantané, et non à la lecture actuelle. La lecture actuelle est une opération de verrouillage et est un verrou pessimiste. Alors, comment parvient-il à lire et à écrire sans verrouillage ? Que signifient la lecture d'instantané et la lecture actuelle ? Nous l’apprendrons tous plus tard.

MySQL peut largement éviter les problèmes de lecture fantôme sous le niveau d'isolement REPEATABLE READ. Comment MySQL fait-il cela ?

Chaîne de versions

Nous savons que pour les tables utilisant le moteur de stockage InnoDB, ses enregistrements d'index cluster contiennent deux colonnes cachées nécessaires (row_id n'est pas nécessaire, la table que nous avons créée a une clé primaire ou une clé UNIQUE non NULL le fera n'inclut pas la colonne row_id) :

trx_id : chaque fois qu'une transaction modifie un enregistrement d'index clusterisé, l'identifiant de la transaction sera attribué à la colonne cachée trx_id.
roll_pointer : chaque fois qu'un enregistrement d'index clusterisé est modifié, l'ancienne version sera écrite dans le journal d'annulation. Cette colonne cachée équivaut à un pointeur, qui peut être utilisé pour trouver l'enregistrement avant les informations de modification.

Pour illustrer ce problème, nous créons une table de démonstration :

CREATE TABLE `teacher` (
  `number` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `domain` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Copier après la connexion

Insérons ensuite une donnée dans cette table :

mysql> insert into teacher values(1, 'J', 'Java');Query OK, 1 row affected (0.01 sec)

Copier après la connexion

Les données ressemblent maintenant à ceci :

mysql> select * from teacher;
+--------+------+--------+
| number | name | domain |
+--------+------+--------+
|      1 | J    | Java   |
+--------+------+--------+
1 row in set (0.00 sec)

Copier après la connexion

Supposons que l'ID de transaction d'insertion de l'enregistrement est 60 , alors le diagramme schématique de l'enregistrement à ce moment est le suivant :

MySQL résume le principe MVCC dInnoDB

Supposons que deux transactions avec les ID de transaction 80 et 120 effectuent des opérations de MISE À JOUR sur cet enregistrement. Le processus d'opération est le suivant :

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

MySQL résume le principe MVCC dInnoDB

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。于是可以利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为多版本并发控制(Mulit-Version Concurrency Control MVCC)。

ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

对于使用SERIALIZABLE隔离级别的事务来说，InnoDB使用加锁的方式来访问记录。

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：READ COMMITTED和REPEATABLE READ隔离级别在不可重复读和幻读上的区别，这两种隔离级别关键是需要判断一下版本链中的哪个版本是当前事务可见的。

为此，InnoDB提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
creator_trx_id：表示生成该ReadView的事务的事务id。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间(min_trx_id
如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

我们还是以表teacher为例，假设现在表teacher中只有一条由事务id为60的事务插入的一条记录，接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。

READ COMMITTED每次读取数据前都生成一个ReadView

假设现在系统里有两个事务id分别为80、120的事务在执行：

# Transaction 80
set session transaction isolation level read committed;
begin
update teacher set name='S' where number=1;
update teacher set name='T' where number=1;

Copier après la connexion

此刻，表teacher中number为1的记录得到的版本链表如下所示：

MySQL résume le principe MVCC dInnoDB

假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

set session transaction isolation level read committed;
# 使用READ COMMITTED隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

Copier après la connexion

这个SELECE1的执行过程如下：

在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，最新版本的列name的内容是’T’，该版本的trx_id值为80，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值也为80，也在m_ids列表内，根据步骤4也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView 中的min_trx_id值，根据步骤2判断这个版本是符合要求的。

之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

set session transaction isolation level read committed;
# Transaction 120
begin
update teacher set name='K' where number=1;
update teacher set name='F' where number=1;

Copier après la connexion

此刻，表teacher 中number为1的记录的版本链就长这样：

MySQL résume le principe MVCC dInnoDB

然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个number 为1的记录，如下：

# 使用READ COMMITTED隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'
# SELECE2：Transaction 80提交、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'T'

Copier après la connexion

这个SELECE2 的执行过程如下：

在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[120]（事务id为80的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为120，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’F’，该版本的trx_id值为120，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name 的内容是’K’，该版本的trx_id值为120，也在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’T’，该版本的trx_id值为80，小于ReadView中的min_trx_id值120，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’‘T’'的记录。

以此类推，如果之后事务id为120的记录也提交了，再次在使用READCOMMITTED隔离级别的事务中查询表teacher中number值为1的记录时，得到的结果就是’F’了，具体流程我们就不分析了。

总结一下就是：使用READCOMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。

假设现在系统里有两个事务id分别为80、120的事务在执行：

# Transaction 80
begin
update teacher set name='S' where number=1;
update teacher set name='T' where number=1;

Copier après la connexion

此刻，表teacher中number为1的记录得到的版本链表如下所示：

MySQL résume le principe MVCC dInnoDB

假设现在有一个使用REPEATABLE READ隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

Copier après la connexion

这个SELECE1的执行过程如下（与READ COMMITTED的过程一致）：

在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，最新版本的列name的内容是’T’，该版本的trx_id值为80，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值也为80，也在m_ids列表内，根据步骤4也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView 中的min_trx_id值，根据步骤2判断这个版本是符合要求的。

之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

# Transaction 80
begin
update teacher set name='K' where number=1;
update teacher set name='F' where number=1;

Copier après la connexion

此刻，表teacher 中number为1的记录的版本链就长这样：

MySQL résume le principe MVCC dInnoDB

然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，如下：

# 使用REPEATABLE READ隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'
# SELECE2：Transaction 80提交、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

Copier après la connexion

这个SELECE2的执行过程如下：

Étant donné que le niveau d'isolement de la transaction en cours est REPEATABLE READ et que ReadView a déjà été généré lors de l'exécution de SELECE1, le ReadView précédent est donc directement réutilisé à ce moment-là. Le contenu de la liste m_ids du ReadView précédent est [80, 120. ], min_trx_id vaut 80, max_trx_id vaut 121 et Creator_trx_id vaut 0.
Sélectionnez ensuite les enregistrements visibles de la chaîne de versions. Comme le montre la figure, le contenu du nom de colonne de la dernière version est 'F'. La valeur trx_id de cette version est 120. Dans la liste m_ids, elle est affichée. n'est pas visible selon l'étape 4. Exigences sexuelles, passez à la version suivante selon roll_pointer.
Le contenu du nom de colonne de la prochaine version est 'K'. La valeur trx_id de cette version est 120, qui est également dans la liste m_ids. Selon l'étape 4, elle ne répond pas aux exigences de visibilité et passe à la liste m_ids. prochaine version selon le roll_pointer.
Le contenu du nom de colonne de la prochaine version est 'T'. La valeur trx_id de cette version est 80, qui est également dans la liste m_ids. Selon l'étape 4, elle ne répond pas aux exigences de visibilité et passe à la liste m_ids. prochaine version selon le roll_pointer.
Le contenu du nom de colonne de la prochaine version est 'S'. La valeur trx_id de cette version est 80, qui est également dans la liste m_ids. Selon l'étape 4, elle ne répond pas aux exigences de visibilité et passe à la liste m_ids. prochaine version selon le roll_pointer.
Le contenu du nom de colonne de la prochaine version est 'J'. La valeur trx_id de cette version est 60, ce qui est inférieur à la valeur min_trx_id 80 dans ReadView, indiquant que la transaction qui a généré cette version a été validée avant le la transaction en cours génère ReadView, donc cette version est Si les conditions sont remplies, la version finale renvoyée à l'utilisateur est l'enregistrement dont le nom de colonne est « J ».

C'est-à-dire que les résultats obtenus par les deux requêtes SELECT sont répétés et que les valeurs de colonne enregistrées sont toutes des ''J''''.

Si nous soumettons l'enregistrement avec l'ID de transaction 120 plus tard, puis continuons à rechercher l'enregistrement avec le numéro 1 dans la transaction qui vient d'utiliser le niveau d'isolement REPEATABLE READ, le résultat sera toujours « J ». Vous pouvez vérifier le spécifique. processus d'exécution Analysez-le vous-même.

Phénomène de lecture fantôme et solution de lecture fantôme sous MVCC

Nous savons déjà avant que MVCC peut résoudre le problème de lecture non répétable sous le niveau d'isolement REPEATABLE READ, mais qu'en est-il de la lecture fantôme ? Comment le MVCC est-il résolu ? Une lecture fantôme se produit lorsqu'une transaction lit des enregistrements plusieurs fois selon les mêmes conditions. La dernière lecture lit un enregistrement qui n'a pas été lu auparavant, et cet enregistrement provient d'un nouvel enregistrement ajouté par une autre transaction.

Nous pouvons y penser, la transaction T1 sous le niveau d'isolement REPEATABLE READ lit d'abord plusieurs enregistrements en fonction d'une certaine condition de recherche, puis la transaction T2 insère un enregistrement qui répond à la condition de recherche correspondante et le soumet, puis la transaction T1 recherche à nouveau en fonction sur le même Exécution conditionnelle de la requête. Quel sera le résultat ? Selon les règles de comparaison dans ReadView :

Que la transaction T2 soit ouverte ou non avant la transaction T1, la transaction T1 ne peut pas voir la soumission de T2. Veuillez l'analyser vous-même en fonction de la chaîne de versions, de ReadView et des règles de jugement de visibilité présentées ci-dessus.

Cependant, MVCC dans InnoDB sous le niveau d'isolement REPEATABLE READ peut éviter dans une large mesure la lecture fantôme, plutôt que d'interdire complètement la lecture fantôme. Que se passe-t-il? Regardons la situation suivante :

Trx80	Trx120
begin
	begin
mettre à jour le nom de l'enseignant = 'S' où numéro = 1;
mettre à jour le professeur définir le nom = 'T' où numéro = 1; ;
commit

T1	T2
begin;
sélectionner * de l'enseignant où nombre=30 ;
insérer dans les valeurs de l'enseignant (30, 'X', 'Java') ; enseignant où nombre = 30 ; a des données
Eh bien, que se passe-t-il ? La transaction T1 présente évidemment un phénomène de lecture fantôme. Sous le niveau d'isolement REPEATABLE READ, T1 génère un ReadView lors de la première exécution d'une instruction SELECT normale, puis T2 insère un nouvel enregistrement dans la table professeur et le soumet. ReadView ne peut pas empêcher T1 d'exécuter l'instruction UPDATE ou DELETE pour modifier l'enregistrement nouvellement inséré (puisque T2 a déjà soumis, la modification de l'enregistrement ne provoquera pas de blocage), mais de cette façon, la valeur de la colonne cachée trx_id de ce nouvel enregistrement sera be Cela devient l'identifiant de transaction de T1. Après cela, T1 peut voir cet enregistrement lorsqu'il utilise une instruction SELECT ordinaire pour interroger cet enregistrement et peut renvoyer cet enregistrement au client. Du fait de l'existence de ce phénomène particulier, on peut aussi penser que MVCC ne peut pas interdire complètement la lecture fantôme. Résumé MVCC Nous pouvons voir dans la description ci-dessus que ce que l'on appelle MVCC (Multi-Version ConcurrencyControl, contrôle de concurrence multi-version) fait référence à l'utilisation des deux niveaux d'isolement de READ COMMITTD et REPEATABLE READ pour exécuter des transactions ordinaires. L'opération SELECT est le processus d'accès à la chaîne de versions enregistrées. Cela permet aux opérations de lecture-écriture et d'écriture-lecture de différentes transactions d'être exécutées simultanément, améliorant ainsi les performances du système. Une grande différence entre les deux niveaux d'isolement de READ COMMITTD et REPEATABLE READ est que le moment de génération de ReadView est différent. READ COMMITTD générera un ReadView avant chaque opération SELECT ordinaire, tandis que REPEATABLE READ ne générera un ReadView que pour la première fois. . Générez simplement un ReadView avant l'opération SELECT et réutilisez ce ReadView pour les opérations de requête ultérieures, évitant ainsi fondamentalement le phénomène de lecture fantôme. Nous avons dit auparavant que l'exécution d'une instruction DELETE ou d'une instruction UPDATE qui met à jour la clé primaire ne supprimera pas immédiatement l'enregistrement correspondant de la page, mais effectuera ce qu'on appelle l'opération de suppression de marque, ce qui équivaut à simplement définir un. supprimer l'indicateur sur l'enregistrement. Ceci est principalement pour MVCC. De plus, ce qu'on appelle MVCC ne prend effet que lorsque nous effectuons des requêtes SEELCT ordinaires. Toutes les instructions SELECT que nous avons vues jusqu'à présent sont des requêtes ordinaires. Quant à ce qu'est une requête extraordinaire, nous en reparlerons plus tard. Apprentissage recommandé : Tutoriel vidéo mysql