Un article expliquant en détail les principes des transactions et MVCC dans MySQL

Cet article vous amènera à comprendre les transactions dans MySQL et à présenter le principe de MVCC. J'espère qu'il pourra vous aider !

01 Qu'est-ce qu'une transaction ?

Une transaction de base de données fait référence à un ensemble d'opérations de données. Les opérations au sein de la transaction sont soit toutes réussies, soit toutes des échecs. En fait, ce n'est pas que rien n'est fait. fait, mais tant qu'une étape échoue, cela annulera toutes les opérations, c'est un peu comme ne rien faire sans rien faire d'autre.

Dans MySQL, la prise en charge des transactions est implémentée au niveau de la couche moteur. MySQL est un système qui prend en charge plusieurs moteurs, mais tous les moteurs ne prennent pas en charge les transactions. Par exemple, le moteur natif MyISAM de MySQL ne prend pas en charge les transactions, ce qui est l'une des raisons importantes pour lesquelles MyISAM a été remplacé par InnoDB.

1.1 Quatre caractéristiques majeures

• Atomicité : Une fois la transaction lancée, toutes les opérations sont soit terminées, soit non terminées, et il est impossible de stagner au milieu. Si une erreur se produit lors de l'exécution de la transaction, elle sera restaurée à l'état avant le début de la transaction et toutes les opérations se dérouleront comme si elles ne s'étaient pas produites. C’est-à-dire que les choses forment un tout indivisible, tout comme les atomes appris en chimie, qui sont les unités de base de la matière.
• Cohérence : les contraintes d'intégrité de la base de données ne sont pas violées avant et après le début et la fin de la transaction. Par exemple, lorsque A transfère de l’argent à B, il est impossible pour A de déduire l’argent mais B de ne pas le recevoir.
• Isolement : une seule transaction est autorisée à demander les mêmes données en même temps, et il n'y a aucune interférence entre les différentes transactions. Par exemple, A retire de l'argent d'une carte bancaire. B ne peut pas transférer d'argent sur cette carte avant que le processus de retrait de A ne soit terminé.
• Durabilité : une fois la transaction terminée, toutes les mises à jour de la base de données par la transaction seront enregistrées dans la base de données et ne pourront pas être annulées.

1.2 Niveau d'isolement

Parmi les quatre caractéristiques des transactions SQL, l'atomicité, la cohérence et la durabilité sont toutes relativement faciles à comprendre. Mais le niveau d'isolation des transactions est effectivement difficile. Aujourd'hui, nous parlerons principalement de l'isolation des transactions MySQL.

Les niveaux d'isolement des transactions standard SQL, de bas en haut, sont : lecture non validée (lecture non validée), lecture validée (lecture validée), lecture répétable (lecture répétable) et sérialisable (sérialisable). Plus le niveau est élevé, plus l'efficacité est faible.

• Lecture non validée : lorsqu'une transaction n'a pas été validée, les modifications qu'elle apporte peuvent être vues par les autres transactions.
• Lire la validation : une fois qu'une transaction est validée, les modifications qu'elle apporte seront visibles par les autres transactions.
• Lecture répétable : les données vues lors de l'exécution d'une transaction sont toujours cohérentes avec les données vues au démarrage de la transaction. Bien entendu, sous le niveau d’isolement de lecture reproductible, les modifications non validées sont également invisibles pour les autres transactions.
• Sérialisation : comme son nom l'indique, pour la même ligne d'enregistrements, "write" ajoutera un "verrouillage en écriture" et "read" ajoutera un "verrouillage en lecture". Lorsqu'un conflit de verrouillage en lecture-écriture se produit, la transaction accédée ultérieurement doit attendre la fin de la transaction précédente avant de pouvoir continuer à s'exécuter. Donc, toutes les données sous ce niveau d'isolement sont les plus stables, mais les performances sont également les pires.

1.3 Problèmes de concurrence résolus

Le niveau d'isolation des transactions SQL est conçu pour résoudre au maximum les problèmes de concurrence :

• Lecture sale : la transaction A lit les données mises à jour par la transaction B, puis B renvoie l'opération Roll , alors les données lues par A sont des données sales
• Lecture non répétable : la transaction A lit les mêmes données plusieurs fois, et la transaction B met à jour et valide les données pendant les multiples lectures de la transaction A, ce qui entraîne Lorsque la transaction A lit la même chose données plusieurs fois, les résultats sont incohérents.
• Lecture fantôme : l'administrateur système A a modifié les notes de tous les élèves de la base de données des notes spécifiques aux notes ABCDE, mais l'administrateur système B a inséré un enregistrement de notes spécifiques à ce moment-là. Lorsque l'administrateur système A a terminé la modification, il a découvert qu'il y avait une note. un autre enregistrement qui n'a pas été modifié, comme si une hallucination s'était produite. C'est ce qu'on appelle une lecture fantôme.

