Detaillierte Erläuterung der Metadatensperre, die Sie beim Ändern der MySQL-Tabellenstruktur kennen müssen

Vorwort

Wer schon einmal mit MySQL gespielt hat, kennt sich bestimmt mit dem Warten auf die Metadatensperre der Tabelle aus. Normalerweise wird es während des Änderungsvorgangs blockiert, sodass wir beim Anzeigen der Prozessliste den Thread-Status „Warten auf die Metadatensperre“ sehen. Dieser Artikel bietet eine detaillierte Einführung in die Metadatensperre für Änderungen der MySQL-Tabellenstruktur.

Wenn wir DDL-Operationen online durchführen, bereitet uns im Vergleich zu der möglichen Systembelastung, die sie mit sich bringen können, das Blockierungsproblem, das durch MDL verursacht werden kann, die größten Sorgen.

Wenn eine DDL-Operation blockiert wird, weil der MDL nicht abgerufen werden kann, werden alle nachfolgenden Operationen an der Tabelle blockiert. Ein typisches Beispiel ist wie folgt: Wenn die Blockade eine Weile anhält, sehen wir einen Anstieg bei Threads_running und einen CPU-Alarm.

mysql> Prozessliste anzeigen;
+----+-----------------+-----------+--------------+---------+------+---------------------------------+------------------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+-----------------+-----------+--------------+---------+------+---------------------------------+------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 122 | Warte auf leere Warteschlange | NULL |
| 9 | root | localhost | NULL | Ruhezustand | 57 | | NULL |
| 12 | root | localhost | Mitarbeiter | Abfrage | 40 | Warte auf Sperre der Tabellenmetadaten | alter table slowtech.t1 add c1 int |
| 13 | root | localhost | Mitarbeiter | Abfrage | 35 | Warte auf Sperre der Tabellenmetadaten | select * from slowtech.t1 |
| 14 | root | localhost | Mitarbeiter | Abfrage | 30 | Warte auf Sperre der Tabellenmetadaten | select * from slowtech.t1 |
| 15 | root | localhost | Mitarbeiter | Abfrage | 19 | Warte auf Sperre der Tabellenmetadaten | select * from slowtech.t1 |
| 16 | root | localhost | Mitarbeiter | Abfrage | 10 | Warte auf Sperre der Tabellenmetadaten | select * from slowtech.t1 |
| 17 | root | localhost | Mitarbeiter | Abfrage | 0 | wird gestartet | Prozessliste anzeigen |
+----+-----------------+-----------+--------------+---------+------+---------------------------------+------------------------------------+
Zeilen im Set (0,00 Sek.)

Wenn dies online geschieht, wird es zweifellos Auswirkungen auf das Geschäft haben. Daher wird im Allgemeinen empfohlen, DDL-Vorgänge außerhalb der Geschäftszeiten durchzuführen. Tatsächlich gibt es zwei Überlegungen: 1. Vermeiden Sie erhebliche Auswirkungen auf die Systemlast. 2. Reduzieren Sie die Wahrscheinlichkeit, dass DDL blockiert wird.

Hintergrund der Einführung von MDL

MDL wurde in MySQL 5.5.3 eingeführt und wird hauptsächlich zur Lösung zweier Probleme verwendet.

Das Problem des nicht wiederholbaren Lesens unter der RR-Transaktionsisolationsebene

Wie unten gezeigt, verwendet die Demonstrationsumgebung MySQL 5.5.0.

Sitzung1> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Sitzung1> wähle * aus t1;
+------+------+
| Ich würde | Name |
+------+------+
| 1 | ein |
| 2 | b |
+------+------+
Zeilen im Set (0,00 Sek.)

Sitzung2> Tabelle ändern t1, c1 int hinzufügen;
Abfrage OK, 2 Zeilen betroffen (0,02 Sek.)
Datensätze: 2 Duplikate: 0 Warnungen: 0

Sitzung1> wähle * aus t1;
Leerer Satz (0,00 Sek.)

Sitzung1> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Sitzung1> wähle * aus t1;
+------+------+------+
| Ich würde | Name | c1 |
+------+------+------+
| 1 | ein | NULL |
| 2 | b | NULL |
+------+------+------+
Zeilen im Set (0,00 Sek.)

