Detaillierte Erklärung zur Lösung des USE DB-Locks in MySQL

Wenn wir auf einen Fehler stoßen, denken wir oft daran, wie wir den Fehler lösen können, anstatt uns die Ursachen des Fehlers zu überlegen. Das Ergebnis ist, dass wir nur den Fang des Fisches erhalten, aber das Angeln selbst verlieren. Heute teilen wir einen Fall, der durch einen USE DB-Sperrefehler verursacht wurde.

Fehlerbeschreibung

Ein Freund hatte heute mit einem schweren Datenbankfehler zu tun. Der Umstand des Fehlers ist wie folgt:

MYSQL 5.6.16

RR-Isolierungsebene

GITD ausgeschaltet

Die Manifestation ist wie folgt:

use db kann nicht in die Datenbank eintreten

show table status kann keine Tabelleninformationen abrufen

In schema.processlist gibt es viele Waiting for table metadata lock

In Panik tötete er eine Vielzahl von Threads ab und bemerkte, dass es immer noch nicht wiederhergestellt werden konnte. Schließlich tötete er eine Transaktion, die nicht rechtzeitig abgeschlossen wurde, und das System konnte wieder normal funktionieren. Es blieb nur noch ein Bild wie folgt zurück:

Fehlerinformationen extrahieren

Nochmals zurück zum obigen Bild, wir können die Typen der Anweisungen wie folgt zusammenfassen:

1,CREATE TABLE A AS SELECT B

Sein STATE ist sending data

2,DROP TABLE A

Sein STATE ist Waiting for table metadata lock

3,SELECT * FROM A

Sein STATE ist Waiting for table metadata lock

4, SHOW TABLE STATUS[like 'A']

Sein STATE ist Waiting for table metadata lock

Informationen analysieren

Es ist nicht allzu einfach, diesen Fall zu analysieren, da es sich um eine kombinierte Fallstudie von MYSQL Layer MDL LOCK und RR-Modus Innodb Row Lock handelt, und wir müssen empfindlich gegenüber dem STATE von schema.processlist sein.

Es wird empfohlen, zunächst meine folgenden Artikel zu lesen, um MDL LOCK zu lernen:

https://de.oldtoolbag.com/article/131383.htm

Dieses Kapitel behandelt die Validierung von MDL LOCK, die auf zwei Arten erfolgt:

Methode einsder Autor hat im MDL LOCK Sourcecode-Verriegelungsfunktion den Protokollierungsausgang hinzugefügt, um verschiedene Arten von MDL LOCK-Abfragen zu analysieren, ist dies der einzige Weg, da MDL LOCK-Verriegelung oft in einem Blitz passiert und performance_schema.metadata_locks kann sie nicht beobachten.

Methode zweiim Blockierzustand verwenden5.7Version performance_schema.metadata_locks beobachten.

Die mdl-Überwachungsmethode in P_S öffnen wie folgt:

Erstens: Analyse von CREATE TABLE A AS SELECT B für B-Tabelle sending data

Über den Zustand sending data kann viel bedeuten. Aus meiner aktuellen Kenntnis ist dies eine allgemeine Bezeichnung für SELECT-Typ-Statements von MYSQL Upper Layer, wenn Daten zwischen INNODB Layer und MYSQL Layer interagieren, daher können die möglichen Ursachen umfassen:

es tatsächlich einen großen Datenverkehr erfordert, möglicherweise Optimierung erforderlich.

denn der Erwerb von row lock im INNODB-Bereich erfordert Warten, z.B. SELECT FOR UPDATE, die wir häufig verwenden.

Gleichzeitig müssen wir darauf achten, dass der Sperrezugriff von SELECT B im RR-Modus und INSERT...SELECT identisch ist, ohne weiter zu erläutern:

seine Reaktion auf den Zustand, da er am Ende eine lange nicht abgeschlossene Transaktion abgebrochen hat, also in diesem Fall2und der gesamte CREATE TABLE A AS SELECT B-Ausdruck kann aufgrund von gesperrten Datenbanken in der Tabelle B nicht abgerufen werden, sodass der Ausdruck im Zustand sending data bleibt.

Zwei: Analyse von SHOW TABLE STATUS[like 'A'] Waiting for table metadata lock

