Detaillierte Analyse von MySQL-Deadlock-Problemen

Vorwort

Wenn sich unser Geschäft in einem sehr frühen Stadium befindet und der Grad der Parallelität relativ gering ist, werden wir möglicherweise mehrere Jahre lang kein Deadlock-Problem haben. Im Gegenteil, wenn der Grad der Parallelität unseres Geschäfts sehr hoch ist, werden uns die von Zeit zu Zeit auftretenden Deadlock-Probleme definitiv sehr verwirren. Wenn jedoch ein Deadlock-Problem auftritt, besteht die erste Reaktion vieler unerfahrener Studenten darin, sich wie ein Vogel Strauß zu verhalten: „Das ist sehr fortgeschritten, ich verstehe es nicht, überlasse es einfach dem Schicksal, das passiert nicht ständig.“ Tatsächlich sollte die Lösung des Deadlock-Problems nicht mehr so ​​verwirrend sein, wenn Sie unsere drei vorherigen Artikel zur Analyse der Anweisungssperre in MySQL sowie diesen Artikel zur Analyse von Deadlock-Protokollen sorgfältig lesen.

Vorbereitung

Damit sich die Geschichte reibungslos entwickeln kann, müssen wir eine Tabelle erstellen:

CREATE TABLE Held (
 Ich würde INT,
 Name VARCHAR(100),
 Land varchar(100),
 Primärschlüssel (ID),
 SCHLÜSSEL idx_name (Name)
) Engine=InnoDB CHARSET=utf8;

Wir haben einen gruppierten Index für die ID-Spalte der Hero-Tabelle und einen sekundären Index für die Name-Spalte erstellt. Diese Heldentabelle wird hauptsächlich verwendet, um einige Helden aus der Zeit der Drei Königreiche zu speichern. Wir fügen einige Datensätze in die Tabelle ein:

INSERT INTO hero VALUES
 (1, 'Liu Bei', 'Shu'),
 (3, 'zZhuge Liang', 'Shu'),
 (8, 'cCao Cao', 'Wei'),
 (15, 'xXun Yu', 'Wei'),
 (20, 'sSun Quan', 'Wu');

Die Daten in der Tabelle sehen nun folgendermaßen aus:

mysql> AUSWÄHLEN * VON Held;
+----+------------+---------+
| ID | Name | Land |
+----+------------+---------+
| 1 | lLiu Bei| Shu|
| 3 | zZhuge Liang | Shu |
| 8 | cCao Cao | Wei |
| 15 | xXun Yu | Wei |
| 20 | sSun Quan | Wu |
+----+------------+---------+
5 Zeilen im Satz (0,00 Sek.)

Die Vorbereitungen sind abgeschlossen.

Erstellen eines Deadlock-Szenarios

Lassen Sie uns zunächst ein Deadlock-Szenario erstellen und zwei Transaktionen in Sitzung A bzw. Sitzung B ausführen. Die konkrete Situation ist wie folgt:

Lassen Sie es uns analysieren:

• Wie aus Schritt ③ hervorgeht, fügt die Transaktion in Sitzung A zunächst eine positive Datensatzsperre vom Typ X zum Datensatz mit dem ID-Wert 1 im gruppierten Index der Hero-Tabelle hinzu.
• Wie aus Schritt ④ ersichtlich, fügt die Transaktion in Sitzung B dem Datensatz mit dem ID-Wert 3 im gruppierten Index der Hero-Tabelle eine positive Datensatzsperre vom Typ X hinzu.
• Wie aus Schritt 5 ersichtlich ist, möchte die Transaktion in Sitzung A dann eine reguläre Datensatzsperre vom Typ X zum Datensatz mit der ID-Wert 3 im gruppierten Index der Hero-Tabelle hinzufügen, sie steht jedoch im Konflikt mit der Sperre, die von der Transaktion in Sitzung B in Schritt 4 hinzugefügt wurde, sodass Sitzung A in einen blockierten Zustand wechselt und darauf wartet, die Sperre zu erhalten.
• Wie aus Schritt 6 ersichtlich ist, möchte die Transaktion in Sitzung B dem Datensatz mit dem ID-Wert 1 im gruppierten Index der Hero-Tabelle eine positive Datensatzsperre vom Typ X hinzufügen, dies steht jedoch im Konflikt mit der Sperre, die von der Transaktion in Sitzung A in Schritt 3 hinzugefügt wurde. Zu diesem Zeitpunkt warten die Transaktionen in Sitzung A und Sitzung B zyklisch auf die von ihnen jeweils gehaltene Sperre, und es kommt zu einem Deadlock. Der Deadlock-Erkennungsmechanismus des MySQL-Servers erkennt dies, sodass eine Transaktion zum Rollback ausgewählt und eine Nachricht an den Client gesendet wird:

