Résumé des transactions de base de données et des transactions MySQL

                                                     

Tutoriel mysqlRésumé des transactions de base de données et des transactions MySQL

Recommandé (gratuit) : Tutoriel MySQL

Fonctionnalités de transaction : ACID

D'un point de vue commercial, Un ensemble d'opérations dans la base de données est nécessaire pour maintenir 4 caractéristiques :

• Atomicité : Une transaction doit être considérée comme une unité de travail minimale indivisible, et toutes les opérations de l'ensemble de la transaction doivent être soumises avec succès. , ou tous échouent et reviennent en arrière. Pour une transaction, il est impossible d'effectuer seulement une partie des opérations.
• Cohérence : la base de données passe toujours d'un état de cohérence à un autre. Les exemples bancaires suivants seront mentionnés.
• Isolement : De manière générale, les modifications apportées par une transaction ne sont pas visibles par les autres transactions avant d'être finalement validées. Notez ici le « de manière générale », qui sera abordé plus tard dans le niveau d'isolation des transactions.
• Durabilité : Une fois une transaction validée, ses modifications seront enregistrées définitivement dans la base de données. Même si le système tombe en panne à ce moment-là, les données modifiées ne seront pas perdues. (Il existe également une certaine relation entre la sécurité de la persistance et le niveau du journal d'actualisation. Différents niveaux correspondent à différents niveaux de sécurité des données.)

Afin de mieux comprendre ACID, considérez le virement bancaire comme un exemple :

START TRANSACTION;SELECT balance FROM checking WHERE customer_id = 10233276;UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;COMMIT;

• Atomicité : Soit un engagement complet (le solde courant de 10233276 diminue de 200, et le solde des économies augmente de 200), soit un rollback complet (les soldes des deux tables ne changent pas)
• Cohérence : La cohérence de cet exemple se reflète dans le fait que les 200 yuans ne seront pas perdus car le système de base de données plante après la 3ème ligne et avant la 4ème ligne, car la transaction n'a pas encore été validée.
• Isolement : les relevés d'opération d'une transaction peuvent être isolés des relevés d'autres transactions. Par exemple, la transaction A s'exécute après la ligne 3 et avant la ligne 4, et la transaction B interroge le solde de contrôle à ce moment-là. , il peut toujours voir les 200 yuans qui ont été soustraits dans la transaction A (l'argent du compte reste inchangé), car les transactions A et B sont isolées l'une de l'autre. Avant la validation de la transaction A, la transaction B ne peut pas observer les modifications des données.
• Persistance : C'est facile à comprendre.
• L'isolation des transactions est obtenue grâce à des verrous, MVCC, etc. (Résumé des verrous MySQL)
• L'atomicité, la cohérence et la durabilité des transactions sont obtenues grâce aux journaux de transactions (voir ci-dessous)

Niveau d'isolement de la transaction

Problèmes causés par des transactions simultanées

• Mise à jour perdue : lorsque deux transactions ou plus sélectionnent la même ligne, puis mettent à jour la ligne en fonction de la valeur initialement sélectionnée, puisque chaque transaction n'est pas au courant de l'existence de l'autre transaction, il y aura un problème de perte de mise à jour. - la dernière mise à jour écrase les mises à jour effectuées par d'autres transactions. Par exemple, deux éditeurs réalisent des copies électroniques du même document. Chaque éditeur modifie indépendamment sa copie, puis enregistre la copie modifiée, écrasant ainsi le document original. L'éditeur qui a enregistré en dernier lieu une copie de ses modifications écrase les modifications apportées par un autre éditeur. Ce problème peut être évité si un éditeur ne peut pas accéder au même fichier jusqu'à ce qu'un autre éditeur termine et valide la transaction.
• Lectures sales : une transaction modifie un enregistrement. Avant que la transaction ne soit terminée et validée, les données de cet enregistrement sont dans un état incohérent à ce moment-là, une autre transaction lit également Si le même enregistrement n'est pas contrôlé ; et une seconde transaction lit les données « sales » et effectue un traitement ultérieur sur la base de celles-ci, des dépendances de données non validées se produiront. Ce phénomène est vivement appelé « lecture sale ».
• Lectures non répétables : une transaction lit à nouveau les données précédemment lues à un certain moment après avoir lu certaines données, pour constater que les données qu'elle a lues ont changé ou que certains enregistrements ont été supprimés ! Ce phénomène est appelé « lecture non répétable ».
• Lectures fantômes : une transaction relit les données précédemment récupérées selon les mêmes conditions de requête, pour constater que d'autres transactions ont inséré de nouvelles données qui satisfont à ses conditions de requête. Ce phénomène est appelé " Lecture fantôme ».

La différence entre la lecture fantôme et la lecture non répétable :

• L'objectif de la lecture non répétable est la modification : dans une même transaction, dans les mêmes conditions, les données lues pour la première fois sont différentes des données lues pour la deuxième fois. (Parce que d'autres transactions ont soumis des modifications au milieu)
• L'objectif de la lecture fantôme est d'ajouter ou de supprimer : dans la même transaction, dans les mêmes conditions, le nombre d'enregistrements lus pour la première et la deuxième fois est différent. . (Car il y a d'autres transactions commettant l'insertion/suppression au milieu)

Solution aux problèmes causés par le traitement simultané des transactions :

• Les « mises à jour perdues » sont généralement quelque chose qui devrait être complètement évité. Cependant, la prévention de la perte de mise à jour ne peut pas être résolue par le seul contrôleur de transactions de base de données. L'application doit ajouter les verrous nécessaires aux données à mettre à jour. Par conséquent, la prévention de la perte de mise à jour doit relever de la responsabilité de l'application.

• "Lecture sale", "lecture non répétable" et "lecture fantôme" sont en fait des problèmes de cohérence de lecture de la base de données, qui doivent être résolus par la base de données fournissant un certain mécanisme d'isolation des transactions :

• L'une est le verrouillage : avant de lire les données, verrouillez-les pour empêcher d'autres transactions de modifier les données.

• L'autre est le contrôle de concurrence de données multi-versions (MVCC ou MCC en abrégé), également connu sous le nom de base de données multi-versions : sans ajouter de verrous, une donnée est générée via un certain mécanisme. Demandez un instantané de données cohérent (Snapshot) à un moment donné et utilisez cet instantané pour fournir un certain niveau (niveau d'instruction ou niveau de transaction) de lectures cohérentes. Du point de vue de l'utilisateur, il semble que la base de données puisse fournir plusieurs versions des mêmes données.

