Ausführliche Erklärung des Sperrmechanismus in MySQL

Vorwort

Um die Konsistenz und Integrität der Daten zu gewährleisten, verfügt jede Datenbank über einen Sperrmechanismus. Die Vor- und Nachteile des Sperrmechanismus hängen direkt mit der gleichzeitigen Verarbeitungsfähigkeit und Leistung eines Datenbanksystems zusammen. Daher ist die Implementierung des Sperrmechanismus zu einer der Kerntechnologien verschiedener Datenbanken geworden.

Vor etwa einigen Monaten wurden im Projekt Transaktionen verwendet und eine starke Datenkonsistenz musste sichergestellt werden. Während dieser Zeit wurden auch MySQL-Sperren verwendet, aber zu diesem Zeitpunkt hatte ich nur einen flüchtigen Blick auf den Sperrmechanismus von MySQL, daher soll dieser Artikel den Sperrmechanismus von MySQL zusammenfassen.

In diesem Artikel wird hauptsächlich der MySQL-Sperrmechanismus behandelt. Die MySQL-Version ist 5.7 und die Engine ist InnoDB. Da es in der Praxis viel Wissen und viele Sperrmethoden im Zusammenhang mit InnoDB-Sperren gibt, reicht die Energie nicht aus, um den Sperrprozess in allen Szenarien aufzulisten und zu analysieren. Ich werde nur auf der Grundlage des aktuellen Wissens und in Kombination mit den offiziellen Dokumenten über mein Verständnis sprechen. Wenn Sie etwas Falsches finden, können Sie mich gerne korrigieren.

Überblick

Im Allgemeinen verfügt InnoDB über sieben Arten von Sperren:

• Gemeinsam genutzte und exklusive Sperren
• Absichtssperren
• Datensatzsperren
• Lückenschlösser
• Schlösser mit gleichschließendem Schlüssel
• Absichtssperren einfügen
• Auto-Inc-Schlösser

Detaillierte Erklärung der MySQL-Sperre

1. Gemeinsam genutzte und exklusive Sperren

• Beim Lesen von Daten werden gemeinsame Sperren (S-Locks) verwendet.
• Exklusive Sperren (eXclusive Locks, bezeichnet als X-Sperren), fügen Sie beim Ändern von Daten X-Sperren hinzu

Die verwendete Semantik ist:

• Gemeinsam genutzte Sperren schließen sich nicht gegenseitig aus. Dies kann wie folgt abgekürzt werden: Lesen und Lesen können parallel erfolgen.
• Exklusive Sperren schließen sich gegenseitig mit allen anderen Sperren aus, was wie folgt abgekürzt werden kann: Schreiben und Lesen, Schreiben und Schreiben können nicht parallel ausgeführt werden

Es ist ersichtlich, dass die Daten nicht von anderen Aufgaben gelesen werden können, wenn die Aufgabe des Schreibens der Daten nicht abgeschlossen ist, was große Auswirkungen auf die Parallelität hat. Entsprechend der Datenbank ist es verständlich, dass, wenn die Schreibtransaktion nicht festgeschrieben wird, auch die Auswahl zum Lesen verwandter Daten blockiert wird. Die Auswahl bezieht sich hier auf eine mit einer Sperre versehene Auswahl, und die normale Auswahl kann die Daten weiterhin lesen (Snapshot-Lesen).

2. Absichtssperren

InnoDB führte Intention-Locks ein, um Sperren mit mehrfacher Granularität zu unterstützen, wodurch Sperren auf Zeilenebene und Sperren auf Tabellenebene nebeneinander bestehen können. Eine Absichtssperre bedeutet, dass zu einem späteren Zeitpunkt für eine Transaktion möglicherweise eine gemeinsame/exklusive Sperre hinzugefügt werden muss, sodass eine Absicht im Voraus erklärt wird.

1. Die Absichtssperre ist eine Sperre auf Tabellenebene (Sperre auf Tabellenebene).

2. Absichtssperren werden unterteilt in:

• Intention Shared Lock (IS), das angibt, dass die Transaktion beabsichtigt, bestimmten Zeilen in der Tabelle gemeinsam genutzte S-Sperren hinzuzufügen;
• Absichtliche exklusive Sperre (IX), die angibt, dass die Transaktion beabsichtigt, bestimmten Zeilen in der Tabelle exklusive X-Sperren hinzuzufügen;

Die Syntax zum Sperren lautet:

Wählen Sie ... Sperre im Freigabemodus; Um die IS-Sperre einzustellen;
Wählen Sie ... zum Aktualisieren; Zum Festlegen der IX-Sperre;

Um S/X-Sperren für bestimmte Zeilen zu erhalten, muss eine Transaktion zuerst die IS/IX-Sperren erhalten, die der Tabelle entsprechen. Absichtssperren zeigen nur Absichten an. Absichtssperren sind miteinander kompatibel. Die kompatiblen und sich gegenseitig ausschließenden Tabellen sind wie folgt:

