Ausführliche Erläuterung der InnoDB-Sperren in der MySQL-Technologie

Vorwort

Seit ich im Juli den Arbeitsplatz gewechselt habe, lerne ich etwas über MySQL und habe mir einige Videokurse angehört, aber ich war immer neugierig, woher die Dozenten ihr Wissen hatten. Also dachte ich darüber nach, die Antwort in Büchern zu suchen. Schließlich,

Auch das Anschauen von Videos stellt keine Lösung dar, da es nicht zur eigenen Wissensbildung beiträgt. Also dachte ich darüber nach, Bücher zu lesen, um mir mein Wissen anzueignen, und las mehrere Bücher zu MySQL, darunter „Mysql in Simple Terms“, „High Performance Mysql“ und „Mysql Technology Insider“.

Die Inhalte, die der Lehrer vermittelte, standen tatsächlich im Buch, daher war ich davon überzeugt, dass das Lesen von Büchern der richtige Weg zum Wissenserwerb ist. Dieser Film dokumentiert hauptsächlich die Untersuchung des Sperrmechanismus von MySQL.

1. Was ist ein Schloss?

Eine Sperre ist ein Computermechanismus, der den gleichzeitigen Zugriff mehrerer Prozesse oder Threads auf eine Ressource koordiniert. In einer Datenbank besteht neben der Konkurrenz um herkömmliche Computerressourcen (wie CPU, RAM, E/A usw.) auch die Gefahr, dass Daten von vielen Benutzern gemeinsam genutzt werden.

Wie die Konsistenz und Effektivität des gleichzeitigen Datenzugriffs sichergestellt werden kann, ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der die Leistung des gleichzeitigen Zugriffs auf die Datenbank beeinträchtigt.

Im Vergleich zu anderen Datenbanken ist der Sperrmechanismus von MySQL relativ einfach. Sein bemerkenswertestes Merkmal ist, dass verschiedene Speicher-Engines unterschiedliche Sperrmechanismen unterstützen. Beispielsweise verwenden die Speicher-Engines MyISAM und MEMORY Sperren auf Tabellenebene.

Die BDB-Speicher-Engine verwendet Sperren auf Seitenebene, unterstützt aber auch Sperren auf Tabellenebene. Die InnoDB-Speicher-Engine unterstützt sowohl Sperren auf Zeilenebene als auch auf Tabellenebene, verwendet aber standardmäßig Sperren auf Zeilenebene.

Die Eigenschaften dieser drei Arten von MySQL-Sperren können grob wie folgt zusammengefasst werden.

• Sperre auf Tabellenebene: geringer Overhead, schnelle Sperrung, kein Deadlock, hohe Sperrgranularität, höchste Wahrscheinlichkeit eines Sperrkonflikts und geringste Parallelität.
• Sperre auf Zeilenebene: Hoher Overhead, langsame Sperrung; es kann zu Deadlocks kommen; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit eines Sperrkonflikts am geringsten und die Parallelität am höchsten.
• Seitensperre: Der Overhead und die Sperrzeit liegen zwischen der Tabellensperre und der Zeilensperre. Es können Deadlocks auftreten. Die Sperrgranularität liegt zwischen der Tabellensperre und der Zeilensperre und die Parallelität ist durchschnittlich.

3 Verwendungswinkel des Schlosses:

• Sperren auf Tabellenebene eignen sich besser für Anwendungen, die abfrageorientiert sind und Daten nur basierend auf Indexbedingungen aktualisieren, wie z. B. Webanwendungen.
• Zeilensperren eignen sich besser für Anwendungen mit einer großen Anzahl gleichzeitiger Aktualisierungen einer kleinen Menge unterschiedlicher Daten basierend auf Indexbedingungen und gleichzeitigen Abfragen, wie z. B. einige Systeme zur Online-Transaktionsverarbeitung (OLTP).
• Die Seitensperre von BDB wurde durch InnoDB ersetzt und wird hier nicht erläutert.

2. Sperren in der InnoDB-Speicher-Engine

2.1 Arten von Schlössern

Die InnoDB-Speicher-Engine implementiert die folgenden zwei Standardsperren auf Zeilenebene:

• Mit einer gemeinsamen Sperre (S Lock) kann eine Transaktion eine Datenzeile lesen.
• Mit einer exklusiven Sperre (X Lock) kann eine Transaktion eine Datenzeile löschen oder aktualisieren.

Wenn eine Transaktion T1 bereits eine gemeinsame Sperre für Zeile r erhalten hat, kann eine andere Transaktion T2 sofort eine gemeinsame Sperre für Zeile r erhalten, da der Lesevorgang die Daten in Zeile r nicht ändert. Diese Situation nennt man

