Exemple de niveau d'isolement de transaction MySQL et lecture sale, lecture fantôme, lecture non répétable

Recommandé (gratuit) : Tutoriel vidéo mysql

Isolement des transactions Propriété

MySQL est un logiciel d'architecture client/serveur. Pour un même serveur, il peut y avoir plusieurs clients connectés. Une fois chaque client connecté au serveur, il peut l'appeler une session. Chaque client peut émettre une instruction de requête au serveur dans sa propre session. Une instruction de requête peut faire partie d'une transaction, c'est-à-dire que le serveur peut traiter plusieurs transactions en même temps. Lorsque plusieurs transactions sont exécutées simultanément sur la base de données, des problèmes tels qu'une lecture sale, une lecture non répétable et une lecture fantôme peuvent survenir. Afin de résoudre ces problèmes, il existe le concept de « niveau d'isolement ».

Théoriquement, lorsqu'une transaction accède à certaines données, les autres transactions doivent être mises en file d'attente. Ce n'est qu'après la soumission de la transaction que les autres transactions peuvent continuer à accéder aux données. Mais d’une manière générale, plus l’isolation est étroite, plus l’efficacité sera faible. C’est pourquoi nous devons souvent trouver un équilibre entre isolement et efficacité.

Problèmes rencontrés lors de l'exécution simultanée de transactions

Lecture sale : Lecture sale signifie qu'une transaction lit une autre donnée non validée modifiée par la transaction.

Par exemple, s'il y a un solde de 100 sur le compte de Xiao Wang, il y aura deux transactions pour accéder au compte de Xiao Wang.

会话A	会话B
begin;
update xxx set balance = balance+50 where client_no = ‘小王客户号’ ;	begin;
	select balance from xxx where client_no = ‘小王客户号’ ; （如果读到150，则意味着发生了脏读）
rollback;	commit;

Comme ci-dessus, la session A et la session B ont chacune ouvert une transaction. La session A a d'abord ajouté 50 au solde du compte de Xiao Wang. À ce moment-là, le compte B a vérifié que le solde du compte de Xiao Wang était de 150, puis. session A Après une restauration, les 150 interrogés par la session B deviennent des données sales incorrectes.

Lecture non répétable : La lecture non répétable fait référence à la lecture du même ensemble de données plusieurs fois au sein de la même transaction, mais les résultats sont différents. Les lectures non répétables se produisent car les données interrogées ont été modifiées par d'autres transactions lors de plusieurs recherches.

Regardez les deux demandes de séances ci-dessous.

会话A	会话B
begin;
select balance from xxx where client_no = ‘小王客户号’ ; （读到余额为100）	begin;
	update xxx set balance = balance+50 where client_no = ‘小王客户号’ ;
	commit;
select balance from xxx where client_no = ‘小王客户号’ ; （如果读到150，则意味着发生了不可重复读）
commit;

Dans la même transaction de la session A, les résultats de deux requêtes identiques sont différents, ce qui signifie qu'une lecture non répétable a eu lieu.

Lecture fantôme : La lecture dite fantôme signifie que lorsqu'une transaction lit des enregistrements dans une certaine plage, une autre transaction insère des enregistrements dans la plage lorsque la transaction précédente lit les enregistrements dans cette plage. à nouveau, il lira les données qui n'ont pas été lues auparavant.

Supposons qu'actuellement, seul le solde de Xiao Wang dans le tableau du compte est de 100, puis examinez les deux demandes de session suivantes.

会话A	会话B
begin;
select name from xxx where balance = 100 ; （读到name为‘小王’）	begin;
	insert into xxx(client_no,name,balance) values(‘小张客户号’,‘小张’,100)；
	commit;
select name from xxx where balance = 100 ; （如果读到了‘小王’和‘小张’，则意味着发生了幻读）
commit;

La deuxième requête de la session Une transaction a trouvé le nom « Xiao Zhang » qui n'a pas été trouvé dans la première requête, ce qui signifie qu'une lecture fantôme s'est produite.

Quatre niveaux d'isolement définis par la norme SQL

Les normes ISO et ANIS SQL définissent quatre niveaux d'isolement des transactions, à savoir : 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).

Voyons d’abord la signification de ces quatre niveaux d’isolement.

• Lire non validé : 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 vues 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 répétable, 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.

La norme SQL stipule que pour différents niveaux d'isolement, les transactions simultanées peuvent avoir des problèmes de gravité différente. La situation spécifique est la suivante :
（ √ 表示可以发生；× 表示不可以发生）

隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	√	√	√
读提交（read committed）	×	√	√
可重复读（repeatable read）	×	×	√
串行化（serializable ）	×	×	×

Prise en charge de MySQL pour quatre niveaux d'isolation

Bien que les normes ISO et ANIS SQL aient établi des normes pour quatre niveaux d'isolation des transactions, tous les fournisseurs de bases de données ne suivent pas ces normes. Par exemple, la base de données Oracle L'isolation des transactions. les niveaux de lecture non validée et répétable ne sont pas pris en charge.

Le moteur de stockage MySQL InnoDB prend en charge 4 niveaux d'isolement, mais ils sont différents de ceux définis dans le standard SQL, InnoDB 存储引擎在默认的可重复读（repeatable read）事务隔离级别下，使用 Next-Key Lock 锁的算法，避免了幻读的产生. En d'autres termes, le moteur de stockage InnoDB peut pleinement garantir les exigences d'isolation des transactions sous le niveau d'isolation des transactions de lecture répétable, c'est-à-dire qu'il a atteint les exigences de niveau d'isolation sérialisable de la norme SQL.

Comment définir le niveau d'isolement des transactions

Dans le moteur de stockage InnoDB, vous pouvez utiliser la commande suivante pour définir le niveau d'isolement des transactions globalement ou pour la session en cours :

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

Si vous souhaitez définir le niveau d'isolement de la session en cours sur lecture-validation, vous pouvez utiliser l'instruction suivante :

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Si vous souhaitez définir le niveau d'isolement par défaut de la transaction lorsque la base de données MySQL est démarrée, vous devez modifier la transaction- dans le fichier de configuration. Par exemple, si nous spécifions transaction-isolation = READ COMMITTED avant le démarrage, alors le niveau d'isolation par défaut de la transaction change par rapport à l'original REPEATABLE READ. à LIRE ENGAGÉ.

Pour afficher le niveau d'isolement des transactions de la session en cours, vous pouvez utiliser l'instruction suivante :

SELECT @@transaction_isolation;

Pour afficher le niveau d'isolement global des transactions, vous pouvez utiliser l'instruction suivante :

SELECT @@global.transaction_isolation;

Remarque : transaction_isolation a été introduit dans MySQL 5.7.20 pour remplacer tx_isolation. Si vous utilisez une version précédente de MySQL, veuillez remplacer la transaction_isolation ci-dessus par tx_isolation.

Pour plus de connaissances sur la programmation, veuillez visiter : Vidéo de programmation ! !

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!