Es ist ersichtlich, dass die zweite Abfrage, obwohl es sich um die RR-Isolationsebene handelt, keine Ergebnisse liefert, wenn die Transaktion aktiviert wird.

Master-Slave-Replikationsproblem

Einschließlich Inkonsistenz zwischen Master- und Slave-Daten, Unterbrechung der Master-Slave-Replikation usw.

Beispielsweise sind die Master-Slave-Daten inkonsistent, wie unten gezeigt.

Sitzung1> Tabelle erstellen t1 (ID int, Name varchar (10)) Engine = innodb;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Sitzung1> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Sitzung1> in t1-Werte einfügen (1, „a“);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

Sitzung2> Tabelle t1 abschneiden;
Abfrage OK, 0 Zeilen betroffen (0,46 Sek.)

Sitzung1> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,35 Sek.)

Sitzung1> wähle * aus t1;
Leerer Satz (0,00 Sek.)

Werfen wir einen Blick auf die Ergebnisse der Bibliothek

Sitzung1> wähle * aus slowtech.t1;
+------+------+------+
| Ich würde | Name | c1 |
+------+------+------+
| 1 | ein | NULL |
+------+------+------+
Zeile im Satz (0,00 Sek.)

Wenn Sie sich den Inhalt des Binärprotokolls ansehen, können Sie sehen, dass zuerst der Abschneidevorgang und später der Einfügevorgang aufgezeichnet wird.

# bei 7140
#180714 19:32:14 Server-ID 1 end_log_pos 7261 Abfrage Thread-ID = 31 Exec_time = 0 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1531567934/*!*/;
Tabelle erstellen t1(id int,name varchar(10)) engine=innodb
/*!*/;

# bei 7261
#180714 19:32:30 Server-ID 1 end_log_pos 7333 Abfrage Thread-ID = 32 exec_time = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1531567950/*!*/;
BEGINNEN
/*!*/;
# bei 7333
#180714 19:32:30 Server-ID 1 End-Log-Pos 7417 Abfrage Thread-ID = 32 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1531567950/*!*/;
Tabelle t1 abschneiden
/*!*/;
# bei 7417
#180714 19:32:30 Server-ID 1 end_log_pos 7444 Xid = 422
BEGEHEN /*!*/;

# bei 7444
#180714 19:32:34 Server-ID 1 End-Log-Pos 7516 Abfrage Thread-ID = 31 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1531567954/*!*/;
BEGINNEN
/*!*/;
# bei 7516
#180714 19:32:24 Server-ID 1 End-Log-Pos 7611 Abfrage Thread-ID = 31 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1531567944/*!*/;
in t1 Werte einfügen(1,'a')
/*!*/;
# bei 7611
#180714 19:32:34 Server-ID 1 end_log_pos 7638 Xid = 421
BEGEHEN /*!*/;

Wenn Sitzung 2 den Drop-Table-Vorgang ausführt, wird die Master-Slave-Beziehung unterbrochen.

Interessanterweise wird Sitzung 2, wenn sie den Vorgang „Tabelle ändern“ ausführt, immer noch blockiert und die Blockierungszeit wird durch den Parameter innodb_lock_wait_timeout begrenzt.

mysql> Prozessliste anzeigen;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| ID | Benutzer | Host | db | Befehl | Zeit | Status | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Abfrage | 0 | NULL | Prozessliste anzeigen |
| 58 | root | localhost | slowtech | Ruhezustand | 1062 | | NULL |
| 60 | root | localhost | slowtech | Abfrage | 11 | in temporäre Tabelle kopieren | Tabelle ändern t1 hinzufügen c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
Zeilen im Set (0,00 Sek.)

Grundlegende Konzepte von MDL

Schauen wir uns zunächst die offizielle Erklärung an.

Um die Serialisierbarkeit von Transaktionen sicherzustellen, darf der Server nicht zulassen, dass in einer Sitzung eine DDL-Anweisung (Data Definition Language) für eine Tabelle ausgeführt wird, die in einer nicht abgeschlossenen, explizit oder implizit gestarteten Transaktion in einer anderen Sitzung verwendet wird.

Der Server erreicht dies, indem er Metadatensperren für die in einer Transaktion verwendeten Tabellen erwirbt und die Freigabe dieser Sperren bis zum Ende der Transaktion aufschiebt.