Obwohl Intention Locks miteinander kompatibel sind, schließen sie sich gegenseitig mit Shared Locks/Exklusiv Locks aus. Die Tabelle der Kompatibilitäten und gegenseitigen Ausschlüsse lautet wie folgt:

Exklusive Schlösser sind sehr starke Schlösser und nicht mit anderen Schlosstypen kompatibel. Dies ist eigentlich sehr leicht zu verstehen. Wenn Sie eine Zeile ändern oder löschen, müssen Sie eine starke Sperre erhalten, um andere gleichzeitige Zugriffe auf diese Zeile zu verhindern und die Datenkonsistenz sicherzustellen.

3. Datensatzsperren

Datensatzsperre, die beispielsweise den Indexdatensatz blockiert (wobei die ID pk ist):

Tabelle erstellen lock_example(id smallint(10),name varchar(20),Primärschlüssel-ID)engine=innodb;

Die Datenbankisolationsebene ist RR und die Tabelle enthält die folgenden Daten:

10, Zhangsan
20, Lisa
30, Wangwu

Wählen Sie * aus t, wobei ID=1 für die Aktualisierung ist;

Tatsächlich erhalten wir hier zuerst die beabsichtigte exklusive Sperre (IX) der Tabelle und dann die exklusive Sperre dieser Datensatzzeile (nach meinem Verständnis liegt das daran, dass der Index hier direkt getroffen wird), um zu verhindern, dass andere Transaktionen die Zeile mit der ID = 1 einfügen, aktualisieren und löschen.

4. Lückenschlösser

Lückensperren, die eine Lücke in einem Indexdatensatz oder den Bereich vor dem ersten Indexdatensatz oder den Bereich nach dem letzten Indexdatensatz blockieren. Immer noch unter Verwendung des obigen Beispiels, InnoDB, RR:

Wählen Sie * aus Lock_Example
wobei id zwischen 8 und 15 liegt 
zum Update;

Diese SQL-Anweisung blockiert das Intervall (8,15), um zu verhindern, dass andere Transaktionen Datensätze einfügen, deren ID in diesem Intervall liegt.

Der Hauptzweck von Lückensperren besteht darin, zu verhindern, dass andere Transaktionen Daten in das Intervall einfügen, was zu „nicht wiederholbaren Lesevorgängen“ führt. Wenn die Transaktionsisolationsebene auf Read Committed (RC) herabgestuft wird, wird die Gap-Sperre automatisch ungültig.

5. Schlösser mit nächstem Schlüssel

Eine temporäre Schlüsselsperre ist eine Kombination aus einer Datensatzsperre und einer Lückensperre. Ihr Sperrbereich umfasst sowohl Indexdatensätze als auch Indexintervalle.

Standardmäßig verwendet InnoDB Next-Key-Sperren, um Datensätze zu sperren. Wenn der abgefragte Index jedoch ein eindeutiges Attribut enthält, wird Next-Key Lock optimiert und auf Record Lock herabgestuft, was bedeutet, dass nur der Index selbst gesperrt wird, nicht der Bereich.

Beispielsweise ist die Tabelle lock_example immer noch dieselbe wie oben, aber id wird auf einen normalen Index (Schlüssel) herabgestuft. Das heißt, selbst wenn hier eine Sperre deklariert wird (für die Aktualisierung) und der Index erreicht wird, verwendet InnoDB Next-Key-Sperren und die Datenbankisolationsebene RR, da der Index hier keine UK-Einschränkung hat:

Transaktion A führt die folgende Anweisung aus, wird aber nicht festgeschrieben:

Wählen Sie * aus Lock_Example, wobei ID = 20 für Update ist;

Transaktion B startet und führt die folgende Anweisung aus, die blockiert:

in lock_example-Werte einfügen ('zhang', 15);

Im obigen Beispiel wird, nachdem Transaktion A die Abfrageanweisung ausgeführt hat, standardmäßig die Next-Key-Sperre zum Datensatz mit der ID=20 hinzugefügt, sodass Transaktion B beim Einfügen von Datensätzen zwischen 10 (einschließlich) und 30 (exklusiv) blockiert wird. Der Hauptzweck der temporären Tastensperre besteht darin, Phantomzugriffe zu verhindern. Wenn die Transaktionsisolationsebene auf RC herabgestuft wird, wird auch die temporäre Schlüsselsperre ungültig.

6. Absichtssperren einfügen

Um vorhandene Datenzeilen zu ändern und zu löschen, muss eine gegenseitige Ausschlusssperre (X-Sperre) verstärkt werden. Ist es also für die Dateneinfügung notwendig, eine so starke Sperre hinzuzufügen, um einen gegenseitigen Ausschluss zu implementieren? Setzen Sie das Absichtsschloss ein und bringen Sie Ihr Kind zur Welt.