Es ist Schloss-kompatibel (Lock Compatible). Wenn jedoch eine andere Transaktion T3 eine exklusive Sperre für Zeile r erhalten möchte, muss sie warten, bis die Transaktionen T1 und T2 die gemeinsame Sperre für Zeile r freigeben – diese Situation wird als Sperreninkompatibilität bezeichnet.

Darüber hinaus unterstützt die InnoDB-Speicher-Engine mehrgranulare Sperren, die es Transaktionen ermöglichen, gleichzeitig Zeilensperren und Tabellensperren aufrechtzuerhalten. Um Sperrvorgänge auf unterschiedlichen Granularitäten zu unterstützen,

Die Speicher-Engine unterstützt eine zusätzliche Sperrmethode namens Intention Locking. Absichtssperren unterteilen gesperrte Objekte in mehrere Ebenen. Absichtssperren bedeuten, dass eine Transaktion mit einer feineren Granularität gesperrt werden möchte. InnoDB-Speicher

Die Speicher-Engine unterstützt Absichtssperren mit einem einfachen Design und ihre Absichtssperren sind Sperren auf Tabellenebene. Es dient in erster Linie dazu, den Sperrtyp anzuzeigen, der für die nächste Zeile in einer Transaktion erworben wird. Es werden zwei Arten von Absichtssperren unterstützt:

• Absichtliche gemeinsame Sperre (IS Lock), die Transaktion möchte eine gemeinsame Sperre für bestimmte Zeilen in einer Tabelle erhalten
• Absichtliche exklusive Sperre (IX Lock), die Transaktion möchte exklusive Sperren für bestimmte Zeilen in einer Tabelle erhalten

2.2 Konsistentes Lesen ohne Sperren

Ein konsistentes, nicht sperrendes Lesen ist eine Methode, bei der die InnoDB-Speicher-Engine Zeilen in der Datenbank zum aktuellen Ausführungszeitpunkt unter Verwendung mehrerer Versionierungen liest.

Daten. Wenn für die gelesene Zeile ein Lösch- oder Aktualisierungsvorgang ausgeführt wird, wartet der Lesevorgang nicht auf die Freigabe der Zeilensperre. Stattdessen liest die InnoDB-Speicher-Engine einen Snapshot der Zeilen.

Version. Wie unten gezeigt.

Die obige Abbildung zeigt intuitiv das konsistente, nicht sperrende Lesen der InnoDB-Speicher-Engine. Dieser Vorgang wird als nicht sperrender Lesevorgang bezeichnet, da nicht auf die Freigabe der X-Sperre für die aufgerufene Zeile gewartet werden muss. Snapshot-Daten beziehen sich auf die vorherige Version der Zeile

Die Daten werden im Undo-Segment gespeichert. Mit „Undo“ werden Daten in einer Transaktion zurückgesetzt, sodass kein zusätzlicher Overhead für die Snapshot-Daten selbst entsteht. Darüber hinaus ist es nicht erforderlich, die Snapshot-Daten zu sperren.

Weil es keine Transaktion gibt, die historische Daten ändern muss.

Aus der obigen Abbildung können wir erkennen, dass Snapshot-Daten tatsächlich die historische Version vor den aktuellen Zeilendaten sind. Jeder Zeilendatensatz kann mehrere Versionen haben. Diese Technologie wird allgemein als Zeilen-Multiversionstechnologie bezeichnet. Die daraus resultierende Parallelitätskontrolle

Es heißt Multi Version Concurrency Control (MVCC).

Auf den Transaktionsisolationsebenen READ COMMITTED und REPEATABLE READ verwendet die InnoDB-Speicher-Engine konsistente Lesevorgänge ohne Sperren. Die Definition von Snapshot-Daten ist jedoch anders. In LESEN

Auf der Transaktionsisolationsebene COMMITTED werden bei einem inkonsistenten Lesevorgang für Snapshot-Daten immer die neuesten Snapshot-Daten der gesperrten Zeile gelesen. Auf der Transaktionsisolationsebene REPEATABLE READ für Snapshots

Daten: Bei einem inkonsistenten Lesevorgang wird immer die Zeilendatenversion zu Beginn der Transaktion gelesen. Die folgende Tabelle zeigt ein Beispiel:

Vorausgesetzt, der Originaldatensatz mit der ID = 1 ist vorhanden, können Sie die entsprechenden Sitzungen in der chronologischen Reihenfolge der obigen Tabelle ausführen, um die beiden zu vergleichen und die Unterschiede zu überprüfen.

2.3 Konsistentes Sperren des Lesezugriffs

Wenn in der Standardkonfiguration die Transaktionsisolationsebene auf den Modus „REPEATABLE READ“ eingestellt ist, verwendet der Auswahlvorgang der InnoDB-Speicher-Engine konsistente Lesevorgänge ohne Sperren. In manchen Fällen müssen Benutzer jedoch

Um die Konsistenz der Datenlogik sicherzustellen, wird der Datenbanklesevorgang gesperrt. Dies erfordert, dass die Datenbank Sperranweisungen unterstützt, auch für schreibgeschützte Auswahlvorgänge. Die InnoDB-Speicher-Engine unterstützt zwei

Ein konsistenter Lesevorgang mit Sperren:

• Wählen Sie ··· zum Aktualisieren
• auswählen ··· im Freigabemodus sperren

select ··· for update fügt dem gelesenen Zeilendatensatz eine X-Sperre hinzu. Andere Transaktionen können der gesperrten Zeile keine Sperren hinzufügen. select ··· lock im Share-Modus fügt dem gelesenen Zeilendatensatz eine S-Sperre hinzu, und andere Transaktionen können

Sie können der gesperrten Zeile ein S-Schloss hinzufügen. Wenn Sie jedoch ein X-Schloss hinzufügen, wird diese blockiert.

Bei konsistenten Lesevorgängen ohne Sperren kann die gelesene Zeile auch dann gelesen werden, wenn für die Zeile „Select... for Update“ ausgeführt wurde. Darüber hinaus muss die Option ··· für Update oder die Option ··· Sperren im Freigabemodus ausgewählt sein.

Bei einer Transaktion wird die Sperre aufgehoben, wenn die Transaktion festgeschrieben wird. Achten Sie daher bei Verwendung der beiden oben genannten Select-Locking-Anweisungen darauf, „begin“, „start transaction“ hinzuzufügen oder „autocommit=0“ festzulegen.

3 Sperralgorithmen

3.1 Drei Zeilensperralgorithmen

Die InnoDB-Speicher-Engine verfügt über drei Zeilensperralgorithmen:

• Datensatzsperre: Sperre für einen einzelnen Zeilendatensatz
• Lückensperre: Lückensperre, sperrt einen Bereich, schließt aber nicht den Datensatz selbst ein.
• Next-Key Lock: Gap Lock + Record Lock, sperrt einen Bereich und sperrt den Datensatz selbst

Record Lock sperrt immer den Primärschlüsselindex-Datensatz. Wenn die InnoDB-Speicher-Engine-Tabelle bei ihrer Erstellung keinen Primärschlüssel oder eindeutigen, nicht leeren Index hat, verwendet die InnoDB-Speicher-Engine eine implizite

Hauptschlüssel zum Abschließen.

Next-Key Lock ist ein Sperralgorithmus, der Gap Lock und Record Lock kombiniert. Unter dem Next-Key Lock-Algorithmus verwendet InnoDB diesen Sperralgorithmus für Zeilenabfragen. Wenn ein Index 10, 11 hat

, 13 und 20, dann ist der mögliche Bereich der Next-Key-Sperre für diesen Index:

(-unendlich, 10], (10, 11], (11, 13], (13, 20], (20, +unendlich)

Die Schließtechnologie mit Next-Key Lock wird Next-Key Locking genannt. Es wurde entwickelt, um das Problem des Phantomlesens zu lösen. Bei dieser Sperrtechnik wird nicht ein einzelner Wert, sondern ein Bereich gesperrt. Jedoch,

Wenn der abgefragte Index ein eindeutiges Attribut enthält, optimiert die InnoDB-Speicher-Engine die Next-Key-Sperre und stuft sie zu einer Datensatzsperre herab, die nur den Index selbst, nicht aber den Bereich sperrt. Nachfolgend sehen Sie ein Beispiel.

mysql> Tabelle t erstellen (ein int-Primärschlüssel);
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)
​
mysql> in t einfügen, auswählen 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Datensätze: 1 Duplikate: 0 Warnungen: 0
​
mysql> in t einfügen, auswählen 2;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Datensätze: 1 Duplikate: 0 Warnungen: 0
​
mysql> in t einfügen, auswählen 5;
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)
Datensätze: 1 Duplikate: 0 Warnungen: 0

Führen Sie dann die Vorgänge in der in der folgenden Tabelle angegebenen chronologischen Reihenfolge aus.

Tabelle t hat drei Werte: 1, 2 und 5. Im obigen Beispiel wird in Sitzung A zuerst a=5 X-gesperrt. Da a der Primärschlüssel und eindeutig ist, wird nur der Wert 5 gesperrt, nicht der Bereich (2,5).

Das Einfügen des Werts 4 in B führt nicht zu einer Blockierung, er kann sofort eingefügt und zurückgegeben werden. Das heißt, die Sperre wird vom Next-Key-Lock-Algorithmus auf die Record-Lock-Sperre herabgestuft, wodurch die Parallelität der Anwendung verbessert wird.

