Eine vollständige Aufzeichnung eines Mysql-Deadlock-Fehlerbehebungsprozesses

Vorwort

Die Datenbank-Deadlocks, die ich zuvor erlebt habe, waren alle auf eine inkonsistente Sperrreihenfolge während der Stapelaktualisierungen zurückzuführen, aber letzte Woche bin ich auf einen Deadlock gestoßen, der sehr schwer zu verstehen war. Ich habe diese Gelegenheit genutzt, um mir mein Wissen über MySQL-Deadlocks und häufige Deadlock-Szenarien erneut anzueignen. Nach umfangreichen Recherchen und Diskussionen mit Kollegen habe ich schließlich die Ursache des Deadlock-Problems herausgefunden und viel daraus gelernt. Obwohl wir Backend-Programmierer sind, müssen wir den sperrenbezogenen Quellcode nicht so gründlich analysieren wie DBAs. Wenn wir jedoch die grundlegenden Methoden zur Fehlerbehebung bei Deadlocks beherrschen, wird dies für unsere tägliche Entwicklung von großem Nutzen sein.

PS: In diesem Artikel werden keine grundlegenden Kenntnisse über Deadlocks vermittelt. Informationen zum Sperrprinzip von MySQL finden Sie unter dem Link in den Referenzmaterialien dieses Artikels.

Ursachen für Deadlocks

Lassen Sie mich zunächst die Datenbank- und Tabellensituation vorstellen. Da es sich um echte Daten innerhalb des Unternehmens handelt, wird das Folgende simuliert und hat keinen Einfluss auf die spezifische Analyse.