Le standard SQL définit 4 types de niveaux d'isolement. Chaque niveau précise les modifications apportées dans une transaction, celles qui sont visibles au sein et entre les transactions, et celles qui ne le sont pas. Des niveaux d’isolement inférieurs prennent généralement en charge une concurrence plus élevée et entraînent une surcharge système inférieure.

Niveau 1 : Lire non validé

• Toutes les transactions peuvent voir d'autres transactions non validées Résultats d'exécution
• Ce niveau d'isolement est rarement utilisé dans des applications pratiques car ses performances ne sont pas bien meilleures que les autres niveaux
• Le problème causé par ce niveau est - Dirty Read : des données non validées ont été lues

Niveau 2 : Lecture validée (lecture du contenu soumis)

• Il s'agit du niveau d'isolement par défaut pour la plupart des systèmes de bases de données (mais pas le niveau par défaut de MySQL)

• Cela répond à la définition simple de l'isolement : une transaction ne peut voir que ce qui a été commis par la transaction. Le problème avec la modification du niveau d'isolement de

• est - Lecture non répétable : la lecture non répétable signifie que lorsque nous exécutons exactement la même instruction select dans la même transaction, il est possible de voir des résultats différents. Les raisons possibles de cette situation incluent :

• Il y a une transaction croisée avec un nouveau commit, entraînant des modifications de données