Wie oben erwähnt, wird Next-Key Lock nur dann zu Record Lock herabgestuft, wenn die abgefragte Spalte ein eindeutiger Index ist. Bei Hilfsindexen ist die Situation völlig anders. Analog dazu erstellen wir zunächst zum Testen eine Testtabelle z:

mysql> erstelle Tabelle z (a int, b int, Primärschlüssel(a), Schlüssel(b));
mysql> in z einfügen, Auswahl 1,1;
mysql> in z einfügen, select 3,1;
mysql> in z einfügen, select 5,3;
mysql> in z einfügen, Auswahl 7,6;
mysql> in z einfügen, select 10,8;

Spalte b der Tabelle z ist ein Sekundärindex. Wenn die folgende SQL-Anweisung in Sitzung A ausgeführt wird:

mysql> select * from z where b = 3 für Update;

Offensichtlich fragt die SQL-Anweisung die Indexspalte b ab, sodass sie mit der herkömmlichen Next-Key-Locking-Technologie gesperrt wird, und da es zwei Indizes gibt, müssen diese separat gesperrt werden. Bei einem Clustered Index werden nur die Spalten

a entspricht dem Index 5 plus Record Lock. Für Hilfsindizes wird Next-Key Lock hinzugefügt und der Sperrbereich beträgt (1, 3). Es ist besonders wichtig zu beachten, dass die InnoDB-Speicher-Engine auch den nächsten Schlüssel des Hilfsindex sperrt.

Der Schlüsselwert plus die Lückensperre bedeutet, dass es auch eine Sperre mit einem Hilfsindexbereich von (3, 6) gibt. Wenn daher die folgenden SQL-Anweisungen in der neuen Sitzung B ausgeführt werden, werden sie blockiert:

mysql> select * from z, wobei a = 5 im Freigabemodus gesperrt ist;
mysql> in z einfügen, select 4,2;
mysql> in z einfügen, select 6,5;

Die erste SQL-Anweisung kann nicht ausgeführt werden, da die in Sitzung A ausgeführte SQL-Anweisung eine X-Sperre zum Wert der Spalte a=5 im gruppierten Index hinzugefügt hat, sodass die Ausführung blockiert wird. Die zweite SQL-Anweisung fügt den Primärschlüssel 4 ein, was in Ordnung ist, aber

Der Hilfsindexwert 2 liegt im gesperrten Bereich (1,3), daher ist die Ausführung ebenfalls gesperrt. In der dritten SQL-Anweisung ist der eingefügte Primärschlüssel 6 nicht gesperrt und 5 liegt nicht im Bereich (1,3). Der eingegebene Wert 5 befindet sich jedoch in einem anderen gesperrten

Es liegt im Bereich (3,6), also muss es auch warten. Die folgende SQL-Anweisung wird nicht blockiert und kann sofort ausgeführt werden:

mysql> in z einfügen, select 8,6;
mysql> in z einfügen, select 2,0;
mysql> in z einfügen, Auswahl 6,7;

Wie aus dem obigen Beispiel ersichtlich ist, besteht der Zweck von Gap Lock darin, zu verhindern, dass mehrere Transaktionen Datensätze in denselben Bereich einfügen, was zu Phantomleseproblemen führen würde. Wenn im obigen Beispiel der Benutzer in Sitzung A gesperrt hat

Datensätze mit b=3. Wenn zu diesem Zeitpunkt keine Lückensperre (3,6) vorhanden ist, kann der Benutzer einen Datensatz mit dem Index b, Spalte 3 einfügen. Dies führt dazu, dass andere Datensätze zurückgegeben werden, wenn der Benutzer in Sitzung A dieselbe Abfrage erneut ausführt. Dabei handelt es sich um einen Phantom-Lesevorgang.

Der Schwerpunkt liegt hier auf dem Tabellensperrmechanismus der InnoDB-Speicher-Engine. Zumindest verstehe ich den Zeilensperrmechanismus von MySQL. Wenn Leser Fragen haben, hinterlassen Sie bitte eine Nachricht. Das nächste Mal werde ich die MySQL-Transaktionsfunktionen und ihren internen Implementierungsmechanismus aufzeichnen.

Einschließlich detaillierter Erläuterungen zur internen Architektur von MySQL, zum InnoDB-Pufferpool, zum Redo-Log, zum Undo-Log usw. Derzeit habe ich das Wissen nur überprüft, aber noch nicht zusammengefasst.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Insider-Geschichte der MySQL-Technologie – InnoDB-Sperre. Weitere Informationen zur MySQL InnoDB-Sperre 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!