Ausführliche Erläuterung der MySQL-Isolationsebene und des Sperrmechanismus

Kurzbeschreibung:

Unser MySQL führt normalerweise mehrere Transaktionen gleichzeitig aus, und mehrere Transaktionen können gleichzeitig CRUD-Operationen für dieselben Daten oder denselben Datenstapel ausführen. Dies kann zu den Problemen führen, die wir normalerweise als „Dirty Reads“, „nicht wiederholbare Reads“ und „Phantom Reads“ bezeichnen.

Der Kern dieser Probleme liegt in der gleichzeitigen Ausführung mehrerer Transaktionen in MySQL. Um das Problem der gleichzeitigen Ausführung mehrerer Transaktionen zu lösen, hat MySQL einen Sperrmechanismus, einen MVCC-Isolationsmechanismus für die gleichzeitige Steuerung mehrerer Versionen und einen Transaktionsisolierungsmechanismus entwickelt, der einen vollständigen Satz von Mechanismen verwendet, um die durch die gleichzeitige Ausführung mehrerer Transaktionen verursachten Probleme zu lösen .

1. Vier Merkmale von Transaktionen

2. Probleme durch mehrere gleichzeitige Transaktionen

3. Transaktionsisolationsebene

Bei Dirty Reads, nicht wiederholbaren Reads und Phantom Reads handelt es sich eigentlich um MySQL-Lesekonsistenzprobleme, die dadurch gelöst werden müssen, dass die Datenbank einen bestimmten Transaktionsisolierungsmechanismus bereitstellt.

Zeigen Sie die Transaktionsisolationsstufe der aktuellen Datenbank an: Zeigen Sie Variablen wie „tx_isolation“ an.

Legen Sie die Transaktionsisolationsstufe fest: set tx_isolation='isolation level'

4. Probleme mit unterschiedlichen Isolationsstufen demonstrieren

MySQL-Version: 5.7.34

Beteiligte Tabellen:

Zwei MySQL-Clients

Kunde A <===================> Kunde B (Die beiden Kunden in jedem Bild unten sind nach dem ersten Bild benannt)

Nicht festgeschrieben lesen

1.1 Legen Sie die Transaktionsisolationsebene fest: set tx_isolation='read-uncommitted';

1.2 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

1.3 Client A führt nur Abfragen durch und Client B ändert den Datensatz mit der ID = 1;

1.4 Wenn beide Transaktionen nicht festgeschrieben sind, liest Transaktion A die von Transaktion B geänderten Daten

1.5 Wenn die Transaktion von Client B aus irgendeinem Grund zurückgesetzt wird, handelt es sich bei den von Client A abgefragten Daten tatsächlich um fehlerhafte Daten, die die Konsistenzanforderungen nicht erfüllen.

Lesen Sie Commitment

2.1 Stellen Sie die Isolationsebene auf „Commited-Lesen“ ein: set tx_isolation='read-committed';

2.2 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

2.3 Client A führt nur Abfragen durch und Client B ändert den Datensatz mit der ID = 1;

2.4 Wenn Client B die Transaktion nicht übermittelt hat, kann Client A die Daten, die Client B nicht übermittelt hat, nicht abfragen, wodurch das Problem des Dirty Reads gelöst wird

2.5 Nachdem Client B die Transaktion festgeschrieben hat, fragt Client A die Tabelle erneut ab. Das Ergebnis stimmt nicht mit dem vorherigen Schritt überein. Dies bedeutet, dass ein nicht wiederholbares Leseproblem auftritt, das die Isolationsanforderung nicht erfüllt.

Wiederholbares Lesen

3.1 Stellen Sie die Isolationsebene auf wiederholbares Lesen ein: set tx_isolation='repeatable-read';

3.2 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

3.3 Kunde B ändert die Daten in der Tabelle und übermittelt sie;

3.4 Client A fragt die Daten in der Tabelle ab und findet keine Inkonsistenzen zum vorherigen Schritt, wodurch das Problem nicht wiederholbarer Lesevorgänge gelöst wird

