Eine kurze Erläuterung des Sperrbereichs der MySQL-Next-Key-Sperre

Vorwort

Eines Tages wurde ich plötzlich nach der Next-Key-Sperre von MySQL gefragt und meine unmittelbare Reaktion war:

Was zur Hölle ist das? ? ?

Ich kann in diesem Screenshot nichts sehen?

Bei genauerem Hinsehen kommt es einem bekannt vor. Ist das nicht der Inhalt von „MySQL 45 Lectures“?

Was ist die Next-Key-Sperre?

Eine Next-Key-Sperre ist eine Kombination aus einer Datensatzsperre für den Indexdatensatz und einer Lückensperre für die Lücke vor dem Indexdatensatz.

Die Erklärung auf der offiziellen Website bedeutet in etwa: Die Next-Key-Sperre ist eine Kombination aus einer Datensatzsperre für den Indexdatensatz und einer Lückensperre für die Lücke vor dem Indexdatensatz.

Setzen Sie sich zunächst viele kleine Fragezeichen? ? ?

• Welche Indizes werden gesperrt, wenn der Primärschlüssel, der eindeutige Index, der gemeinsame Index und das gemeinsame Feld gesperrt werden?
• Welche Datenbereiche werden durch unterschiedliche Abfragebedingungen gesperrt?
• Was sind die Sperrbereiche für Wertabfragen und Bereichsabfragen, beispielsweise zum Teilen und Aktualisieren?
• Was ist der Sperrumfang, wenn der abgefragte gleichwertige Wert nicht existiert?
• Was ist der Unterschied, wenn die Abfragebedingungen Primärschlüssel, eindeutiger Index und gemeinsamer Index sind?

Da ich nichts verstehe, muss ich von vorne anfangen und üben!

Schauen wir uns zunächst die Schlussfolgerung von Herrn Ding Qi in „MySQL 45 Lectures“ an:

Nach dem Lesen dieser Schlussfolgerung sollten die meisten Fragen beantwortet sein, aber es gibt einen sehr wichtigen Satz, der beachtet werden muss: MySQL 后面的版本可能會改變加鎖策略，所以這個規則只限于截止到現在的最新版本，即5.x 系列<=5.7.24，8.0 系列<=8.0.13

Daher sind die oben genannten Regeln möglicherweise nicht auf die aktuelle Version anwendbar. Im Folgenden werde ich MySQL 8.0.25 als Beispiel verwenden, um den Sperrbereich des nächsten Schlüssels aus mehreren Blickwinkeln zu überprüfen.

Umgebungsvorbereitung

MySQL-Version: 8.0.25

Isolationsebene: Wiederholbares Lesen (RR)

Speicher-Engine: InnoDB

mysql> wähle @@global.transaction_isolation,@@transaction_isolation\G
mysql> zeigen erstellen Tabelle t\G 

Informationen zum Installieren von MySQL mit Docker finden Sie in einem anderen Artikel: Installieren und Herstellen einer Verbindung mit MySQL mit Docker.

Primärschlüsselindex

Lassen Sie uns zunächst den Bereich der Next-Key-Sperre des Primärschlüsselindex überprüfen.

Die Datenbankdaten zu diesem Zeitpunkt sind in der Abbildung dargestellt. Für den Primärschlüsselindex ist die Datenlücke wie folgt:

Abfrage mit gleichem Primärschlüsselwert – Daten vorhanden

mysql> begin; select * from t where id = 10 für Update;

Diese SQL-Anweisung sperrt id = 10 Sie können zunächst darüber nachdenken, welche Sperre hinzugefügt wird? Welche Daten sind gesperrt?

Sie können die Sperrinformationen über data_locks anzeigen. Das SQL lautet wie folgt:

# mysql> wähle * aus performance_schema.data_locks;
mysql> wähle * aus performance_schema.data_locks\G

Informationen zu spezifischen Feldbedeutungen finden Sie in der offiziellen Dokumentation

Die Ergebnisse enthalten hauptsächlich Informationen wie Engines, Bibliotheken und Tabellen. Wir müssen uns auf die folgenden Felder konzentrieren:

• INDEX_NAME: Der Name des gesperrten Indexes
• LOCK_TYPE: Der Sperrtyp. Für InnoDB sind die zulässigen Werte RECORD-Sperre auf Zeilenebene und TABLE-Sperre auf Tabellenebene.
• LOCK_MODE: Sperrtyp: S-, X-, IS-, IX- und Lückensperren
• LOCK_DATA: Sperrenbezogene Daten. Für InnoDB wird der Wert angezeigt, wenn LOCK_TYPE RECORD (Zeilensperre) ist. Wenn die Sperre auf einem Primärschlüsselindex liegt, ist der Wert der Primärschlüsselwert des gesperrten Datensatzes. Wenn die Sperre auf einem sekundären Index liegt, wird der Wert des sekundären Indexes angezeigt, an den der Wert des Primärschlüssels angehängt wird.