Wir verwenden die MySQL-Datenbankversion 5.5, die Transaktionsisolationsebene ist standardmäßig RR (Repeatable-Read) und es wird die InnoDB-Engine verwendet. Angenommen, es gibt eine Testtabelle:

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned DEFAULT NULL,
 Primärschlüssel (`id`),
 EINZIGARTIGER SCHLÜSSEL `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

Die Struktur der Tabelle ist sehr einfach, mit einem Primärschlüssel „id“ und einem weiteren eindeutigen Index „a“. Die Daten in der Tabelle sind wie folgt:

mysql> wähle * aus Test;
+----+------+
| Ich würde | ein |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
3 Zeilen im Satz (0,00 Sek.)

Die Vorgänge, die einen Deadlock verursachen, sind die folgenden:

Anschließend können wir das Deadlock-Protokoll über SHOW ENGINE INNODB STATUS; ; anzeigen:

------------------------
Zuletzt erkannter Deadlock
------------------------
170219 13:31:31
*** (1) TRANSAKTION:
TRANSAKTION 2A8BD, AKTIV, 11 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 2 Sperrstrukturen, Heap-Größe 376, 1 Zeilensperre
MySQL-Thread-ID 448218, OS-Thread-Handle 0x2abe5fb5d700, Abfrage-ID 18923238 renjun.fangcloud.net 121.41.41.92 Root-Aktualisierung
aus dem Test löschen, bei dem a = 2
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
RECORD LOCKS Bereichs-ID 0 Seitennummer 923 n Bits 80 Index „a“ der Tabelle „oauthdemo“. „test“ TRX-ID 2A8BD Sperrmodus X wartend
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 00000002; aufsteigend ;;
 1: Länge 4; Hex 00000002; aufsteigend ;;
*** (2) TRANSAKTION:
TRANSAKTION 2A8BC, AKTIV 18 Sek. Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
4 Sperrstruktur(en), Heapgröße 1248, 3 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 448217, OS-Thread-Handle 0x2abe5fd65700, Abfrage-ID 18923239 renjun.fangcloud.net 121.41.41.92 Root-Update
in Test einfügen (id,a) Werte (10,2)
*** (2) HÄLT DAS SCHLOSS:
Datensatzsperren, Speicherplatz-ID 0, Seitennummer 923, n Bits 80, Index „a“ der Tabelle „oauthdemo“. „test“, TRX-ID 2A8BC, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 00000002; aufsteigend ;;
 1: Länge 4; Hex 00000002; aufsteigend ;;
*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Speicherplatz-ID 0, Seitennummer 923, n Bits 80, Index „a“ der Tabelle „oauthdemo“. „test“, TRX-ID 2A8BC, Sperrmodus S, wartend
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 00000002; aufsteigend ;;
 1: Länge 4; Hex 00000002; aufsteigend ;;
*** WIR MACHEN DIE TRANSAKTION ZURÜCK (1)

analysieren

Lesen des Deadlock-Protokolls

Wenn ein Deadlock auftritt, besteht der erste Schritt darin, das Deadlock-Protokoll zu lesen. Das Deadlock-Log ist in der Regel in zwei Teile gegliedert. Der erste Teil zeigt, auf welche Sperre Transaktion 1 wartet:

170219 13:31:31
*** (1) TRANSAKTION:
TRANSAKTION 2A8BD, AKTIV, 11 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 2 Sperrstrukturen, Heap-Größe 376, 1 Zeilensperre
MySQL-Thread-ID 448218, OS-Thread-Handle 0x2abe5fb5d700, Abfrage-ID 18923238 renjun.fangcloud.net 121.41.41.92 Root-Aktualisierung
aus dem Test löschen, bei dem a = 2
*** (1) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
RECORD LOCKS Bereichs-ID 0 Seitennummer 923 n Bits 80 Index „a“ der Tabelle „oauthdemo“. „test“ TRX-ID 2A8BD Sperrmodus X wartend
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 00000002; aufsteigend ;;
 1: Länge 4; Hex 00000002; aufsteigend ;;

Aus dem Protokoll können wir ersehen, dass Transaktion 1 derzeit delete from test where a = 2 ausführt. Diese Anweisung wendet eine X-Sperre auf den Index a an, daher wird lock_mode X waiting angezeigt.

In der unteren Hälfte des Protokolls werden dann die Sperren angezeigt, die Transaktion 2 derzeit hält und auf die sie wartet:

*** (2) TRANSAKTION:
TRANSAKTION 2A8BC, AKTIV 18 Sek. Einfügen
MySQL-Tabellen in Verwendung 1, gesperrt 1
4 Sperrstruktur(en), Heapgröße 1248, 3 Zeilensperre(n), Undo-Logeinträge 2
MySQL-Thread-ID 448217, OS-Thread-Handle 0x2abe5fd65700, Abfrage-ID 18923239 renjun.fangcloud.net 121.41.41.92 Root-Update
in Test einfügen (id,a) Werte (10,2)
*** (2) HÄLT DAS SCHLOSS:
Datensatzsperren, Speicherplatz-ID 0, Seitennummer 923, n Bits 80, Index „a“ der Tabelle „oauthdemo“. „test“, TRX-ID 2A8BC, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 00000002; aufsteigend ;;
 1: Länge 4; Hex 00000002; aufsteigend ;;
*** (2) WARTEN AUF DIE GEWÄHRUNG DIESER SPERRE:
Datensatzsperren, Speicherplatz-ID 0, Seitennummer 923, n Bits 80, Index „a“ der Tabelle „oauthdemo“. „test“, TRX-ID 2A8BC, Sperrmodus S, wartend
Datensatzsperre, Heap Nr. 3 PHYSIKALISCHER DATENSATZ: n_Felder 2; kompaktes Format; Infobits 32
 0: Länge 4; Hex 00000002; aufsteigend ;;
 1: Länge 4; Hex 00000002; aufsteigend ;;

Aus HOLDS THE LOCKS(S) im Protokoll können wir erkennen, dass Transaktion 2 eine X-Sperre auf Index a hält und es sich um eine Datensatzsperre handelt. Die Sperre wird durch die Löschanweisung erworben, die von Transaktion 2 in Schritt 2 ausgeführt wird. Da es sich um eine Gleichheitsabfrage handelt, die auf einem eindeutigen Index im RR-Isolationsmodus basiert (wobei a = 2), wird anstelle einer Next-Key-Sperre eine Datensatzsperre angefordert.

Aus WAITING FOR THIS LOCK TO BE GRANTED , bei der es sich um eine gemeinsam genutzte Sperre handelt. Die Sperre wird durch die Anweisung insert into test(id,a) values ​​(10,2) angewendet. Normalerweise gilt für die Einfügeanweisung eine exklusive Sperre, also eine X-Sperre, hier erscheint jedoch eine S-Sperre. Dies liegt daran, dass Feld a ein eindeutiger Index ist. Daher führt die Einfügeanweisung vor dem Einfügen eine Überprüfung duplicate key durch. Damit diese Überprüfung erfolgreich ist, muss eine S-Sperre beantragt werden, um zu verhindern, dass andere Transaktionen Feld a ändern.

Warum also versagt das S-Schloss? Dies bedeutet, dass Anträge auf Sperren desselben Feldes in die Warteschlange gestellt werden müssen. Vor der S-Sperre gibt es eine erfolglose X-Sperre, sodass die S-Sperre warten muss. Dadurch entsteht ein Wartezirkel und es kommt zu einem Deadlock.

Durch Lesen des Deadlock-Protokolls können wir deutlich erkennen, welche Art von zyklischem Warten durch die beiden Transaktionen verursacht wird. Nach weiterer Analyse können wir umgekehrt auf die Ursache des zyklischen Wartens schließen, d. h. auf die Ursache des Deadlocks.

Flussdiagramm zur Entstehung von Deadlocks

Um Ihnen zu helfen, die Ursachen von Deadlocks besser zu verstehen, erläutern wir den Prozess der Deadlock-Entstehung in Form einer Tabelle:

expandieren

Während der Fehlerbehebung beim Deadlock stellte ein Kollege fest, dass das obige Szenario einen weiteren Deadlock erzeugen würde, der nicht manuell reproduziert werden konnte und nur in einem Szenario mit hoher Parallelität reproduziert werden konnte.

Das diesem Deadlock entsprechende Protokoll wird hier nicht veröffentlicht. lock_mode X locks gap before rec insert intention waiting bevor auf die beabsichtigte Rec-Einfügung gewartet wird.

Wir verwenden dennoch eine Tabelle, um den Prozess der Deadlock-Generierung im Detail zu erklären:

Zusammenfassen

Bei der Fehlerbehebung bei Deadlocks müssen Sie zunächst das Szenario des zyklischen Wartens anhand des Deadlock-Protokolls analysieren, dann den Sperrtyp und die Sequenz anhand des von jeder aktuellen Transaktion ausgeführten SQL analysieren und umgekehrt ableiten, wie das zyklische Warten zustande kommt. Auf diese Weise können Sie die Ursache des Deadlocks finden.

Das ist alles für diesen Artikel. Ich hoffe, dass der Inhalt dieses Artikels Ihnen bei Ihrem Studium oder Ihrer Arbeit hilfreich sein kann. Die obige Analyse basiert auf Erfahrung. Ich hoffe, dass andere Freunde auf die Fehler und Mängel hinweisen können. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.