3.5 Führen Sie im Client A „Konto aktualisieren“ aus und setzen Sie den Kontostand auf „Kontostand – 100“, wobei „ID“ 1 ist. Der Kontostand wird nicht zu 800 – 100 = 700. Er wird stattdessen anhand der von Client B übermittelten Daten berechnet und beträgt daher 600. Die Datenkonsistenz wird nicht zerstört. Der MVCC-Mechanismus wird unter der Isolationsebene für wiederholbares Lesen verwendet. Der Auswahlvorgang aktualisiert die Versionsnummer nicht. Es handelt sich um einen Snapshot-Lesevorgang (historische Version), wodurch wiederholbares Lesen unter derselben Transaktion sichergestellt wird. Durch Einfügen/Aktualisieren/Löschen wird die Versionsnummer aktualisiert. Es handelt sich um einen aktuellen Lesevorgang (aktuelle Version), um die Datenkonsistenz sicherzustellen.

3.6 Client B öffnet eine Transaktion erneut, fügt Daten ein und führt eine Transaktion aus

3.7 Fragen Sie die Tabellendaten im Client A erneut ab. Die gerade von Client B hinzugefügten Daten werden nicht angezeigt und es erfolgt kein Phantomlesen.

3.8 Phantomlesen überprüfen: Ändern Sie in Client A die Daten mit der ID = 4. Die Aktualisierung ist erfolgreich. Führen Sie eine erneute Abfrage durch, um die von Client B hinzugefügten neuen Daten zu finden. Dies weist auf ein Phantomleseproblem hin und erfüllt die Isolationsanforderung nicht.

Serialisierung

4.1 Stellen Sie die Isolationsebene auf serialisierbar ein: set tx_isolation='serializable';

4.2 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

4.3 Client A fragt zunächst die Daten in der Tabelle mit der ID = 1 ab

4.4 Wenn die Transaktion von Client A nicht festgeschrieben ist, aktualisiert Client B die Daten in der Tabelle mit der ID = 1. Da die Transaktion von Client A nicht festgeschrieben ist, wird die Aktualisierungsaktion von Client B blockiert, bis Client A die Transaktion festschreibt oder eine Zeitüberschreitung auftritt. Wenn die Zeitüberschreitung auftritt, lautet der SQL-Fehler: Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.

4.5 Die Daten mit der ID = 2 können im Client B erfolgreich aktualisiert werden, was bedeutet, dass unter der serialisierten Isolationsebene auch die InnoDB-Abfrage gesperrt wird.

4.6 Wenn Client A eine Bereichsabfrage ausführt, werden alle Zeilen im Bereich gesperrt, einschließlich des Lückenintervallbereichs, in dem sich jeder Zeilendatensatz befindet (auch wenn die Zeile nicht eingefügt wurde, wird sie gesperrt, dies wird als Lückensperre bezeichnet) . Wenn Client B zu diesem Zeitpunkt eine Operation an den Daten im Bereich ausführt, wird diese blockiert; so werden Phantom-Lesevorgänge vermieden;

4.7 Serialisierung Diese Isolationsstufe weist eine extrem geringe Parallelität auf, sodass sie in der realen Entwicklung selten anzutreffen ist. Dies ist auch ein wichtiger Grund, warum MySQL wiederholbares Lesen als Standardisolationsstufe verwendet.

5. Verriegelungsmechanismus

Die Standardisolationsebene von MySQL ist wiederholbares Lesen, es können jedoch trotzdem Phantomlesevorgänge auftreten. In manchen Fällen können Lückensperren Phantomlesevorgänge beheben.

Lückensperre

Übersicht: Die Lückensperre sperrt die Lücke zwischen zwei Werten.

Nehmen Sie an, dass die Daten in der Tabelle wie folgt lauten:

Dann gibt es drei Lücken: (4,10), (10,15) und (15, positive Unendlichkeit);

1.1 Stellen Sie die Isolationsebene auf wiederholbares Lesen ein: set tx_isolation='repeatable-read';

1.2 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

1.3 Führen Sie auf Client A „Update Account Set Balance = 1000“ aus, wobei ID > 5 und ID < 13 ist.