Eine Metadatensperre für eine Tabelle verhindert Änderungen an der Tabellenstruktur.

Dieser Sperransatz hat zur Folge, dass eine Tabelle, die von einer Transaktion innerhalb einer Sitzung verwendet wird, bis zum Ende der Transaktion nicht in DDL-Anweisungen anderer Sitzungen verwendet werden kann.

Aus der obigen Beschreibung können wir erkennen, dass

1. Die ursprüngliche Absicht von MDL besteht darin, die Struktur einer Tabelle in einer Transaktion vor Änderungen zu schützen.

2. Die hier erwähnten Transaktionen umfassen zwei Typen: explizite Transaktionen und AC-NL-RO-Transaktionen (Auto-Commit Non-Locking Read-Only). Es gibt zwei Arten expliziter Transaktionen: 1. Vorgänge mit deaktiviertem AutoCommit und 2. Vorgänge, die mit „begin“ oder „start transaction“ gestartet wurden. AC-NL-RO kann als Auswahlvorgang mit aktiviertem AutoCommit verstanden werden.

3. MDL ist auf Transaktionsebene und wird erst nach Abschluss der Transaktion freigegeben. Zuvor gab es tatsächlich einen ähnlichen Schutzmechanismus, allerdings auf Anweisungsebene.

Es ist zu beachten, dass MDL nicht nur auf Tabellen, sondern auch auf andere Objekte anwendbar ist, wie in der folgenden Tabelle gezeigt, wobei „Wartezustand“ dem Zustand in „Prozessliste anzeigen“ entspricht.

Um die Parallelität der Datenbank zu verbessern, wird MDL in 11 Typen unterteilt.

• MDL_INTENTION_EXCLUSIVE
• MDL_SHARED
• MDL_SHARED_HIGH_PRIO
• _GEMEINSAMES _LESEN VON MDL
• MDL_SHARED_WRITE
• MDL_SHARED_WRITE_LOW_PRIO
• MDL_SHARED_UPGRADABLE
• MDL_SHARED_READ_ONLY
• MDL_SHARED_NO_WRITE
• MDL_SHARED_NO_READ_WRITE
• MDL_EXCLUSIVE

Häufig verwendete sind MDL_SHARED_READ, MDL_SHARED_WRITE und MDL_EXCLUSIVE, die jeweils für SELECT-Operationen, DML-Operationen und DDL-Operationen verwendet werden. Informationen zu anderen Arten entsprechender Vorgänge finden Sie im Quellcode sql/mdl.h.

Für MDL_EXCLUSIVE lautet die offizielle Erklärung:

/*
Eine exklusive Metadatensperre.
Eine Verbindung, die diese Sperre aufrechterhält, kann sowohl die Metadaten als auch die Daten der Tabelle ändern.
Solange diese Sperre besteht, kann keine andere Art von Metadatensperre gewährt werden.
Zu verwenden für CREATE/DROP/RENAME TABLE-Anweisungen und zur Ausführung von
bestimmte Phasen anderer DDL-Anweisungen.
*/

Kurz gesagt ist MDL_EXCLUSIVE eine exklusive Sperre. Während der Haltedauer dürfen keine anderen MDL-Typen gewährt werden, einschließlich SELECT- und DML-Operationen.

Aus diesem Grund werden beim Blockieren eines DDL-Vorgangs auch andere nachfolgende Vorgänge blockiert.

Ergänzende Informationen zu MDL

1. Die maximale Wartezeit von MDL wird durch den Parameter lock_wait_timeout bestimmt und sein Standardwert beträgt 31536000 (365 Tage). Dieser Wert ist nicht sinnvoll, wenn Tools zum Ausführen von DDL-Operationen verwendet werden. Tatsächlich haben pt-online-schema-change und gh-ost entsprechende Anpassungen vorgenommen, wobei ersteres 60 s und letzteres 3 s benötigte.

2. Wenn eine SQL-Anweisung syntaktisch gültig ist, während der Ausführung jedoch ein Fehler auftritt (z. B. der Spaltenname nicht vorhanden ist), wird auch die MDL-Sperre erworben und erst nach Abschluss der Transaktion freigegeben.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.