Analysieren Sie MySQL-Transaktionen und Probleme bei der Datenkonsistenzverarbeitung

In diesem Artikel wird die Konsistenzverarbeitung von MySQL-Transaktionen und -Daten im Hinblick auf Sicherheit, Nutzung, gleichzeitige Verarbeitung usw. detailliert analysiert. Im Folgenden finden Sie den vollständigen Inhalt:

Bei der Arbeit stoßen wir häufig auf solche Probleme. Wir müssen den Bestand aktualisieren. Wenn wir den verfügbaren Bestand abfragen und uns darauf vorbereiten, ihn zu ändern, haben andere Benutzer möglicherweise die Bestandsdaten geändert. Infolgedessen treten Probleme mit den von uns abgefragten Daten auf. Schauen wir uns die Lösung an.

In MySQL InnoDB ist die Standard-Transaction-Isolationsebene REPEATABLE READ (wieder lesbar).

Wenn Sie nach SELECT die gleiche Tabelle UPDATEN möchten, verwenden Sie am besten SELECT ... UPDATE.

Zum Beispiel:

Angenommen, im Produktformular „Produkte“ ist eine Menge vorhanden, um die Produktmenge zu speichern. Bevor eine Bestellung aufgegeben wird, muss zunächst ermittelt werden, ob die Produktmenge ausreichend ist (Menge > 0), und dann die Menge auf 1 aktualisiert werden. Der Code lautet wie folgt:

WÄHLE Menge AUS Produkten, WO ID=3; AKTUALISIERE Produkte, SETZE Menge = 1, WO ID=3;

Warum ist es unsicher?

In einigen wenigen Fällen mag es kein Problem geben, aber bei Zugriffen auf große Datenmengen wird es definitiv Probleme geben. Wenn wir den Bestand nur dann reduzieren müssen, wenn die Menge > 0 ist, nehmen wir an, dass das Programm in der ersten SELECT-Zeile die Menge 2 liest. Es scheint, dass die Zahl korrekt ist, aber wenn MySQL gerade UPDATE durchführt, hat möglicherweise jemand den Bestand bereits auf 0 reduziert, aber das Programm weiß davon nichts und setzt das UPDATE ohne Fehler fort. Daher muss ein Transaktionsmechanismus verwendet werden, um sicherzustellen, dass die gelesenen und übermittelten Daten korrekt sind.

Damit wir es in MySQL wie folgt testen können, lautet der Code wie folgt:

SETZE AUTOCOMMIT=0; BEGINNE MIT DER ARBEIT; WÄHLE die Menge aus den Produkten, wobei ID=3 für die Aktualisierung ist;

Zu diesem Zeitpunkt sind die Daten mit der ID = 3 in den Produktdaten gesperrt (Hinweis 3) und andere Transaktionen müssen warten, bis diese Transaktion festgeschrieben ist, bevor sie ausgeführt werden können.

Wählen Sie * aus Produkten, wobei id=3 für Update ist

Dadurch wird sichergestellt, dass die in anderen Transaktionen gelesene Mengennummer korrekt ist.

UPDATE-Produkte SETZEN Menge = '1' WHERE id=3; COMMIT WORK;

Commit schreibt in die Datenbank und entsperrt Produkte.

Hinweis 1: BEGIN/COMMIT sind die Start- und Endpunkte einer Transaktion. Sie können zwei oder mehr MySQL-Befehlsfenster verwenden, um den Sperrstatus interaktiv zu beobachten.

Hinweis 2: Während einer Transaktion wartet nur SELECT ... FOR UPDATE oder LOCK IN SHARE MODE für dieselben Daten, bis andere Transaktionen beendet sind, bevor sie ausgeführt werden. Normales SELECT ... ist hiervon nicht betroffen.

Hinweis 3: Da InnoDB standardmäßig auf Zeilensperre eingestellt ist, lesen Sie bitte diesen Artikel zum Sperren von Datenspalten.

Hinweis 4: Versuchen Sie, den Befehl LOCK TABLES nicht für InnoDB-Tabellen zu verwenden. Wenn Sie ihn verwenden müssen, lesen Sie bitte zuerst die offiziellen Anweisungen zur Verwendung von LOCK TABLES mit InnoDB, um häufige Deadlocks im System zu vermeiden.

Erweiterte Nutzung

Wenn wir zuerst die Daten abfragen und dann aktualisieren müssen, verwenden wir am besten die Anweisung wie folgt:

UPDATE Produkte SET Menge = '1', wobei ID=3 und Menge > 0;

Auf diese Weise können Sie es verarbeiten, ohne Dinge hinzuzufügen.

