Detaillierte Analyse der MySQL MDL-Metadatensperre

Vorwort:

Wenn Sie eine SQL-Anweisung in MySQL ausführen, wird die Anweisung nicht innerhalb der erwarteten Zeit abgeschlossen. Zu diesem Zeitpunkt melden wir uns normalerweise bei der MySQL-Datenbank an, um zu prüfen, ob ein Problem vorliegt. Ein normalerweise verwendeter Befehl ist „show processlist“, um zu sehen, welche Sitzungen vorhanden sind und was diese Sitzungen tun. Wenn Sie „Warten auf Sperre der Tabellenmetadaten“ sehen, handelt es sich um eine MDL-Metadatensperre. Dieser Artikel stellt den Generierungs- und Fehlerbehebungsprozess der MDL-Sperre vor.

1. Was ist eine MDL-Sperre?

MDL steht für Metadata Lock. Die Hauptfunktion der MDL-Sperre besteht darin, die Datenkonsistenz der Tabellenmetadaten aufrechtzuerhalten. Wenn aktive Transaktionen (explizit oder implizit) für die Tabelle vorhanden sind, können die Metadaten nicht geschrieben werden. Daher wurden ab MySQL Version 5.5 MDL-Sperren eingeführt, um die Metadateninformationen der Tabelle zu schützen und die Konsistenz zwischen DDL-Operationen und DML-Operationen zu lösen bzw. sicherzustellen.

Die Einführung von MDL löst hauptsächlich zwei Probleme. Das eine ist das Problem der Transaktionsisolierung. Wenn beispielsweise Sitzung A unter der wiederholbaren Isolationsstufe die Tabellenstruktur während zweier Abfragen ändert, sind die beiden Abfrageergebnisse inkonsistent und können die Anforderung des wiederholbaren Lesens nicht erfüllen. Das andere ist das Problem der Datenreplikation. Wenn beispielsweise Sitzung A mehrere Aktualisierungsanweisungen ausführt und eine andere Sitzung B die Tabellenstruktur ändert und diese zuerst übermittelt, führt der Slave zuerst die Änderung und dann die Aktualisierung erneut aus, was zu Replikationsfehlern führt.

Die Metadatensperre ist eine Sperre auf Serverebene, eine Sperre auf Tabellenebene. Für jede ausgeführte DML- oder DDL-Anweisung wird eine MDL-Sperre angewendet. DML-Operationen erfordern eine MDL-Lesesperre und DDL-Operationen erfordern eine MDL-Schreibsperre (der MDL-Sperrvorgang wird automatisch vom System gesteuert und kann nicht direkt beeinflusst werden. Lesen-Lesen wird gemeinsam genutzt, Lesen-Schreiben schließen sich gegenseitig aus und Schreiben-Schreiben schließen sich gegenseitig aus). Der Vorgang zum Anwenden einer MDL-Sperre bildet eine Warteschlange, und die Priorität zum Erlangen einer Schreibsperre in der Warteschlange ist höher als die der Lesesperre. Sobald eine Schreibsperre auftritt, wird nicht nur der aktuelle Vorgang blockiert, sondern auch alle nachfolgenden Vorgänge in der Tabelle. Wenn für eine Transaktion eine MDL-Sperre beantragt wird, wird die Sperre erst aufgehoben, wenn die Transaktion abgeschlossen ist. (Hier gibt es einen Sonderfall. Wenn die Transaktion DDL-Operationen enthält, führt MySQL implizit ein Commit durch, bevor die DDL-Operationsanweisung ausgeführt wird, um sicherzustellen, dass die DDL-Anweisungsoperation als separate Transaktion vorhanden ist und auch die Freigabe der exklusiven Metadatensperre sichergestellt wird).

Hinweis: Sowohl in InnoDB-Engine-Tabellen, die Transaktionen unterstützen, als auch in MyISAM-Engine-Tabellen, die keine Transaktionen unterstützen, tritt das Metadata Lock Wait-Phänomen auf. Wenn das Phänomen „Metadata Lock Wait“ auftritt, werden alle nachfolgenden Zugriffe auf die Tabelle während dieser Wartezeit blockiert, was zu einer Anhäufung von Verbindungen und zu Geschäftsauswirkungen führt.

