Verständnis und Anwendungsanalyse der pessimistischen und optimistischen Sperre von MySQL

In diesem Artikel werden die pessimistische und die optimistische Sperre von MySQL anhand von Beispielen beschrieben. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Pessimistisches Sperren und optimistisches Sperren sind von Menschen definierte Konzepte. Sie können sie als eine Art Gedanke verstehen, der ein gängiges Mittel zum Umgang mit gleichzeitigen Ressourcen darstellt.

Verwechseln Sie sie nicht mit den in MySQL bereitgestellten Sperrmechanismen (Tabellensperren, Zeilensperren, exklusive Sperren, gemeinsame Sperren).

1. Pessimistische Sperre

Wie der Name schon sagt, bedeutet es, bei der Datenverarbeitung pessimistisch zu sein und immer davon auszugehen, dass es zu Parallelitätskonflikten kommt und dass andere beim Abrufen und Ändern von Daten die Daten ändern. Daher müssen die Daten während des gesamten Datenverarbeitungsprozesses gesperrt werden.

Die Implementierung der pessimistischen Sperre basiert normalerweise auf dem von der Datenbank bereitgestellten Sperrmechanismus, z. B. der exklusiven Sperre von MySQL, „select ...“ für die Aktualisierung, um eine pessimistische Sperre zu implementieren.

Beispiel: Bei einem Flash-Sale wird die Lagermenge reduziert, um einen Überverkauf zu vermeiden.

Tabelle „tb_goods_stock“ erstellen (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'ID',
 `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT 'Produkt-ID',
 `nums` int(11) unsigned DEFAULT '0' COMMENT 'Produktbestandsmenge',
 `create_time` datetime DEFAULT NULL COMMENT 'Erstellungszeit',
 `modify_time` datetime DEFAULT NULL COMMENT 'Aktualisierungszeit',
 Primärschlüssel (`id`),
 EINZIGARTIGER SCHLÜSSEL `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Produktbestandstabelle';

Legen Sie den Feldtyp „Nums“ für die Produktbestandsmenge auf „ohne Vorzeichen“ fest, um sicherzustellen, dass auf Datenbankebene keine negativen Zahlen auftreten.

Beachten Sie, dass Sie zum Verwenden der pessimistischen Sperre die Autocommit-Funktion von MySQL deaktivieren und Autocommit auf 0 setzen müssen.

Beachten Sie, dass Zeilensperren in MySQL auf Indizes basieren. Wenn SQL keine Indizes verwendet, werden Tabellensperren verwendet, um die gesamte Tabelle zu sperren.

1. Starten Sie eine Transaktion, fragen Sie die zu verkaufenden Produkte ab und sperren Sie den Datensatz.

beginnen;
Wählen Sie Zahlen aus tb_goods_stock, wobei goods_id = {$goods_id} für die Aktualisierung ist;

2. Stellen Sie fest, ob die Warenmenge größer ist als die Einkaufsmenge. Wenn Sie nicht zufrieden sind, machen Sie die Transaktion rückgängig.

3. Wenn die Bedingungen erfüllt sind, reduzieren Sie den Bestand und führen Sie die Transaktion aus.

Aktualisiere tb_goods_stock und setze Nums = Nums - {$num} 
wobei goods_id = {$goods_id} und nums >= {$num};
begehen;

Die während der Transaktion aufrechterhaltenen Sperren werden freigegeben, wenn die Transaktion festgeschrieben wird.

Beim pessimistischen Sperren wird eine konservative Strategie angewendet, bei der zuerst gesperrt und dann die Daten unter gleichzeitiger Kontrolle verarbeitet werden. Dies gewährleistet zwar die Sicherheit der Datenverarbeitung, verringert jedoch auch die Effizienz.

2. Optimistisches Sperren

Wie der Name schon sagt, bedeutet es, eine optimistische Haltung gegenüber der Datenverarbeitung einzunehmen und optimistisch zu glauben, dass es im Allgemeinen keine Datenkonflikte gibt. Nur beim Senden von Datenaktualisierungen werden Datenkonflikte erkannt.

Wenn ein Konflikt gefunden wird, wird dem Benutzer eine Fehlermeldung zurückgegeben, die es ihm ermöglicht, zu entscheiden, wie er weiter vorgehen möchte.

Die Implementierung der optimistischen Sperre beruht nicht auf dem von der Datenbank bereitgestellten Sperrmechanismus, sondern erfordert, dass wir ihn selbst implementieren. Die Implementierungsmethode besteht im Allgemeinen darin, die Datenversion aufzuzeichnen, einmal über die Versionsnummer und einmal über den Zeitstempel.

Fügen Sie der Tabelle eine Versionsnummer oder ein Zeitstempelfeld hinzu. Lesen Sie beim Lesen von Daten die Versionsnummer mit. Wenn die Daten aktualisiert werden, erhöhen Sie die Versionsnummer um 1.

Bei der Übermittlung von Datenaktualisierungen prüfen wir, ob die aktuelle Versionsnummer mit der erstmals ausgelesenen Versionsnummer übereinstimmt. Wenn sie gleich sind, werden sie aktualisiert. Andernfalls gelten die Daten als abgelaufen und die Aktualisierung wird abgelehnt, so dass der Benutzer den Vorgang erneut durchführen muss.

Tabelle „tb_goods_stock“ erstellen (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'ID',
 `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT 'Produkt-ID',
 `nums` int(11) unsigned DEFAULT '0' COMMENT 'Produktbestandsmenge',
 `create_time` datetime DEFAULT NULL COMMENT 'Erstellungszeit',
 `modify_time` datetime DEFAULT NULL COMMENT 'Aktualisierungszeit',
 `version` bigint(20) unsigned DEFAULT '0' COMMENT 'Versionsnummer',
 Primärschlüssel (`id`),
 EINZIGARTIGER SCHLÜSSEL `goods_id` (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='Produktbestandstabelle';

1. Fragen Sie das zu verkaufende Produkt ab und ermitteln Sie die Versionsnummer.

beginnen;
Wählen Sie Nums, Version von tb_goods_stock, wobei goods_id = {$goods_id};

2. Stellen Sie fest, ob die Warenmenge größer ist als die Einkaufsmenge. Wenn Sie nicht zufrieden sind, machen Sie die Transaktion rückgängig.

3. Wenn die Bedingungen erfüllt sind, reduzieren Sie den Bestand. (Stellen Sie beim Aktualisieren fest, ob die aktuelle Version mit der in Schritt 1 erhaltenen Version übereinstimmt.)

Aktualisiere tb_goods_stock und setze Nums = Nums - {$num}, Version = Version + 1 
wobei goods_id = {$goods_id} 
und Version = {$version} und Nums >= {$num};

4. Bestimmen Sie, ob der Aktualisierungsvorgang erfolgreich ausgeführt wurde. Wenn er erfolgreich war, führen Sie einen Commit aus, andernfalls führen Sie ein Rollback durch.

Die optimistische Sperre wird programmbasiert implementiert, sodass es zu keinen Deadlocks kommt und sie für leseintensive Anwendungsszenarien geeignet ist. Wenn Konflikte häufig auftreten, fordert die Anwendung auf höherer Ebene den Benutzer ständig auf, erneut zu arbeiten, was die Leistung verringert. In diesem Fall ist eine pessimistische Sperre besser geeignet.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen ansehen: „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“, „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-gespeicherten Prozeduren“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Protokollvorgängen“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Transaktionsvorgängen“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.