Introduction détaillée aux quatre niveaux d'isolation des transactions dans MySQL (image et texte)

Cet article présente principalement en détail les informations pertinentes sur les quatre niveaux d'isolation des transactions de MySQL, qui ont une certaine valeur de référence. Les amis intéressés peuvent se référer à

L'environnement de test de l'expérience de cet article : Windows 10+cmd+MySQL5. .6.36+InnoDB

1. Éléments de base de la transaction (ACID)

　1. Atomicité : toutes les opérations après le démarrage de la transaction, faites tout ou ne faites pas faites-le du tout, et il est impossible de rester coincé 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.

2. Cohérence : Avant et après le début et la fin de la transaction, l'intégrité de la base de données contrainte n'a pas été détruite. Par exemple, si A transfère de l’argent à B, il est impossible pour A de déduire l’argent mais B de ne pas le recevoir.

3. Isolement : dans le même temps, une seule transaction est autorisée à demander les mêmes données, 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é.

4. 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.

Résumé : L'atomicité est la base de l'isolement des transactions, l'isolement et la durabilité sont des moyens, et le but ultime est de maintenir la cohérence des données.

2. Problèmes de concurrence des transactions

1. Lecture sale : la transaction A lit les données mises à jour par la transaction B, puis B annule l'opération, puis A lit The les données reçues sont des données sales

2. Lecture non répétable : la transaction A lit les mêmes données plusieurs fois, et la transaction B met à jour et soumet les données lors des multiples lectures de la transaction A, ce qui entraîne une transaction lorsque A lit les mêmes données plusieurs fois, les résultats sont incohérents.

3. Lecture fantôme : l'administrateur système A a modifié les scores de tous les étudiants dans la base de données des scores spécifiques aux notes ABCDE, mais l'administrateur système B a inséré un enregistrement des scores spécifiques à ce moment-là lorsque l'administrateur système a terminé. le changement était terminé, le membre A a découvert qu'il y avait encore un enregistrement qui n'avait pas été modifié. C'était comme s'il avait halluciné.

Résumé : La lecture non répétable et la lecture fantôme sont faciles à confondre. 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 des lectures non répétables, il vous suffit de verrouiller les lignes qui remplissent les conditions. Pour résoudre le problème des lectures fantômes, vous devez verrouiller la table

. 3. Niveau d'isolement des transactions MySQL

Le niveau d'isolement des transactions par défaut de MySQL est en lecture répétable

4. Utilisez des exemples pour. illustrer chaque niveau d'isolement

1. Lecture non validée :

(1) Ouvrez un client A et définissez le mode de transaction actuel pour lire non validé (lecture non validée). ), interroger la table Valeur initiale du compte :

(2) Avant que la transaction du client A ne soit validée, ouvrez un autre client B et mettez à jour le compte de la table :

(3) À ce stade, bien que la transaction du client B n'ait pas encore été soumise, le client A peut interroger les données mises à jour de B :

(4) Une fois la transaction du client B annulée pour une raison quelconque, toutes les opérations seront annulées et les données interrogées par le client A sont en fait des données sales :

(5) Exécutez l'instruction de mise à jour update account set balance = balance - 50 où id =1 sur le client A. Le solde de lilei n'est pas devenu 350, mais en fait 400. N'est-ce pas étrange que je n'ai pas demandé la cohérence des données ? , Si vous le pensez, vous êtes trop naïf. Dans l'application, nous utiliserons 400-50=350, et nous ne savons pas que d'autres sessions ont été annulées. Pour résoudre ce problème, vous pouvez lire le. soumis Niveau d'isolement

2. Lire validé

(1) Ouvrez un client A et définissez le mode de transaction actuel sur lire validé (et non soumettre lire), interroger la valeur initiale du compte de table :

(2) Avant que la transaction du client A ne soit validée, ouvrez un autre client B et mettez à jour le compte de table :

(3) À l'heure actuelle, la transaction du client B n'a pas encore été soumise et le client A ne peut pas interroger les données mises à jour de B, ce qui résout le problème de lecture sale :

(4) Soumission de la transaction du client B

(5) Le client A exécute la même requête qu'à l'étape précédente, et le résultat est L'étape précédente est incohérente, ce qui crée un problème de lecture non reproductible. Dans l'application, en supposant que nous sommes dans la session du client A, nous demandons que le solde de lilei est de 450, mais d'autres transactions modifient la valeur du solde de lilei à 400. Nous ne savons pas qu'il y aura un problème si vous utilisez la valeur 450 pour effectuer d'autres opérations, mais la probabilité est vraiment faible. Pour éviter ce problème, vous pouvez utiliser le niveau d'isolement de lecture répétable

3. Lecture répétable

(1) Ouvrez un client A, définissez le mode de transaction actuel sur lecture répétable et interrogez la valeur initiale du compte de table :

(2) Avant que la transaction du client A ne soit soumise, ouvrez un autre client B, mettez à jour le compte de table et soumettez, la transaction du client B peut en fait modifier la requête de transaction du client A. Les lignes atteintes, c'est-à-dire la lecture répétable de MySQL ne verrouillera pas les lignes interrogées par la transaction. Cela dépasse mes attentes. Lorsque le niveau d'isolement des transactions dans la norme SQL est une lecture répétable, les opérations de lecture et d'écriture doivent verrouiller les lignes de Mysql. alors je suis sorti. Attention à bien verrouiller les lignes dans l'application, sinon vous utiliserez le solde de 400 de lilei à l'étape (1) comme valeur intermédiaire pour faire d'autres opérations