Différents niveaux d'isolation des transactions SQL peuvent résoudre différents problèmes de concurrence, comme le montre le tableau suivant : Seul le niveau d'isolation de sérialisation résout les trois problèmes, et les trois autres niveaux d'isolation ont des défauts.

Niveau d'isolement des transactions	Lecture sale	Lecture non répétable	Lecture fantôme
Lecture non validée	Possible	Possible	Pos sible
Lire soumis	impossible	possible	possible
lecture répétable	impossible	impossible	possible
sérialisation	impossible	impossible	impossible

PS : Il est facile de confondre la lecture non répétable et la lecture fantôme. La lecture non répétable se concentre sur la modification, tandis que la lecture fantôme se concentre sur l'ajout ou la suppression. Pour résoudre le problème de lecture non répétable, il vous suffit de verrouiller les lignes qui remplissent les conditions. Pour résoudre le problème de lecture fantôme, vous devez verrouiller la table

1.4 Donnez-moi un exemple

Ceci. peut être un peu difficile à comprendre. Laissez-moi vous donner un exemple. C'est toujours la structure de la table et les données de la table précédentes

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

Supposons maintenant que je veuille démarrer deux aliments en même temps, une transaction A interroge l'âge de l'élève avec l'identifiant = 2 et une transaction B met à jour l'âge de l'étudiant avec id = 2 . Le processus est le suivant. Quelles sont les valeurs de X1, X2 et X3 sous les quatre niveaux d'isolement ?

• Lire non validé : La valeur de X1 est 23, car bien que la transaction B n'ait pas été validée, ses modifications ont été vues par A. (Si B annule plus tard la valeur de X1, c'est sale). Les valeurs de X2 et X3 sont également de 23, ce qui est compréhensible.
• Lire soumise : La valeur de X1 est 22, car bien que B ait changé, A ne peut pas le voir. (Si B est annulé plus tard, la valeur de X1 reste inchangée, ce qui résout le problème de lecture sale Les valeurs de X2 et).
• Lecture répétable : X1 et (Peu importe comment B est modifié pendant cette période, tant que A n'a pas soumis, il sera invisible, ce qui résout le problème de lecture non répétable), et la valeur de X3 est 23, car A a soumis, et la valeur modifiée de B peut être vue.
• Sérialisation : B sera verrouillé lors de l'exécution de modifications jusqu'à ce que A soit validé. B peut continuer l'exécution. (Pendant que A lit, B ne peut pas écrire. Il faut s'assurer que les données sont les plus récentes à ce moment. La lecture fantôme est résolue) Donc X1 et X2 valent tous deux 22, et le X3 final est exécuté après la soumission de B, et sa valeur est 23.

Alors pourquoi ce résultat se produit-il ? Comment le niveau d’isolement des transactions est-il mis en œuvre ?

Comment le niveau d'isolement des transactions est-il mis en œuvre ? J'ai trouvé la réponse dans le cours du professeur Ding Qi à Geek Time :

En fait, une vue sera créée dans la base de données, et le résultat logique de la vue prévaudra lors de l'accès. Sous le niveau d'isolement « Lecture répétable », cette vue est créée au démarrage de la transaction et est utilisée tout au long de la transaction. Sous le niveau d'isolement « lecture-commise », cette vue est créée au début de chaque instruction SQL. Ce qu'il faut noter ici, c'est que sous le niveau d'isolement « lecture non validée », la dernière valeur de l'enregistrement est directement renvoyée, sans la notion de vue, tandis que sous le niveau d'isolement « sérialisation », le verrouillage est directement utilisé pour éviter le parallélisme ; accès.

1.5 Définir le niveau d'isolement des transactions

Les niveaux d'isolement des transactions par défaut définis par différentes bases de données sont également très différents. Le niveau d'isolement par défaut de la base de données Oracle est lecture validée, tandis que MySQL est lecture répétable. Par conséquent, Lorsque votre système doit migrer la base de données d'Oracle vers MySQL, veuillez définir le niveau pour qu'il soit cohérent avec celui-ci avant la migration (lecture-validation) pour éviter des problèmes imprévisibles. 1.5.1 Afficher le niveau d'isolement des transactions level]

La portée est facultative : SESSION (session), GLOBAL (global) ; le niveau d'isolement est les quatre mentionnés ci-dessus et n'est pas sensible à la casse. Par exemple : définissez le niveau d'isolement global pour lire validé

