Tiefgreifendes Verständnis der Probleme mit der Transaktionsisolationsebene und dem Sperrmechanismus von MySQL

Überblick

Datenbanken führen im Allgemeinen mehrere Transaktionen gleichzeitig aus. Mehrere Transaktionen können gleichzeitig Hinzufügungs-, Lösch-, Änderungs- und Abfragevorgänge für denselben Datenstapel ausführen, was zu fehlerhaften Lesevorgängen, fehlerhaften Schreibvorgängen, Nichtwiederholbarkeit und Phantomlesevorgängen führen kann. Der Kern dieser Probleme ist das Problem der gleichzeitigen Ausführung mehrerer Transaktionen in der Datenbank. Um das Problem der gleichzeitigen Ausführung von Transaktionen zu lösen, hat die Datenbank einen Transaktionsisolierungsmechanismus, einen Sperrmechanismus und einen MVCC-Isolationsmechanismus zur Steuerung der gleichzeitigen Ausführung mehrerer Versionen entwickelt, wobei ein vollständiger Satz von Mechanismen verwendet wird, um das Problem der gleichzeitigen Ausführung mehrerer Transaktionen zu lösen .

Transaktionen und ihre ACID-Eigenschaften

Atomarität: Unteilbarkeit von Operationen;

Konsistenz: Konsistenz der Daten;

Isolierung: Transaktionen stören sich nicht gegenseitig;

Persistenz: Datenänderungen sind dauerhaft;

Probleme bei der gleichzeitigen Transaktionsverarbeitung

Dirty Write: verlorene Aktualisierungen, die letzte Aktualisierung überschreibt Aktualisierungen, die von anderen Transaktionen vorgenommen wurden;

Dirty Read: Transaktion A liest Daten, die von Transaktion B geändert, aber nicht festgeschrieben wurden.

Nicht wiederholbares Lesen: Dieselbe Abfrage innerhalb einer Transaktion hat zu unterschiedlichen Zeiten unterschiedliche Ergebnisse, was auf Datenaktualisierungs- und Löschvorgänge abzielt;

Phantomlesen: Transaktion A liest die neu hinzugefügten Daten, die von der später gestarteten Transaktion B übermittelt wurden. Dies dient der Dateneinfügung.

Transaktionsisolationsebene

LESEN-UNCONMMITTED, LESEN-COMMITTED, WIEDERHOLBARES LESEN, SERIALISIERBAR

Zeigen Sie die Transaktionsisolationsstufe der aktuellen Datenbank an:

Variablen wie „tx_isolation“ anzeigen

Legen Sie die Transaktionsisolationsebene fest:

Setzen Sie tx_isolation='WIEDERHOLBARES LESEN';

Die standardmäßige Transaktionsisolationsstufe von MySQL ist wiederholbares Lesen. Wenn bei der Entwicklung eines Programms mit Spring die Isolationsstufe nicht festgelegt ist, wird standardmäßig die von MySQL festgelegte Isolationsstufe verwendet. Wenn Spring festgelegt ist, wird die festgelegte Isolationsstufe verwendet.

Sperrdetails

Eine Sperre ist ein Computermechanismus, der den gleichzeitigen Zugriff mehrerer Prozesse oder Threads auf eine Ressource koordiniert.

Sperrklassifizierung

Aus Leistungssicht kann es in optimistische Sperren (implementiert durch Versionsvergleich) und pessimistische Sperren unterteilt werden.

Je nach Art der Datenbankoperation: Lese-/Schreibsperre und Schreibsperre (pessimistische Sperre);

Lesesperre (gemeinsame Sperre, S-Sperre (gemeinsam genutzt)): Für dieselben Daten können mehrere Lesevorgänge gleichzeitig ausgeführt werden, ohne sich gegenseitig zu beeinträchtigen.

Schreibsperre (exklusive Sperre, X-Sperre (exklusiv)): blockiert andere Schreibsperren und Lesesperren, bevor der aktuelle Schreibvorgang abgeschlossen ist;

Aus der Granularität von Datenbankoperationen: Tabellensperren und Zeilensperren

Tabellensperre: Sperrt die gesamte Tabelle bei jedem Vorgang mit geringem Overhead und schneller Sperrung. Es kommt nicht zu Deadlocks. Die Sperrgranularität ist groß, die Wahrscheinlichkeit eines Sperrkonflikts ist am höchsten und die Parallelität am geringsten. Wird im Allgemeinen im Szenario der Migration ganzer Tabellendaten verwendet.

# Tabellensperre manuell hinzufügen, Tabellensperre, Tabellenname lesen (schreiben), Tabellenname 2 lesen (schreiben);
# Zeigen Sie die der Tabelle hinzugefügten Sperren an. Offene Tabellen anzeigen.
# Tabellensperre löschen, Tabellen entsperren;

