Was Sie über MySQL-Sperren wissen müssen

1. Einleitung

MySQL-Sperren können je nach Umfang in globale Sperren, Tabellensperren und Zeilensperren unterteilt werden. Zeilensperren werden von der Datenbank-Engine implementiert. Nicht alle Engines bieten Zeilensperren. MyISAM unterstützt keine Zeilensperren, daher wird in diesem Artikel die InnoDB-Engine als Beispiel zur Einführung in Zeilensperren verwendet.

2. Globale Sperre

MySQL bietet eine globale Sperre, um die gesamte Datenbankinstanz zu sperren.

Grammatik:

FLUSH-TABELLEN MIT LESE-SPERRE

Diese Anweisung wird im Allgemeinen zur Sicherung verwendet. Wenn diese Anweisung ausgeführt wird, werden alle offenen Tabellen in der Datenbank geschlossen und alle Tabellen in der Datenbank mit einer globalen Lesesperre gesperrt. Gleichzeitig werden Aktualisierungsanweisungen (Hinzufügungen, Löschungen und Änderungen) anderer Threads, Datendefinitionsanweisungen (Erstellen von Tabellen, Ändern von Tabellenstrukturen) und Aktualisierungstransaktionsübermittlungen blockiert.

Nach MySQL 8.0 kann MySQL die Sicherungssperre direkt für die Sicherung verwenden.

Stellungnahme:

INSTANZEN FÜR BACKUP sperrenINSTANZEN sperren

Diese Sperre hat einen größeren Umfang. Sie verhindert das Erstellen, Umbenennen und Löschen von Dateien, einschließlich REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE -Vorgängen und der Kontoverwaltung. Natürlich können diese Operationen auch auf temporären Speichertabellen ausgeführt werden. Warum unterliegen Speichertabellen nicht diesen Einschränkungen? Da Speichertabellen nicht gesichert werden müssen, müssen diese Bedingungen nicht erfüllt werden.

3. Tischsperre

Die Sperren auf Tabellenebene von MySQL sind in zwei Kategorien unterteilt: eine ist die Metadatensperre (MDL) und die andere ist die Tabellensperre.

Metadatensperren (MDL) müssen nicht explizit verwendet werden und werden automatisch beim Zugriff auf eine Tabelle erworben. Zur Unterstützung dieser Funktion ist MySQL Version 5.5 oder höher erforderlich. Beim Hinzufügen, Löschen, Ändern oder Überprüfen einer Tabelle wird der Tabelle eine MDL-Lesesperre hinzugefügt; wenn die Tabellenstruktur geändert wird, wird eine MDL-Schreibsperre hinzugefügt. Für MDL-Sperren gelten einige Regeln:

Lesesperren schließen sich nicht gegenseitig aus, sodass mehrere Threads dieselbe Tabelle ergänzen, löschen, ändern und abfragen können. Lese-/Schreibsperren und Schreibsperren schließen sich gegenseitig aus. Um die Sicherheit von Änderungen in der Tabellenstruktur zu gewährleisten, werden die Operationen in der Tabellenstruktur, wie z. B. das Hinzufügen von Feldern zur gleichen Tabelle, serialisiert und erfordern ein Warten auf die Sperre, wenn mehrere Threads sie ausführen müssen. Die Schreibsperrenpriorität von MDL ist höher als die Lesesperrenpriorität von MDL, aber Sie können die Systemvariable max_write_lock_count festlegen, um diese Situation zu ändern. Wenn die Schreibsperrenanforderung die von dieser Variablen festgelegte Zahl überschreitet, ist die MDL-Lesesperrenpriorität höher als die MDL-Schreibsperrenpriorität. (Standardmäßig ist diese Zahl hoch, Sie müssen sich also keine Sorgen über eine Verringerung der Schreibsperrenpriorität machen.) Die MDL-Sperre darf erst nach Abschluss der Transaktion freigegeben werden.

Daher müssen wir beim Bedienen der Datenbanktabellenstruktur darauf achten, keine langen Transaktionen zu verwenden. Was bedeutet das konkret? Lassen Sie mich Ihnen ein Beispiel geben:

Die obige Abbildung zeigt die Ausführung von Anweisungen durch vier Sitzungen. Zuerst startet Sitzung A eine Transaktion, führt sie aber nicht aus. Dann führt Sitzung B eine Abfrage aus. Da sie die MDL-Lesesperre erhalten, beeinflussen sie sich nicht gegenseitig und können normal ausgeführt werden. Sitzung C fügt ein Feld hinzu. Da MDL-Schreiben und -Lesen sich gegenseitig ausschließen, wird Sitzung C blockiert. Dann beginnt Sitzung D mit der Ausführung einer Abfrageanweisung. Da Sitzung C blockiert ist, ist auch Sitzung D blockiert. Daher ist die von uns simulierte Transaktion von SessionA eine lange Transaktion, und dann wird die Tabellenstruktur geändert, was alle nachfolgenden Lese- und Schreibvorgänge für die Tabelle unmöglich macht. Daher können in tatsächlichen Szenarien bei häufigen Geschäftsanforderungen Änderungen an der Tabellenstruktur dazu führen, dass die Threads der Bibliothek blockiert werden.