MySQL verarbeitet hohe Parallelität, um einen Überverkauf des Lagerbestands zu verhindern

Ich habe einen sehr guten Artikel gesehen und ihn hier zum Studium geteilt.

Heute hat uns Herr Wang eine weitere Lektion erteilt. Tatsächlich hat Herr Wang letztes Jahr erwähnt, wie man mit hoher Parallelität in MySQL umgeht und einen Überverkauf des Inventars verhindert. Aber obwohl es damals jeder verstanden hat, gibt es leider in der tatsächlichen Entwicklung immer noch kein Bewusstsein dafür. Heute möchte ich dieses Problem auf der Grundlage meines Verständnisses klären und hoffe, dass es in Zukunft weitere Kurse dieser Art geben wird.

Lassen Sie uns zunächst das Problem des überverkauften Lagerbestands beschreiben: Im Allgemeinen gibt es auf E-Commerce-Websites Aktivitäten wie Gruppenkäufe, Flash-Verkäufe und Sonderangebote. Ein gemeinsames Merkmal solcher Aktivitäten ist ein Anstieg der Besuche, wobei Tausende oder sogar Zehntausende von Menschen herbeieilen, um ein Produkt zu kaufen. Da es sich jedoch um ein Werbeprodukt handelt, ist der Bestand definitiv begrenzt. Wie der Bestand kontrolliert werden kann, um Überkäufe und unnötige Verluste zu vermeiden, bereitet vielen Programmierern von E-Commerce-Websites Kopfzerbrechen und ist zugleich das grundlegendste Problem.

Bei einer technischen Analyse werden viele Leute sicherlich an Transaktionen denken, aber Transaktionen sind eine notwendige Voraussetzung zur Kontrolle des Überverkaufs von Lagerbeständen, jedoch keine hinreichende und notwendige Voraussetzung.

Beispiel:

Gesamtbestand: 4 Artikel

Anforderer: a, 1 Produkt b, 2 Produkte c, 3 Produkte

Das Vorgehen ist wie folgt:

