Analyse eines MySQL-Deadlock-Szenariobeispiels

Vorwort

Kürzlich stieß ich auf ein Deadlock-Problem in MySQL auf RR-Ebene. Ich fand es interessant, also untersuchte ich es und machte einen Bericht darüber.

Beteiligte Wissenspunkte: gemeinsame Sperre, exklusive Sperre, Absichtssperre, Lückensperre, Einfügungsabsichtssperre, Sperrwarteschlange

Szenario

Isolationsebene: Wiederholbares Lesen

Der Tabellenaufbau ist wie folgt

Tabelle erstellen t (
 id int nicht null Primärschlüssel AUTO_INCREMENT,
 eine Ganzzahl ungleich null, Standard 0,
 b varchar(10) ungleich null Standard '',
 c varchar(10) ungleich null Standard '',
 eindeutiger Schlüssel uniq_a_b(a,b),
 eindeutiger Schlüssel uniq_c(c)
);

Daten initialisieren

in t(a,b,c) Werte(1,'1','1') einfügen;

Es gibt zwei Sitzungen A/B und zwei Transaktionen werden in der folgenden Reihenfolge ausgeführt

sich herausstellen

• Nachdem B 4 ausgeführt hat, ist alles noch normal
• Wenn A 5 ausführt, wird es blockiert
• B führt dann 6 aus, B meldet einen Deadlock, B führt ein Rollback aus und A fügt Daten ein

Sie können die Deadlock-Informationen in show engine innodb status sehen. Ich werde sie hier nicht veröffentlichen. Zuerst werde ich die Konzepte mehrerer Sperren erklären und dann den Deadlock-Prozess verstehen.

Gemeinsame Sperre (S)/Mutex-Sperre (X)

• Gemeinsame Sperren ermöglichen Transaktionen das Lesen von Datensätzen
• Mutex-Sperren ermöglichen Transaktionen das Lesen und Schreiben von Datensätzen

Diese beiden Sperrtypen können mit Zeilensperren und Lückensperren gemischt werden. Mehrere Transaktionen können gleichzeitig S-Sperren halten, aber nur eine Transaktion kann X-Sperren halten.

Absichtssperre

Eine Tabellensperre (auch Sperrmodus) zeigt an, dass eine Transaktion im Begriff ist, den Datensätzen der entsprechenden Tabelle eine S- oder X-Sperre hinzuzufügen. SELECT ... LOCK IN SHARE MODE fügt der Tabelle eine IS-Sperre hinzu, bevor dem Datensatz eine S-Sperre hinzugefügt wird, und SELECT ... FOR UPDATE fügt der Tabelle eine IX-Sperre hinzu, bevor dem Datensatz eine X-Sperre hinzugefügt wird.

Dies ist eine MySQL-Sperroptimierungsstrategie. Ich bin mir über den Optimierungspunkt der Intention-Sperre nicht ganz im Klaren. Bitte geben Sie mir einen Rat.

Die Kompatibilität der beiden Schlösser ist wie folgt

Zeilensperre

Das geht ganz einfach: Sperren Sie einfach die entsprechende Zeile. Beispielsweise werden durch Aktualisieren, Auswählen zum Aktualisieren, Löschen usw. den betroffenen Zeilen Zeilensperren hinzugefügt, um die Ausführung anderer Transaktionen zu verhindern.

Lückensperre

Um Phantom-Lesevorgänge auf der RR-Isolationsebene zu verhindern, müssen zusätzlich zum Datensatz selbst auch Lückensperren zu den Lücken auf beiden Seiten des Datensatzes hinzugefügt werden.
Wenn beispielsweise ein gemeinsamer Index für Spalte a vorhanden ist und drei Datensätze 1, 5 und 10 vorhanden sind, select * from t where a=5 for update nicht nur eine Zeilensperre für Datensatz 5 hinzu, sondern fügt auch Lückensperren für die Lücken (1,5) und (5,10) hinzu, um zu verhindern, dass andere Transaktionen Daten mit einem Wert von 5 einfügen und Phantomlesevorgänge verursachen.
Wenn der normale Index auf a zu einem eindeutigen Index wird, ist keine Lückensperre erforderlich, da der Wert eindeutig ist und select * from t where a=5 for update nicht zwei Datensätze lesen kann.

