Tiefgreifendes Verständnis von globalen Sperren und Tabellensperren in MySQL

Vorwort

Je nach Umfang der Sperrung können Sperren in MySQL grob in globale Sperren, Tabellensperren und Zeilensperren unterteilt werden.

Zeilensperren wurden in früheren Artikeln erwähnt

1. Globale Sperre

Eine globale Sperre sperrt die gesamte Datenbankinstanz. MySQL bietet eine Flush tables with read lock (FTWRL) .

Wenn Sie die gesamte Datenbank schreibgeschützt machen müssen, können Sie diesen Befehl verwenden. Danach werden die folgenden Anweisungen anderer Threads blockiert: Datenaktualisierungsanweisungen (Hinzufügen, Löschen und Ändern von Daten), Datendefinitionsanweisungen (einschließlich Tabellenerstellung, Tabellenstrukturänderung usw.) und Commit-Anweisungen von Aktualisierungstransaktionen.

1.1 Szenarien für die Verwendung globaler Sperren

Das typische Anwendungsszenario globaler Sperren besteht darin, eine logische Sicherung der gesamten Datenbank (mysqldump) zu erstellen. Wenn du wieder Herr und Sklave wirst

Das bedeutet, dass jede Tabelle in der gesamten Datenbank ausgewählt und als Text gespeichert wird.

In der Vergangenheit gab es eine Methode, die FTWRL verwendete, um sicherzustellen, dass keine anderen Threads die Datenbank aktualisierten, und dann die gesamte Datenbank sicherte. Beachten Sie, dass während des Sicherungsvorgangs die gesamte Bibliothek vollständig schreibgeschützt ist.

Die Gefahren eines schreibgeschützten Datenbankzustands:

Wenn Sie die Sicherung auf der primären Datenbank durchführen, können während des Sicherungszeitraums keine Aktualisierungen durchgeführt werden und der Geschäftsbetrieb kann grundsätzlich eingestellt werden. Wenn Sie auf einem Slave sichern, kann der Slave das vom Master synchronisierte Binärprotokoll während der Sicherung nicht ausführen, was zu einer Verzögerung zwischen dem Master und dem Slave führt.

Hinweis: Das obige logische Backup enthält nicht --single-transaction

Es scheint, dass das Hinzufügen einer globalen Sperre keine gute Idee ist. Aber denken Sie darüber nach: Warum muss das Backup gesperrt werden? Mal sehen, welche Probleme entstehen, wenn wir es nicht abschließen.

1.2 Probleme durch fehlende Verriegelung

Zum Beispiel Handykarten, Paketkaufinformationen

Hier gibt es zwei Tabellen: u_acount (für die Bilanztabelle) und u_pricing (für die Tarifpakettabelle)
Schritt:
1. Daten in der Tabelle u_account Kontostand von Benutzer A: 300
Daten in der Tabelle „u_pricing“ Paket für Benutzer A: leer

2. Starten Sie eine Sicherung. Während des Sicherungsvorgangs wird zuerst die Tabelle u_account gesichert. Nach der Sicherung dieser Tabelle beträgt der Benutzersaldo u_account 300.
3. Zu diesem Zeitpunkt kaufte der Benutzer ein Paket mit einem Tarif von 100, der Essenskauf wurde abgeschlossen, der Kauf war erfolgreich und die Daten während des Sicherungszeitraums wurden in die Pakettabelle u_print geschrieben.
4. Sicherung abgeschlossen

Durch die Sicherung haben sich die Daten in der Tabelle u_account nicht geändert, die Daten in der Tabelle u_pricing wurden jedoch auf das gekaufte Paket von 100 aktualisiert.

Wenn Benutzer A jetzt die Sicherungsdatei zur Datenwiederherstellung verwendet, verdient er 100. Wäre er nicht sehr glücklich? Dabei muss man aber auch die Interessen des Unternehmens im Auge behalten.

Mit anderen Worten: Wenn die Datenbank nicht gesperrt ist, liegt die Sicherung durch das Sicherungssystem nicht zu einem logischen Zeitpunkt vor und die Daten sind logisch inkonsistent.

1.3 Warum brauchen wir eine globale Lesesperre (FTWRL)?

Einige fragen sich vielleicht: Das offizielle logische Backup-Tool ist mysqldump. Wenn mysqldump den Parameter --single-transaction verwendet, wird vor dem Importieren der Daten eine Transaktion gestartet, um eine konsistente Snapshot-Ansicht sicherzustellen. Und dank der Unterstützung von MVCC können die Daten während dieses Vorgangs normal aktualisiert werden.

Warum brauchen wir FTWRL?
Konsistentes Lesen ist gut, aber Voraussetzung ist, dass die Engine dieses Isolationslevel unterstützt. Beispielsweise können bei einer Engine wie MyISAM, die keine Transaktionen unterstützt, bei einem Update während des Sicherungsvorgangs nur die neuesten Daten abgerufen werden, wodurch die Konsistenz der Sicherung zerstört wird. Zu diesem Zeitpunkt müssen wir den Befehl FTWRL verwenden.

Daher ist der Einzeltransaktionsansatz nur auf Bibliotheken anwendbar, die die Transaktions-Engine für alle Tabellen verwenden. Wenn einige Tabellen eine Engine verwenden, die keine Transaktionen unterstützt, kann die Sicherung nur mit der FTWRL-Methode durchgeführt werden. Dies ist häufig einer der Gründe, warum DBAs Geschäftsentwickler bitten, InnoDB anstelle von MyISAM zu verwenden.

1.4 Zwei Methoden der globalen Sperre

1. Tabellen leeren, schreiben, lesen, sperren

2. setze global readonly=true

Da die gesamte Datenbank schreibgeschützt sein muss, warum verwenden Sie nicht die Methode „set global readonly=true“? Zwar kann mit der readonly-Methode auch die gesamte Datenbank in einen schreibgeschützten Zustand versetzt werden, dennoch empfehle ich aus mehreren Gründen die Verwendung der FTWRL-Methode:

Erstens wird der schreibgeschützte Wert in einigen Systemen für andere Logik verwendet, beispielsweise um zu bestimmen, ob es sich bei einer Datenbank um eine Primärdatenbank oder eine Sicherungsdatenbank handelt. Daher hat die Änderung globaler Variablen größere Auswirkungen und ich empfehle Ihnen nicht, diese zu verwenden.

Zweitens gibt es Unterschiede in den Mechanismen zur Ausnahmebehandlung. Wenn die Verbindung des Clients nach der Ausführung des FTWRL-Befehls abnormal getrennt wird, hebt MySQL automatisch die globale Sperre auf und die gesamte Datenbank kehrt in einen Zustand zurück, in dem sie normal aktualisiert werden kann. Wenn nach dem Festlegen des Schreibschutzes für die gesamte Datenbank auf dem Client eine Ausnahme auftritt, verbleibt die Datenbank im Schreibschutzstatus. Dies führt dazu, dass die gesamte Datenbank für längere Zeit nicht beschreibbar ist, was ein hohes Risiko darstellt.

Drittens ist readonly für Superuser-Berechtigungen ungültig

Hinweis: Bei Geschäftsaktualisierungen geht es nicht nur um das Hinzufügen, Löschen und Ändern von Daten (DML), sondern sie können auch Vorgänge wie das Hinzufügen von Feldern zum Ändern der Tabellenstruktur (DDL) umfassen. Unabhängig davon, welche Methode Sie verwenden: Sobald eine Datenbank global gesperrt ist, wird auch jede Tabelle darin gesperrt, der Sie ein Feld hinzufügen möchten.

Auch wenn es nicht global gesperrt ist, ist das Hinzufügen von Feldern nicht ganz einfach. Es gibt auch Sperren auf Tabellenebene.

2. Sperre auf Tabellenebene

In MySQL gibt es zwei Arten von Sperren auf Tabellenebene: die Tabellensperre und die Metadatensperre (MDL).

2.1 Tabellensperre

Tabellen sperren Tabellenname lesen; #Die Tabelle kann gelesen werden, kann aber in DDL und DML nicht hinzugefügt, gelöscht oder geändert werden. Nur Tabellendaten können gelesen werden

Tabellen sperren Tabellenname lesen; # kann weder lesen noch schreiben

Die Syntax für die Tabellensperre lautet „Tabellen sperren … lesen/schreiben“. Ähnlich wie bei FTWRL können Sie Entsperrtabellen verwenden, um die Sperre aktiv freizugeben, oder Sie können sie automatisch freigeben, wenn der Client die Verbindung trennt. Es ist zu beachten, dass die Syntax der Sperrtabellen nicht nur das Lesen und Schreiben anderer Threads einschränkt, sondern auch die nachfolgenden Operationsobjekte dieses Threads begrenzt.

Wenn beispielsweise die Anweisung „lock tables t1 read, t2 write“ im Thread A ausgeführt wird, werden Anweisungen anderer Threads, die t1 schreiben und t2 lesen und schreiben, blockiert. Gleichzeitig kann Thread A vor der Ausführung der Entsperrtabellen nur Vorgänge wie das Lesen von t1 und das Lesen und Schreiben von t2 ausführen. Das Schreiben in t1 ist nicht erlaubt und natürlich kann auf andere Tabellen nicht zugegriffen werden.