Die Einfügeabsichtssperre ist eine Art Lückensperre (sie wird also auch auf dem Index implementiert), die speziell für Einfügevorgänge gedacht ist. Wenn mehrere Transaktionen Datensätze in denselben Index und Bereich einfügen, blockieren sie sich nicht gegenseitig, sofern es keine Konflikte zwischen den Einfügepositionen gibt.

Die Einfügeabsichtssperre signalisiert die Einfügeabsicht so, dass mehrere Transaktionen, die in dieselbe Indexlücke eingefügt werden, nicht aufeinander warten müssen, wenn sie nicht an derselben Position innerhalb der Lücke eingefügt werden.

Beispielsweise wird zuerst Transaktion A ausgeführt (die Tabelle ist immer noch lock_example und die Daten sind immer noch dieselben wie oben). Dabei wird eine Zeile in die Datensätze 10 und 20 eingefügt, die aber noch nicht festgeschrieben wurde:

in t-Werte (11, xxx) einfügen;

Transaktion B wird später ausgeführt und fügt auch eine Zeile in die Datensätze 10 und 20 ein:

in t-Werte einfügen (12, ooo);

Da es sich um einen Einfügevorgang handelt, kommt es trotz der Einfügungen im selben Intervall nicht zu Konflikten zwischen den eingefügten Datensätzen, sodass eine Einfügeabsichtssperre verwendet wird. Hier blockiert Transaktion A Transaktion B nicht.

7. Auto-Inc-Schlösser

Die Auto-Inkrement-Sperre ist eine spezielle Sperre auf Tabellenebene, die speziell für Transaktionen verwendet wird, die AUTO_INCREMENT-Spalten einfügen. Im einfachsten Fall, wenn eine Transaktion Datensätze in eine Tabelle einfügt, müssen alle anderen Transaktionen warten, damit die von der ersten Transaktion eingefügten Zeilen aufeinanderfolgende Primärschlüsselwerte haben.

Die AUTO-INC-Sperre ist eine spezielle Sperre auf Tabellenebene, die von Transaktionen ausgeführt wird, die in Tabellen mit AUTO_INCREMENT-Spalten einfügen. Im einfachsten Fall, wenn eine Transaktion Werte in die Tabelle einfügt, müssen alle anderen Transaktionen warten, um ihre eigenen Einfügungen in diese Tabelle vorzunehmen, damit die von der ersten Transaktion eingefügten Zeilen aufeinanderfolgende Primärschlüsselwerte erhalten.

Beispiel: (Die Tabelle ist immer noch lock_example wie oben), aber die ID ist AUTO_INCREMENT und die Daten in der Datenbanktabelle sind:

1, Zhangsan
2, Lisa
3, Wangwu

Transaktion A wird zuerst ausgeführt, aber noch nicht festgeschrieben: insert into t(name) values(xxx);

Nach Transaktion B ausführen: in t(Name) Werte(ooo) einfügen;

Zu diesem Zeitpunkt wird der Einfügevorgang der Transaktion B blockiert, bis Transaktion A festgeschrieben ist.

Zusammenfassen

Die oben zusammengefassten 7 Schlosstypen können auf zwei Arten unterschieden werden:

1. Je nach Grad des gegenseitigen Ausschlusses von Sperren können diese in gemeinsam genutzte und exklusive Sperren unterteilt werden.

• Gemeinsam genutzte Sperren (S-Sperren, IS-Sperren) können die gleichzeitige Lese- und Lesezugriffe verbessern.
• Um eine starke Datenkonsistenz zu gewährleisten, verwendet InnoDB starke Mutex-Sperren (X-Sperren, IX-Sperren), um die Serialität der Änderung und Löschung derselben Datensatzzeile sicherzustellen.

2. Je nach Granularität des Schlosses kann es unterteilt werden in:

• Tabellensperre: Absichtssperre (IS-Sperre, IX-Sperre), Selbstinkrementierungssperre;
• Zeilensperren: Datensatzsperre, Lückensperre, temporäre Schlüsselsperre, Einfügeabsichtssperre;

In

• Die feinkörnigen Sperren von InnoDB (d. h. Zeilensperren) werden auf Indexdatensätzen implementiert (meines Wissens schlagen sie fehl, wenn der Index nicht erreicht wird).
• Die Datensatzsperre sperrt den Indexdatensatz; die Lückensperre sperrt das Intervall, um das Einfügen anderer Transaktionen in das Intervall zu verhindern; die temporäre Schlüsselsperre sperrt den Indexdatensatz + Intervall, um Phantomlesevorgänge zu verhindern;
• InnoDB verwendet Einfügesperren, um die Einfügeparallelität zu verbessern.
• Gap Lock und Next-Key Lock sind nur auf Ebenen über RR wirksam und schlagen bei RC fehl.

Nun, 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.