Dies ist das wichtigste Glied in diesem Fall, SHOW TABLE STATUS[like 'A'] wurde plötzlich blockiert, und der Zustand ist Waiting for table metadata lock. Beachten Sie, dass dies hier table ist, da MDL LOCK-Typen in viele Kategorien unterteilt sind. Ich habe in dem Artikel über MDL erwähnt, dass das beschreiben eines Tables MDL_SHARED_HIGH_PRIO(SH) aktiviert,其实在SHOW TABLE STATUS时也会对本表上MDL_SHARED_HIGH_PRIO(SH)。

Methode eins

Methode zwei

Beide Methoden können die Existenz von MDL_SHARED_HIGH_PRIO(SH) beobachten, und ich simulierte den Zustand des Blockierens.

aber MDL_SHARED_HIGH_PRIO(SH) ist ein sehr hoher Prioritätsgrad eines MDL LOCK-Typs und zeigt sich wie folgt:

Kompatibilität:

Priorität der Blockierungsqueue:

Die Bedingungen, unter denen es blockiert wird, sind außer dem Blockieren durch MDL_EXCLUSIVE(X) keine anderen möglichen. Daher ist dies ein sehr wichtiger Durchbruch.

III, Analyse der MDL LOCK bei CREATE TABLE A AS SELECT B für Tabelle A

Dies ist etwas, das ich vorher nicht wusste und das am längsten in diesem Fall dauerte. Wie im vorangegangenen Abschnitt analysiert wurde, gibt es nur eine Möglichkeit, dass SHOW TABLE STATUS[like 'A'] nur MDL_SHARED_HIGH_PRIO(SH) MDL LOCK aufnimmt, und dies ist, dass Tabelle A MDL_EXCLUSIVE(X) aufnimmt.

Daher beginne ich zu vermuten, dass dieser DDL-Ausdruck vor dem Abschluss des Satzes MDL_EXCLUSIVE(X) auf Tabelle A aufnimmt, und die tatsächliche Testung ergab, dass dies tatsächlich der Fall ist, wie folgt:

Methode eins

Methode zwei

Es ist schade, dass in performance_schema.metadata_locks keine MDL_EXCLUSIVE(X) angezeigt wird, sondern MDL_SHARED(S) wird in meinen Protokollen angezeigt, dass hier ein Upgrade durchgeführt wurde, bei dem MDL_SHARED(S) auf MDL_EXCLUSIVE(X) hochgestuft wurde. Und basierend auf der Kompatibilitätsliste scheint nur MDL_EXCLUSIVE(X) MDL_SHARED_HIGH_PRIO(SH) blockieren zu können. Daher sollten wir bestätigen können, dass hier tatsächlich ein Upgrade durchgeführt wurde,否则SHOW TABLE STATUS[like 'A'] wird nicht blockiert.

IV, Analyse von SELECT * FROM A, Analyse der Wartezeit für die Tabellenmetadaten-Lock

Vielleicht denken einige, dass SELECT keine Locks aufnimmt, aber das ist im InnoDB-Bereich, im MYSQL-Bereich wird MDL_SHARED_READ(SR) aufgenommen, wie folgt:

Methode eins

Methode zwei

Man kann sehen, dass tatsächlich MDL_SHARED_READ(SR) existiert und derzeit blockiert ist

Die Kompatibilität ist wie folgt:

Natürlich sind MDL_SHARED_READ(SR) und MDL_SHARED_HIGH_PRIO(SH) inkompatibel und müssen gewartet werden.

V, Analyse der Wartezeit für die Tabellenmetadaten-Lock bei DROP TABLE A

Dies ist gut zu analysieren, weil Tabelle A den X-Lock aufnimmt und DROP TABLE A zwangsläufig den MDL_EXCLUSIVE(X)-Lock aufnimmt, was natürlich mit MDL_EXCLUSIVE(X) inkompatibel ist. Hier ist das Beispiel:

Methode eins

Methode zwei

Dabei ist EXCLUSIVE das, was wir MDL_EXCLUSIVE(X) nennen, es existiert tatsächlich und ist derzeit blockiert

Sechster Abschnitt: Warum wird USE DB auch blockiert?

Wenn der mysql-Client verwendet wird, nicht-Option A (oder no-auto-rehash) bei USE DB mindestens folgendes tun:

1, Für jede Tabelle in db wird MDL (SH) lock gesetzt wie folgt (MDL_context::acquire_lock aufgerufen, hier werden Informationen zur Blockierung angegeben)

Methode eins

Methode zwei

Es kann gesehen werden, dass USE DB tatsächlich auch durch MDL_SHARED_HIGH_PRIO(SH) blockiert wurde.

2, Jede Tabelle wird in den Table Cache aufgenommen und geöffnet (open_table_from_share() aufgerufen)