FEHLER 1213 (40001): Beim Versuch, eine Sperre zu erhalten, wurde ein Deadlock festgestellt. Versuchen Sie, die Transaktion neu zu starten.

Oben sehen Sie unsere Deadlock-Analyse aus der Perspektive, welche Sperren den Anweisungen hinzugefügt werden. In tatsächlichen Anwendungen wissen wir jedoch möglicherweise überhaupt nicht, welche Anweisungen den Deadlock verursacht haben. Um unser Geschäft zu optimieren, müssen wir anhand des von MySQL beim Auftreten des Deadlocks generierten Deadlock-Protokolls umgekehrt ermitteln, welche Anweisungen den Deadlock verursacht haben.

Deadlock-Protokoll anzeigen

Der Entwickler von InnoDB hat uns mit dem Befehl SHOW ENGINE INNODB STATUS einige Statusinformationen zur InnoDB-Speicher-Engine angezeigt, darunter den Sperrstatus des Systems beim letzten Auftreten eines Deadlocks. Wenn der Deadlock im obigen Beispiel auftritt, führen wir diesen Befehl aus:

mysql> SHOW ENGINE INNODB STATUS\G
...viele andere Informationen werden weggelassen------------------------
Zuletzt erkannter Deadlock
------------------------
2019-06-20 13:39:19 0x70000697e000
*** (1) TRANSAKTION:
TRANSAKTION 30477, AKTIV 10 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 3 Sperrstruktur(en), Heap-Größe 1160, 2 Zeilensperren
MySQL-Thread-ID 2, OS-Thread-Handle 123145412648960, Abfrage-ID 46, localhost 127.0.0.1, Root-Statistiken
Wählen Sie * vom Helden, wobei ID = 3 für das Update ist
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Speicherplatz-ID 171, Seitennummer 3, n Bits 72, Primärindex der Tabelle „dahaizi“. „hero“, TRX-ID 30477, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000003; aufsteigend ;;
 1: Länge 6; Hex 000000007517; Asc u ;;
 2: Länge 7; Hex 80000001d0011d; aufsteigend ;;
 3: Länge 10; Hex 7ae8afb8e8919be4baae; asc z ;;
 4: Länge 3; Hex e89c80; aufsteigend ;;

*** (2) TRANSAKTION:
TRANSAKTION 30478, AKTIV 8 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
3 Sperrstruktur(en), Heap-Größe 1160, 2 Zeilensperren
MySQL-Thread-ID 3, OS-Thread-Handle 123145412927488, Abfrage-ID 47, localhost 127.0.0.1, Root-Statistiken
Wählen Sie * vom Helden, wobei ID = 1 für das Update ist
*** (2) HÄLT DAS SCHLOSS:
Datensatzsperren, Speicherplatz-ID 171, Seitennummer 3, n Bits 72, Primärindex der Tabelle „dahaizi“. „hero“, TRX-ID 30478, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000003; aufsteigend ;;
 1: Länge 6; Hex 000000007517; Asc u ;;
 2: Länge 7; Hex 80000001d0011d; aufsteigend ;;
 3: Länge 10; Hex 7ae8afb8e8919be4baae; asc z ;;
 4: Länge 3; Hex e89c80; aufsteigend ;;

