Spezifische Verwendung von MySQL-Globalsperren und Sperren auf Tabellenebene

Vorwort

Wenn ich MySQL in einer echten Unternehmensentwicklungsumgebung verwende, bin ich definitiv nicht der Einzige, der MySQL verwendet. Stattdessen verwendet ein Team MySQL explizit oder das Unternehmen verwendet MySQL implizit. Wenn sich also mehrere Benutzer oder Clients verbinden, um es zu verwenden, sollten wir uns eine Frage stellen: Wie kann die Konsistenz des gleichzeitigen Datenzugriffs sichergestellt werden? In diesem Artikel werde ich über MySQL-Sperren sprechen, nicht über MySQL-Transaktionsisolationsebenen.

Globale Sperre

Die globale Sperre von MySQL schließt alle offenen Tabellen und macht alle Tabellen schreibgeschützt. Die Befehle lauten:

# Globale Sperre, bezeichnet als FTWRL
FLUSH-TABELLEN MIT LESE-SPERRE;

# Entsperrbefehl UNLOCK TABLES;

Experimente mit FTWRL: (Alle folgenden Experimente wurden in MySQL 8.0.22 durchgeführt)

Aus den obigen Experimenten können wir schließen, dass nach der Ausführung von FTWRL alle Tabellen schreibgeschützt sind und andere Aktualisierungsvorgänge blockiert werden.

Die Hauptfunktion der globalen Sperre besteht darin, eine logische Sicherung der gesamten Datenbank zu erstellen, d. h. jede Tabelle in der Datenbank auszuwählen und als Text zu speichern.

Während des Sicherungsvorgangs ist die gesamte Datenbank schreibgeschützt und das Risiko ist extrem hoch. Wenn die Sicherung auf der Master-Datenbank durchgeführt wird, können in keiner der Geschäftstabellen die Daten geändert werden. Wenn die Sicherung auf der Slave-Datenbank durchgeführt wird, kann die Slave-Datenbank das von der Master-Datenbank gesendete Binärprotokoll nicht ausführen, was zu einer Verzögerung zwischen der Master- und der Slave-Datenbank führt.

Glücklicherweise unterstützt die InnoDB-Speicher-Engine Transaktionen und mysqldump verfügt über einen Parameter „Single-Transaction“, der in der Transaktion einen konsistenten Snapshot erstellen und anschließend alle Tabellen sichern kann. Mit diesem Parameter können Daten während der Sicherung geändert werden. Daher wird empfohlen, bei der normalen Entwicklung die InnoDB-Speicher-Engine zu verwenden.

Tabellensperre

Es gibt zwei Arten von Sperren auf Tabellenebene: Tabellensperre und Metadatensperre.

Tabellensperre

Tabellensperren werden in Tabellenlesesperren und Tabellenschreibsperren unterteilt. Die Befehle in MySQL lauten:

# Tabelle lesen, sperren, Tabellen sperren, testen, lesen;

# Tabelle schreiben, sperren, Tabellen testen, schreiben;

Als nächstes wollen wir uns durch Experimentieren den Unterschied zwischen der Tabellenlesesperre und der Tabellenschreibsperre ansehen.

Tabellenlesesperre

Der Sitzung von Sitzung1 wird eine Tabellenlesesperre hinzugefügt. Zu diesem Zeitpunkt können sowohl Sitzung1 als auch Sitzung2 normal Daten lesen, aber Sitzung1 meldet beim Schreiben von Daten einen Fehler und Sitzung2 wird beim Schreiben von Daten blockiert. Sitzung2 kann Daten erst erfolgreich schreiben, nachdem Sitzung1 entsperrt wurde.

Aus diesem Experiment können wir schließen, dass dieser Thread und andere Threads nach dem Sperren der Tabelle Daten lesen können, dieser Thread beim Schreiben von Daten einen Fehler meldet und andere Threads beim Schreiben von Daten blockiert werden.

Tabellenschreibsperre

Aus den obigen Experimenten können wir schließen, dass der aktuelle Thread nach dem Sperren der Tabelle Lese- und Schreibvorgänge ausführen kann und die Lese- und Schreibvorgänge anderer Threads blockiert werden.

Metadatensperre (MDL-Sperre)