Bevor feinkörnigere Sperren aufkamen, waren Tabellensperren die am häufigsten verwendete Methode zum Umgang mit Parallelität. Bei Engines wie InnoDB, die Zeilensperren unterstützen, wird der Befehl „lock tables“ im Allgemeinen nicht zur Steuerung der Parallelität verwendet. Schließlich hat das Sperren der gesamten Tabelle enorme Auswirkungen.

2.2 MDL-Sperre

Ein anderer Typ von Sperre auf Tabellenebene ist MDL (Metadatensperre). MDL muss nicht explizit verwendet werden, es wird beim Zugriff auf eine Tabelle automatisch hinzugefügt. Die Rolle von MDL besteht darin, die Richtigkeit beim Lesen und Schreiben sicherzustellen. Sie können sich Folgendes vorstellen: Wenn eine Abfrage die Daten in einer Tabelle durchläuft und während der Ausführung ein anderer Thread die Tabellenstruktur ändert und eine Spalte löscht, stimmt das vom Abfrage-Thread erhaltene Ergebnis nicht mit der Tabellenstruktur überein, was definitiv nicht akzeptabel ist.

Aus diesem Grund wurde MDL in MySQL 5.5 eingeführt. Beim Hinzufügen, Löschen, Ändern oder Abfragen einer Tabelle wird eine MDL-Lesesperre hinzugefügt. Wenn eine strukturelle Änderung an der Tabelle vorgenommen wird, wird eine MDL-Schreibsperre hinzugefügt.

• Lesesperren schließen sich nicht gegenseitig aus, d. h. mehrere Threads können gleichzeitig eine Tabelle hinzufügen, löschen, ändern und abfragen.
• Lese-/Schreibsperren und Schreibsperren schließen sich gegenseitig aus und dienen der Sicherheit von Vorgängen, die Tabellenstrukturen ändern. Wenn also zwei Threads gleichzeitig Felder zu einer Tabelle hinzufügen möchten, muss einer von ihnen warten, bis der andere fertig ist, bevor er mit der Ausführung beginnen kann.

Obwohl die MDL-Sperre standardmäßig hinzugefügt wird, handelt es sich dabei um einen Mechanismus, den Sie nicht ignorieren können.

Im folgenden Beispiel erlebe ich beispielsweise häufig, wie Leute in diese Falle tappen: Das Hinzufügen eines Felds zu einer kleinen Tabelle führt zum Absturz der gesamten Datenbank.

Sie müssen wissen, dass das Hinzufügen eines Felds zu einer Tabelle, das Ändern eines Felds oder das Hinzufügen eines Indexes das Scannen der gesamten Tabellendaten erfordert. Beim Arbeiten mit großen Tabellen müssen Sie besonders vorsichtig sein, um eine Beeinträchtigung der Onlinedienste zu vermeiden. Tatsächlich kann sogar eine kleine Uhr Probleme verursachen, wenn sie nicht sorgfältig bedient wird. Schauen wir uns die folgende Abfolge von Operationen an und gehen dabei davon aus, dass es sich bei Tabelle t um eine kleine Tabelle handelt.

show full processlist , um die Details zur MDL-Sperre anzuzeigen.

Wir können sehen, dass Sitzung A zuerst startet und der Tabelle t eine MDL-Lesesperre hinzugefügt wird. Da Sitzung B auch eine MDL-Lesesperre erfordert, kann sie normal ausgeführt werden.

Anschließend wird Sitzung C blockiert, da die MDL-Lesesperre von Sitzung A nicht freigegeben wurde, Sitzung C jedoch die MDL-Schreibsperre benötigt und daher nur blockiert werden kann.

Es ist keine große Sache, wenn nur Sitzung C blockiert ist, aber alle nachfolgenden Anforderungen für neue MDL-Lesesperren für Tabelle t werden auch von Sitzung C blockiert. Wie bereits erwähnt, müssen alle Vorgänge an der Tabelle zunächst eine MDL-Lesesperre beantragen, was bedeutet, dass die Tabelle nun vollständig unlesbar und unschreibbar ist.

Wenn die Abfrageanweisungen für eine bestimmte Tabelle häufig sind und der Client über einen Wiederholungsmechanismus verfügt, d. h. nach einem Timeout eine neue Sitzung gestartet und dann erneut angefordert wird, sind die Threads dieser Bibliothek bald voll.

Die MDL-Sperre in einer Transaktion wird zu Beginn der Anweisungsausführung angewendet, aber nicht sofort nach Ende der Anweisung freigegeben. Sie wird stattdessen freigegeben, nachdem für die gesamte Transaktion ein Commit ausgeführt wurde.
Hinweis: Im Allgemeinen unterliegen Zeilensperren einer Sperrzeitüberschreitung. Für die MDL-Sperre gibt es jedoch kein Zeitlimit und sie bleibt gesperrt, bis die Transaktion festgeschrieben ist.

