Exemple d'analyse de quatre niveaux d'isolation des transactions dans MySQL

Dans les opérations de base de données, afin de garantir efficacement l'exactitude des données lues simultanément, le niveau d'isolation des transactions est proposé. Il existe 4 niveaux d'isolement pour les transactions de base de données. La norme SQL définit 4 niveaux d'isolement, y compris des règles spécifiques pour limiter les modifications visibles et invisibles à l'intérieur et à l'extérieur de la transaction. L'article suivant analyse en détail les informations pertinentes sur les quatre niveaux d'isolation des transactions dans MySQL à travers des exemples. Les amis dans le besoin peuvent s'y référer.

Avant-propos

Pas grand chose à dire ci-dessous, jetons un œil à l'introduction détaillée.

Il existe quatre niveaux d'isolement pour les transactions de base de données :

• Lecture non validée : les lectures incorrectes sont autorisées, c'est-à-dire que les transactions non validées dans d'autres sessions peuvent être lues.

• Lecture validée : seules les données soumises peuvent être lues par défaut.

• Lecture répétée : lecture répétable. Les requêtes au sein d'une même transaction sont cohérentes au début de la transaction, niveau par défaut d'InnoDB. Dans le standard SQL, ce niveau d'isolement élimine les lectures non répétables, mais les lectures fantômes existent toujours.

• Lecture série (sérialisable) : lecture entièrement sérialisée. Chaque lecture nécessite un verrou partagé au niveau de la table, et la lecture et l'écriture se bloqueront.

Les amis qui sont exposés pour la première fois au concept d'isolement des transactions peuvent être déroutés par la définition du manuel ci-dessus. Expliquons les quatre niveaux d'isolement à travers des exemples spécifiques.

Nous créons d'abord une table utilisateur :

CREATE TABLE user (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Lire le niveau d'isolement non validé

Nous définissons d'abord le niveau d'isolement de la transaction pour lire validé :

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED  |
+------------------------+
1 row in set (0.00 sec)

Ci-dessous, nous avons ouvert deux terminaux pour simuler respectivement la transaction un et la transaction deux. p.s : L'opération un et l'opération deux sont censées être exécutées dans l'ordre chronologique.

Transaction 1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('ziwenxie'); # 操作3
Query OK, 1 row affected (0.05 sec)

Transaction 2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作4
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

Il ressort clairement des résultats d'exécution ci-dessus que sous le niveau de lecture non engagé, nous sommes dans la première transaction. est possible de lire des données qui ne sont pas validées dans la transaction deux, ce qui constitue une lecture sale.

Niveau d'isolement de lecture validé

Le problème de lecture sale ci-dessus peut être résolu en définissant le niveau d'isolement sur validé.

mysql> set session transaction isolation level read committed;

Transaction 1

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

Transaction 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

Bien que le problème de lecture sale ait été résolu, veuillez noter que dans l'opération 7 de la transaction 1, transaction 2. Après la validation de l'opération 6, la transaction 1 lira des données différentes deux fois dans la même transaction. Il s'agit d'un problème de lecture non répétable. L'utilisation de la lecture répétable du troisième niveau d'isolement de transaction peut résoudre ce problème.

Niveau d'isolement de lecture répétable

Le niveau d'isolement des transactions par défaut du moteur de stockage Innodb de MySQL est le niveau d'isolement de lecture répétable, nous n'avons donc pas besoin de définir de paramètres supplémentaires.

Transaction 1

mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

Transaction 2

mysql> start tansactoin; # 操作二
mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作四

Dans l'opération 5 de la transaction 1, nous n'avons pas lu la mise à jour de la transaction 2 dans l'opération 3. La mise à jour les données ne peuvent être lues qu'après validation.

InnoDB a-t-il résolu les lectures fantômes ?

En fait, des lectures fantômes peuvent se produire au niveau RR. Le moteur InnoDB prétend officiellement utiliser le contrôle de concurrence multi-version MVCC pour résoudre ce problème. qu'Innodb est vraiment. La lecture fantôme a-t-elle été résolue ?

Pour faciliter l'affichage, j'ai modifié le tableau utilisateur ci-dessus :

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)
mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+----------+----------+
| id | name  | salary |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

Transaction 1

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # 操作六，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user; # 操作七， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name  | salary |
+----+----------+--------+
| 10 | ziwenxie | 4444 |
| 11 | zhangsan | 4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

Transaction 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

Comme le montre l'exemple ci-dessus, Innodb ne résout pas la lecture fantôme comme indiqué officiellement, mais le scénario ci-dessus n'est pas très courant et il n'y a pas lieu de trop s'inquiéter.

Niveau d'isolement de sérialisation

Toutes les transactions sont exécutées en série au niveau d'isolement le plus élevé, les lectures fantômes ne se produiront pas et les performances seront très médiocres.

Recommandations associées :

Tutoriel d'exemple de niveau d'isolation des transactions MySQL

Explication détaillée et comparaison des quatre niveaux d'isolation des transactions MySQL

Une brève analyse de l'impact du niveau d'isolation des transactions MySQL sur ses performances

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!