Implémentation spécifique du verrouillage optimiste et pessimiste MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement l'implémentation spécifique du verrouillage optimiste et pessimiste de MySQL. L'article présente le contenu détaillé autour du thème. Examinons-le ensemble, j'espère que cela vous sera utile. à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Pour le verrouillage optimiste et le verrouillage pessimiste dans MySQL, de nombreux développeurs ne le connaissent peut-être pas très bien et ne savent pas comment l'implémenter. Cet article va vous donner une démonstration de cas pratique sur cette problématique afin que vous puissiez bien comprendre la différence entre les deux serrures.

Classification des verrous

Les verrous intermédiaires de MySQL sont principalement divisés en verrous de table, verrous de ligne et verrous de page en fonction de leur portée. Le moteur de stockage myisam ne prend en charge que les verrous de table, tandis qu'InnoDB prend en charge non seulement les verrous de ligne, mais également les verrous de table dans une certaine mesure. Selon le comportement, il peut être divisé en verrous partagés (verrous de lecture), verrous exclusifs (verrous d'écriture) et verrous d'intention. Selon leurs idées, ils sont divisés en verrous optimistes et verrous pessimistes.

L’article d’aujourd’hui montre comment le verrouillage optimiste et le verrouillage pessimiste fonctionnent dans la pratique.

Structure de la table

L'instruction SQL suivante est la structure de la table :