beginTranse (eine Transaktion starten)
versuchen{
 $result = $dbca->query('Wählen Sie den Betrag aus s_store, wo PostID = 12345');
 wenn(Ergebnis->Betrag > 0){
  //Menge ist die Lagermenge, deren Reduzierung angefordert wurde$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 }
}catch($e Ausnahme){
 rollBack
}
Commit (Transaktion festschreiben)

Der obige Code ist das, was wir normalerweise zur Bestandskontrolle schreiben. Die meisten Leute schreiben ihn so. Es scheint, als gäbe es kein großes Problem, aber tatsächlich verbirgt er große Lücken. Beim Zugriff auf eine Datenbank wird eigentlich auf Festplattendateien zugegriffen. Tabellen in einer Datenbank sind eigentlich auf der Festplatte gespeicherte Dateien, und eine Datei kann sogar mehrere Tabellen enthalten. Beispielsweise haben aufgrund der hohen Parallelität drei Benutzer A, B und C diese Transaktion eingegeben. Zu diesem Zeitpunkt wird eine gemeinsame Sperre generiert. Daher beträgt die von diesen drei Benutzern bei der Auswahl gefundene Bestandsmenge 4. Gleichzeitig ist zu beachten, dass die von MySQL InnoDB gefundenen Ergebnisse versioniert sind. Bevor andere Benutzer aktualisieren und festschreiben (d. h. bevor eine neue Version generiert wird), ist das vom aktuellen Benutzer gefundene Ergebnis immer noch die alte Version.

Dann kommt das Update. Wenn die drei Benutzer gleichzeitig beim Update ankommen, serialisiert die Update-Anweisung die Parallelität, d. h. die drei Benutzer, die gleichzeitig hier ankommen, werden sortiert, nacheinander ausgeführt und eine exklusive Sperre wird generiert. Bevor die aktuelle Update-Anweisung festgeschrieben wird, warten andere Benutzer auf die Ausführung. Nach dem Festschreiben wird eine neue Version generiert. Nach der Ausführung wird der Bestand definitiv negativ sein. Gemäß der obigen Beschreibung können wir den Code jedoch ändern, um das Überkaufphänomen zu vermeiden. Der Code lautet wie folgt:

beginTranse (eine Transaktion starten)
versuchen{
 //Menge ist die Lagermenge, deren Reduzierung angefordert wurde$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 $result = $dbca->query('Wählen Sie den Betrag aus s_store, wo PostID = 12345');
 wenn(Ergebnis->Betrag < 0){
  neue Ausnahme auslösen (,,Unzureichender Lagerbestand");
 }
}catch($e Ausnahme){
 rollBack
}
Commit (Transaktion festschreiben)

Alternativ eine prägnantere Methode:

beginTranse (eine Transaktion starten)
versuchen{
 //Menge ist die Lagermenge, deren Reduzierung angefordert wurde$dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');
}catch($e Ausnahme){
 rollBack
}
Commit (Transaktion festschreiben)

1. Bei Flash-Verkäufen können Sie die Datenbank nicht mit einer so hohen Frequenz lesen und schreiben, was zu ernsthaften Leistungsproblemen führt.

Sie müssen einen Cache verwenden, um die Produkte, die per Flash-Verkauf verkauft werden müssen, in den Cache zu legen, und eine Sperre verwenden, um die Parallelität zu handhaben. Wenn ein Benutzer eine Flash-Sale-Bestellung erhält, wird die Menge des Produkts vor der weiteren Verarbeitung verringert (gesperrt/entsperrt). Wenn die Verarbeitung fehlschlägt, werden die Daten um 1 erhöht (gesperrt/entsperrt). Andernfalls ist die Transaktion erfolgreich.
Wenn die Anzahl der Produkte auf 0 sinkt, bedeutet dies, dass das Produkt ausverkauft ist und Anfragen anderer Benutzer abgelehnt werden.

2. Dies kann die Datenbank sicherlich nicht direkt bedienen, es wird fehlschlagen. Direktes Lesen und Schreiben in die Datenbank belastet die Datenbank zu stark, daher ist ein Cache erforderlich.

Legen Sie die Produkte, die Sie verkaufen möchten, z. B. 10 Produkte, in den Cache. Legen Sie dann einen Zähler in Memcache fest, um die Anzahl der Anfragen aufzuzeichnen. Sie können die Anzahl der Produkte, die Sie verkaufen möchten, als Basiszahl für dieses Anfragebuch verwenden. Wenn Sie beispielsweise 10 Produkte verkaufen möchten, dürfen nur 100 Anfragen eingehen. Wenn der Zähler dann 100 erreicht, wird bei den nachfolgenden Bestellungen das Ende des Flash-Sales angezeigt, was die Belastung Ihres Servers verringern kann. Dann sind die Produkte auf Grundlage dieser 100 Anfragen nach dem Prinzip „Wer zuerst kommt, mahlt zuerst“ und „Wer zuerst bezahlt hat“ innerhalb von Sekunden ausverkauft.

3. Erstens: Wenn mehrere Benutzer gleichzeitig denselben Datensatz ändern, überschreibt der Benutzer, der die Änderung später vornimmt, mit Sicherheit das vom vorherigen Benutzer übermittelte Ergebnis.

Dies kann direkt durch die Verwendung eines Sperrmechanismus, einer optimistischen oder einer pessimistischen Sperre gelöst werden.

Optimistische Sperre: Entwerfen Sie ein Versionsnummernfeld in der Datenbank und erhöhen Sie es bei jeder Änderung um 1. Auf diese Weise können Sie beim Senden die Versionsnummer vor dem Senden vergleichen, um festzustellen, ob es sich um eine gleichzeitige Übermittlung handelt. Es besteht jedoch der Nachteil, dass dies nur in der Anwendung gesteuert werden kann. Wenn dieselben Daten in mehreren Anwendungen geändert werden, funktioniert die optimistische Sperre nicht. Zu diesem Zeitpunkt können Sie eine pessimistische Sperre in Betracht ziehen.

Pessimistische Sperre: Die Daten werden direkt auf Datenbankebene gesperrt, ähnlich wie bei der Verwendung select xxxxx from xxxx where xx=xx for update in Oralce, sodass andere Threads keine Daten übermitteln können.

Zusätzlich zur Sperrmethode können Sie auch die Empfangssperrmethode verwenden. Die Idee besteht darin, ein Statusflag in der Datenbank zu entwerfen. Bevor der Benutzer die Daten ändert, wird das Statusflag als bearbeitet markiert. Auf diese Weise erkennt das System, dass andere Benutzer diesen Datensatz bearbeiten, wenn sie ihn bearbeiten möchten, und lehnt ihre Bearbeitungsanforderungen ab. Dies ist vergleichbar damit, wenn eine Datei im Betriebssystem ausgeführt wird und Sie die Datei ändern möchten. Das System erinnert Sie daran, dass die Datei nicht bearbeitet oder gelöscht werden kann.

4. Es wird nicht empfohlen, auf Datenbankebene zu sperren. Es wird empfohlen, die serverseitige Speichersperre zu verwenden (den Primärschlüssel zu sperren). Wenn ein Benutzer die Daten einer bestimmten ID ändern möchte, wird die zu ändernde ID im Memcache gespeichert. Wenn andere Benutzer die Änderung der Daten dieser ID auslösen und den Wert dieser ID im Memcache lesen, wird der Benutzer daran gehindert, sie zu ändern.

5. In tatsächlichen Anwendungen muss MySQL nicht direkt mit großen gleichzeitigen Lese- und Schreibvorgängen umgehen. Stattdessen verwendet es „externe Kräfte“ wie Caching, die Verwendung von Master-Slave-Bibliotheken zum Trennen von Lese- und Schreibvorgängen, Partitionieren von Tabellen und Verwenden von Warteschlangenschreiben, um gleichzeitige Lese- und Schreibvorgänge zu reduzieren.

Pessimistisches Sperren und optimistisches Sperren

Erstens: Wenn mehrere Benutzer gleichzeitig denselben Datensatz ändern, wird das Ergebnis des späteren Benutzers definitiv das Ergebnis des früheren Benutzers überschreiben. Dies kann direkt durch die Verwendung eines Sperrmechanismus, einer optimistischen oder einer pessimistischen Sperre gelöst werden.

Pessimistische Sperre ist, wie der Name schon sagt, sehr pessimistisch. Jedes Mal, wenn Sie Daten erhalten, denken Sie, dass andere sie ändern werden, also sperren Sie sie jedes Mal, wenn Sie Daten erhalten. Auf diese Weise werden andere, die die Daten erhalten möchten, blockiert, bis sie die Sperre erhalten. Viele solcher Sperrmechanismen werden in herkömmlichen relationalen Datenbanken verwendet, wie etwa Zeilensperren, Tabellensperren, Lesesperren, Schreibsperren usw., die alle vor der Durchführung von Vorgängen gesperrt werden.

Optimistic Lock ist, wie der Name schon sagt, sehr optimistisch. Jedes Mal, wenn Sie Daten erhalten, gehen Sie davon aus, dass andere sie nicht ändern werden, und sperren sie daher nicht. Beim Aktualisieren stellen Sie jedoch fest, ob andere die Daten während dieses Zeitraums aktualisiert haben. Sie können Mechanismen wie Versionsnummern verwenden. Optimistisches Sperren eignet sich für Anwendungstypen mit mehreren Lesevorgängen, da es den Durchsatz verbessern kann. Wenn die Datenbank beispielsweise einen Mechanismus ähnlich write_condition bereitstellt, bietet sie tatsächlich optimistisches Sperren.

Beide Schlösser haben ihre eigenen Vor- und Nachteile und es lässt sich nicht einfach sagen, welches besser ist als das andere. Optimistisches Sperren eignet sich besser für Szenarien, in denen Daten selten geändert, aber häufig gelesen werden. Selbst wenn eine kleine Anzahl von Konflikten auftritt, wird eine große Menge an Sperr-Overhead eingespart, wodurch der Systemdurchsatz verbessert wird. Wenn jedoch häufig Konflikte auftreten (beim Schreiben großer Datenmengen), versucht die Anwendung der oberen Ebene immer wieder, den Vorgang zu wiederholen, was die Leistung verringert. In diesem Fall ist es besser, pessimistische Sperren zu verwenden.

Tatsächlicher Kampf

Ändern Sie den Betrag dieser Tabelle und öffnen Sie zwei Befehlszeilenfenster

Das erste Fenster ist A;

SETZEN SIE AUTOCOMMIT=0; BEGINNEN SIE MIT DER ARBEIT; WÄHLEN SIE * AUS order_tbl, WO order_id='124' FÜR UPDATE;

Zweites Fenster B:

# Aktualisieren Sie die Lagermenge der Bestell-ID 124. UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;

Wir können sehen, dass Fenster A eine Transaktion hinzufügt und die Daten sperrt. Wenn Fenster B ausgeführt wird, tritt das folgende Problem auf:

Das erste Fenster schließt die Übermittlungstransaktion ab:

SETZEN SIE AUTOCOMMIT=0; BEGINNEN SIE MIT DER ARBEIT; WÄHLEN SIE * AUS order_tbl, WO order_id='124' FÜR UPDATE;
UPDATE `order_tbl` SETze Betrag = 10 WHERE order_id = 124;
ARBEIT VERPFLICHTEN;

MySQL bewältigt hohe Parallelität und verhindert Lagerbestandsüberverkäufe. Das Obige ist der vollständige Inhalt dieses Artikels. Wenn Sie noch Fragen haben, können Sie unten eine Nachricht zur Diskussion hinterlassen.