Zeilensperre: Jeder Vorgang sperrt eine Datenzeile. Der Overhead ist hoch und die Sperrung ist langsam. Es kann zu Deadlocks kommen. Die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit eines Sperrkonflikts am geringsten und die Parallelität am höchsten.

Die größten Unterschiede zwischen InnoDB und MYISAM: 1. InnoDB unterstützt Transaktionen; 2. InnoDB unterstützt Zeilensperren.

Zusammenfassen:

MyISAM fügt vor der Ausführung einer Abfrageanweisung automatisch eine Lesesperre für die betreffende Tabelle hinzu und eine Schreibsperre beim Ausführen von Aktualisierungs-, Einfüge- und Löschvorgängen.

InnoDB sperrt keine Zeilen, bevor eine Abfrageanweisung ausgeführt wird (nicht serielle Isolationsebene). Zeilensperren werden beim Ausführen von Aktualisierungs-, Einfüge- und Löschvorgängen hinzugefügt.

Eine Lesesperre blockiert Schreibvorgänge, jedoch keine Lesevorgänge. Die Schreibsperre blockiert sowohl das Lesen als auch das Schreiben.

Fallstudie zu Zeilensperren und Transaktionsisolationsebenen

MySQL bereitet eine Tabelle vor

1. Dirty Read: Transaktion A liest Daten, die geändert, aber nicht von einer anderen Transaktion festgeschrieben wurden. Diese Situation ist einfach und wird nicht im Detail erklärt. Die entsprechende Transaktionsisolationsebene wird ohne Commit gelesen.

2. Nicht wiederholbares Lesen, entsprechende Transaktionsisolationsstufe: Lesen festgeschrieben

Transaktion A:

Isolationsstufe für Sitzungstransaktionen festlegen, Lesen festgeschrieben;
 
Transaktion starten;
 
wähle * aus t_user;

Transaktion B:

Isolationsstufe für Sitzungstransaktionen festlegen, Lesen festgeschrieben;
 
Transaktion starten;
 
-- in t_user-Werte einfügen (1,'张',8);
Aktualisiere t_user, setze Alter = 9, wobei ID = 1;
 
begehen;

Wenn Transaktion A zum ersten Mal eine Abfrageanweisung ausführt, ist das Ergebnis wie folgt:

Zu diesem Zeitpunkt ist Transaktion B abgeschlossen, Transaktion A jedoch noch nicht beendet. Wir führen weiterhin eine Abfrage aus und die Ergebnisse sind wie folgt:

Es tritt ein nicht wiederholbares Leseproblem auf. Die Datenergebnisse zweier Abfragen innerhalb einer Transaktion sind inkonsistent und es werden Daten gelesen, die von anderen Transaktionen übermittelt wurden.

3. Wiederholbares Lesen, stellen Sie die Transaktionsisolationsstufe auf wiederholbares Lesen ein;

Die Ergebnisse der ersten Ausführung der Transaktion A sind wie folgt:

Transaktion B wird ausgeführt, ändert, aktualisiert age=8 und führt ein Commit aus. Die Ergebnisse sind wie folgt:

Links ist Transaktion A. Das Abfrageergebnis ist das gleiche wie am Anfang, wodurch das Problem des nicht wiederholbaren Lesens gelöst wird. Direkte Abfrage, Alter = 8 zu diesem Zeitpunkt.

Der MVCC-Mechanismus (Multi-Version Concurrency Control) wird unter der Isolationsebene „Repeatable Read“ verwendet. Der Auswahlvorgang aktualisiert die Versionsnummer nicht und ist ein Snapshot-Lesevorgang (historische Version); die Einfüge-, Aktualisierungs- und Löschvorgänge aktualisieren die Versionsnummer und sind aktuelle Lesevorgänge (aktuelle Version).

4. Phantomlesen, in 3. Fügen Sie wie folgt neue Daten hinzu

Zu diesem Zeitpunkt führt Transaktion A eine erneute Abfrage durch und die Ergebnisse sind wie folgt:

Das Ergebnis ist immer noch dasselbe wie am Anfang. In diesem Szenario verhindert die Isolationsebene für wiederholbare Lesevorgänge effektiv die Probleme von nicht wiederholbaren Lesevorgängen und Phantomlesevorgängen.

Wenn Transaktion A nach der ersten Abfrage ein bedingungsloses Update ausführt, wirkt sich das Update auf alle Zeilen aus, einschließlich der durch Transaktion B neu hinzugefügten Daten. Führen Sie an diesem Punkt die Abfrage erneut aus. Die Ergebnisse sind wie folgt:

Phantomlesevorgänge treten auf . Die offizielle Erklärung für Phantomlesevorgänge von MySQL lautet: Solange in der zweiten Auswahl in einer Transaktion ein Phantomlesevorgang zur Berechnung einer zusätzlichen Zeile stattfindet.