# 查看事务隔离级别
5.7.20 之前
SELECT @@transaction_isolation
show variables like 'transaction_isolation';

# 5.7.20 以及之后
SELECT @@tx_isolation
show variables like 'tx_isolation'

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

1.6 Démarrage des transactionsLes transactions MySQL peuvent être démarrées des manières suivantes :

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，或者回滚语句是 rollback。

# 更新学生名字
START TRANSACTION;
update student set name = '张三' where id = 2;
commit;

• set autocommit = 0，这个命令会将线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
• set autocommit = 1，表示 MySQL 自动开启和提交事务。 比如执行一个 update 语句，语句只完成后就自动提交了。不需要显示的使用 begin、commit 来开启和提交事务。所以当我们执行多个语句的时候，就需要手动的用 begin、commit 来开启和提交事务。
• start transaction with consistent snapshot；上面提到的 begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 命令。 第一种启动方式，一致性视图是在执行第一个快照读语句时创建的； 第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

02 事务隔离的实现

理解了隔离级别，那事务的隔离是怎么实现的呢？要想理解事务隔离，先得了解 MVCC 多版本的并发控制这个概念。而 MVCC 又依赖于 undo log 和 read view 实现。

2.1 什么是 MVCC？

百度上的解释是这样的：

MVCC，全称 Multi-Version Concurrency Control，即多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC 使得数据库读不会对数据加锁，普通的 SELECT 请求不会加锁，提高了数据库的并发处理能力；数据库写才会加锁。 借助 MVCC，数据库可以实现 READ COMMITTED，REPEATABLE READ 等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了 ACID 中的 I 特性（隔离性)。

MVCC 只在 REPEATABLE READ 和 READ COMMITIED 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容 ，因为 READ UNCOMMITIED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

2.1.1 InnDB 中的 MVCC

InnDB 中每个事务都有一个唯一的事务 ID，记为 transaction_id。它在事务开始时向 InnDB 申请，按照时间先后严格递增。

而每行数据其实都有多个版本，这就依赖 undo log 来实现了。每次事务更新数据就会生成一个新的数据版本，并把  transaction_id 记为 row trx_id。同时旧的数据版本会保留在 undo log 中，而且新的版本会记录旧版本的回滚指针，通过它直接拿到上一个版本。

所以，InnDB 中的 MVCC 其实是通过在每行记录后面保存两个隐藏的列来实现的。一列是事务 ID：trx_id；另一列是回滚指针：roll_pt。

2.2 undo log

回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

根据操作的不同，undo log 分为两种： insert undo log 和 update undo log。

2.2.1 insert undo log

insert 操作产生的 undo log，因为 insert 操作记录没有历史版本只对当前事务本身可见，对于其他事务此记录不可见，所以 insert undo log 可以在事务提交后直接删除而不需要进行 purge 操作。

purge 的主要任务是将数据库中已经 mark del 的数据删除，另外也会批量回收 undo pages

所以，插入数据时。它的初始状态是这样的：

2.2.2 update undo log

UPDATE 和 DELETE 操作产生的 Undo log 都属于同一类型：update_undo。（update 可以视为 insert 新数据到原位置，delete 旧数据，undo log 暂时保留旧数据）。

事务提交时放到 history list 上，没有事务要用到这些回滚日志，即系统中没有比这个回滚日志更早的版本时，purge 线程将进行最后的删除操作。

Une transaction modifie les données actuelles :

Une autre transaction modifie les données :

Il existe plusieurs versions du même enregistrement dans la base de données, qui est le contrôle de concurrence multi-versions mentionné par MVCC. au-dessus de .

De plus, vous pouvez utiliser le journal d'annulation pour revenir à l'état de la version précédente. Par exemple, pour revenir à la V1, il vous suffit d’effectuer deux rollbacks consécutifs.

2.3 read-view

read view est une vue de lecture cohérente utilisée par InnDB lors de la mise en œuvre de MVCC pour prendre en charge la mise en œuvre des niveaux d'isolement RC (Read Commit) et RR (Repeatable Read).

la vue lecture n'existe pas vraiment, c'est juste un concept, et l'annulation du journal en est l'incarnation. Il est principalement calculé via la version et l'annulation. Sa fonction est de déterminer quelles donnéesla transaction peut voir.

Chaque transaction ou relevé a sa propre vue de cohérence. Les instructions de requête ordinaires sont des lectures cohérentes. Les lectures cohérentes déterminent la visibilité de la version des données en fonction de la ligne trx_id et des vues cohérentes.