Die Syntax für Tabellensperren lautet wie folgt:

TABELLEN SPERREN Tabellenname [[AS] Alias] Sperrtyp [, Tabellenname [[AS] Alias] Sperrtyp] ... Sperrtyp: { LESEN [LOKAL] | [NIEDRIGE_PRIORITÄT] SCHREIBEN} TABELLEN ENTSPERREN

Tabellensperren werden in Lesesperren und Schreibsperren unterteilt. Lesesperren schließen sich nicht gegenseitig aus, aber nach Erhalt einer Lesesperre können keine Daten mehr geschrieben werden. Andere Sitzungen, die keine Lesesperre erhalten haben, können die Tabelle ebenfalls lesen. Der Zweck einer Lesesperre besteht also darin, das Schreiben in die Tabelle zu verhindern. Wenn die Tabelle durch eine Lesesperre gesperrt ist, wird beim Ausführen der Insert-Anweisung ein Fehler gemeldet. Der Fehler lautet wie folgt:

1099 - Tabelle 'XXXX' wurde mit einer Lesesperre gesperrt und kann nicht aktualisiert werden

Nachdem die Schreibsperre aktiviert wurde, kann die Tabelle gelesen und beschrieben werden. Die Schreibsperre schließt sich gegenseitig aus. Sobald eine Sitzung die Schreibsperre einer Tabelle aktiviert hat, können andere Sitzungen nicht mehr auf die Tabelle zugreifen, bis die Schreibsperre aufgehoben wird.

Die Tabelle kann mithilfe von unlock tables oder automatisch über den Client-Port entsperrt werden. lock tables von Tabellen wird die Tabelle exklusiv gesperrt. Dadurch werden nicht nur andere Threads am Lesen und Schreiben der Tabelle gehindert, sondern auch das nächste Operationsobjekt dieses Threads wird eingeschränkt.

4. Zeilensperre (InnoDB)

MySQL-Zeilensperren werden auf Engine-Ebene implementiert, daher besprechen wir hier Zeilensperren unter der InnoDB-Engine. Im Folgenden werden einige gängige Zeilensperren unter InnoDB ausführlich vorgestellt.

4.1 Gemeinsam genutzte Sperren

Gemeinsam genutzte Sperren ermöglichen Transaktionen, Lesevorgänge auszuführen, nachdem die Sperre erhalten wurde. Gemeinsam genutzte Sperren schließen sich nicht gegenseitig aus. Nachdem eine Transaktion eine gemeinsam genutzte Sperre erhalten hat, kann eine andere Transaktion diese Sperre ebenfalls erhalten. Schreibvorgänge können nach dem Erhalt einer gemeinsam genutzten Sperre nicht mehr ausgeführt werden.

4.2 Exklusive Sperre

Eine exklusive Sperre ermöglicht einer Transaktion, eine Zeile zu aktualisieren oder zu löschen, nachdem die Sperre erhalten wurde. Wie der Name schon sagt, ist eine exklusive Sperre gegenseitig exklusiv. Nachdem eine Transaktion eine exklusive Sperre erhalten hat, können andere Transaktionen die exklusive Sperre erst erhalten, wenn die Sperre freigegeben wird.

4.3 Absichtssperre

InnoDB unterstützt Sperren mit mehreren Granularitäten, sodass Zeilensperren und Tabellensperren nebeneinander bestehen können. Die hier erwähnte beabsichtigte Sperre ist eigentlich eine Sperre auf Tabellenebene, aber ich habe sie in die Zeilensperre eingefügt, da sie nicht allein existieren wird. Ihr Auftreten wird definitiv von einer Zeilensperre (gemeinsame Sperre oder exklusive Sperre) begleitet. Ihr Hauptzweck besteht darin, anzuzeigen, dass die Zeilen in der Tabelle gesperrt werden oder gesperrt werden.

Intention Locks lassen sich je nach ihrer Kombination mit Zeilensperren in folgende Typen unterteilen:

• Absichtliche exklusive Sperre: Gibt an, dass für bestimmte Zeilen in der Tabelle exklusive Sperren erworben werden.
• Absichtliche gemeinsame Sperre: Gibt an, dass für bestimmte Zeilen in der Tabelle gemeinsame Sperren erworben werden.

Der Erwerb der Absichtssperre muss vor dem Erwerb der Zeilensperre erfolgen, d. h. die gemeinsam genutzte Absichtssperre muss vor der gemeinsam genutzten Sperre erworben werden, und dasselbe gilt für die exklusive Sperre.

Was also genau macht diese Absichtssperre?

Bevor wir dies erklären, werfen wir einen Blick auf die Kompatibilitätsbeziehung zwischen Absichtssperren und Zeilensperren: