Une brève analyse du niveau d'isolation des transactions dans MySQL et une discussion de ses principes de mise en œuvre

Cet article vous amènera à comprendre les transactions dans MySQL et à parler du principe de mise en œuvre de l'isolation des transactions. J'espère qu'il pourra vous aider !

En parlant de transactions de base de données, un tas de connaissances liées aux transactions doivent facilement apparaître dans l'esprit de chacun, comme les caractéristiques ACID des transactions, les niveaux d'isolement, les problèmes résolus (lectures sales, lectures non répétables, lectures fantômes) , etc. , mais peu de gens savent vraiment comment ces caractéristiques des transactions sont mises en œuvre et pourquoi il existe quatre niveaux d'isolement.

Aujourd'hui, nous parlerons d'abord du principe de mise en œuvre de l'isolation des transactions dans MySQL, et nous continuerons à publier des articles pour analyser les principes de mise en œuvre d'autres fonctionnalités à l'avenir.

Bien sûr, MySQL est complet et profond, et les omissions dans l'article sont inévitables. Les critiques et les corrections sont les bienvenues.

Explication

La logique d'implémentation des transactions de MySQL est située au niveau de la couche moteur, et tous les moteurs ne prennent pas en charge les transactions. Les instructions suivantes sont basées sur le moteur InnoDB.

Définition

L'isolement fait référence au fait qu'après que différentes transactions soient soumises et exécutées les unes après les autres, l'effet final est en série, c'est-à-dire que pour une transaction, les données qu'elle perçoit lors de son exécution ne doivent être provoquées que par. vos propres opérations, et il ne devrait y avoir aucune modification de données causée par d'autres transactions.

L'isolement résout le problème des transactions simultanées.

Niveau d'isolation SQL standard

Le moyen le plus simple de mettre en œuvre l'isolation est que chaque transaction est exécutée en série. Si la transaction précédente n'est pas terminée, les transactions suivantes attendront. Cependant, cette méthode d’implémentation n’est évidemment pas très efficace en simultanéité et n’est pas adaptée à une utilisation dans des environnements réels.

Afin de résoudre les problèmes ci-dessus et d'atteindre différents niveaux de contrôle de concurrence, les créateurs de standards SQL ont proposé différents niveaux d'isolement : lecture non validée (lecture non validée), lecture validée (lecture validée), lecture répétable (lecture répétable), séquence sérialisable. Le niveau d'isolement le plus avancé est la lecture sérialisée, et dans d'autres niveaux d'isolement, puisque les transactions sont exécutées simultanément, certains problèmes sont plus ou moins autorisés. Voir le tableau matriciel suivant :

+

Niveau d'isolement (+ : autorisé à se produire, - : non autorisé)

lecture sale

lecture non répétable

lecture fantôme

Ne pas soumettre la lecture

+

+

+

Soumettre la lecture

-

+

peut être lu à plusieurs reprises

-

+

séquence Lecture chimique                                                                                                            

Notez que le moteur InnoDB de MySQL résout le problème de lecture fantôme grâce à des verrous d'espacement au niveau de lecture répétable, et résout le problème de lecture non répétable via MVCC. Voir l'analyse ci-dessous pour plus de détails.

Principe d'implémentation

Principe d'implémentation du niveau d'isolement des transactions SQL standard

Le problème que nous avons rencontré ci-dessus est en fait le problème de contrôle sous les transactions simultanées. Le moyen le plus courant de résoudre les transactions simultanées est le contrôle de concurrence pessimiste (c'est-à-dire les verrous dans la base de données). . La mise en œuvre du niveau d'isolation des transactions SQL standard repose sur des verrous. Paire de transactions Les données en cours de lecture sont non verrouillé ;

Au moment où une transaction met à jour certaines données (c'est-à-dire au moment où la mise à jour a lieu), elle doit d'abord ajouter un verrouillage partagé au niveau de la ligne, qui ne sera libéré qu'à la fin de la transaction. Lecture validée (RC) La transaction ajoute un verrou partagé au niveau de la ligne (verrouillé uniquement en lecture) aux données actuellement lues. Une fois la ligne lue, le verrou partagé au niveau de la ligne est immédiatement libéré ; Lorsqu'une transaction met à jour certaines données (c'est-à-dire le moment où la mise à jour a lieu), elle doit d'abord y ajouter un Lecture répétable (RR)Au moment où une transaction lit certaines données (c'est-à-dire au moment où elle commence à lire), elle doit d'abord ajouter un , qui ne sera libéré qu'à la fin de la transaction ; Lecture sérialisée (S) Lorsqu'une transaction lit des données, elle doit d'abord y ajouter un , qui ne sera libéré qu'à la fin de la transaction

On peut voir que lorsque l'on utilise uniquement des verrous pour mettre en œuvre le contrôle du niveau d'isolement, des verrouillages et déverrouillages fréquents sont nécessaires, et des conflits de lecture et d'écriture sont faciles à se produire (par exemple, au niveau RC, la transaction A met à jour la ligne de données 1, la transaction B Ensuite, avant que la transaction A ne soit validée, la ligne de données de lecture 1 doit attendre que la transaction A soit validée et libère le verrou).

Afin de résoudre le problème des conflits de lecture et d'écriture sans verrouillage, MySQL a introduit le mécanisme MVCC. Pour plus de détails, veuillez consulter mon article d'analyse précédent : Comprendre les verrous optimistes, les verrous pessimistes et MVCC dans la base de données dans un article.

Principe de mise en œuvre du niveau d'isolement des transactions InnoDB

Avant de procéder à l'analyse, nous devons d'abord comprendre plusieurs concepts :

1. Lecture verrouillée et lecture non verrouillable cohérente

Lecture verrouillée : dans un. transaction, verrouille activement la lecture, telle que SELECT ... LOCK IN SHARE MODE et SELECT ... FOR UPDATE. Des verrous partagés de ligne et des verrous exclusifs de ligne sont ajoutés respectivement. La classification des verrous peut être trouvée dans mon article d'analyse précédent : Classifications des verrous MySQL à connaître).

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Lectures cohérentes non bloquantes : InnoDB utilise MVCC pour fournir un certain moment dans le temps aux requêtes de transaction Instantané de base de données. La requête verra les modifications apportées par les transactions validées avant ce moment, mais pas les modifications apportées par les transactions ultérieures ou non validées (autres que cette transaction). C'est-à-dire qu'après le démarrage d'une transaction, les données vues par la transaction sont les données au moment où la transaction est démarrée, et les modifications ultérieures des autres transactions ne seront pas visibles dans cette transaction.

La lecture cohérente est le mode par défaut d'InnoDB pour le traitement des instructions SELECT aux niveaux d'isolement RC et RR. Les lectures non verrouillables cohérentes ne définissent aucun verrou sur les tables auxquelles elles accèdent. Ainsi, tout en effectuant des lectures non verrouillables cohérentes sur les tables, d'autres transactions peuvent les lire ou les modifier simultanément.

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

2 Lecture actuelle et lecture instantanée

La lecture actuelle

est lue Le. dernière version, comme UPDATE, DELETE, INSERT, SELECT... LOCK IN SHARE MODE, SELECT... FOR UPDATECes opérations sont toutes des lectures actuelles. Pourquoi sont-elles appelées lectures actuelles ? Autrement dit, il lit la dernière version de l'enregistrement lors de la lecture, il doit également s'assurer que d'autres transactions simultanées ne peuvent pas modifier l'enregistrement actuel et que l'enregistrement lu sera verrouillé.

Snapshot read

lit la version de l'instantané, qui est la version historique. L'opération SELECT sans verrouillage est une lecture d'instantané, qui est une lecture non bloquante sans verrouillage La prémisse de la lecture d'instantané est le niveau d'isolement ; n'est pas les niveaux de lecture non validée et de lecture sérialisée, car la lecture non validée lit toujours la dernière ligne de données, plutôt que la ligne de données conforme à la version actuelle de la transaction, et la lecture sérialisée verrouillera la table.

3. Verrouillage implicite et verrouillage explicite

Verrouillage implicite

InnoDB utilise un protocole de verrouillage en deux phases lors de l'exécution de la transaction (sans effectuer activement de verrouillage explicite) :

• Vous pouvez le faire à tout moment. verrouillage, InnoDB se verrouillera automatiquement en cas de besoin en fonction du niveau d'isolement ;
• Le verrou ne sera libéré que lorsque la validation ou la restauration sera exécutée, et tous les verrous seront libérés en même temps.

Verrouillage explicite

• InnoDB prend également en charge le verrouillage explicite (couche moteur de stockage) via des instructions spécifiques

  select ... lock in share mode //共享锁
  select ... for update //排他锁

• Verrouillage explicite de la couche MySQL Server :

  lock table
  unlock table

Après avoir compris les concepts ci-dessus, prenons un regardez comment les transactions InnoDB sont implémentées (les lectures suivantes font toutes référence à des sélections non activement verrouillées)

	verrou exclusif au niveau de la ligne , et il ne sera libéré qu'à la fin de la transaction.
verrouillage partagé au niveau de la ligne	Transaction Au moment où certaines données sont mises à jour (c'est-à-dire au moment où la mise à jour a lieu), un verrouillage exclusif au niveau de la ligne doit d'abord y être ajouté, et il ne sera libéré qu'après la fin de la transaction.
verrou partagé au niveau de la table	Lorsqu'une transaction est mise à jour ; les données, il doit d'abord les ajouter Le verrouillage exclusif au niveau de la table n'est libéré qu'à la fin de la transaction.

事务隔离级别	实现方式
未提交读（RU）	事务对当前被读取的数据不加锁，都是当前读； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级共享锁，直到事务结束才释放。
提交读（RC）	事务对当前被读取的数据不加锁，且是快照读； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁（Record），直到事务结束才释放。
可重复读（RR）	事务对当前被读取的数据不加锁，且是快照读； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁（Record，GAP，Next-Key），直到事务结束才释放。 通过间隙锁，在这个级别MySQL就解决了幻读的问题 通过快照，在这个级别MySQL就解决了不可重复读的问题
序列化读（S）	事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放，都是当前读； 事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。

可以看到，InnoDB通过MVCC很好的解决了读写冲突的问题，而且提前一个级别就解决了标准级别下会出现的幻读问题，大大提升了数据库的并发能力。

一些常见误区

幻读到底包不包括了delete的情况?

不可重复读：前后多次读取一行，数据内容不一致，针对其他事务的update和delete操作。为了解决这个问题，使用行共享锁，锁定到事务结束（也就是RR级别，当然MySQL使用MVCC在RC级别就解决了这个问题）

幻读：当同一个查询在不同时间生成不同的行集合时就是出现了幻读，针对的是其他事务的insert操作，为了解决这个问题，锁定整个表到事务结束（也就是S级别，当然MySQL使用间隙锁在RR级别就解决了这个问题）

网上很多文章提到幻读和提交读的时候，有的说幻读包括了delete的情况，有的说delete应该属于提交读的问题，那到底真相如何呢？我们实际来看下MySQL的官方文档（如下）

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT) is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

可以看到，幻读针对的是结果集前后发生变化，所以看起来delete的情况应该归为幻读，但是我们实际分析下上面列出的标准SQL在RR级别的实现原理就知道，标准SQL的RR级别是会对查到的数据行加行共享锁，所以这时候其他事务想删除这些数据行其实是做不到的，所以在RR下，不会出现因delete而出现幻读现象，也就是幻读不包含delete的情况。

MVCC能解决了幻读问题?

网上很多文章会说MVCC或者MVCC+间隙锁解决了幻读问题，实际上MVCC并不能解决幻读问题。如以下的例子：

begin;

#假设users表为空，下面查出来的数据为空

select * from users; #没有加锁

#此时另一个事务提交了，且插入了一条id=1的数据

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，所以更新成功，并生成一个更新的快照

select * from users; #读快照，查出来id为1的一条记录，因为MVCC可以查到当前事务生成的快照

commit;

可以看到前后查出来的数据行不一致，发生了幻读。所以说只有MVCC是不能解决幻读问题的，解决幻读问题靠的是间隙锁。如下：

begin;

#假设users表为空，下面查出来的数据为空

select * from users lock in share mode; #加上共享锁

#此时另一个事务B想提交且插入了一条id=1的数据，由于有间隙锁，所以要等待

select * from users; #读快照，查出来的数据为空

update users set name='mysql' where id=1;#update是当前读，由于不存在数据，不进行更新

select * from users; #读快照，查出来的数据为空

commit;

#事务B提交成功并插入数据

注意，RR级别下想解决幻读问题，需要我们显式加锁，不然查询的时候还是不会加锁的。

原文地址：https://segmentfault.com/a/1190000025156465

作者: X先生

【相关推荐：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!