2.3.1 Règles de visibilité de la version des données

La vue de lecture contient principalement d'autres transactions de lecture et d'écriture actives dans le système actuel. Dans l'implémentation, InnDB construit un tableau pour chaque transaction à enregistrer au moment où cette transaction démarre, il s'agit actuellement d'une transaction active (pas encore soumise). Comme mentionné précédemment, l'ID de transaction augmente strictement avec le temps.

La valeur maximale de l'ID de transaction soumis dans le système est enregistrée comme niveau d'eau bas du tableau, et l'ID de transaction créé + 1 est enregistré comme niveau d'eau haut. niveau

.

Ce tableau de vue et le niveau d'eau haut forment la vue de cohérence (vue de lecture) de la transaction en cours

Dessinez une image de ce tableau, il ressemble à ceci :

Les règles sont les suivantes :

1 Si trx_id Dans la zone grise, cela indique que le trx_id de la version accédée est inférieur à la valeur id du niveau d'eau bas dans le tableau, c'est-à-dire que la transaction qui a généré cette version a été validée avant de générer la vue lecture, cette version est donc visible et accessible par la transaction en cours.

• 2 Si trx_id est dans la zone orange, cela signifie que le trx_id de la version accédée est supérieur à la valeur id du niveau d'eau élevé dans le tableau, c'est-à-dire que la transaction qui a généré cette version a été générée après la vue en lecture a été généré, cette version est donc invisible et n'est pas accessible par la transaction en cours.
• 3 S'il est dans la zone verte, il y aura deux situations :

• a) trx_id est dans le tableau, prouvant que cette version est générée par une transaction non validée et est invisible
  • b) trx_id n'est pas dans le tableau, Cela prouve que cette version est générée par la transaction soumise. On peut voir que
Le troisième point, j'étais un peu confus en regardant le tutoriel. Heureusement, certains internautes enthousiastes ont répondu :

tombé dans le. La zone verte signifie que l'ID de transaction se trouve dans la plage du niveau d'eau bas et du niveau d'eau haut, sa visibilité dépend du fait que la zone verte ait cette valeur. Si la zone verte ne possède pas cet identifiant de transaction, elle est visible, si c'est le cas, elle n'est pas visible. Être dans cette plage ne signifie pas que cette plage a cette valeur, comme [1,2,3,5], 4 est dans la plage 1-5 de ce tableau, mais pas dans ce tableau.

Cela peut être un peu difficile à comprendre. Je suppose un scénario : trois transactions interrogent et mettent à jour les mêmes données. J'ai dessiné une image pour faciliter la compréhension :

Les données d'origine sont toujours comme l'image ci-dessous. Oui. , mettez à jour les informations de Zhang San avec l'identifiant = 2 :

Concernant la photo ci-dessus, je voudrais poser une question.

Sous les niveaux d'isolement RC (Read Comended) et RR (Repeatable Read), quelles sont les valeurs d'âge des requêtes aux points temporels T4 et T5 respectivement ? Quelle est la valeur actualisée du T4 ?

Réfléchissez-y un instant, je crois que chacun a sa propre réponse. La réponse se trouve à la fin de l'article. J'espère que vous pourrez continuer à lire avec vos propres questions.

2.3.2 Résultats sous RR (Repeatable Read)

Niveau RR, la requête ne reconnaît que les données qui ont été soumises avant le démarrage de la transaction, et la vue sera construite une fois la transaction démarrée. Utilisez donc la commande de démarrage de transaction avec une commande d'instantané cohérente et la vue sera créée immédiatement

.

现在假设：

• 事务 A 开始前，只有一个活跃的事务，ID = 2，
• 已提交的事务也就是插入数据的事务 ID = 1
• 事务 A、B、C 的事务 ID 分别是 3、4、5

在这种隔离级别下，他们创建视图的时刻如下：

根据上图得，事务 A 的视图数组是[2,3]；事务 B 的视图数组是 [2,3,4]；事务 C 的视图数组是[2,3,4,5]。分析一波：

• T4 时刻，B 读数据都是从当前版本读起，过程是这样的：

  • 读到当前版本的 trx_id = 4，刚好是自己，可见
  • 所以 age = 24

• T5 时刻，A 读数据都是从当前版本读起，过程是这样的：

  • 读到当前版本的 trx_id = 4，比自己视图数组的高水位大，不可见
  • 再往上读到 trx_id = 5，比自己视图数组高水位大，不可见
  • 再往上读到 trx_id = 1，比自己视图数组低水位小，可见
  • 所以 age = 22

这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

其实视图是否可见主要看创建视图和提交的时机，总结下规律：