Das Ergebnis ist offensichtlich. Hier wird der Tabelle eine IX-Sperre hinzugefügt und dem Datensatz mit der Primärschlüsselindex-ID = 10 eine X,REC_NOT_GAP Sperre, was bedeutet, dass nur der Datensatz gesperrt ist.

In ähnlicher Weise wird for share eine IS-Sperre zur Tabelle und eine S-Sperre zum Datensatz mit der Primärschlüsselindex-ID = 10 hinzugefügt.

Es kann folgender Schluss gezogen werden:

Wenn der Primärschlüsselwert gesperrt wird und der Wert vorhanden ist, wird der Tabelle eine Absichtssperre und dem Primärschlüsselindex eine Zeilensperre hinzugefügt.

Abfrage mit gleichem Wert für Primärschlüssel – Daten nicht vorhanden

mysql> select * from t where id = 11 für Update;

Wenn die Daten nicht vorhanden sind, welche Sperre wird hinzugefügt? Welchen Umfang hat die Sperre?

Analysieren Sie vor der Überprüfung die Lücken in den Daten.

• id = 11 existiert definitiv nicht. Aber nach dem Hinzufügen for update ist eine Sperre für den nächsten Schlüssel erforderlich, und das Intervall mit id = 11 ist das erste offene und das zweite geschlossene Intervall von (10,15).
• Da es sich um等值查詢handelt, besteht keine Notwendigkeit, den Datensatz id = 15 zu sperren, und die Nächste-Schlüssel-Sperre degeneriert zu einer Lückensperre.
• Das letzte Intervall ist das Front-Open- und Back-Open-Intervall von (10,15).

Verwenden Sie data_locks, um die Sperrinformationen zu analysieren:

Sehen Sie sich die Sperrinformationen X,GAP gibt an, dass eine Lückensperre hinzugefügt wurde, wobei LOCK_DATA = 15 bedeutet, dass die Sperre für die Lücke vor der Primärschlüsselindex-ID = 15 gilt.

Wenn zu diesem Zeitpunkt SQL in einer anderen Sitzung ausgeführt wird, ist die Antwort offensichtlich: ID = 12 kann nicht eingefügt werden, aber ID = 15 kann aktualisiert werden.

Man kann den Schluss ziehen, dass, wenn die Daten nicht vorhanden sind, die Abfrage mit gleichem Wert des Primärschlüssels die Lücke schließt, in der sich die Abfragebedingung des Primärschlüssels befindet.

Abfrage des Primärschlüsselbereichs (Schlüsselpunkt)

mysql> begin; select * from t where id >= 10 und id < 11 für Update;

Nach der Analyse von „MySQL 45 Lectures“ ergeben sich folgende Ergebnisse:

• id >= 10 lokalisiert das Intervall (10,+∞), in dem sich 10 befindet;
• Da >= ein gleichwertiges Urteil hat, muss der Wert 10 einbezogen werden, und es wird ein abgeschlossenes Intervall [10,+∞);
• id < 11 begrenzt den nachfolgenden Bereich, daher wird basierend auf 11 das nächste Intervall als das erste offene und das zweite geschlossene Intervall von 15 bestimmt;
• Zusammengefasst ergibt das [10,15]. (Nicht ganz richtig)

Erster Blick auf data_locks

Man erkennt, dass neben der Tabellensperre auch noch eine Zeilensperre ( X,REC_NOT_GAP ) für die ID = 10 und eine Lückensperre ( X,GAP ) vor dem Primärschlüsselindex ID = 15 existiert.

Daher kann tatsächlich die ID = 15 aktualisiert werden. Das heißt, es gibt ein Problem前開后閉區間. Ich persönlich denke, es sollte die Bedingungsbeurteilung von id < 11 sein, was zur unnötigen Sperrung von Zeile 15 führt.

Auch die Ergebnisüberprüfung ist korrekt, die ID = 12 ist für die Einfügung gesperrt und die ID = 15 wurde erfolgreich aktualisiert.

Was passiert, wenn die rechte Seite des Bereichs eine Abfrage mit gleichem Wert enthält?

mysql> begin; select * from t where id > 10 und id <= 15 für Update;

Lassen Sie uns dieses SQL analysieren:

id > 10 liegt im Intervall (10, +∞), in dem 10 liegt; id <= 15 liegt in (-∞, 15]; kombiniert ist es (10, 15].

Schauen Sie sich auch data_locks an

Es ist ersichtlich, dass nur eine X-Sperre für die Primärschlüsselindex-ID = 15 hinzugefügt wird.

Überprüfen, ob ID = 15 aktualisiert werden kann? Nochmals prüfen, ob id=16 eingefügt werden kann?

Es stellt sich heraus, dass es kein Problem gibt!

