Quels sont les niveaux d'isolement des transactions MySQL ?-tutoriel mysql-php.cn

Il existe quatre niveaux d'isolement des transactions : 1. Lecture non validée (lecture non validée), qui permet la lecture des modifications de données non validées, ce qui peut provoquer des lectures sales, des lectures non répétables et des lectures fantômes. 2. Lecture validée (Read Comended) permet de lire les données qui ont été soumises par des transactions simultanées, ce qui peut éviter des lectures sales, mais peut provoquer des lectures non répétables et fantômes. 3. Lecture répétable (lecture répétable), les résultats de plusieurs lectures du même champ sont cohérents. 4. Sérialisable (sérialisable).

Quels sont les niveaux d'isolement des transactions MySQL ?

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

1. Qu'est-ce qu'une transaction ?

Une transaction est un ensemble logique d'opérations, soit toutes sont exécutées, soit aucune n'est exécutée.

L’exemple de transaction le plus classique et le plus souvent cité est le virement bancaire. Par exemple, si Xiao Ming souhaite transférer 1 000 yuans à Xiao Hong, ce transfert impliquera deux opérations clés : réduire le solde de Xiao Ming de 1 000 yuans et réduire le solde de Xiao Hong de 1 000 yuans. Si une erreur se produit soudainement entre ces deux opérations, entraînant une diminution du solde de Xiao Ming mais une augmentation du solde de Xiao Hong, cette situation n'est définitivement pas autorisée. La transaction vise à garantir que ces deux opérations clés réussissent ou qu’aucune ne réussit.

2. Caractéristiques de la transaction (ACID)

**Atomicité : **La plus petite unité d'exécution d'une transaction, le fractionnement n'est pas autorisé. L'atomicité des transactions garantit que les actions sont soit toutes exécutées, soit aucune.
**Cohérence :**Les données restent cohérentes avant et après l'exécution de la transaction. Par exemple, dans une entreprise de transfert d’argent, le montant total du cédant et du bénéficiaire doit rester inchangé, que la transaction réussisse ou non.
** Isolation : ** Lors de l'accès simultané à la base de données, la transaction d'un utilisateur ne doit pas être affectée par d'autres transactions et la base de données est indépendante entre les transactions simultanées.
**Persistance :**Une fois qu'une transaction est validée, ses modifications apportées aux données dans la base de données sont durables et ne devraient pas être affectées même en cas de défaillance de la base de données.

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

Dans une application typique, plusieurs transactions s'exécutent simultanément et exploitent souvent les mêmes données pour accomplir leurs tâches respectives (plusieurs utilisateurs opèrent sur les mêmes données). Bien que la simultanéité soit nécessaire, elle peut entraîner les problèmes suivants :

**Lecture sale : **Lorsqu'une transaction accède aux données et les modifie, mais n'a pas encore soumis la transaction, une autre transaction a également accédé à ces données et les a ensuite utilisées. ces données. Parce que la modification de ces données n'a pas été soumise à la base de données, les données lues par une autre transaction sont des "données sales". Ce comportement est une "lecture sale" , opérations basées sur des "données sales". " peut poser des problèmes.
signifie que lorsqu'une transaction lit une donnée, une autre donnée accède également aux données, puis après que la première transaction modifie les données, la deuxième transaction la modifie également ces données. De cette façon, les résultats de la modification lors de la première transaction sont perdus. Cette situation est appelée perte de modification. Par exemple : La transaction 1 lit les données dans la table, mais l'enregistrement de modification de la transaction 1 est perdu. A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果都是19Lecture non reproductible :
Lecture fantôme : La lecture fantôme est similaire à la lecture non répétable. La lecture fantôme signifie qu'une transaction lit plusieurs lignes de données. La transaction n'est pas encore terminée, puis une autre transaction insère des données, puis une autre transaction les insère. certaines données. Dans la requête, la première transaction lira plus de données que celles lues à l'origine, comme si une hallucination s'était produite, c'est pourquoi on l'appelle
.

