Detaillierte Analyse der MySQL-Sperrblockierung

Detaillierte Analyse der MySQL-Sperrblockierung

Bei der täglichen Wartung werden Threads häufig blockiert, wodurch die Datenbank sehr langsam reagiert. Sehen wir uns an, wie Sie herausfinden können, welcher Thread die Blockierung verursacht hat.

1. Umgebungsbeschreibung

RHEL 6.4 x86_64 + MySQL 5.6.19

Transaktionsisolationsebene: RR

2. Testprozess

3. Informationen zum Sperrblockierungsthread anzeigen

Hier sind mehrere Methoden zur Analyse:

3.1 Verwenden Sie show processlist zum Anzeigen

MySQL [(keine)]> Prozessliste anzeigen;
+----+------+--------------+------+---------+------+----------+--------------+------------------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+--------------+------+---------+------+----------+--------------+------------------------------------------+
| 2 | root | localhost | NULL | Abfrage | 0 | init | Prozessliste anzeigen |
| 3 | root | localhost | test | Abfrage | 70 | Daten werden gesendet | select count(*) from t3 a,t3 b |
| 4 | root | localhost | test | Abfrage | 65 | Aktualisieren | Löschen von emp, wobei empno=7788 |
| 7 | root | localhost | test | Abfrage | 68 | Aktualisierung | update emp set sal=3500 where empno=7788 |
+----+------+--------------+------+---------+------+----------+--------------+------------------------------------------+
4 Zeilen im Satz (0,00 Sek.)

Wenn die Datenbank viele Threads enthält, ist diese Methode tatsächlich schwer zu bestätigen.

3.2 Verwenden Sie den InnoDB-Status der Show Engine zur direkten Anzeige

------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 4131
Bereinigung für TRXs Nr. 0 < 4119 durchgeführt Rückgängig machen Nr. 0 < 0 Status: läuft, aber im Leerlauf
Länge der Verlaufsliste: 126
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSACTION 0, nicht gestartet
MySQL-Thread-ID 2, OS-Thread-Handle 0x7f953ffff700, Abfrage-ID 115, Localhost-Root-Init
Engine-InnoDB-Status anzeigen
---TRANSACTION 4130, AKTIV 41 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 2 Sperrstruktur(en), Heap-Größe 360, 1 Zeilensperre(n)
MySQL-Thread-ID 4, OS-Thread-Handle 0x7f953ff9d700, Abfrage-ID 112, Localhost-Root wird aktualisiert
Löschen von emp, wobei empno=7788
------- TRX HAT 41 SEKUNDEN AUF DIE GEWÄHRUNG DIESER SPERRE GEWARTET: ## 41 Sekunden gewartet
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4130, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0 ## Thread 4 wartet darauf, dem Primärschlüssel in test.emp, Seitennummer=3, eine X-Sperre hinzuzufügen.
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;
 
------------------
---TRANSACTION 4129, AKTIV 45 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 2 Sperrstruktur(en), Heap-Größe 360, 1 Zeilensperre(n)
MySQL-Thread-ID 7, OS-Thread-Handle 0x7f953ff6c700, Abfrage-ID 111, Localhost-Root wird aktualisiert
Aktualisiere emp setze sal=3500, wobei empno=7788
------- TRX HAT 45 SEKUNDEN AUF DIE GEWÄHRUNG DIESER SPERRE GEWARTET: ## 45 Sekunden gewartet
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4129, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0 ## Thread 7 wartet darauf, dem Primärschlüssel in test.emp, Seitennummer=3, eine X-Sperre hinzuzufügen.
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;
 
------------------
---TRANSAKTION 4128, AKTIV 51 Sek.
2 Sperrstrukturen, Heap-Größe 360, 1 Zeilensperre
MySQL-Thread-ID 3, OS-Thread-Handle 0x7f953ffce700, Abfrage-ID 110, Localhost-Root wird bereinigt

Wir wissen, dass die Hauptursache immer noch Thread=3 ist, aber dieses Ergebnis kann nicht vom InnoDB-Status aus analysiert werden.

Aus dem Obigen können wir erkennen, dass sowohl Thread 4 als auch Thread 7 darauf warten, dem Primärschlüssel in test.emp, Seite num=3, eine X-Sperre hinzuzufügen. Thread 7 wartet jedoch 45 Sekunden, während Thread 4 41 Sekunden wartet. Die Sperre wird später als bei Thread 7 angewendet, sodass festgestellt werden kann, dass Thread 7 Thread 4 blockiert hat. Die Grundursache, warum Thread 7 wartet, kann hier nicht analysiert werden.

3.3 Verwenden Sie mysqladmin debug zum Anzeigen

# mysqladmin -S /tmp/mysql3306.sock debug

Im Fehlerprotokoll wird dann Folgendes angezeigt:

Thread database.table_name Gesperrt/Wartend Lock_type
 
 
3 test.t3 Gesperrt - lesen Lesesperre mit niedriger Priorität
7 test.emp Gesperrt - schreiben Schreibsperre mit hoher Priorität