Natürlich werden einige Freunde hier sagen, dass es in „MySQL 45 Lectures“ einen Fehler gibt, der den nächsten Next-Key sperrt.

Es stellt sich heraus, dass dieser Fehler behoben wurde. Die korrigierte Version ist MySQL 8.0.18 . Aber es ist nicht vollständig behoben! ! !

Referenzlinkadresse:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

Suchbegriffe: Fehler Nr. 29508068)

Wir können es jeweils mit 8.0.17 reproduzieren:

In 8.0.17 werden id <= 15 auch die Daten mit der ID = 20 gesperrt, in Version 8.0.25 ist dies jedoch nicht der Fall. Dieser Fehler wurde also behoben.

Schauen wir uns einmal die Frage an, ob es前開后閉前開后開hinten offen ist. Seien wir genau und vergleichen 8.0.17 und 8.0.18.

Nun schätze ich es als sehr wahrscheinlich ein, dass dieser前開后閉bei der Behebung Bug #29508068 in Version 8.0.18 in前開后開optimiert wurde.

Vergleichen Sie data_locks-Daten:

Beachten Sie den rot unterstrichenen Teil. In Version 8.0.17 ist LOCK_MODE X , wenn id < 17 ist, während es in Version 8.0.25 X,GAP ist.

Zusammenfassen

In diesem Artikel wird hauptsächlich der Sperrbereich des nächsten Schlüssels beim Sperren des Primärschlüssels durch tatsächliche Vorgänge überprüft. Außerdem werden Materialien konsultiert und Versionen verglichen, um unterschiedliche Schlussfolgerungen zu ziehen.

Schlussfolgerung 1:

• Beim Sperren wird zunächst eine Absichtssperre, IX oder IS, zur Tabelle hinzugefügt.
• Wenn mehrere Bereiche vorhanden sind, werden mehrere Sperren separat hinzugefügt und jeder Bereich verfügt über eine Sperre. (Dies kann im Fall einer ID < 20 praktiziert werden.)
• Bei Abfragen mit identischem Primärschlüsselwert wird, sofern die Daten vorhanden sind, eine Zeilensperre X,REC_NOT_GAP zum Wert des Primärschlüsselindex hinzugefügt.
• Bei Abfragen mit identischem Primärschlüsselwert wird, wenn die Daten nicht vorhanden sind, eine Lückensperre X,GAP zur Lücke hinzugefügt, in der sich der Primärschlüsselwert der Abfragebedingung befindet.
• Die Abfrage von Primärschlüsseln mit gleichem Wert und die Bereichsabfrage sind komplizierter:
  • Version 8.0.17 ist vorne offen und hinten geschlossen, während Version 8.0.18 und höher optimiert wurden. Der Primärschlüssel wird als ungleich beurteilt und das hinten geschlossene Intervall wird nicht gesperrt.
  • Beim Ausführen einer kritischen <= Abfrage sperrt 8.0.17 das erste offene und letzte geschlossene Intervall des nächsten Next-Keys, aber 8.0.18 und spätere Versionen beheben diesen Fehler.

Nach der Optimierung wird es später geöffnet. Ich weiß nicht, ob dies daran liegt, dass das Primärschlüsselintervall nach der Optimierung direkt später geöffnet wird, oder ob es sich um einen Fehler handelt. Bestimmte Freunde können es versuchen.

Schlussfolgerung 2

Durch die Verwendung select * from performance_schema.data_locks; und der Betriebspraxis können Sie die Beziehung zwischen LOCK_MODE und LOCK_DATE erkennen:

LOCK_MODE = X ist ein Open-First-Closed-Intervall; X,GAP ist ein Open-First-Closed-Intervall (Lückensperre); X,REC_NOT_GAP ist eine Zeilensperre.

Der Next-Key-Sperrbereich des Primärschlüssels wurde grundsätzlich verstanden. Beachten Sie, dass die verwendete Version 8.0.25 ist.

zweifeln

• Was ist der nächste Schlüsselsperrbereich des eindeutigen Indexes?
• Was sind Sperrbereich und Sperrindex, wenn der Index abgedeckt ist?
• Der Grund, warum ich sagte, dass dieser Fehler nicht vollständig behoben wurde, besteht darin, dass dieser Fehler in einem eindeutigen Index ohne Primärschlüssel reproduziert wurde.

Der Artikel ist in seiner Länge begrenzt, so dass du dir zunächst selbst Gedanken machen und es selbst ausprobieren kannst. Übung macht den Meister. Um die konkrete Antwort zu erhalten, muss der nächste Artikel überprüft und die Schlussfolgerung zusammengefasst werden.

Dies ist das Ende dieses Artikels über den Sperrbereich der MySQL-Next-Key-Sperre. Weitere Informationen zum Sperrbereich der MySQL-Next-Key-Sperre finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!