• A Lorsque la base de données ; est exploité par plusieurs instances, d'autres instances de la même transaction peuvent avoir de nouveaux commits lors du traitement de l'instance

Niveau 3 : Lecture répétable (lisible )

• Il s'agit du niveau d'isolation des transactions par défaut de MySQL
• Il garantit que plusieurs instances de la même transaction verront la même vue lors de la lecture simultanée des lignes de données
.
• Problèmes possibles à ce niveau - Lecture fantôme : lorsque l'utilisateur lit une certaine plage de lignes de données, une autre transaction insère une nouvelle ligne dans la plage. Lorsque les utilisateurs lisent des lignes de données dans cette plage, ils trouveront de nouvelles lignes "fantômes".
• Les moteurs de stockage InnoDB et Falcon résolvent le problème de lecture fantôme grâce au mécanisme de contrôle de concurrence multiversion (MVCC, Multiversion Concurrency Control) ; InnoDB Le problème de lecture fantôme est également résolu grâce aux verrous d'espacement

Contrôle de concurrence multi-version :

La plupart des implémentations de moteur de stockage transactionnel de Mysql Il ne s'agit pas d'un simple verrouillage au niveau des lignes. Sur la base de l'amélioration de la concurrence, le contrôle de concurrence multiversion (MVCC) est généralement implémenté en même temps, y compris Oracle et PostgreSQL. Cependant, les implémentations varient.

MVCC est implémenté en enregistrant un instantané des données à un moment donné. En d’autres termes, quel que soit le temps nécessaire à la mise en œuvre, les données vues par chaque élément sont cohérentes.

Il est divisé en contrôle de concurrence optimiste et contrôle de concurrence pessimiste.

Comment fonctionne MVCC :

Le MVCC d'InnoDB est implémenté en enregistrant deux colonnes cachées derrière chaque ligne d'enregistrements. L'une de ces deux colonnes stocke l'heure de création de la ligne et l'autre stocke l'heure d'expiration (heure de suppression) de la ligne. Bien entendu, ce qui est stocké n’est pas l’heure réelle mais le numéro de version du système. Chaque fois qu'une nouvelle transaction est démarrée, le numéro de version du système sera automatiquement ajouté. Le numéro de version du système au début de la transaction sera utilisé comme numéro de version de la transaction, qui est utilisé pour interroger le numéro de version de chaque ligne d'enregistrements à des fins de comparaison.

Comment fonctionne MVCC sous le niveau d'isolement REPEATABLE READ :

• SELECT

InnoDB vérifiera chaque ligne d'enregistrements selon les conditions suivantes :

1. InnoDB recherche uniquement les lignes de données dont la version est antérieure à la version actuelle de la transaction, de sorte que Assurez-vous que les lignes lues par la transaction existent avant de démarrer la transaction ou sont insérées ou modifiées par la transaction elle-même. Le numéro de version supprimé de la ligne
2. est soit indéfini, soit supérieur au. numéro de version actuel de la transaction. Cela garantit que la transaction lue Les lignes récupérées n'ont pas été supprimées avant le début de la transaction

Seules celles qui remplissent les deux conditions ci-dessus seront interrogées

• INSERT

InnoDB enregistre le numéro de version actuel du système comme numéro de version de ligne pour chaque ligne nouvellement insérée

• DELETE

InnoDB enregistre le numéro de version actuel du système comme ligne supprimée pour chaque ligne supprimée Identification

• UPDATE

InnoDB enregistre le numéro de version actuel du système comme numéro de version de ligne pour le nouvel enregistrement inséré, et enregistre le numéro de version actuel du système dans la ligne d'origine comme identifiant de suppression

Enregistrez ces deux numéros de version afin que la plupart des opérations ne nécessitent pas de verrouillage. Il simplifie les opérations sur les données, fonctionne bien et garantit que seules les lignes requises par des exigences complexes sont lues. Les inconvénients sont que chaque ligne d'enregistrements nécessite un espace de stockage supplémentaire, davantage de vérifications de lignes et des travaux de maintenance supplémentaires.