2. MDL-Sperren simulieren und finden

MDL-Sperren treten normalerweise auf, wenn ein DDL-Vorgang angehalten wird, weil nicht festgeschriebene Transaktionen vorhanden sind, die DML-Vorgänge auf der Tabelle ausführen. Es gibt jedoch so viele MySQL-Sitzungen, dass nicht bekannt ist, welche Vorgänge der Sitzung nicht rechtzeitig übermittelt wurden und sich auf den DDL ausgewirkt haben. Wenn wir diese Art von Problem beheben, müssen wir normalerweise die aktuell ausgeführte Transaktion aus der Tabelle information_schema.innodb_trx abfragen. Wenn das SQL jedoch ausgeführt und nicht festgeschrieben wurde, ist das SQL in dieser Tabelle nicht sichtbar.

In MySQL 5.7 wurde der Bibliothek performance_schema eine neue metadata_locks-Tabelle hinzugefügt, um MDL-bezogene Informationen aufzuzeichnen. Zuerst müssen Sie die MDL-Sperrprotokollierung aktivieren. Führen Sie den folgenden SQL-Befehl aus, um sie zu aktivieren:

AKTUALISIEREN Sie performance_schema.setup_instruments
AKTIVIERT = „JA“, ZEITGESTEUERT = „JA“
WO NAME = 'warten/sperren/Metadaten/SQL/MDL';

Im Folgenden wird der Vorgang zum Simulieren und Suchen von MDL-Sperren gezeigt:

# Sitzung 1 Führt DML-Operationen in der Transaktion mysql> begin; aus.
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> in student_tb (stu_id, stu_name) Werte einfügen (1009, 'xin');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> wähle * aus student_tb;
+--------------+--------+----------+---------------------+---------------------+
| Inkrement-ID | Stu-ID | Stu-Name | Erstellungszeit | Aktualisierungszeit |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | von1 | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 2 | 1002 | dfsfd | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 3 | 1003 | fdgfg | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 4 | 1004 | sdfsdf | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 5 | 1005 | dsfsdg | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 6 | 1006 | fgd | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 7 | 1007 | fgds | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 8 | 1008 | dgfsa | 28.11.2019 16:36:14 | 28.11.2019 16:36:14 |
| 9 | 1009 | xin | 28.11.2019 17:05:29 | 28.11.2019 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+

# Sitzung 2 Führen Sie den DDL-Vorgang aus, um der Tabelle Felder hinzuzufügen, und stellen Sie fest, dass DDL hängt: mysql> alter table student_tb add stu_age int after stu_name;

# Sitzung 3: Alle Sitzungen abfragen und feststellen, dass eine MDL-Sperre auftritt mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| 31 | root | localhost | testdb | Ruhezustand | 125 | | NULL |
| 32 | root | localhost | testdb | Abfrage | 7 | Warte auf Sperre der Tabellenmetadaten | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | Abfrage | 0 | wird gestartet | Prozessliste anzeigen |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+

# Sitzung 3 Überprüfen Sie die Datensätze der Tabelle metadata_locks und stellen Sie fest, dass in der Tabelle student_tb ein MDL-Sperrkonflikt vorliegt. mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+
| OBJEKTTYP | OBJEKTSCHEMA | OBJEKTNAME | OBJEKTINSTANZ_BEGIN | SPERRTYP | SPERRTAUFZEIT | SPERRTSTATUS | QUELLE | EIGENTÜMER_THREAD_ID | EIGENTÜMER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABELLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSAKTION | GEWÄHRT | | 56 | 34 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GEWÄHRT | | 57 | 18 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 |
| TABELLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSAKTION | GEWÄHRT | | 57 | 18 |
| TABELLE | testdb | student_tb | 139764477697696 | EXKLUSIV | TRANSAKTION | AUSSTEHEND | | 57 | 18 |
| TABELLE | Leistungsschema | Metadatensperren | 139764544135120 | SHARED_READ | TRANSAKTION | GEWÄHRT | | 58 | 20 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+