*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Speicherplatz-ID 171, Seitennummer 3, n Bits 72, Primärindex der Tabelle „dahaizi“. „hero“, TRX-ID 30478, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80000001; aufsteigend ;;
 1: Länge 6; Hex 000000007517; Asc u ;;
 2: Länge 7; Hex 80000001d00110; aufsteigend ;;
 3: Länge 7; Hex 6ce58898e5a487; asc l ;;
 4: Länge 3; Hex e89c80; aufsteigend ;;

*** WIR MACHEN DIE TRANSAKTION ZURÜCK (2)
------------
...viele andere Informationen werden weggelassen

Für uns sind nur die aktuellsten Deadlock-Informationen von Interesse, daher werden wir den Abschnitt „ZULETZT ERKENNTE DEADLOCKS“ separat analysieren. Schauen wir uns die Bedeutung der Deadlock-Protokollausgabe Zeile für Zeile an:

Schauen Sie sich zunächst den ersten Satz an:

2019-06-20 13:39:19 0x70000697e000

Dieser Satz bedeutet, dass der Deadlock zum folgenden Zeitpunkt aufgetreten ist: 20.06.2019, 13:39:19 Uhr. Die folgende hexadezimale Zeichenfolge 0x70000697e000 stellt die Thread-ID des Threads dar, die vom Betriebssystem der aktuellen Sitzung zugewiesen wurde.

Anschließend folgen Informationen zur ersten Transaktion, bei der der Deadlock auftrat:

*** (1) TRANSAKTION:

# Die Transaktions-ID ist 30477. Die Transaktion befindet sich seit 10 Sekunden im Status AKTIV. Der aktuell von der Transaktion ausgeführte Vorgang ist: „Indexlesen wird gestartet“
TRANSAKTION 30477, AKTIV 10 Sek. Start des Indexlesens

# Diese Transaktion verwendet 1 Tabelle und sperrt 1 Tabelle (das bedeutet nicht, dass der Tabelle eine Tabellensperre hinzugefügt wird. Solange die Tabelle nicht konsistent gelesen wird, muss sie gesperrt werden. Spezifische Sperrmethoden finden Sie in der Analyse der Sperranweisung oder im Kapitel im Booklet).
MySQL-Tabellen in Verwendung 1, gesperrt 1

# Diese Transaktion befindet sich im Status LOCK WAIT mit 3 Sperrstrukturen (2 Zeilensperrstrukturen, 1 Tabellensperrstruktur vom Typ X, die Sperrstruktur wird im Detail im Handbuch vorgestellt), die Heap-Größe ist die zum Speichern der Sperrstruktur angeforderte Speichergröße (wir können sie ignorieren), es gibt 2 Zeilensperrstrukturen LOCK WAIT 3 Sperrstruktur(en), Heap-Größe 1160, 2 Zeilensperren

# Die Thread-ID dieser Transaktion ist 2 (MySQLs eigene Thread-ID). Die Thread-ID auf Betriebssystemebene ist die lange Zahlenfolge. Die ID der aktuellen Abfrage ist 46 (MySQL-interne Verwendung, kann ignoriert werden), und es gibt auch Benutzernamen und Hostinformationen MySQL-Thread-ID 2, OS-Thread-Handle 123145412648960, Abfrage-ID 46 localhost 127.0.0.1 Root-Statistiken

# Die Anweisung, die diese Transaktion blockiert select * from hero where id = 3 for update

# Die Sperre, auf deren Erwerb diese Transaktion derzeit wartet:
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:

