Exemple de didacticiel sur le niveau d'isolement des transactions MySQL

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

1. Éléments de base des transactions (ACID)

1. commence, toutes les opérations sont terminées ou non, 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.

2. Cohérence : avant et après le début et la fin de la transaction, les contraintes d'intégrité de la base de données ne sont pas violées. 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.

Modifier , la lecture fantôme se concentre sur l'ajout ou la suppression de . Pour résoudre le problème de lecture non répétable, il vous suffit de verrouiller les lignes qui remplissent les conditions, et pour résoudre le problème de lecture fantôme, il vous suffit de 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. Utiliser des exemples pour illustrer chaque niveau d'isolement

1. Lire non engagé :

(1) Ouvrez un client A et définissez le mode de transaction actuel sur lecture non validée (lecture non validée), interrogez 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) À 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écuter l'instruction de mise à jour update account set balance = balance - 50 où id =1 sur le solde du client A. lilei n'est pas devenu 350, mais en réalité 400. N'est-ce pas étrange ? La cohérence des données n'a pas été demandée,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 sont annulées. Pour résoudre ce problème, vous pouvez utiliser le niveau d'isolement de lecture validée

Dans l'application, en supposant que nous sommes dans la session du client A, la requête constate que le solde de lilei est de 450, mais d'autres transactions modifieront le solde de lilei. Je ne sais pas que si nous utilisons la valeur 450 pour effectuer d'autres opérations, il y aura un problème, 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 et définissez le mode de transaction actuel sur lecture répétable, 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,

(3 ) Exécutez la 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), et il n'y a pas de problème de lecture non reproductible ; Ensuite, exécutez update balance = balance - 50 où id = 1, le solde ne devient pas 400-50 = 350, la valeur du solde de lilei est calculée en utilisant 350 à l'étape (2), elle est donc 300, et la cohérence des données n'est pas détruit. , 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 |+------+--------+---------+3 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: 0mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     300 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 rows in set (0.00 sec)

　 (5) Démarrer une transaction sur le client A et interroger 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 |+------+--------+---------+3 rows in set (0.00 sec)

(6) Ouvrez la transaction sur le client B, ajoutez une nouvelle donnée, dans laquelle 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, et la valeur est 300+16000+2400=18700. La valeur du client B n'est pas incluse. Une fois que le client A a soumis, la somme. du solde est calculé, et il s'avère être 19300. En effet, les 600 du client B sont inclus

, du point de vue du client, le client ne peut pas voir le client B, et il aura l'impression que le monde a a perdu son gâteau, avec 600 yuans supplémentaires. Cette lecture Phantom, du point de vue du développeur, ne détruit pas la cohérence des données. 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

　4. Sérialisation

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

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 |+------+--------+---------+4 rows in set (0.00 sec)

　(2) Ouvrez un client B et définissez le mode de transaction actuel. Il est sérialisable et une erreur est signalée lors de l'insertion d'un record. La table est verrouillée et l'insertion échoue. Lorsque le niveau d'isolement des transactions dans MySQL est 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, et souvent une seule transaction. occupe une table, et des milliers d'autres transactions ne peuvent rester inactives que jusqu'à ce qu'elles soient terminées et soumises avant de pouvoir être utilisées. Ceci 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

1. La mise en œuvre spécifique des normes stipulées dans la spécification SQL peut être quelque peu différente selon les bases de données

　2 . Le niveau d'isolement des transactions par défaut dans MySQL est une lecture répétable et les lignes lues ne seront pas verrouillées

3. Isolation des transactions Lorsque le niveau est sérialisation, la lecture des données verrouillera la table entière

4. Lors de la lecture de cet article , si vous êtes du point de vue d'un développeur, il peut n'y avoir aucun problème logique avec les lectures non répétables et les lectures fantômes, et les données finales sont toujours cohérentes. Cependant, du point de vue de l'utilisateur, ils ne peuvent généralement voir qu'une seule transaction (une seule). la transaction peut être vue). Le client A ne connaît pas l'existence du client infiltré B) et ne considère pas le phénomène d'exécution simultanée de transactions une fois que les mêmes données sont lues plusieurs fois avec des résultats différents, ou que de nouveaux enregistrements apparaissent de manière inattendue. air, ils peuvent avoir des doutes. Il s’agit de problèmes d’expérience utilisateur.

5. Lorsqu'une transaction est exécutée dans MySQL, le résultat final n'aura pas de problèmes de cohérence des données, car dans une transaction dans MySQL , effectuer une opération n'utilisera pas nécessairement les résultats intermédiaires de l'opération précédente. Elle sera traitée en fonction de la situation réelle des autres transactions concurrentes, mais cela garantit la cohérence des données mais la transaction est exécutée dans le application , le résultat d'une opération est utilisé par l'opération suivante et d'autres calculs sont 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 les traitera de manière exhaustive en fonction de la situation réelle de chaque transaction, ce qui n'entraînera aucun cohérence des données. Détruit, mais l'application joue aux cartes selon des routines logiques, ce qui n'est pas aussi intelligent que MySQL, et des problèmes de cohérence des données surviendront inévitablement.

　7

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!