• 版本未提交，不可见
• 版本已提交，但在视图创建后提交，不可见
• 版本已提交，但在视图创建前提交，可见

2.3.2.1 快照读和当前读

事务 B 的 update 语句，如果按照上图的一致性读，好像结果不大对？

如下图周明，B 的视图数组是先生成的，之后事务 C 才提交。那就应该看不见 C 修改的 age = 23 呀？最后 B 怎么得出 24 了？

没错，如果 B 在更新之前执行查询语句，那返回的结果肯定是 age = 22。问题是更新就不能在历史版本更新了呀，否则 C 的更新不就丢失了？

所以，更新有个规则：更新数据都是先读后写（读是更新语句执行，不是我们手动执行），读的就是当前版本的值，叫当前读；而我们普通的查询语句就叫快照读。

因此，在更新时，当前读读到的是 age = 23，更新之后就成 24 啦。

2.3.2.2 select 当前读

除了更新语句，查询语句如果加锁也是当前读。如果把事务 A 的查询语句 select age from t where id = 2 改一下，加上锁（lock in mode 或者 for update），也都可以得到当前版本 4 返回的 age = 24

下面就是加了锁的 select 语句：

select age from t where id = 2 lock in mode;
 select age from t where id = 2 for update;

2.3.2.3 事务 C 不马上提交

假设事务 C 不马上提交，但是 age = 23 版本已生成。事务 B 的更新将会怎么走呢？

事务 C 还没提交，写锁还没释放，但是事务 B 的更新必须要当前读且必须加锁。所以事务 B 就阻塞了，必须等到事务 C 提交，释放锁才能继续当前的读。

2.3.3 RC（读提交）下的结果

在读提交隔离级别下，查询只承认在语句启动前就已经提交完成的数据；每一个语句执行之前都会重新算出一个新的视图。

注意：在上图的表格中用于启动事务的是 start transaction with consistent snapshot 命令，它会创建一个持续整个事务的视图。所以，在  RC 级别下，这命令其实不起作用。等效于普通的 start transaction（在执行 sql 语句之前才算是启动了事务）。所以，事务 B 的更新其实是在事务 C 之后的，它还没真正启动事务，而 C 已提交。

现在假设：

• Avant le début de la transaction A, il n'y a qu'une seule transaction active, ID = 2,
• La transaction validée est l'ID de transaction d'insertion des données = 1
• Les ID de transaction des transactions A, B et C sont 3, 4, et 5 respectivement.

Sous ce niveau d'isolement, le moment où ils créent la vue est le suivant :

Selon l'image ci-dessus, le tableau de vues de la transaction A est [2,3,4], mais sa limite supérieure est de 6 ou plus (ID de transaction créé + 1) ; le tableau de vues de la transaction B est [2,4] ; le tableau de vues de la transaction C est [2,5]. Une vague d'analyse :

• Au moment T4, B lit les données de la version actuelle. Le processus est le suivant :

  • lit le trx_id = 4 de la version actuelle, qui se trouve être lui-même. que
  • so age = 24

• À T5, A lit les données à partir de la version actuelle Le processus est le suivant :

  • lit le trx_id = 4 de la version actuelle, qui entre dans le cadre de son. propre vue de cohérence mais en contient 4, qui est invisible
  • Lisez plus loin jusqu'à trx_id = 5, qui est dans la plage de votre propre vue de cohérence mais n'inclut pas 5. On peut voir que
  • donc age = 23

03 Les épaules des géants

• cnblogs.com/wyaokai/p /10921323.html
• time.geekbang.org/column/article/70562
• zhuanlan.zhihu.com/p/117476959
• cnblogs .com/xd502djj/p/6668632.html
• blog.csdn.net/article /details/109044141
• blog.csdn.net/u014078930/article/details/99659272

04 Résumé

Cet article traite tous les aspects des transactions en détail, tels que : quatre fonctionnalités principales, les niveaux d'isolement, les problèmes de concurrence résolus, comment configurer, vérifier le niveau d'isolement, comment démarrer une transaction, etc. De plus, nous avons également acquis une compréhension approfondie de la manière dont les deux niveaux d’isolement, RR et RC, sont atteints ? Y compris une explication détaillée de la façon dont MVCC, l'annulation du journal et la vue de lecture fonctionnent ensemble pour implémenter MVCC. Enfin, nous avons parlé de lecture d'instantanés, de lecture actuelle, etc. On peut dire que tous les points de connaissance liés aux affaires sont ici. Si vous ne comprenez toujours pas après avoir lu cet article, venez me frapper !

【Recommandation associée : tutoriel vidéo 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!