Mit dieser Methode können wir feststellen, dass Thread-ID=3 und 7 Blockierer sind, aber es ist immer noch nicht genau genug, um zu bestimmen, dass Thread 7 auch durch Thread-ID=3 blockiert wird.

3.4 Verwenden Sie innodb_lock_monitor, um den blockierenden Sperrthread abzurufen

MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## Erstellen Sie diese Tabelle in einer beliebigen Datenbank und der Sperrmonitor wird aktiviert
Abfrage OK, 0 Zeilen betroffen, 1 Warnung (0,07 Sek.)
 
MySQL [test]> Warnungen anzeigen\G
*************************** 1. Reihe ***************************
 Stufe: Warnung
 Code: 131
Nachricht: Die Verwendung des Tabellennamens innodb_lock_monitor zum Aktivieren der Diagnoseausgabe ist veraltet und wird in zukünftigen Versionen möglicherweise entfernt. Verwenden Sie INFORMATION_SCHEMA- oder PERFORMANCE_SCHEMA-Tabellen oder SET GLOBAL innodb_status_output=ON.
1 Zeile im Satz (0,00 Sek.)

Hinweis: Dies führt zu einer Warnung in 5.6, hat jedoch keine Auswirkungen auf die Nutzung.

Verwenden Sie dann „show engine innodb status“, um Folgendes anzuzeigen:

------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 4667
Bereinigung für TRXs Nr. 0 < 4659 durchgeführt Rückgängig machen Nr. 0 < 0 Status: läuft, aber im Leerlauf
Länge der Verlaufsliste 138
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSACTION 0, nicht gestartet
MySQL-Thread-ID 9, OS-Thread-Handle 0x7f813c5f7700, Abfrage-ID 152, Localhost-Root-Init
Engine-InnoDB-Status anzeigen
---TRANSACTION 4663, AKTIV 78 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 2 Sperrstruktur(en), Heap-Größe 360, 1 Zeilensperre(n)
MySQL-Thread-ID 4, OS-Thread-Handle 0x7f813c628700, Abfrage-ID 149, Localhost-Root-Aktualisierung
Löschen von emp, wobei empno=7788
------- TRX HAT 78 SEKUNDEN AUF DIE GEWÄHRUNG DIESER SPERRE GEWARTET: ## 78 Sekunden gewartet
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4663, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0 ## Thread 4 wartet darauf, dem Primärschlüssel in test.emp, Seitennummer=3, eine X-Sperre hinzuzufügen.
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;
 
------------------
TABELLENSPERRE Tabelle „test“. „emp“ TRX-ID 4663 Sperrmodus IX ## Bevor Sie der Primärschlüsselzeile eine X-Sperre hinzufügen, fügen Sie der Tabelle zuerst eine Absichtssperre IX hinzu.
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4663, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;
 
---TRANSACTION 4662, AKTIV 81 Sek. Start des Indexlesens
MySQL-Tabellen in Verwendung 1, gesperrt 1
LOCK WAIT 2 Sperrstruktur(en), Heap-Größe 360, 1 Zeilensperre(n)
MySQL-Thread-ID 7, OS-Thread-Handle 0x7f813c5c6700, Abfrage-ID 148, Localhost-Root-Aktualisierung
Aktualisiere emp setze sal=3500, wobei empno=7788
------- TRX HAT 81 SEKUNDEN AUF DIE GEWÄHRUNG DIESER SPERRE GEWARTET: ## 81 Sekunden gewartet
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4662, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0 ## Thread 7 wartet darauf, dem Primärschlüssel in test.emp, Seitennummer=3, eine X-Sperre hinzuzufügen.
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;
 
------------------
TABELLENSPERRE Tabelle „test“. „emp“ TRX-ID 4662 Sperrmodus IX ## Bevor Sie der Primärschlüsselzeile eine X-Sperre hinzufügen, fügen Sie der Tabelle zuerst eine Absichtssperre IX hinzu.
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4662, Sperrmodus X sperrt Datensatz, aber nicht Lückenwartezeit
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;
 
---TRANSAKTION 4615, AKTIV 1579 Sek., Thread innerhalb von InnoDB 1222 deklariert
MySQL-Tabellen im Einsatz 2, gesperrt 0
2 Sperrstrukturen, Heap-Größe 360, 1 Zeilensperre
MySQL-Thread-ID 3, OS-Thread-Handle 0x7f813c659700, Abfrage-ID 147, Localhost-Root, Daten werden gesendet
select count(*) from t3 a,t3 b ## Dies ist das SQL, das aktuell von Thread 3 ausgeführt wird
Trx-Lese-Ansicht sieht keine TRX mit ID >= 4662, sieht < 4659
TABELLENSPERRE Tabelle „test“. „emp“ TRX-ID 4615 Sperrmodus IX ## Thread 3 hält eine beabsichtigte IX-Sperre für die Tabelle und eine Zeilensperre X für den Primärschlüssel der Tabelle „test.emp“, Seitennummer=3
Datensatzsperren, Speicherplatz-ID 16, Seitennummer 3, n Bits 88, Index „PRIMARY“ der Tabelle „test“. „emp“ TRX-ID 4615, Sperrmodus X sperrt Datensatz, aber nicht Lücke
Datensatzsperre, Heap Nr. 9 PHYSIKALISCHER DATENSATZ: n_Felder 10; kompaktes Format; Infobits 0
 0: Länge 4; Hex 80001e6c; asc l;;
 1: Länge 6; Hex 000000001018; aufsteigend ;;
 2: Länge 7; Hex 91000001420084; aufsteigend B ;;
 3: Länge 5; Hex 53434f5454; aufsteigend SCOTT;;
 4: Länge 7; Hex 414e414c595354; aufsteigend ANALYST;;
 5: Länge 4; Hex 80001d8e; aufsteigend ;;
 6: Länge 4; Hex 208794f0; aufsteigend ;;
 7: Länge 4; Hex 80000bb8; aufsteigend ;;
 8: SQL NULL;
 9: Länge 4; Hex 80000014; aufsteigend ;;

