Die spezifische Implementierung von optimistischem Sperren und pessimistischem Sperren in MySQL

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL, das hauptsächlich die spezifische Implementierung von MySQL optimistischer Sperre und pessimistischer Sperre vorstellt. Ich hoffe, dass es für alle hilfreich ist . .

Empfohlenes Lernen: MySQL-Video-Tutorial

Viele Entwickler sind möglicherweise nicht mit optimistischem Sperren und pessimistischem Sperren in MySQL vertraut und wissen nicht, wie sie diese implementieren sollen. In diesem Artikel wird eine praktische Falldemonstration zu diesem Thema gegeben, damit Sie den Unterschied zwischen den beiden Schlössern vollständig verstehen können.

Sperrenklassifizierung

Die mittleren Sperren von MySQL werden je nach Umfang hauptsächlich in Tabellensperren, Zeilensperren und Seitensperren unterteilt. Die Myisam-Speicher-Engine unterstützt nur Tabellensperren, während InnoDB nicht nur Zeilensperren, sondern bis zu einem gewissen Grad auch Tabellensperren unterstützt. Je nach Verhalten kann es in gemeinsame Sperren (Lesesperren), exklusive Sperren (Schreibsperren) und Absichtssperren unterteilt werden. Nach ihren Vorstellungen werden sie in optimistische Schlösser und pessimistische Schlösser unterteilt.

Der heutige Artikel zeigt, wie optimistisches Sperren und pessimistisches Sperren in der Praxis funktionieren.

Tabellenstruktur

Die folgende SQL-Anweisung ist die Struktur der Tabelle:

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;

Simulierte Daten einfügen:

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;

Daten in die Tabelle.

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

Pessimistische Sperre

Pessimistische Sperre, eine relativ negative Art, mit Sperren umzugehen. Fassen Sie das Schloss direkt an, wenn Sie Daten bedienen. Andere laufende Transaktionen warten, bis die Transaktion, die die Sperre hält, die Sperre aufhebt.

Diese Verarbeitungsmethode kann die maximale Konsistenz der Daten gewährleisten, kann jedoch leicht zu Problemen wie Sperrzeitüberschreitungen und geringer Parallelität führen. Zuerst starten wir die erste Transaktion und aktualisieren die Daten mit der ID=1. Zu diesem Zeitpunkt senden wir die Transaktion nicht.

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

Dann starten wir Transaktion zwei und aktualisieren die Daten mit id=1, um zu sehen, was zu diesem Zeitpunkt passieren wird?

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;

Nachdem wir die Update-Anweisung ausgeführt haben, befinden wir uns in einem Wartezustand und die SQL-Anweisung wird nicht sofort ausgeführt. Dies liegt daran, dass die Schreibsperre für die Daten mit der ID = 1 nicht besteht, sobald die Transaktion nicht festgeschrieben ist freigegeben.

Der Effekt ist wie folgt:

Durch das obige Beispiel können wir den Implementierungsprozess des pessimistischen Sperrens intuitiv spüren.

Optimistische Sperre

Optimistische Sperre geht davon aus, dass Daten unter normalen Umständen keine Konflikte verursachen. Datenkonflikte werden nur behandelt, wenn die Daten geändert werden. Wie wird hier der Konflikt entdeckt? Die herkömmliche Methode besteht darin, der Datenzeile ein Feld wie eine Versionsnummer oder einen Zeitstempel hinzuzufügen. (Dieser Artikel verwendet die Version als gute Möglichkeit zur Versionierung und verwendet aus demselben Grund den Zeitstempel.)

Das Implementierungsprinzip der optimistischen Sperre:

• Wenn eine Transaktion Daten liest, wird das entsprechende Versionsnummernfeld gelesen Zu diesem Zeitpunkt ist die Versionsnummer 1.
• Eine andere Transaktion führt ebenfalls den gleichen Lesevorgang aus. Wenn die Transaktion festgeschrieben wird, ist die Versionsnummer +1. Zu diesem Zeitpunkt ist die Versionsnummer der Datenzeile 2.

• Wenn die zweite Transaktion einen Änderungsvorgang durchführt, werden Bedingungen basierend auf den Geschäftsdaten erstellt und standardmäßig eine Versionsnummer als Where-Bedingung hinzugefügt. Zu diesem Zeitpunkt erfüllt das Versionsnummernfeld in der Änderungsanweisung nicht die Where-Bedingung und die Transaktion kann nicht ausgeführt werden. Auf diese Weise wird die Sperrfunktion erreicht.

Client eins:

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

Die Reihenfolge der Ausführung von Aktualisierungsanweisungen sollte nach der Ausführung von select durch Client zwei erfolgen.

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

Zu diesem Zeitpunkt ist gemäß der von Update zurückgegebenen Struktur ersichtlich, dass die Anzahl der betroffenen Zeilen 0 ist. Gleichzeitig sind nach der Auswahlabfrage die Cashback-Daten vorhanden auch die Daten der Transaktion eins.

Anwendbare Szenarien

Pessimistische Sperre: Es eignet sich besser für Szenarien mit häufigen Schreibvorgängen. Wenn eine große Anzahl von Lesevorgängen vorhanden ist, wird bei jedem Lesevorgang eine Sperre durchgeführt, was den Sperraufwand erheblich erhöht und den Durchsatz des Systems verringern.

Optimistische Sperre: Es eignet sich besser für Szenarien, in denen Lesevorgänge häufiger auftreten. Wenn eine große Anzahl von Schreibvorgängen auftritt, erhöht sich die Wahrscheinlichkeit von Datenkonflikten, um die Konsistenz der Daten sicherzustellen Muss Daten ständig neu erfasst werden, erhöht dies die Anzahl der Abfragevorgänge und verringert den Durchsatz des Systems.

Zusammenfassung

Beide Typen haben ihre eigenen Vor- und Nachteile. Sie werden für häufige Lesevorgänge verwendet, während pessimistische Sperren für häufige Schreibvorgänge verwendet werden.

Optimistisches Sperren eignet sich für Situationen, in denen es relativ wenige Schreibvorgänge gibt, das heißt, wenn Konflikte wirklich selten auftreten. Dadurch können die Kosten für das Sperren eingespart und der Gesamtdurchsatz des Systems erhöht werden. Wenn jedoch häufig Konflikte auftreten, wird die Anwendung der oberen Ebene weiterhin versuchen, die Leistung zu verringern. Daher ist es in diesem Fall angemessener, pessimistisches Sperren zu verwenden Das Aktualisieren derselben Daten ist hoch. Wenn der Konflikt schwerwiegend ist, wird eine pessimistische Sperre verwendet.

Pessimistische Sperren eignen sich besser für Szenarien mit starker Konsistenz, aber die Effizienz ist relativ gering, insbesondere ist die Leseparallelität gering. Optimistisches Sperren eignet sich für Szenarien mit mehr Lesevorgängen und weniger Schreibvorgängen sowie weniger Parallelitätskonflikten.

Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonDie spezifische Implementierung von optimistischem Sperren und pessimistischem Sperren in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!