Gründe und Methoden zum Warten auf die Sperre der Tabellenmetadaten in MySQL

Wenn MySQL DDL-Operationen wie „Alter Table“ ausführt, kann das Szenario „Warten auf Sperre der Tabellenmetadaten“ auftreten. Wenn außerdem die Operation „Tabelle ändern“ von TableA im Zustand „Warten auf Sperre der Tabellenmetadaten“ feststeckt, können keine nachfolgenden Operationen an TableA (einschließlich Lesen) ausgeführt werden, da sie in der Phase „Tabellen öffnen“ ebenfalls in die Sperrwarteschlange „Warten auf Sperre der Tabellenmetadaten“ gelangen. Wenn eine solche Warteschlange für Sperren in der Kerntabelle der Produktionsumgebung auftritt, hat dies katastrophale Folgen.

Der Grund, warum bei „Alter Table“ das Warten auf eine Tabellenmetadatensperre ausgelöst wird, ist eigentlich ganz einfach und liegt im Allgemeinen an den folgenden einfachen Szenarien:

Szenario 1: Eine lange Transaktionsausführung blockiert DDL und blockiert anschließend alle nachfolgenden Vorgänge für dieselbe Tabelle.

Durch die Anzeige der Prozessliste können Sie sehen, dass auf Tabelle A laufende Vorgänge (einschließlich Lesevorgänge) ausgeführt werden. Zu diesem Zeitpunkt kann die Anweisung „Alter Table“ die exklusive Metadatensperre nicht erhalten und wartet.

Dies ist die grundlegendste Situation, die nicht mit dem Online-DDL in MySQL 5.6 in Konflikt steht. Während des allgemeinen Vorgangs „Tabelle ändern“ (siehe Abbildung unten) wird im Schritt „Nach dem Erstellen“ eine exklusive Metadatensperre erworben. Wenn der Vorgang „Tabelle ändern“ ausgeführt wird (normalerweise der zeitaufwändigste Schritt), kann das Lesen und Schreiben in die Tabelle normal fortgesetzt werden. Dies ist die Leistung von Online-DDL, und das Schreiben wird während des gesamten Vorgangs „Tabelle ändern“ nicht wie zuvor blockiert. (Natürlich können nicht alle Arten von Änderungsvorgängen online durchgeführt werden. Einzelheiten finden Sie im offiziellen Handbuch: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
Lösung: Beenden Sie die Sitzung, in der sich das DDL befindet.

Szenario 2: Die Transaktion wird nicht übermittelt, wodurch der DDL und damit auch alle nachfolgenden Vorgänge in derselben Tabelle blockiert werden.

Über die Anzeige der Prozessliste können Sie keine Vorgänge in Tabelle A sehen, es gibt jedoch tatsächlich nicht festgeschriebene Transaktionen, die in information_schema.innodb_trx angezeigt werden können. Bevor die Transaktion abgeschlossen ist, wird die Sperre für TableA nicht aufgehoben und „Alter Table“ erhält nicht die exklusive Sperre für die Metadaten.

Lösung: Verwenden Sie „select * from information_schema.innodb_trx\G“, um die Seite der nicht festgeschriebenen Transaktion zu finden, und beenden Sie sie dann, um ein Rollback durchzuführen.

Szenario 3:

Es gibt keine Vorgänge auf TableA über die Anzeige der Prozessliste und es gibt keine laufenden Transaktionen in information_schema.innodb_trx. Dies liegt höchstwahrscheinlich daran, dass in einer expliziten Transaktion eine fehlgeschlagene Operation für TableA ausgeführt wurde (z. B. wurde ein nicht vorhandenes Feld abgefragt). Zu diesem Zeitpunkt wurde die Transaktion nicht gestartet, aber die durch die fehlgeschlagene Anweisung erworbene Sperre war noch gültig und wurde nicht freigegeben. Die fehlgeschlagene Anweisung kann in der Tabelle performance_schema.events_statements_current gefunden werden.

Im offiziellen Handbuch steht Folgendes:

Wenn der Server Metadatensperren für eine Anweisung erwirbt, die syntaktisch gültig ist, aber während der Ausführung fehlschlägt, gibt er die Sperren nicht vorzeitig frei. Die Freigabe der Sperre wird dennoch bis zum Ende der Transaktion verschoben, da die fehlgeschlagene Anweisung in das Binärprotokoll geschrieben wird und die Sperren die Protokollkonsistenz schützen.

Das heißt, mit Ausnahme von Syntaxfehlern werden die durch andere fehlerhafte Anweisungen erworbenen Sperren erst freigegeben, wenn die Transaktion festgeschrieben oder zurückgesetzt wird. Der Grund für dieses Verhalten ist jedoch schwer zu verstehen, da die fehlgeschlagene Anweisung in das Binärprotokoll geschrieben wird und die Sperren die Protokollkonsistenz schützen, da die fehlgeschlagene Anweisung überhaupt nicht im Binärprotokoll aufgezeichnet wird.

Lösung: Suchen Sie die SID über performance_schema.events_statements_current und beenden Sie die Sitzung. Sie können auch die Sitzung beenden, in der sich das DDL befindet.

Kurz gesagt, die Anweisung „Alter Table“ ist sehr gefährlich (tatsächlich wird ihre Gefahr durch nicht festgeschriebene Transaktionen oder lange Transaktionen verursacht). Vor der Operation sollten Sie am besten sicherstellen, dass an der zu bearbeitenden Tabelle keine laufenden Operationen, keine nicht festgeschriebenen Transaktionen und keine Fehleranweisungen in expliziten Transaktionen vorhanden sind. Wenn eine Wartungsaufgabe zum Ändern der Tabelle unbeaufsichtigt ausgeführt wird, empfiehlt es sich, das Timeout über lock_wait_timeout festzulegen, um lange Wartezeiten beim Sperren von Metadaten zu vermeiden.