In MySQL gehört die Datenbank-DDL nicht zum Transaktionsbereich. Wenn Sie in Sitzung1 eine Datenzeile auswählen, fügt Sitzung2 dieser Tabelle eine Spalte xxx hinzu. Zu diesem Zeitpunkt können Fehler wie die Zerstörung von Transaktionsmerkmalen und die Störung der Binlog-Reihenfolge auftreten (ähnliche Fehler wurden auf der offiziellen MySQL-Website angekündigt. Sie können sich bei Interesse darüber informieren).

Um die oben genannten Probleme zu lösen, wurden in MySQL 5.5.3 Metadatensperren eingeführt. MDL-Sperren müssen nicht explizit verwendet werden, MySQL fügt sie standardmäßig hinzu. Ihre Funktion besteht darin, die Richtigkeit des Lesens und Schreibens in der Datenbank sicherzustellen. Im Folgenden wird MDL zur Darstellung der Metadatensperre verwendet.

Wenn Sie eine Tabelle hinzufügen, löschen, abfragen oder ändern, wird standardmäßig eine MDL-Lesesperre hinzugefügt. Wenn Sie die Tabellenstruktur einer Tabelle ändern, wird standardmäßig eine MDL-Schreibsperre hinzugefügt.

Wenn Sitzung1 zu Beginn den Test abfragt, erhält sie die MDL-Lesesperre und kann die Daten normal abfragen. Dann erhält Sitzung2 beim Abfragen von Daten auch die MDL-Lesesperre, sodass kein Konflikt auftritt und die Daten normal abgefragt werden können.

Wenn jedoch Sitzung 3 erreicht wird, muss die MDL-Schreibsperre erworben werden. Zu diesem Zeitpunkt wird sie blockiert, da die MDL-Lesesperre von Sitzung 1 nicht freigegeben wurde. Später benötigt Sitzung 4 auch eine MDL-Lesesperre, aber da Sitzung 3 blockiert ist, wird Sitzung 4 ebenfalls blockiert.

Handelt es sich um eine Online-Geschäftstabelle, werden durch dieses Szenario alle nachfolgenden Vorgänge ungültig und die Tabelle wird unlesbar und unschreibbar. Wenn der Client den MySQL-Wiederholungsmechanismus konfiguriert, wird bei Auftreten des Timeouts eine Sitzung wiederhergestellt und die Anforderung erneut gestellt. Anschließend stürzt MySQL aufgrund des kontinuierlichen Hinzufügens neuer Threads ab.

Aus dem obigen Beispiel können wir erkennen, dass die MDL-Sperre standardmäßig hinzugefügt wird, wenn die Anweisung ausgeführt wird, aber nach der Ausführung der Anweisung nicht freigegeben wird. Die MDL-Sperre wird erst freigegeben, nachdem die gesamte Transaktion festgeschrieben wurde.

Daher sollten wir Entwickler versuchen, langsame Abfragen zu vermeiden, sicherzustellen, dass Transaktionen rechtzeitig übermittelt werden, große Transaktionen zu vermeiden usw. Als DBA sollten wir außerdem versuchen, die Durchführung von DDL-Vorgängen während der Geschäftsspitzenzeiten zu vermeiden.

Zusammenfassen

• Durch eine globale Sperre werden alle Tabellen schreibgeschützt und sämtliche Aktualisierungsvorgänge werden blockiert.
• Die Tabellenlesesperre bedeutet, dass sowohl dieser Thread als auch andere Threads sie lesen können. Wenn dieser Thread schreibt, wird ein Fehler gemeldet und andere Threads werden blockiert.
• Die Tabellenschreibsperre ermöglicht diesem Thread das Lesen und Schreiben, während das Lesen und Schreiben für andere Threads blockiert wird.
• Einführung von MDL-Sperren zur Behebung von Fehlern, die durch die gleichzeitige Ausführung von Transaktionen und DDL verursacht werden

Verweise

• MySQL in Depth, Zweite Ausgabe: 20.3.8 Wann werden Tabellensperren verwendet?
• "MySQL-Praxis 45 Vorlesungen" von Lin Xiaobin
  

Damit ist dieser Artikel über die spezifische Verwendung von globalen MySQL-Sperren und Sperren auf Tabellenebene abgeschlossen. Weitere Informationen zu globalen MySQL-Sperren und Sperren auf Tabellenebene finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!