(3) Exécuter le requête de l'étape (1) sur le client A :

(4) Exécutez l'étape (1), le solde de lilei est toujours de 400, ce qui est cohérent avec le résultat de la requête de l'étape ( 1) , il n'y a pas de problème de lecture non reproductible ; puis exécutez update balance = balance - 50 où id = 1, la balance ne devient pas 400-50 = 350, la valeur de la balance de lilei est calculée en utilisant 350 à l'étape (2), donc c'est 300. La cohérence des données n'a pas été détruite. C'est un peu magique. C'est peut-être une fonctionnalité de mysql

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  400 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(5) Démarrer un. transaction sur le client A, interrogez la valeur initiale du compte de table

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(6) Démarrez la transaction sur le client B, ajoutez une nouvelle donnée, dans laquelle le la valeur du champ de solde est 600, et soumettez

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(4,'lily',600);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

(7) Calculez la somme du solde du client A, la valeur est 300+16000+2400=18700, la La valeur du client B n'est pas incluse, le client Après que le client A l'ait soumis, le solde a été calculé et il s'est avéré être de 19 300. En effet, les 600 du client B ont été inclus dans le calcul. Du point de vue du client, le client ne peut pas voir. client B, et il pensera que c'est le monde. Le gâteau est perdu, 600 yuans de plus. Du point de vue du développeur, la cohérence des données n'est pas détruite. Mais dans l'application, notre code peut soumettre 18700 à l'utilisateur. Si vous devez éviter cette situation de faible probabilité, vous devez alors adopter le niveau d'isolement des transactions « sérialisation » introduit ci-dessous

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 18700 |
+--------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 19300 |
+--------------+
1 row in set (0.00 sec)

4. Sérialisation

(1) Ouvrez un client A, définissez le mode de transaction actuel sur sérialisable et interrogez la valeur initiale de la table de compte :

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 10000 |
| 2 | hanmei | 10000 |
| 3 | lucy | 10000 |
| 4 | lily | 10000 |
+------+--------+---------+
rows in set (0.00 sec)

(2) Ouvrez un client B et définissez le mode de transaction actuel sur sérialisable. Lors de l'insertion d'un enregistrement, une erreur est signalée et l'insertion échoue. Transaction dans mysql Lorsque le niveau d'isolement est atteint. sérialisable, la table sera verrouillée, donc les lectures fantômes ne se produiront pas. Ce niveau d'isolement a une concurrence extrêmement faible. Souvent, une transaction occupe une table, et des milliers d'autres transactions ne peuvent être utilisées qu'après soumission. est rarement utilisé dans le développement.

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Informations supplémentaires : Différences

2. Lorsque le niveau d'isolement des transactions par défaut dans MySQL est répétable, la lecture est effectuée. les lignes ne seront pas verrouillées

3. Lorsque le niveau d'isolement des transactions est la sérialisation, les données sont lues Verrouillera la table entière

4. Lors de la lecture de cet article, du point de vue d'un développeur, vous pouvez penser que la lecture non répétable et la lecture fantôme n'ont aucun problème logique et que les données finales sont toujours cohérentes. Oui, mais du point de vue de l'utilisateur, il ne peut généralement voir qu'une seule transaction (uniquement le client A, ne connaissant pas l'existence de la transaction). client secret B), et ne prendra pas en compte le phénomène d'exécution simultanée de transactions. Une fois la même transaction effectuée, si les données sont lues plusieurs fois avec des résultats différents, ou si de nouveaux enregistrements apparaissent de nulle part, ils peuvent avoir des doutes. un problème d’expérience utilisateur.

5. Lorsqu'une transaction est exécutée dans MySQL, le résultat final n'aura pas de problème de cohérence des données, car dans une transaction, MySQL n'utilise pas nécessairement les résultats intermédiaires de l'opération précédente lors de l'exécution d'une opération. autre La situation réelle des transactions simultanées est traitée, ce qui semble illogique, mais cela garantit la cohérence des données, mais lorsque la transaction est exécutée dans l'application, le résultat d'une opération sera utilisé par l'opération suivante et d'autres calculs seront effectués. C'est pourquoi nous devons être prudents. Nous devons verrouiller les lignes lors de la lecture répétable et verrouiller les tables lors de la sérialisation, sinon la cohérence des données sera détruite.

6. Lorsque les transactions sont exécutées dans MySQL, MySQL traitera complètement chaque transaction en fonction de la situation réelle, ce qui empêchera la cohérence des données d'être détruite, mais l'application suivra des routines logiques, ce qui n'est pas le cas. Aussi intelligent que MySQL, des problèmes de cohérence des données surgiront inévitablement.

7. Plus le niveau d'isolement est élevé, plus les données peuvent être garanties complètes et cohérentes, mais l'impact sur les performances de concurrence sera également plus grand. Vous ne pouvez pas avoir le gâteau et le manger aussi. Pour la plupart des applications, vous pouvez donner la priorité à la définition du niveau d'isolement du système de base de données sur Lecture validée, ce qui peut éviter les lectures incorrectes et offrir de meilleures performances de concurrence. Bien que cela entraîne des problèmes de concurrence tels que des lectures non répétables et des lectures fantômes, dans des situations individuelles où de tels problèmes peuvent survenir, l'application peut utiliser des verrous pessimistes ou optimistes pour les contrôler.

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!