Warum führt Thread 3 derzeit eine Select-t3-Tabellenoperation aus, sperrt aber die Seite num=3 in der Tabelle test.emp?

Es ist möglich, dass die Transaktion von Thread 3 in der Tabelle test.emp nicht rechtzeitig festgeschrieben wurde.

Daraus können wir schließen, dass Thread 3 Thread 7 blockiert und Thread 7 Thread 4, sodass die Grundursache Thread 3 ist. Senden Sie Thread 3 einfach so schnell wie möglich ab oder beenden Sie ihn.

4. Fazit

Bei der Analyse der Sperrblockierung in InnoDB vergleichen wir mehrere Methoden:

(1) Die Verwendung von „show processlist“ zum Anzeigen ist unzuverlässig.

(2) Die Grundursache des Problems kann nicht direkt mithilfe von „show engine innodb status“ ermittelt werden.

(3) Wenn Sie mit dem Debug-Tool von mysqladmin alle Threads anzeigen, die Sperren generieren, können Sie diese zwar sehen, aber nicht feststellen, welcher die Grundursache ist.

(4) Nachdem Sie innodb_lock_monitor aktiviert haben, verwenden Sie „show engine innodb status“, um die Grundursache für die Sperrblockierung zu finden.

Originallink: https://blog.csdn.net/hw_libo/article/details/39080809

Damit ist dieser Artikel zur eingehenden Analyse der MySQL-Sperrblockierung abgeschlossen. Weitere Informationen zur MySQL-Sperrblockierung 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!

Das könnte Sie auch interessieren:
  • Analyse von MySQL-Lock-Wait- und Deadlock-Problemen
  • Zusammenfassung der MySQL-Sperrwissenspunkte
  • Probleme mit Sperren in MySQL

<<:  Markodwns ausführliche Erklärung der Idee des synchronen Scrollens mit Titelausrichtung

>>:  Tutorial zur Installation von Elasticsearch 7.6.2 in Docker

Artikel empfehlen

Der Unterschied zwischen Datenzeit und Zeitstempel in MySQL

In MySQL gibt es drei Datumstypen: Datum (Jahr-Mo...

HTML übertrifft das Implementierungsprinzip und den Code des Textzeilenabfangs

Der HTML-Code zum Abfangen von mehrzeiligem Text l...

Analyse des Unterschieds zwischen relativem und absolutem HTML-Pfad

Gerade HTML-Anfänger stehen häufig vor dem Problem...

Lösung für falsche Zeichenfolgenwerte in MySQL

Viele Freunde berichten von folgendem Fehler, wen...

Vue basierend auf einer Element-Button-Berechtigungsimplementierungslösung

Hintergrundanforderungen: Das ERP-System muss ein...

So verwenden Sie SessionStorage und LocalStorage in Javascript

Inhaltsverzeichnis Vorwort Einführung in SessionS...

Lösung für Klickereignisfehler bei Verwendung von Better-Scroll bei der Vue-Mobilentwicklung

Vor Kurzem habe ich Vue verwendet, um das Entwick...

Codebeispiele für die Dekomprimierung, Änderung und andere Betriebsmethoden von Linux-Deb-Paketen

Aus verschiedenen Gründen müssen Sie manchmal den...

Optimieren Sie MySQL mit 3 einfachen Tricks

Ich erwarte nicht, ein erfahrener Datenbankadmini...

HTML erlaubt teilweise erzwungene Bildlaufleisten, um den Gesamtstil und das Layout nicht zu zerstören.

Poste zuerst das Effektbild: Damit der Gesamtstil ...

Beispiel für das Hinzufügen von Attributen mithilfe von Stilen in HTML

Fügen Sie den erforderlichen Links Inline-Stile hi...

Verwenden Sie vue3, um ein Mensch-Katze-Kommunikations-Applet zu implementieren

Inhaltsverzeichnis Vorwort Initialisieren des Pro...

Konvertieren von XHTML-CSS-Seiten in Druckerseiten

In der Vergangenheit bedeutete das Erstellen eine...

Anleitung zur Vermeidung von Docker Win Ping-Fehlern bei Containern

Über Win Docker-Desktop möchte ich mich mit der C...

Spezifische Verwendung der MySQL-Segmentierungsfunktion substring()

Es gibt vier wichtige MySQL-Zeichenfolgenabfangfu...