MVCC ne fonctionne que sous deux niveaux d'isolement : COMMITTED READ (soumission en lecture) et REPEATABLE READ (lecture répétable).

On peut considérer que MVCC est une variante du verrouillage au niveau des lignes, mais il évite les opérations de verrouillage dans de nombreux cas et entraîne une surcharge moindre. Bien que les mécanismes d'implémentation des différentes bases de données soient différents, la plupart d'entre elles implémentent des opérations de lecture non bloquantes (la lecture ne nécessite pas de verrouillage et peut éviter les lectures non répétables et les lectures fantômes), et les opérations d'écriture verrouillent uniquement les lignes nécessaires (l'écriture doit être verrouillée). ). , sinon l'écriture simultanée par différentes transactions entraînera une incohérence des données).

Niveau 4 : Sérialisable

• Il s'agit du niveau d'isolement le plus élevé
• Il résout le problème de lecture fantôme en obliger les transactions à être ordonnées de manière à ce qu’elles ne puissent pas entrer en conflit les unes avec les autres. En bref, il ajoute un verrou partagé sur chaque ligne de données lue. Résumé du verrouillage MySQL
• À ce niveau, cela peut conduire à un grand nombre de délais d'attente et de concurrence de verrouillage

Comparaison du niveau d'isolement

Chaque base de données spécifique peut ne pas mettre entièrement en œuvre les quatre niveaux d'isolement ci-dessus, par exemple :

• Oracle ne fournit que deux normes : Lecture validée et le niveau d'isolement sérialisable, en outre, il fournit également son propre niveau d'isolement en lecture seule défini

• En plus de prendre en charge les quatre niveaux d'isolement définis par ISO/ANSI SQL92 ci-dessus, SQL Server également prend en charge un niveau d'isolement appelé "instantané", mais à proprement parler, il s'agit d'un niveau d'isolement sérialisable implémenté à l'aide de MVCC.

• MySQL prend en charge les 4 niveaux d'isolement, mais dans l'implémentation spécifique, il existe certaines caractéristiques, telles que la lecture de cohérence MVCC est utilisée dans certains niveaux d'isolement, mais dans certains cas, ce n'est pas le cas.

• Mysql peut définir le niveau d'isolement en exécutant la commande set transaction isolation level. Le nouveau niveau d'isolement prendra effet au démarrage de la prochaine transaction. Par exemple : définir le niveau d'isolement des transactions de session en lecture validée ;

Journal des transactions

Le journal des transactions peut aider à améliorer l'efficacité des transactions :

• À l'aide du journal des transactions, le moteur de stockage n'a besoin que de modifier sa copie de mémoire lors de la modification des données de la table, puis d'enregistrer le comportement de modification dans le journal des transactions qui est conservé sur le disque dur. de le mettre à jour à chaque fois. Les données modifiées elles-mêmes sont conservées sur le disque.
• Le journal des transactions utilise une méthode d'ajout, de sorte que l'opération d'écriture du journal est une E/S séquentielle dans une petite zone du disque, contrairement aux E/S aléatoires qui nécessitent de déplacer la tête à plusieurs endroits sur le disque. , la méthode du journal des transactions est donc relativement beaucoup plus rapide.
• Une fois le journal des transactions conservé, les données modifiées dans la mémoire peuvent être lentement vidées sur le disque en arrière-plan.
• Si la modification des données a été enregistrée dans le journal des transactions et a persisté, mais que les données elles-mêmes n'ont pas été réécrites sur le disque et que le système plante, le moteur de stockage peut restaurer automatiquement cette partie des données modifiées. au redémarrage.

Actuellement, la plupart des moteurs de stockage sont implémentés de cette manière. Nous appelons généralement cela la journalisation à écriture anticipée (la modification des données nécessite une écriture deux fois sur le disque).

Principe d'implémentation des transactions dans Mysql