Lückensperren sind miteinander kompatibel. Wenn sie sich gegenseitig ausschließen, hält Transaktion A die linke Hälfte (1,5) und Transaktion B die rechte Hälfte (1,10). Wenn dann der Datensatz a = 5 im vorherigen Beispiel gelöscht wird, sollten die linke und die rechte Lückensperre theoretisch zu einer neuen Sperre (1,10) zusammengeführt werden. Wem gehört dann diese neue Sperre mit großem Bereich? So sind die Spaltschlösser untereinander kompatibel, egal ob S-Spaltschloss oder X-Spaltschloss

Absichtssperre einfügen

Die Einfügeabsichtssperre ist eigentlich eine spezielle Lückensperre. Aus der vorherigen Beschreibung der Lückensperre können wir erkennen, dass zwei Transaktionen eine Lückensperre für einen Zeitraum vor dem eigentlichen Einfügen aufrechterhalten können, aber die eigentliche Einfügeaktion nicht sperren können. Vor dem eigentlichen Einfügen versucht MySQL auch, die Einfügeabsichtssperre des entsprechenden Datensatzes zu erhalten, um die Absicht anzuzeigen, einen Wert in die Lücke einzufügen.

Die Einfügeabsichtssperre und die Lückensperre schließen sich gegenseitig aus. Wenn beispielsweise Transaktion 1 die Lücke (1,5) sperrt, kann Transaktion 2 die Einfügeabsichtssperre für a=3 nicht erhalten und muss daher auf die Sperre warten.

Deadlock-Prozessanalyse

Als nächstes können wir den Deadlock-Prozess im vorherigen Beispiel analysieren. Schauen Sie sich zuerst den Show Engine InnoDB Status an.

 *** (1) TRANSAKTION:
TRANSAKTION 5967, AKTIV 8 Sek. Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 3 Sperrstruktur(en), Heap-Größe 1136, 2 Zeilensperre(n), Undo-Log-Einträge 1
MySQL-Thread-ID 9, OS-Thread-Handle 140528848688896, Abfrage-ID 537 192.168.128.1 Root-Update
in t(a,b) Werte(0,'0') einfügen
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Speicherplatz-ID 64, Seitennummer 4, n Bits 72, Index uniq_a_b der Tabelle „t2“. „t“ TRX-ID 5967, Sperrmodus X, sperrt Lücke vor Datensatz, Einfügeabsicht, Warten
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 3; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 1; Hex 31; aufsteigend 1;;
 2: Länge 4; Hex 80000001; aufsteigend ;;

*** (2) TRANSAKTION:
TRANSAKTION 5968, AKTIV 7 Sek. Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
3 Sperrstruktur(en), Heapgröße 1136, 2 Zeilensperre(n), Undo-Logeinträge 1
MySQL-Thread-ID 8, OS-Thread-Handle 140528848484096, Abfrage-ID 538 192.168.128.1 Root-Update
in t(a,b) Werte(0,'0') einfügen
*** (2) HÄLT DAS SCHLOSS:
Datensatzsperren Speicherplatz-ID 64 Seitennummer 4 n Bits 72 Index uniq_a_b der Tabelle `t2`.`t` TRX-ID 5968 Sperrmodus X sperrt Lücke vor Datensatz
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 3; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 1; Hex 31; aufsteigend 1;;
 2: Länge 4; Hex 80000001; aufsteigend ;;

*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren Speicherplatz-ID 64 Seitennummer 4 n Bits 72 Index uniq_a_b der Tabelle `t2`.`t` TRX-ID 5968 Sperrmodus X sperrt Lücke vor Datensatz Einfügeabsicht Warten
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 3; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 1; Hex 31; aufsteigend 1;;
 2: Länge 4; Hex 80000001; aufsteigend ;;

*** WIR MACHEN DIE TRANSAKTION ZURÜCK (2)

Sitzung A (also TRANSACTION 5967) wartet auf die Einfügeabsichtssperre vor dem Datensatz (a=1, b='1'), und Sitzung B (also TRANSACTION 5968) hält die Lückensperre vor dem Datensatz (a=1, b='1'), wartet aber auch auf die Einfügeabsichtssperre. Was zur Hölle ist das? Ist das nicht seltsam?

Analysieren Sie den Prozess von Grund auf

1. A und B starten jeweils die Transaktion
2. A führt zuerst select * from t where a=0 and b='0' for update; , aber der Datensatz existiert nicht, also wird es eine exklusive Lückensperre (-∞,1).
3. B führt dann select * from t where a=0 and b='0' for update; aus und fügt außerdem vorher eine IX-Sperre hinzu. Da der Datensatz nicht existiert, wird eine exklusive Lückensperre (-∞,1) hinzugefügt. Da Lückensperren jedoch miteinander kompatibel sind, gibt es keine Sperre.
4. A führt insert into t(a,b) values(0,'0'); aus. Um zu diesem Zeitpunkt tatsächlich mit dem Einfügen beginnen zu können, muss A die Einfügeabsichtssperre für (0,'0') erhalten. Da sie mit der exklusiven Lückensperre (-∞,1) von B in Konflikt steht, wartet die Sperre und tritt in die Sperrwarteschlange des Datensatzes (0,'0') ein (obwohl der Datensatz nicht existiert).
5. B führt das Einfügen insert into t(a,b) values(0,'0'); . Es stellt fest, dass B zwar die exklusive Lückensperre (-∞,1) besitzt, A diese aber auch besitzt. Daher begibt sich B in die Warteschlange und wartet darauf, dass A sie freigibt.
6. Ding, Deadlock tritt auf

Interpretation von Deadlock-Informationen

Transaktion 1 (TRANSACTION 5967), wartet auf den Erhalt des Sperrindex uniq_a_b der Tabelle t2.t trx id 5967 lock_mode X sperrt Lücke vor rec Insert Intention Warten, das heißt, die Einfügeabsichtssperre auf dem eindeutigen Index uniq_a_b (lock_mode X sperrt Lücke vor rec Insert Intention)
Die Sperrgrenze ist

 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 1; Hex 31; aufsteigend 1;;
 2: Länge 4; Hex 80000001; aufsteigend ;;

Zeigt zwei Zeilen von Datensätzen an

• 0 und 1 stellen die Werte auf uniq_a_b dar, a=1, b=0x31 (also den ASCII-Code von ‚1‘)
• a = 1, b = '1' entspricht der Primärschlüssel-ID = 1. Aufgrund der Indexstruktur von InnoDB zeigt der Sekundärindex (Nicht-Primärschlüsselindex) auf den Primärschlüsselindex und der Primärschlüsselindex auf die Daten, sodass der Primärschlüssel indiziert werden muss.

Was den int-Wert bitweises OR von 0x80000000 betrifft, ist mir nicht ganz klar, warum, ich brauche eine Erklärung von einem Experten

Transaktion 2 (TRANSACTION 5968) hält eine Lückensperre mit dem Index uniq_a_b der Tabelle t2.t trx id 5968 lock_mode X, sperrt die Lücke vor der Aufnahme und wartet auf eine Einfügeabsichtssperre mit dem Index uniq_a_b der Tabelle t2.t trx id 5968 lock_mode X, sperrt die Lücke vor der Aufnahme der Einfügeabsicht, sodass ein Deadlock auftritt.

Im Prinzip setzt die InnoDB-Engine die Transaktion zurück, die die niedrigsten Rollback-Kosten verursacht, aber die spezifischen Kriterien sind nicht sehr klar (auch hier brauchen wir einen großen Schuss). Hier entscheidet sich InnoDB für das Rollback der Transaktion 2. An diesem Punkt ist die Deadlock-Prozessanalyse abgeschlossen

Noch etwas

Es ist noch nicht vorbei. . . Es gibt ein magisches Phänomen: Wenn die Tabellenstruktur

Tabelle erstellen t (
 id int nicht null Primärschlüssel AUTO_INCREMENT,
 eine Ganzzahl ungleich null, Standard 0,
 b varchar(10) ungleich null Standard '',
 c varchar(10) ungleich null Standard '',
 eindeutiger Schlüssel uniq_c(c),
 eindeutiger Schlüssel uniq_a_b(a,b)
);
in t(a,b,c) Werte(1,1,1) einfügen;