# Die abzurufende Tablespace-ID ist 151, die Seitennummer ist 3, d. h. die Sperre eines Datensatzes im PRIMAY-Index der Hero-Tabelle (n_bits ist eine Zeichenfolge des Speicherplatzes, der zum Speichern der Sperrinformationen dieser Seite zugewiesen ist, was in der Broschüre ausführlich beschrieben wird). Der Sperrtyp ist X-Typ rec, aber keine Lückensperre
Datensatzsperren, Speicherplatz-ID 171, Seitennummer 3, n Bits 72, Primärindex der Tabelle „dahaizi“. „hero“, TRX-ID 30477, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit

# Die Heap-Nummer dieses Datensatzes auf der Seite ist 2. Die spezifischen Datensatzinformationen lauten wie folgt:
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0

# Dies ist der Primärschlüsselwert 0: Länge 4; Hex 80000003; Asc ;;

# Dies ist die versteckte Spalte 1 von trx_id: Länge 6; Hex 000000007517; Asc u ;;

# Dies ist die versteckte Spalte 2 des Rollpointers: Länge 7; Hex 80000001d0011d; asc ;;

# Dies ist Namensspalte 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;;

# Dies ist Länderspalte 4: Länge 3; Hex e89c80; Asc ;;

Aus diesen Informationen können wir ersehen, dass die Transaktion in Sitzung A Sperrstrukturen für zwei Datensätze generiert hat, aber die reguläre Datensatzsperre vom Typ X (rec, aber nicht gap) für einen der Datensätze nicht erworben wurde. Der Speicherort des nicht gesperrten Datensatzes lautet: Tablespace-ID ist 151, Seitennummer ist 3 und Heap-Nr. ist 2. Natürlich hat der Typ, der InnoDB entwickelt hat, auch freundlicherweise die Details dieses Datensatzes angegeben. Sein Primärschlüsselwert ist 80000003, was tatsächlich das Format ist, das für die interne Speicherung in InnoDB verwendet wird. Es stellt tatsächlich die Zahl 3 dar, was bedeutet, dass die Transaktion darauf wartet, die reguläre Datensatzsperre vom Typ X des Datensatzes mit dem Primärschlüsselwert 3 im gruppierten Index der Hero-Tabelle zu erhalten.

Anschließend folgen Informationen zur zweiten Transaktion, bei der der Deadlock auftrat:

Die meisten Informationen haben wir bereits vorgestellt, deshalb erwähnen wir nur die wichtigsten:

*** (2) TRANSAKTION:
TRANSAKTION 30478, AKTIV 8 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
3 Sperrstruktur(en), Heap-Größe 1160, 2 Zeilensperren
MySQL-Thread-ID 3, OS-Thread-Handle 123145412927488, Abfrage-ID 47, localhost 127.0.0.1, Root-Statistiken
Wählen Sie * vom Helden, wobei ID = 1 für das Update ist

# Gibt die durch die Transaktion erhaltenen Sperrinformationen an. *** (2) HÄLT DIE SPERRE(N):
Datensatzsperren, Speicherplatz-ID 171, Seitennummer 3, n Bits 72, Primärindex der Tabelle „dahaizi“. „hero“, TRX-ID 30478, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0

# Der Primärschlüsselwert ist 3
0: Länge 4; Hex 80000003; aufsteigend ;;
1: Länge 6; Hex 000000007517; Asc u ;;
2: Länge 7; Hex 80000001d0011d; aufsteigend ;;
3: Länge 10; Hex 7ae8afb8e8919be4baae; asc z ;;
4: Länge 3; Hex e89c80; aufsteigend ;;

# Gibt die Sperrinformationen an, auf deren Erhalt die Transaktion wartet. *** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Speicherplatz-ID 171, Seitennummer 3, n Bits 72, Primärindex der Tabelle „dahaizi“. „hero“, TRX-ID 30478, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 2 PHYSIKALISCHER DATENSATZ: n_Felder 5; kompaktes Format; Infobits 0

# Der Primärschlüsselwert ist 1
0: Länge 4; Hex 80000001; aufsteigend ;;
1: Länge 6; Hex 000000007517; Asc u ;;
2: Länge 7; Hex 80000001d00110; aufsteigend ;;
3: Länge 7; Hex 6ce58898e5a487; asc l ;;
4: Länge 3; Hex e89c80; aufsteigend ;;