CREATE TABLE `demo`.`user` (
`id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`sex` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`version` int(1) NULL DEFAULT 1 COMMENT '数据版本号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

Insérer des données simulées :

BEGIN;
INSERT INTO `user` VALUES (0000000001, '张三', 0, '18228937997@163.com', '18228937997', 1);
INSERT INTO `user` VALUES (0000000002, '李四', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000003, '李四1', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000004, '李四2', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000005, '李四3', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000006, '李四4', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000007, '李四55', 0, '1005349393@163.com', '15683202302', 1);
COMMIT;

Données dans la table.

mysql root@127.0.0.1:demo> select * from user;
+----+--------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+--------+-----+---------------------+-------------+---------+
| 1 | 张三 | 0 | 18228937997@163.com | 18228937997 | 2 |
| 2 | 李四 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 3 | 李四1 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 4 | 李四2 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 5 | 李四3 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 6 | 李四4 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 7 | 李四55 | 0 | 1005349393@163.com | 15683202302 | 1 |
+----+--------+-----+---------------------+-------------+---------+
7 rows in set
Time: 0.011s

Verrouillage pessimiste

Verrouillage pessimiste, une manière relativement négative de gérer les verrous. Saisissez le verrou directement lors de l'exploitation des données. Les autres transactions en cours attendront que la transaction détenant le verrou libère le verrou.

Cette méthode de traitement peut garantir la cohérence maximale des données, mais elle peut facilement entraîner des problèmes tels qu'un délai d'attente de verrouillage et une faible concurrence. Tout d'abord, nous démarrons la première transaction et mettons à jour les données avec id=1. Pour le moment, nous ne soumettons pas la transaction.

mysql root@127.0.0.1:demo> begin;
Query OK, 0 rows affected
Time: 0.002s
mysql root@127.0.0.1:demo> update `user` set name = '张三111111'where id = 1;
Query OK, 1 row affected
Time: 0.004s

Ensuite, nous commençons la transaction deux et mettons à jour les données avec id=1 pour voir ce qui va se passer à ce moment-là ?

mysql root@127.0.0.1:demo> begin;
Query OK, 0 rows affected
Time: 0.002s
mysql root@127.0.0.1:demo> update `user` set sex = 1 where id = 1;

Après avoir exécuté l'instruction de mise à jour, nous sommes dans un état d'attente et l'instruction SQL ne sera pas exécutée immédiatement car une fois la transaction non validée, le verrou d'écriture correspondant aux données avec id=1 ne l'est pas. libéré.

L'effet est le suivant :

Grâce à l'exemple ci-dessus, nous pouvons ressentir intuitivement le processus de mise en œuvre du verrouillage pessimiste.

Verrouillage optimiste

Le verrouillage optimiste estime que les données ne provoqueront pas de conflits dans des circonstances normales. Ce n'est que lorsque les données sont modifiées que les conflits de données seront traités. Comment le conflit est-il découvert ici ? La méthode conventionnelle consiste à ajouter un champ tel qu'un numéro de version ou un horodatage à la ligne de données. (Cet article utilise la version comme un bon moyen de versionner, et utilise l'horodatage pour la même raison)

Le principe de mise en œuvre du verrouillage optimiste :

• Lorsqu'une transaction lit des données, elle lit le champ du numéro de version correspondant. à l'heure actuelle, le numéro de version est 1.
• Une autre transaction effectue également la même opération de lecture. Lorsque la transaction est validée, le numéro de version est +1. À ce stade, le numéro de version de la ligne de données est 2.

• Lorsque la deuxième transaction effectue une opération de modification, des conditions sont définies en fonction des données commerciales et un numéro de version est ajouté par défaut comme condition Where. À l'heure actuelle, le champ du numéro de version dans l'instruction de modification ne remplit pas la condition Where et la transaction ne parvient pas à s'exécuter. De cette façon, la fonction de verrouillage est réalisée.

Client un :

mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+------------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+------------+-----+---------------------+-------------+---------+
| 1 | 张三111111 | 0 | 18228937997@163.com | 18228937997 | 1 |
+----+------------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.012s
mysql root@127.0.0.1:demo> update `user` set name = '事务一', version = version + 1 where id = 1 and version = 1;
Query OK, 1 row affected
Time: 0.008s
mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+--------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+--------+-----+---------------------+-------------+---------+
| 1 | 事务一 | 1 | 18228937997@163.com | 18228937997 | 2 |
+----+--------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.009s

L'ordre d'exécution des instructions de mise à jour doit être après que le client deux exécute la sélection.

Client 2 :

mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+------------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+------------+-----+---------------------+-------------+---------+
| 1 | 张三111111 | 1 | 18228937997@163.com | 18228937997 | 1 |
+----+------------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.015s
mysql root@127.0.0.1:demo> update `user` set name = '事务二', version = version + 1 where id = 1 and version = 1;
Query OK, 0 rows affected
Time: 0.003s
mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+--------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+--------+-----+---------------------+-------------+---------+
| 1 | 事务一 | 1 | 18228937997@163.com | 18228937997 | 2 |
+----+--------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.012s

A ce moment, selon la structure renvoyée par update, on peut voir que le nombre de lignes affectées est 0. En même temps, après la requête de sélection, les données de cashback sont également les données de la première transaction.

Scénarios applicables

Verrouillage pessimiste : Il est plus adapté aux scénarios avec des opérations d'écriture fréquentes. S'il y a un grand nombre d'opérations de lecture, le verrouillage sera effectué à chaque lecture, ce qui augmentera considérablement la surcharge de verrouillage. et réduire le débit du système.

Verrouillage optimiste : Il est plus adapté aux scénarios où les opérations de lecture sont plus fréquentes. Si un grand nombre d'opérations d'écriture se produisent, la possibilité de conflits de données augmentera. Afin d'assurer la cohérence des données, la couche application. doit continuellement réacquérir des données, cela augmentera un grand nombre d'opérations de requête et réduira le débit du système.

Résumé

Les deux types ont leurs propres avantages et inconvénients. Les verrous optimistes sont utilisés pour les lectures fréquentes, et les verrous pessimistes sont utilisés pour les écritures fréquentes.

Le verrouillage optimiste convient aux situations où il y a relativement peu d'écritures, c'est-à-dire lorsque les conflits se produisent très rarement. Cela peut réduire le coût du verrouillage et augmenter le débit global du système. Mais si des conflits se produisent souvent, l'application de couche supérieure continuera à réessayer, ce qui réduit les performances. Par conséquent, dans ce cas, il est plus approprié d'utiliser le verrouillage pessimiste. La raison pour laquelle le verrouillage pessimiste est utilisé est la probabilité de deux utilisateurs. la mise à jour du même élément de données est élevée, c'est-à-dire que lorsque le conflit est grave, un verrouillage pessimiste est utilisé.

Le verrouillage pessimiste est plus adapté aux scénarios de cohérence forte, mais l'efficacité est relativement faible, en particulier la concurrence de lecture est faible. Le verrouillage optimiste convient aux scénarios avec plus de lectures, moins d'écritures et moins de conflits de concurrence.

Apprentissage recommandé : Tutoriel vidéo mysql

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!