Setzen Sie einfach den eindeutigen Index uniq_c auf c vor uniq_a_b, dann ändern sich die endgültigen Deadlock-Informationen!

 *** (1) TRANSAKTION:
TRANSAKTION 5801, AKTIV 5 Sek. Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 4 Sperrstruktur(en), Heap-Größe 1136, 3 Zeilensperre(n), Undo-Log-Einträge 1
MySQL-Thread-ID 5, OS-Thread-Handle 140528848688896, Abfrage-ID 380 192.168.128.1 Root-Update
in t2(a,b) Werte(0,'0') einfügen
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren Speicherplatz-ID 56 Seitennummer 5 n Bits 72 Index uniq_a_b der Tabelle `t2`.`t2` TRX-ID 5801 Sperrmodus X sperrt Lücke vor Datensatz Einfügeabsicht Warten
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 3; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 1; Hex 31; aufsteigend 1;;
 2: Länge 4; Hex 80000001; aufsteigend ;;

*** (2) TRANSAKTION:
TRANSAKTION 5802, AKTIV 4 Sek. Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
3 Sperrstruktur(en), Heapgröße 1136, 2 Zeilensperre(n), Undo-Logeinträge 1
MySQL-Thread-ID 6, OS-Thread-Handle 140528848484096, Abfrage-ID 381 192.168.128.1 Root-Update
in t2(a,b) Werte(0,'0') einfügen
*** (2) HÄLT DAS SCHLOSS:
Datensatzsperren Speicherplatz-ID 56 Seitennummer 5 n Bits 72 Index uniq_a_b der Tabelle `t2`.`t2` TRX-ID 5802 Sperrmodus X sperrt Lücke vor Datensatz
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 3; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 1; Hex 31; aufsteigend 1;;
 2: Länge 4; Hex 80000001; aufsteigend ;;

*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Bereichs-ID 56, Seitennummer 4, n Bits 72, Index uniq_c der Tabelle „t2“. „t2“ TRX-ID 5802, Sperrmodus S, wartend
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 0
 0: Länge 0; hex; aufsteigend ;;
 1: Länge 4; Hex 80000002; aufsteigend ;;

*** WIR MACHEN DIE TRANSAKTION ZURÜCK (2)

Die Sperre, auf die Transaktion 2 wartet, wurde von der vorherigen Einfügeabsichtssperre in eine gemeinsame Sperre geändert. Was zur Hölle?

Da ich den Quellcode nicht gelesen habe, kann ich nur aus dem Phänomen schlussfolgern: Da der eindeutige Index von c in der Tabellenstruktur vor (a, b) liegt und der Wert von c beim Einfügen nicht angegeben wird, wird der Standardwert 0 verwendet. InnoDB muss zuerst prüfen, ob ein Datensatz mit 0 vorhanden ist. Wenn dies der Fall ist, wird ein eindeutiger Schlüsselkonflikt gemeldet, sodass zuerst eine S-Sperre hinzugefügt werden muss, aber für den Datensatz bereits eine IX-Sperre vorhanden ist (0, '0'). Wenn man sich die obige Kompatibilitätsmatrix ansieht, schließen sich S-Sperren und IX-Sperren gegenseitig aus, sodass nur gesperrt und gewartet werden kann.

Zusammenfassen

Es sieht aus wie eine einfache Select- und Insert-Anweisung, aber dahinter verbirgt sich ein sehr komplexer Sperrmechanismus. Das Verständnis dieser Sperrmechanismen ist hilfreich, um effizientes (zumindest korrektes 😂) SQL zu schreiben.

Verbleibende Probleme:

1. Was ist der Optimierungspunkt der Intention Lock?
2. Was ist 0x80000000 im bitweisen ODER des Zeilendatensatzes in den Sperrinformationen?
3. Die Reihenfolge der Bestimmung des gegenseitigen Ausschlusses von Sperren. In Szenario 1 gibt es eine kompatible Lückensperre auf (0,'0') und eine Sperre in der Warteschlange. Welche soll zuerst bestimmt werden?
4. InnoDBs Algorithmus zur Berechnung der Transaktions-Rollback-Kosten

Verweise

• http://hedengcheng.com/?p=771
• https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
• https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-understanding-innodb-locking.html

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.