L'implémentation des transactions est basée sur le moteur de stockage de la base de données. Différents moteurs de stockage ont différents niveaux de prise en charge des transactions. Les moteurs de stockage qui prennent en charge les transactions dans MySQL incluent innoDB et NDB.

innoDB est le moteur de stockage par défaut de MySQL. Le niveau d'isolement par défaut est RR (Repeatable Read), et il va encore plus loin sous le niveau d'isolement de RR, via le Contrôle de concurrence (MVCC, Multiversion Concurrency Control) résout le problème de lecture non répétable, et le verrouillage des espaces (c'est-à-dire le contrôle de concurrence) résout le problème de lecture fantôme. Par conséquent, le niveau d'isolation RR d'innoDB produit réellement l'effet du niveau de sérialisation et conserve des performances de concurrence relativement bonnes.

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。说到事务日志，不得不说的就是redo和undo。

1.redo log

在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

在系统启动的时候，就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。如下一个简单示例：

记录1：

记录2：

记录3：

记录4：

记录5：

2.undo log

undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

以下是undo+redo事务的简化过程

假设有2个数值，分别为A和B,值为1，2

1. start transaction;

2. 记录 A=1 到undo log;

3. update A = 3；

4. 记录 A=3 到redo log；

5. 记录 B=2 到undo log；

6. update B = 4；

7. 记录B = 4 到redo log；

8. 将redo log刷新到磁盘

9. commit

在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化。若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。

所以，redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。

Mysql中的事务使用

MySQL的服务层不管理事务，而是由下层的存储引擎实现。比如InnoDB。

MySQL支持本地事务的语句：

START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}

• START TRANSACTION 或 BEGIN 语句：开始一项新的事务。
• COMMIT 和 ROLLBACK：用来提交或者回滚事务。
• CHAIN 和 RELEASE 子句：分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
• SET AUTOCOMMIT 可以修改当前连接的提交方式， 如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚

事务使用注意点：

• 如果在锁表期间，用 start transaction 命令开始一个新事务，会造成一个隐含的 unlock
  tables 被执行。
• 在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事
  务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
• 和 Oracle 的事务管理相同，所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。
• 在事务中可以通过定义 SAVEPOINT（例如：mysql> savepoint test; 定义 savepoint，名称为 test），指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚
  不同的 SAVEPOINT。需要注意的是，如果定义了相同名字的 SAVEPOINT，则后面定义的SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT， 删除后的 SAVEPOINT， 不能再执行 ROLLBACK TO SAVEPOINT命令。

自动提交（autocommit）：
Mysql默认采用自动提交模式，可以通过设置autocommit变量来启用或禁用自动提交模式

• 隐式锁定

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

• 显式锁定

InnoDB也支持通过特定的语句进行显示锁定（存储引擎层）：

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

MySQL Server层的显示锁定：

lock table和unlock table

（更多阅读：MySQL锁总结）

MySQL对分布式事务的支持

分布式事务的实现方式有很多，既可以采用innoDB提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下innoDB对分布式事务的支持。

MySQL 从 5.0.3 开始支持分布式事务，当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

如图，mysql的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

• 应用程序：定义了事务的边界，指定需要做哪些事务；
• 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
• 事务管理器：协调参与了全局事务中的各个事务。

分布式事务采用两段式提交（two-phase commit）的方式：

• 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
• 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

分布式事务（XA 事务）的 SQL 语法主要包括：

XA {START|BEGIN} xid [JOIN|RESUME]

虽然 MySQL 支持分布式事务，但是在测试过程中，还是发现存在一些问题：
如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写 binlog，存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。

如果分支事务在执行到 prepare 状态时，数据库异常，且不能再正常启动，需要使用备份和 binlog 来恢复数据，那么那些在 prepare 状态的分支事务因为并没有记录到 binlog，所以不能通过 binlog 进行恢复，在数据库恢复后，将丢失这部分的数据。

如果分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，如果此时分支事务已经执行到 prepare 状态， 那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。
总之， 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!