Aus der obigen Ausgabe können wir erkennen, dass die Transaktion in Sitzung B die ordnungsgemäße Datensatzsperre vom Typ X für den Datensatz mit dem Primärschlüsselwert 3 im gruppierten Index der Hero-Tabelle erworben hat und darauf wartet, die ordnungsgemäße Datensatzsperre vom Typ X für den Datensatz mit dem Primärschlüsselwert 1 im gruppierten Index der Hero-Tabelle zu erwerben (die implizite Bedeutung ist, dass die ordnungsgemäße Datensatzsperre vom Typ X für den Datensatz mit dem Primärschlüsselwert 1 im gruppierten Index der Hero-Tabelle durch die Transaktion in SITZUNG A erworben wurde).

Schauen Sie sich den letzten Teil an:

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

Schließlich beschließt die InnoDB-Speicher-Engine, die zweite Transaktion rückgängig zu machen, nämlich die Transaktion in Sitzung B.

Ideen zur Deadlock-Analyse

1. Überprüfen Sie beim Überprüfen des Deadlock-Protokolls zunächst, auf welche Anweisungen die blockierte Transaktion wartet, um die Sperre zu erhalten.

In diesem Beispiel lautet die Anweisung, die SESSION A blockiert:

Wählen Sie * vom Helden, wobei ID = 3 für das Update ist

Die Anweisung, in der SESSION B blockiert wird, lautet:

Wählen Sie * vom Helden, wobei ID = 1 für das Update ist

Denken Sie dann daran: Suchen Sie in Ihrem eigenen Geschäftscode nach weiteren Anweisungen in der Transaktion, in der sich diese beiden Anweisungen befinden.

2. Nachdem Sie alle Anweisungen in der Transaktion gefunden haben, in denen der Deadlock auftritt, analysieren Sie den Deadlock-Prozess, indem Sie die Informationen zu den von der Transaktion erhaltenen Sperren und den Sperren, auf die gewartet wird, vergleichen.

Aus dem Deadlock-Protokoll können wir ersehen, dass SESSION A die reguläre Datensatzsperre vom Typ X für den Datensatz mit dem ID-Wert 1 des gruppierten Index der Hero-Tabelle erhält (diese wird tatsächlich von der Sperre erhalten, auf die SESSION B wartet). Wenn wir uns die Anweisungen in SESSION A ansehen, stellen wir fest, dass die folgende Anweisung die Ursache ist (siehe die drei Artikel zur Analyse von Anweisungssperren):

Wählen Sie * vom Helden, wobei ID = 1 für Update ist;

Darüber hinaus erhält SESSION B eine reguläre Datensatzsperre vom Typ X für den Datensatz mit dem Clustered-Index-ID-Wert 3 in der Hero-Tabelle. Bei Betrachtung der Anweisungen in SESSION B stellt sich heraus, dass die folgende Anweisung die Ursache ist (siehe die drei Artikel zur Analyse von Anweisungssperren):

Wählen Sie * vom Helden, wobei ID = 3 für Update ist;

Dann können wir sehen, dass SESSION A auf die reguläre Datensatzsperre vom Typ X des Datensatzes mit dem Clustered-Index-ID-Wert 3 in der Hero-Tabelle wartet. Dies wird durch die folgende Anweisung verursacht:

Wählen Sie * vom Helden, wobei ID = 3 für Update ist;

Dann sehen wir, dass SESSION B auf die reguläre Datensatzsperre vom Typ X des Datensatzes mit dem Clustered-Index-ID-Wert 1 in der Hero-Tabelle wartet. Dies wird durch die folgende Anweisung verursacht:

Wählen Sie * vom Helden, wobei ID = 1 für Update ist;

Anschließend wird der gesamte Deadlock-Entstehungsprozess auf Basis des Deadlock-Protokolls wiederhergestellt.

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.