Diese Situation ist genau so wie die Situation von SHOW TABLE STATUS[like 'A'] blockiert, auch durch Inkompatibilität von MDL-Sperren verursacht.

Analyse und Klarstellung

Mit der vorangegangenen Analyse können wir die Ursachen der Störung wie folgt zusammenfassen:

Es gibt einen langfristig nicht abgeschlossenen DML in der B-Tabelle
Die Anweisung sperrt bestimmte Daten der B-Tabelle auf der Ebene von innodb row lock.

von Schritt1hat CREATE TABLE A AS SELECT B blockiert
Weil SELECT B im RR-Modus notwendigerweise auf die gesperrten Daten der B-Tabelle sperren muss, weil Schritt1wurde gesperrt und daher wurde gewartet, der STATE ist sending data.

von Schritt2hat andere Anweisungen blockiert
Weil CREATE TABLE A AS SELECT B vor der Fertigstellung der A-Tabelle MDL_EXCLUSIVE(X) setzt, was andere alle Anweisungen blockiert, einschließlich DESC/SHOW TABLE STATUS/USE DB(nicht-A) Diese Anweisung hat nur MDL_SHARED_HIGH_PRIO(SH) MDL LOCK. Der STATE ist einheitlich Waiting for table metadata lock.

Simulierte Tests

Testumgebung:

5.7.14

GITD ausgeschaltet

RR-Isolierungsebene

Verwenden Sie das Skript:

Die Schritte sind wie folgt:

session1 session2 session3 session4------use test;---use test;begin; delete from b;------------use test;create table a asselect * from b;(weil b-Tabelle innodb row lock blockiert)------------show table status like 'a';(weil a-Tabelle MDL LOCK blockiert)------------use test(weil a-Tabelle MDL LOCK blockiert)

Letztendlich sehen wir den Wartezustand so aus:

So können wir den Online-Status perfekt simulieren, wenn wir die Session töten.1der Transaktionen natürlich vollständig entsperrt, lassen Sie uns noch einmal die Ausgabe von performance_schema.metadata_locks betrachten:

Wir können die obige Ausgabe sehen, aber wir müssen beachten, dass LOCK_TYPE: SHARED nicht blockieren kann LOCK_TYPE: SHARED_HIGH_PRIO (siehe Anhang oder meinen früheren Artikel über MDL LOCK-Analyse). Wie oben analysiert, wurde hier tatsächlich eine Upgrade-Operation durchgeführt und auf MDL_EXCLUSIVE(X) aktualisiert.

Zusammenfassung

Unter RC-Modus wird die Tabelle B in CREATE TABLE A SELECT B keine INNODB ROW LOCK aufnehmen, aber wenn die Tabelle B sehr groß ist, wird die Tabelle A auch unter dem Schutz von MDL_EXCLUSIVE(X) stehen, daher kann auch eine Wartezeit für USE DB\SHOW TABLE STATUS ausgelöst werden.

Wenn GTID geöffnet ist, kann der Befehl CREATE TABLE A SELECT B nicht verwendet werden.

Für DML/Bei Systemen, die DDL und DML mischen, muss unbedingt die Konkurrenz beachtet werden, wie im Beispiel, wenn man die Situation unter hohem Konflikt erkennt, kann versucht werden, dies zu vermeiden.

Dieser Fall zeigt erneut, dass lange nicht abgeschlossene Transaktionen möglicherweise zu Tragödien führen können, daher wird empfohlen, Transaktionen zu überwachen, die länger als N Sekunden dauern.

Anhang

MDL LOCK TYPE

Kompatibilitätsmatrix

Prioritätsmatrix der Warteschlange

Erklärung: Der Inhalt dieses Artikels wurde aus dem Internet übernommen und gehört dem Urheberrechtlichem Eigentümer. Der Inhalt wurde von Internetbenutzern freiwillig beigesteuert und hochgeladen. Diese Website besitzt keine Eigentumsrechte und hat den Inhalt nicht manuell bearbeitet. Sie übernimmt auch keine rechtlichen Verantwortlichkeiten. Wenn Sie urheberrechtlich geschützte Inhalte entdecken, sind Sie herzlich eingeladen, eine E-Mail an notice#w zu senden.3codebox.com (Bitte ersetzen Sie # durch @ beim Senden von E-Mails zur Meldung von Verstößen und stellen Sie relevante Beweise zur Verfügung. Sobald nachgewiesen, wird diese Website die beanstandeten urheberrechtlichen Inhalte sofort löschen.)