2.2.1 So lösen Sie diese MDL-Sperre

Habe ich oben nicht gesagt, diese Transaktion festzuschreiben oder rückgängig zu machen? Um diese Transaktion zu finden

Wie finde ich diese Transaktion? Überprüfen Sie die Ausführungszeit der Transaktion über information_schema.innodb_trx

# Transaktionen anzeigen, die 60 Sekunden überschreitenmysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started gibt an, wann die Transaktion ausgeführt wurde. #Aktuelle Systemzeit anzeigenmysql> select now(); 

Die Startzeit der Transaktion und die aktuelle Systemzeit zeigen, dass die Transaktion über einen so langen Zeitraum ausgeführt wurde.

Überprüfen Sie diese Thread-ID

Wie gehe ich mit der Thread-ID dieser langen Transaktion um?

Sehen Sie sich zunächst das Hostfeld in der vollständigen Prozessliste an, um zu sehen, wer eine Verbindung zur Datenbank herstellt. Beispiel: Ich befinde mich in einer Localhost-Umgebung und gebe „Commit“ oder „Rollback“ ein. Wenn es sich nicht um eine Localhost-Umgebung handelt, ist das Programm verbunden und muss beendet werden.

2.2.2 Interessante Dinge, die mir passiert sind

Letztes Mal hat mich ein DBA gefragt, wie sich das Problem der großen Master-Slave-Verzögerung lösen lässt.

Ich habe Sie gefragt, wie Sie die Verzögerung gelöst haben. Kennen Sie die spezifischen Gründe für die Master-Slave-Verzögerung?

Er teilte mir mit, dass Multithreading aktiviert sei, die Verzögerung aber trotzdem noch sehr groß sei und Multithreading grundsätzlich nicht genutzt werde.

Ich fragte ihn, woher er wisse, dass die Master-Slave-Verzögerung durch die Aktivierung der Multithread-Replikation gelöst werden müsse, und er sagte mir, dass jemand anders dies in diesem Internet-Blog gesagt habe, was mich zum Bluten brachte.

Als ich ihn später fragte, welche Operationen ausgeführt wurden, als die Master-Slave-Verzögerung normal war, sagte er mir, dass er die ALTER-Table-Struktur geändert habe.

Dann bat ich ihn, nachzuschauen, ob die Ursache die MDL-Sperre war. Ich bat ihn, mir show full processlist , und es stellte sich heraus, dass es die MDL-Sperre war.

Sagen Sie ihm dann, er solle die lange Transaktion finden und besprechen Sie nach dem Auffinden mit dem Entwickler, ob der Vorgang dieser langen Transaktion abgebrochen werden kann.

Hinweis: Dies ist ein echtes Problem, auf das ich gestoßen bin, und jemand hat mir diese Frage gestellt. Zunächst müssen Sie wissen, was dieses Ergebnis verursacht hat und was Sie zuvor getan haben, und dann das Problem lösen. Am grundlegendsten ist es, die Ursache zu kennen und sie beim nächsten Mal zu vermeiden.

Vertrauen Sie außerdem nicht blind auf die Online-Umgebung, die Systemversion, die Anwendungsversion und das aufgetretene Problem.

2.3 Wie fügt man einer kleinen Tabelle sicher Felder hinzu?

Zuerst müssen wir das Problem langer Transaktionen lösen. Wenn die Transaktion nicht festgeschrieben wird, wird die MDL-Sperre belegt. In der innodb_trx-Tabelle der MySQL-Bibliothek information_schema können Sie die aktuell ausgeführten Transaktionen überprüfen. Wenn für die Tabelle, in der Sie DDL-Änderungen vornehmen möchten, eine lange Transaktion ausgeführt wird, sollten Sie zunächst den DDL anhalten oder die lange Transaktion abbrechen. Aus diesem Grund ist es notwendig, DDL-Änderungen außerhalb der Spitzenzeiten vorzunehmen. Natürlich müssen Sie auch überlegen, welche spezifische DDL Sie verwenden möchten, und sich auf die offizielle Online-DDL beziehen.

2.4 Online-DDL-Prozess

• MDL-Schreibsperre übernehmen
• Downgrade auf MDL-Lesesperre
• Mache tatsächlich DDL
• Upgrade auf MDL-Schreibsperre
• MDL-Sperre aufheben

1, 2, 4, 5 Wenn kein Sperrkonflikt vorliegt, ist die Ausführungszeit sehr kurz. Schritt 3 nimmt den größten Teil der DDL-Zeit in Anspruch. Während dieser Zeit kann die Tabelle normal Daten lesen und schreiben, daher wird sie als "online" bezeichnet.

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. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.