5. Serialisierbare InnoDB-Abfragen werden ebenfalls gesperrt. Wenn es sich bei der Abfrage um einen Bereich handelt, werden alle Zeilen im Bereich, einschließlich des Lückenbereichs, in dem sich die einzelnen Datensatzzeilen befinden, gesperrt, auch wenn die Datenzeile noch nicht eingefügt wurde.

Lückensperre

Session_1 führt das Update t_user set name = 'Benutzername' aus, wobei id>8 und id<18; anschließend können andere Sitzungen in allen Zeilen und Lücken innerhalb dieses Bereichs keine Daten einfügen oder ändern.

Lückensperren werden nur unter der wiederholbaren Leseisolationsstufe wirksam.

Schlösser mit gleichschließendem Schlüssel

Next-Key-Locks sind eine Kombination aus Zeilensperren und Lückensperren. Im Bereich der Lückensperre (8,18) finden wir tatsächlich vorhandene Werte. Die diesem Intervall am nächsten liegende ID ist beispielsweise 3,20; dann liegt der Bereich (3,20] tatsächlich innerhalb des Zeilensperrbereichs.

Nicht-Indexzeilensperren werden zu Tabellensperren aufgewertet

Sperren werden hauptsächlich zu Indizes hinzugefügt. Wenn nicht indexierte Felder aktualisiert werden, können Zeilensperren zu Tabellensperren werden.

Die Zeilensperre von InnoDB ist eine Sperre für den Index und nicht für den Datensatz. Und der Index darf nicht ungültig sein, sonst wird er von der Zeilensperre zur Tabellensperre hochgestuft

Sie können die Sperre auch im Freigabemodus (gemeinsame Sperre) und zum Aktualisieren (exklusive Sperre) verwenden, um eine Zeile zu sperren.

abschließend:

Da die Innodb-Speicher-Engine eine Sperre auf Zeilenebene implementiert, ist der durch die Implementierung des Sperrmechanismus verursachte Leistungsverlust zwar möglicherweise höher als bei der Sperre auf Tabellenebene, hinsichtlich der allgemeinen gleichzeitigen Verarbeitungsfunktionen ist sie der Sperre auf Tabellenebene von MYISAM jedoch weit überlegen.

Allerdings hat die Zeilensperre von Innodb auch ihre Schwächen. Bei unsachgemäßer Verwendung kann sie die Gesamtleistung verschlechtern.

Zeilensperrenanalyse

Analysieren Sie Zeilensperrenkonflikte im System, indem Sie die Statusvariable InnoDB_row_lock überprüfen.

Status wie „innodb_row_lock%“ anzeigen; 

Die wichtigsten sind:

Innodb_row_lock_time_avg (durchschnittliche Wartezeit)

Innodb_row_lock_waits (Gesamtzahl der Wartezeiten)

Innodb_row_lock_time (Gesamtwartezeit)

Wenn die Anzahl der Wartezeiten hoch und die Dauer jeder Wartezeit nicht gering ist, muss analysiert werden, warum es im System so viele Wartezeiten gibt, und basierend auf den Analyseergebnissen ein Optimierungsplan entwickelt werden.

Deadlock

Legen Sie die Isolationsstufe für Sitzungstransaktionen fest und erlauben Sie ein wiederholbares Lesen.
 
Transaktion starten;
 
Wählen Sie * von t_user, wobei ID = 2 für Update ist;
Wählen Sie * von t_user, wobei ID = 1 für Update ist;

Transaktion A sperrt zuerst id=1, dann id=2; Transaktion B hat die umgekehrte Reihenfolge, was zu einem Deadlock führt. Die Ergebnisse sind wie folgt:

In den meisten Fällen kann MySQL Deadlocks automatisch erkennen und die Transaktion zurücksetzen, die den Deadlock verursacht hat. In manchen Fällen gibt es dafür jedoch keine Lösung.

Zeigen Sie die aktuellen Deadlock-Protokollinformationen an:

Engine-InnoDB-Status anzeigen\G;

Vorschläge zur Schlossoptimierung:

1. Versuchen Sie, den gesamten Datenabruf über Indizes durchzuführen, um zu vermeiden, dass nicht indizierte Zeilensperren in Tabellensperren umgewandelt werden.

2. Gestalten Sie den Index sinnvoll, um den Umfang der Sperre zu minimieren.

3. Reduzieren Sie den Umfang der Indexbedingungen so weit wie möglich, um Lückensperren zu vermeiden.

4. Versuchen Sie, die Transaktionsgröße zu kontrollieren, die Menge der gesperrten Ressourcen und die Dauer zu reduzieren und versuchen Sie, das SQL mit Transaktionssperren am Ende der Transaktion auszuführen.

5. Isolieren Sie Transaktionen auf der niedrigstmöglichen Ebene

Dies ist das Ende dieses Artikels über ein tiefgreifendes Verständnis der MySQL-Transaktionsisolationsebenen und Sperrmechanismen. Weitere relevante MySQL-Transaktionsisolationsebenen und Sperrmechanismen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!