# Sitzung 3 kombiniert andere Systemtabellen, um die Sitzungs-ID zu finden
mysql> wähle m.*,t.PROCESSLIST_ID aus performance_schema.metadata_locks m links, schließe dich performance_schema.threads t an auf m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+----------------+----------------+
| OBJEKTTYP | OBJEKTSCHEMA | OBJEKTNAME | OBJEKTINSTANZ_BEGIN | SPERRTYP | SPERRDAUER | SPERRSTATUS | QUELLE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+----------------+----------------+
| TABELLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSAKTION | GEWÄHRT | | 56 | 34 | 31 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GEWÄHRT | | 57 | 18 | 32 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABELLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSAKTION | GEWÄHRT | | 57 | 18 | 32 |
| TABELLE | testdb | student_tb | 139764477697696 | EXKLUSIV | TRANSAKTION | AUSSTEHEND | | 57 | 18 | 32 |
| TABELLE | Leistungsschema | Metadatensperren | 139764544135120 | SHARED_READ | TRANSAKTION | GEWÄHRT | | 58 | 22 | 33 |
| TABELLE | Leistungsschema | Threads | 139764549217280 | SHARED_READ | TRANSAKTION | GEWÄHRT | | 58 | 22 | 33 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+------------+--------+-----------------+----------------+----------------+----------------+

# Ergebnisinterpretation: Aus den obigen Ergebnissen ist offensichtlich, dass Sitzung 31 die SHARED_WRITE-Sperre der Tabelle student_tb hält.
# Sie müssen auf die Übermittlung warten oder die Sitzung manuell beenden, um die MDL-Sperre freizugeben.

3. So optimieren und vermeiden Sie MDL-Sperren

Sobald eine MDL-Sperre auftritt, hat dies enorme Auswirkungen auf das Geschäft, da alle nachfolgenden Zugriffe auf die Tabelle blockiert werden und es zu einem Rückstau an Verbindungen kommt. Wir sollten versuchen, das Auftreten von MDL-Sperren in unserem täglichen Leben zu vermeiden. Hier sind einige Optimierungsvorschläge zu Ihrer Information:

• Aktivieren Sie die Tabelle metadata_locks, um MDL-Sperren aufzuzeichnen.
• Setzen Sie den Parameter lock_wait_timeout auf einen kleineren Wert, damit das blockierte Ende aktiv stoppt.
• Verwenden Sie Transaktionen auf standardisierte Weise, übermitteln Sie Transaktionen rechtzeitig und vermeiden Sie die Verwendung großer Transaktionen.
• Verbessern Sie die Überwachung und Alarmierung, um MDL-Sperren rechtzeitig zu erkennen.
• DDL-Vorgänge und Sicherungsvorgänge werden außerhalb der Geschäftszeiten durchgeführt.
• Verwenden Sie weniger Tools, um Transaktionen für Abfragen zu öffnen, und schließen Sie grafische Tools rechtzeitig.

Zusammenfassen:

Dieser Artikel erläutert die MDL-Sperre hauptsächlich in drei Aspekten. Zunächst werden die Ursachen und Funktionen der MDL-Sperre vorgestellt. Dann simulieren wir die MDL-Sperre und stellen die Such- und Lösungsmethoden bereit. Abschließend geben wir einige Vorschläge zur Vermeidung der MDL-Sperre. Tatsächlich treten MDL-Sperren während des Betriebs und der Wartung von Datenbanken häufig auf. Sie sind keine Plage, sondern werden nur zum Schutz von Datenbankobjekten und zur Gewährleistung der Datenkonsistenz verwendet. Ich hoffe, dass Sie nach dem Lesen dieses Artikels ein klareres Verständnis der MDL-Sperre haben.

Oben finden Sie eine detaillierte Analyse der Details der MySQL MDL-Metadatensperre. Weitere Informationen zur MySQL MDL-Metadatensperre finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!