1.4 Wenn Client A die Anforderung nicht übermittelt hat, kann Client B keine Einfüge-/Aktualisierungsvorgänge für alle Zeilen im Bereich (einschließlich Lückenzeilen) und die Lücken, in denen sich die Zeilen befinden, ausführen . Das heißt, Daten im Bereich 4<id<=15 können nicht geändert werden, und id = 15 kann ebenfalls nicht geändert werden.

1.5 Gap-Locks sind nur auf der wiederholbaren Leseisolationsebene wirksam

Vorübergehende Sperre

Übersicht: Temporäre Sperren sind eine Kombination aus Zeilen- und Lückensperren. Beispielsweise handelt es sich bei 4<id<=15 oben um temporäre Sperren.

Nicht-Indexzeilensperren werden zu Tabellensperren aufgewertet

3.1 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

3.2 Führen Sie auf Client A „Konto aktualisieren“ aus und setzen Sie den Kontostand auf 1000, wobei Name = „Li Si“ ist.

3.3 Wenn Client A nicht übermittelt hat, führt Client B ein Update des Kontostands mit dem Wert 800 aus, wobei die ID 15 beträgt. Auch dies wird blockiert, bis Client A übermittelt oder eine Zeitüberschreitung eintritt.

3.4 Die Sperren in MySQL werden hauptsächlich auf Indexfelder geladen. Bei Verwendung auf Nicht-Indexfeldern wird die Zeilensperre zu einer Tabellensperre aufgewertet.

Exklusives Schloss

4.1 Kunde A und Kunde B eröffnen jeweils eine Transaktion.

4.2 Führen Sie „select * from account where id = 1“ für die Aktualisierung auf Client A aus.

4.3 Wenn Client A nicht übermittelt hat, führt Client B ein Update des Kontostands mit dem Wert 800 aus, wobei die ID 1 ist. Dies wird blockiert, bis Client A übermittelt oder eine Zeitüberschreitung eintritt.

Fazit: Die Innodb-Engine implementiert Zeilensperren. Obwohl der Leistungsverlust durch die Implementierung des Zeilensperrmechanismus höher sein kann als bei Tabellensperren, ist die Gesamtleistung bei gleichzeitiger Verarbeitung definitiv höher als bei Tabellensperren. Bei hoher Systemparallelität haben Zeilensperren gegenüber Tabellensperren klare Vorteile. Allerdings sind Zeilensperren komplizierter zu verwenden als Tabellensperren. Bei unsachgemäßer Verwendung ist die Leistung von Zeilensperren möglicherweise nicht besser als die von Tabellensperren, sondern sogar schlechter.

Warum ist der Overhead von Zeilensperren größer als der von Tabellensperren, wenn die Granularität von Zeilensperren gering ist?

Da Sperren auf Tabellenebene nur die aktuelle Tabelle zum Sperren finden müssen, müssen Zeilensperren die Datensätze in der Tabelle durchsuchen, bis die zu sperrende Zeile gefunden wird. Daher sind die Kosten für Zeilensperren höher als für Sperren auf Tabellenebene.

Einige Vorschläge zur Sperroptimierung in realen Entwicklungssituationen:

• Indexfeldsperren sinnvoll einsetzen, um den Sperrumfang einzuschränken
• Fügen Sie möglichst alle Sperren den Indexfeldern hinzu, um eine Eskalation von Nicht-Indexzeilensperren zu Tabellensperren zu vermeiden.
• Minimieren Sie den Abfrageumfang so weit wie möglich, um Lückensperren bei großen Lücken zu vermeiden
• Minimieren Sie die Transaktionsisolation
• Versuchen Sie, die Transaktionsgröße so weit wie möglich zu kontrollieren, die Menge der gesperrten Ressourcen zu reduzieren und das an der Transaktionssperre beteiligte SQL am Ende der Transaktion zu platzieren, um die Sperrzeit zu verkürzen.

Zusammenfassen

Dies ist das Ende dieses Artikels über MySQL-Isolationsebenen und Sperrmechanismen. Weitere Informationen zu MySQL-Isolationsebenen und Sperrmechanismen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!