L'objectif de la lecture non répétable est la modification, tandis que l'objectif de la lecture fantôme est l'ajout ou la suppression. Châtaigne 1 (Mêmes conditions, les données que vous lisez seront différentes lorsque vous les relisez) : M. A dans la transaction 1 a lu que son salaire est de 1 000. L'opération n'est pas encore terminée, M. B dans la transaction 2 I. modifié le salaire de M. A à 2000. Lorsque M. A relit son salaire, il devient 2000. Il s'agit d'une lecture non répétable.

Chestnut 2 (mêmes conditions, le nombre d'enregistrements lus pour la première et la deuxième fois est différent) : S'il y a 4 personnes dans une grille de salaire avec un salaire supérieur à 3 000, la transaction 1 lit tous les enregistrements avec un salaire supérieur supérieur à 3 000 Au total, 4 enregistrements ont été interrogés. La transaction 2 a interrogé un autre enregistrement avec un salaire supérieur à 3 000. La transaction 1 a relu les enregistrements interrogés et le nombre d'enregistrements interrogés était de 5. Il s'agit d'une lecture fantôme.

4. Niveaux d'isolement des transactions

La norme SQL définit quatre niveaux d'isolement :

**READ-UNCOMMITTED :**Le niveau d'isolement le plus bas, autorisant les lectures qui n'ont pas encore été validées. Les modifications de données peuvent entraîner lectures sales, lectures non répétables et lectures fantômes.
peut éviter des lectures sales, mais peut provoquer des lectures non répétables et fantômes.
Des lectures sales et non répétables peuvent être évitées, mais des lectures fantômes peuvent se produire. .
peut éviter les lectures sales, les lectures non répétables et les lectures fantômes.

Niveau d'isolementLecture saleLecture non répétableLecture fantômeLecture non validée√√ √Lire soumis

MySQL InnoDB Le niveau d'isolation des transactions par défaut du moteur de stockage est Repeatable-READ (REPEATABLE-READ), qui peut être transmis via la commande select @@tx_isolation ; instruction Pour vérifier, MySQL 8.0 Cette instruction est modifiée en SELECT @@transaction_isolation;MySQL InnoDB存储引擎默认的事务隔离级别是可重复读（REPEATABLE-READ），可以通过命令select @@tx_isolation;语句来查看，MySQL 8.0 该语句改为SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

Copier après la connexion

MySQL InnoDB存储引擎的可重复读并不能避免幻读，需要应用使用加锁读来保证，这加锁读使用到的机制就是Next-Key Locks。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是读取已提交（READ-COMMITTED），InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB存储引擎在分布式事务的情况下一般会用到可串行化隔离级别。

? 拓展一下(以下内容摘自《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章)：

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对事务的原有ACID要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

四、实际情况演示

MySQL命令行的默认配置中事务都是自动提交的，即执行SQL语句就会马上执行COMMIT操作。可以用命令START TRANSACTION开始一个事务。

我们可以通过下面命令设置事务隔离级别。

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

Copier après la connexion

我们再来看一下我们在实际操作中使用到的一些并发控制语句：

START TRANSACTION | BEGIN ：显示的开启一个事务。
COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
ROLLBACK：回滚到结束用户的事务，并撤销正在进行的所有未提交的修改。

（脏读）读取未提交

（避免脏读）读取已提交

不可重复读

还是刚才上面的读已提交的图，虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了不可重复读问题。

可重复读

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ysjbfC4b-1651149978452)(https://qtspace.cn/contentimg/81.jpg)]

幻读

演示幻读出现的情况

sql 脚本 1 在第一次查询工资为 500 的记录时只有一条，sql 脚本 2 插入了一条工资为 500 的记录，提交之后；sql 脚本 1 在同一个事务中再次使用当前读查询发现出现了两条工资为 500 的记录这种就是幻读。

幻读和不可重复读有些相似之处，但是不可重复读的重点是修改，幻读的重点在于新增或者删除。

解决幻读的方法

将事务隔离级别调整为 SERIALIZABLE。
在可重复读的事务级别下，给事务操作的这张表添加表锁。
在可重复读的事务级别下，给事务操作的这张表添加 Next-Key Locks。

说明：Next-Key Locksrrreee
MySQL InnoDB Les lectures fantômes de lecture et de fusion répétables du moteur de stockage ne peuvent pas être évitées, et l'application doit utiliser des lectures verrouillées pour garantir que le mécanisme utilisé pour les lectures verrouillées est Next-Key Locks.

Parce que plus le niveau d'isolement est bas, moins de verrous sont demandés par la transaction, donc le niveau d'isolement de la plupart des systèmes de bases de données est READ-COMMITTED, InnoDB Le moteur de stockage utilise READ-COMMITTED, InnoDB. strong>REPEATABLE-READ (lisible) par défaut sans aucune pénalité de performances. InnoDBLe moteur de stockage utilise généralement le niveau d'isolation Sérialisable dans le cas de transactions distribuées.

🎜 ? Développer (le contenu suivant est extrait du chapitre 7.7 de "MySQL Technology Insider : InnoDB Storage Engine (2e édition)") : 🎜

🎜Le moteur de stockage InnoDB prend en charge les transactions XA et prend en charge la distribution via les transactions XA. transactions formelles. Les transactions distribuées font référence au fait de permettre à plusieurs ressources de transaction indépendantes de participer à une transaction globale. Les ressources transactionnelles sont généralement des systèmes de bases de données relationnelles, mais peuvent être d'autres types de ressources. Les transactions globales nécessitent que toutes les transactions participantes soient validées ou annulées, ce qui améliore encore les exigences ACID d'origine pour les transactions. De plus, lors de l'utilisation de transactions distribuées, le niveau d'isolation des transactions du moteur de stockage InnoDB doit être défini sur SERIALIZABLE. 🎜🎜
🎜4. Démonstration de la situation réelle
🎜MySQLDans la configuration par défaut de la ligne de commande, les transactions sont automatiquement soumis, c'est-à-dire que l'exécution de l'instruction SQL exécutera immédiatement l'opération COMMIT. Vous pouvez utiliser la commande START TRANSACTION pour démarrer une transaction. 🎜🎜Nous pouvons définir le niveau d'isolement des transactions via la commande suivante. 🎜rrreee🎜Jetons un coup d'œil à certaines des instructions de contrôle de concurrence que nous utilisons dans les opérations réelles : 🎜

START TRANSACTION | BEGIN : ouvrez explicitement une transaction.

COMMIT : valide la transaction, rendant permanentes toutes les modifications apportées à la base de données.

ROLLBACK : revient à la fin de la transaction de l'utilisateur et annule toutes les modifications non validées en cours.

🎜(lecture sale) lecture non validée
🎜🎜
🎜 (éviter les lectures sales) lire Soumis fort>
🎜🎜🎜Lecture non répétable🎜Il s'agit toujours de l'image de lecture validée juste au-dessus. Bien qu'elle évite la lecture non validée, une transaction apparaît avant sa fin. Un problème de lecture répétable s'est produit. 🎜🎜🎜
🎜Lecture répétable
🎜[Le transfert de l'image du lien externe a échoué. Le site source peut avoir un mécanisme anti-sangsue. Il est recommandé de sauvegarder l'image et de la télécharger directement (img-ysjbfC4b. -1651149978452) (https: //qtspace.cn/contentimg/81.jpg)]🎜
🎜Lecture fantôme
🎜Démontrer l'apparition de lecture fantôme🎜🎜🎜🎜le script SQL 1 interroge l'enregistrement avec un salaire de 500 pour la première fois Il n'y avait qu'un seul enregistrement à ce moment-là. Le script SQL 2 a inséré un enregistrement avec un salaire de 500. Après la soumission, le script SQL 1 a de nouveau utilisé la requête de lecture actuelle dans la même transaction et a constaté que deux enregistrements. avec un salaire de 500 est apparu. C'est une lecture fantôme. 🎜🎜La lecture fantôme et la lecture non répétable sont quelque peu similaires, mais 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. 🎜🎜Méthodes pour résoudre les lectures fantômes🎜

Ajustez le niveau d'isolation des transactions sur SERIALIZABLE.

Au niveau de la transaction de lecture répétable, ajoutez un verrou de table à la table dans l'opération de transaction.

Au niveau des transactions de lecture répétables, ajoutez Next-Key Locks au tableau pour les opérations de transaction.

🎜Explication : Next-Key Locks est équivalent au verrouillage de ligne + verrouillage d'espacement🎜🎜🎜[Recommandation associée : 🎜tutoriel vidéo MySQL🎜]🎜