Welche Regeln gelten für das Sperren von MySQL-Gap-Locks?

MySQL 11-Regeln für die Lückensperre-Sperre

Die Lückensperre wird nur unter der wiederholbaren Leseisolationsstufe wirksam: Die Sperre für den nächsten Schlüssel wird tatsächlich durch die Lückensperre plus Zeilensperre implementiert, wenn Sie zur Lese-Commit-Isolationsstufe wechseln (read-committed). Es ist leicht zu verstehen, dass dabei der Lückensperrteil entfernt wird, dh nur der Reihensperrteil übrig bleibt. Unter der Lese-Commit-Isolationsstufe gibt es keine Lückensperre. Um die mögliche Inkonsistenz zwischen Daten und Protokollen zu beheben, muss das Binlog-Format auf Zeile eingestellt werden. Mit anderen Worten, die Konfiguration vieler Unternehmen lautet: Lese-Commit-Isolationsstufe plus binlog_format=row. Das Unternehmen benötigt keine Garantie für wiederholbares Lesen. Wenn man also bedenkt, dass der Sperrbereich der gelesenen Betriebsdaten kleiner ist (keine Lückensperre), ist diese Wahl sinnvoll
.

Next-Key-Sperrregeln

　Die zusammengefassten Sperrregeln umfassen zwei „Prinzipien“, zwei „Optimierungen“ und einen „Fehler“.
　Prinzip 1: Die Grundeinheit des Schlosses ist das Next-Key-Schloss. Die Next-Key-Sperre ist ein offenes und geschlossenes Intervall.
　Prinzip 2: Nur Objekte, auf die während des Suchvorgangs zugegriffen wird, werden gesperrt. Jede Sperre für einen Sekundärindex oder eine Sperre für eine nicht indizierte Spalte wird schließlich auf den Primärschlüssel zurückgeführt, und dem Primärschlüssel wird auch eine Sperre hinzugefügt.
　Optimierung 1: Bei äquivalenten Abfragen im Index degeneriert die Next-Key-Sperre beim Sperren des eindeutigen Index in eine Zeilensperre. Mit anderen Worten: Wenn InnoDB einen Primärschlüssel oder einen eindeutigen Index scannt, verwendet InnoDB nur Zeilensperren zum Sperren: Für äquivalente Abfragen für den Index (nicht unbedingt den eindeutigen Index) wird nach rechts gelaufen, wenn der letzte Wert vorhanden ist Wenn die Gleichheitsbedingung nicht erfüllt ist, degeneriert die nächste Tastensperre zu einer Lückensperre.
　Ein Fehler: Eine Bereichsabfrage für einen eindeutigen Index greift auf den ersten Wert zu, der die Bedingung nicht erfüllt.

Fallanalyse

Wir nehmen den Tabellentest als Beispiel. Die Tabellenerstellungsanweisung und die Initialisierungsanweisung lauten wie folgt: ID ist der Primärschlüsselindex Es gibt keine ID in der Tabelle test =7 Datensatz

Gemäß Prinzip 1 ist die Sperreinheit die Sperre für den nächsten Schlüssel, und der Sperrbereich von Sitzung A beträgt (5,10];

Gleichzeitig gemäß Optimierung 2 , dies ist eine äquivalente Abfrage (id=7), und id=10 erfüllt nicht die Abfragebedingungen und die Next-Key-Sperre degeneriert in eine Lückensperre, sodass der endgültige Sperrbereich (5,10) ist

Fall 2 : Nicht eindeutige indexäquivalente Abfragesperre

Hier ist Sitzung A. Fügen Sie eine Lesesperre zur Zeile mit Spalte 1 = 5 für Index Spalte 1 hinzu.

　 Gemäß Prinzip 1 ist die Sperreinheit die Sperre für die nächste Taste und bleibt offen und rechts geschlossen, 5 ist geschlossen, also wird es sein (0,5] Next-Key-Sperre hinzufügen

Es ist zu beachten, dass c ein normaler Index ist, sodass nur der Zugriff auf den Datensatz c=5 nicht sofort gestoppt werden kann (möglicherweise). Andere Datensätze mit Spalte 1 = 5),

muss nach rechts durchlaufen werden, um „Aufgeben“ nur zu finden, wenn c = 10. Gemäß dem zweiten Prinzip müssen alle Zugriffe gesperrt werden, daher muss die Sperre für den nächsten Schlüssel hinzugefügt werden Intervall (5, 10). ,10). Gemäß Prinzip 2 wird bei dieser Abfrage nur ein abdeckender Index verwendet, und es besteht keine Notwendigkeit, auf den Primärschlüsselindex zuzugreifen.

Daher wird dem Primärschlüsselindex keine Sperre hinzugefügt. Dies erklärt, warum die Aktualisierungsanweisung von Sitzung B erfolgreich ausgeführt werden kann

　Sitzung C möchte jedoch einen Datensatz von (7,7,7) einfügen, der durch die Lückensperre (5,10) von Sitzung A gesperrt wird Das Beispiel zeigt, dass die Sperre zum Index hinzugefügt wird. Bei der Ausführung geht das System davon aus, dass Sie die Daten als Nächstes aktualisieren möchten, und indiziert daher nebenbei Zeilensperren für Zeilen, die die Bedingungen erfüllen

　　Wenn Sie die Sperre im Freigabemodus verwenden möchten, um Lesesperren zu den Zeilen hinzuzufügen, um zu verhindern, dass Daten aktualisiert werden, müssen Sie die Optimierung des Abdeckindex umgehen, da der Abdeckindex nicht auf den Primärschlüsselindex zugreift wird nicht gesperrt

Fall 3: Abfragesperre für den Indexbereich des Primärschlüssels

Zu Beginn der Ausführung müssen wir die erste Zeile mit der ID = 10 finden, daher sollte es sich um die Sperre für den nächsten Schlüssel (5, 10) handeln. Gemäß Optimierung 1 wird die äquivalente Bedingung für den Primärschlüssel

id in eine Zeilensperre degeneriert und nur die Zeilensperre von id=10 hinzugefügt

　Es handelt sich um eine Bereichsabfrage und die Bereichssuche findet weiterhin id=15 . Diese Zeile stoppt und die Bedingung ist nicht erfüllt, daher muss

next-key lock(10,15] hinzugefügt werden.

　Der Umfang der Sperre für Sitzung A ist zu diesem Zeitpunkt der Primärschlüsselindex, die Zeilensperre-ID = 10 und die nächste Schlüsselsperre (10,15). Wenn Sitzung A die Zeile mit

id = 10 zum ersten Mal findet, Es wird als äquivalente Abfrage beurteilt, und beim Scannen nach rechts bis zur ID=15 wird die Bereichsabfragebeurteilung verwendet

.

Fall 4: Abfragesperre für nicht eindeutigen Indexbereich

　 Wenn col1=10 zum ersten Mal zum Suchen des Datensatzes verwendet wird, wird nach der nächsten Tastensperre von (5,10] Index c hinzugefügt, weil index col1 ist nicht eindeutig
Ein Index hat keine Optimierungsregeln, was bedeutet, dass er nicht in eine Zeilensperre umgewandelt wird. Daher sind die letzten von Sitzung A hinzugefügten Sperren die beiden Next-Keylocks (5,10] und
(10, 15] auf Index c.
　Es ist sinnvoll, den Scanvorgang zu beenden, wenn Spalte 1=15 gescannt wird, da InnoDB erst Spalte 1=15 scannen muss, bevor es weiß, dass die Suche nicht fortgesetzt werden muss.

Fall 5: Fehler beim Sperren der eindeutigen Indexbereichsabfrage

　Sitzung A ist eine Bereichsabfrage. Gemäß Prinzip 1 sollte nur die Next-Key-Sperre von (10,15] zur Index-ID hinzugefügt werden, und da die ID der einzige Schlüssel ist, wird die Schleife beurteilt die Zeile id=15 Es sollte aufhören. In der Implementierung scannt InnoDB jedoch vorwärts zur ersten Zeile, die die Bedingung id=20 nicht erfüllt. Und da es sich um einen Bereichsscan handelt, lautet die Index-ID (15,20). ) Diese Next-Key-Sperre wird ebenfalls gesperrt. Logischerweise ist das Verhalten des Sperrens der ID = 20-Zeile hier eigentlich unnötig, da Sie nach dem Scannen von ID = 15 sicher sein können, dass Sie nicht danach suchen müssen

Fall 6: Beispiel für „Äquivalent“ auf einem nicht eindeutigen Index

Hier füge ich einen neuen Datensatz in die Tabelle t ein: insert into t Values(30,10,30); Jetzt gibt es in der Tabelle zwei Zeilen mit c=10, aber ihre Primärschlüsselwerte sind unterschiedlich (10 bzw. 30), sodass zwischen den beiden Datensätzen mit c=10 eine Lücke besteht Wir verwenden die Löschanweisung zur Überprüfung. Beachten Sie, dass die Sperrlogik der Löschanweisung tatsächlich der von select... for update ähnelt. Dies sind die beiden „Prinzipien“ und zwei „Optimierungen“, die ich am Anfang des Artikels zusammengefasst habe Ein „Fehler“. Zu diesem Zeitpunkt greift Sitzung A zunächst auf den ersten Datensatz col1=10 zu. In ähnlicher Weise wird hier
(col1=10,id=) hinzugefügt 10) Diese Next-Key-Sperre

Da c ein normaler Index ist, wird die Suche nach rechts fortgesetzt, bis die Zeile (col1=15,id=15) gefunden wird. Gemäß Optimierung 2 ist dies

eine äquivalente Abfrage. Zeilen, die die Bedingungen nicht erfüllen, werden rechts gefunden, sodass es zu einer Lücke

lock von (col1=10,id=10) bis (col1=15,id=15) kommt.

　Der Sperrbereich dieser Löschanweisung für Index c ist der Teil, der durch den blauen Bereich im Bild oben abgedeckt wird. Die linken und rechten Seiten dieses blauen Bereichs sind gepunktete Linien, die offene Intervalle anzeigen, d. h. (col1=5,id=5) und (col1=15,id=15). Auf diesen beiden Linien gibt es keine Sperren

Fall 7: Limit-Anweisung hinzufügen Lock




　Die Löschanweisung von Sitzung A fügt Limit 2 hinzu. Sie wissen, dass es in Tabelle t eigentlich nur zwei Datensätze mit c=10 gibt, daher ist der Effekt des Hinzufügens oder Löschens von Limit 2 derselbe. Der Sperreffekt ist jedoch anders. Dies liegt daran, dass die Löschanweisung in Fall 7 eindeutig ein Limit von Limit 2 hinzufügt, sodass nach dem Durchlaufen der Zeile (col1 = 10, id = 30) bereits zwei Anweisungen vorhanden sind, die die Bedingungsleiste erfüllen , der Zyklus ist vorbei. Daher wird der Sperrbereich auf Index col1 zum vorne-offenen und hinten-geschlossenen Bereich von (col1=5,id=5)
bis (col1=10,id=30), wie in der folgenden Abbildung dargestellt:

　Die zentrale Bedeutung dieses Beispiels für unsere Praxis besteht darin, zu versuchen, beim Löschen von Daten ein Limit hinzuzufügen.
　Dadurch wird nicht nur die Anzahl der gelöschten Daten kontrolliert, was den Vorgang sicherer macht, sondern auch der Umfang der Sperrung verringert.

Fall 8: Ein Beispiel für einen Deadlock

　Sitzung A führt die Abfrageanweisung aus und sperrt sie nach dem Start der Transaktion im Freigabemodus und fügt next-keylock(5,10] und
gap lock(10, 15) hinzu. (Der Indexdurchlauf nach rechts degeneriert in eine Lückensperre);
Die Aktualisierungsanweisung von Sitzung B muss auch die Sperre für den nächsten Schlüssel (5,10] zum Index c hinzufügen und die Sperrwartezeit eingeben; sie ist tatsächlich in zwei Schritte unterteilt,
Fügen Sie zuerst die Lückensperre von (5,10) hinzu und fügen Sie dann die Zeilensperre von col1 = 10 hinzu, da Sitzung A dieser Zeile bereits eine Lesesperre hinzugefügt hat und die Anwendung für Deadlock zu diesem Zeitpunkt blockiert wird. Dann muss Sitzung A die Zeile (8,8,8) einfügen, die durch die Lückensperre von Sitzung B gesperrt ist. Aufgrund eines Deadlocks führt InnoDB ein Rollback von Sitzung B durch.

Fall 9: Sortieren nach Indexsortierlückensperre 1

Wie in der folgenden Anweisung gezeigt
Die folgende Abbildung ist ein schematisches Diagramm der Index-ID dieser Tabelle.
begin;
select * from test where id>9 and id id　Dieser Prozess wird durch den Suchprozess des Indexbaums erhalten. Wir möchten tatsächlich den Wert von id=12 finden, aber am Ende wurde nicht gefunden, sondern die Lücke (10 ,15) wurde gefunden. (id=15 erfüllt die Bedingung nicht, daher degeneriert die Next-Key-Sperre in eine Gap-Sperre (10,

15).)

Während des Durchlaufvorgangs ist dies kein Äquivalent Abfrage. Scannen Sie bis zur Zeilen-ID=5, und da das Intervall links offen und rechts
geschlossen ist, wird eine Next-Key-Sperre (0,5) hinzugefügt. Mit anderen Worten, während der Ausführung Prozess, durch Baumsuche Beim Auffinden des Datensatzes
wird die Methode „Äquivalente Abfrage“ verwendet

Fall 10: Sortierung nach Index, Lückensperre 2
#🎜🎜 ##🎜 🎜#
Da es sich um die Reihenfolge col1 desc handelt, ist das erste, was gefunden werden muss, die Zeile „ganz rechts“ col1=20 im Index col1. Dies ist eine äquivalente Abfrage mit einem nicht eindeutigen Index: #🎜 🎜## 🎜🎜#Fügen Sie zuerst die Next-Key-Sperre zum offenen Intervall auf der linken Seite hinzu, um das (15,20]-Intervall zu bilden. Durchqueren Sie nach rechts, Spalte 1 = 25 erfüllt die Bedingungen nicht und degeneriert zu einer Lückensperre, also a Lückensperre (20, 25) und Nächste-Tasten-Sperre (15,20). auf rechts geöffnetes und links geschlossenes Intervall (5,10) angewendet werden

Dies ist der Grund, warum die Einfügeanweisung von Sitzung B während des Scanvorgangs blockiert ist, Spalte1=20, Spalte1=15, Spalte1 =10 haben alle Werte, da es sich um select * handelt, sodass dem Primärschlüssel

id drei Zeilensperren hinzugefügt werden. Daher ist der Bereich der select-Anweisungssperre von Sitzung A: #🎜🎜 #　　On index col1 (5, 25);# 🎜🎜#　Zwei Zeilensperren für den Primärschlüsselindex sind id=15 und 20.

Fall 11: Beispiel für eine Aktualisierung zum Ändern von Daten – zuerst einfügen und dann löschen

Hinweis: Der erste gemäß Spalte 1>5 gefundene Datensatz ist Spalte 1=10, daher ist die nächste Tastensperre von (0,5] wird nicht hinzugefügt.

Sitzung Eine Sperre Der Bereich ist (5,10], (10,15], (15,20], (20,25] und (25,supremum] auf Indexspalte1. #🎜 🎜#Die erste Aktualisierungsanweisung nach Sitzung B. Um col1=5 in col1=1 zu ändern, können Sie es in zwei Schritten verstehen:

Einfügen des Datensatzes (col1=1, id=5);

Löschen (col1=5, id=5) Datensatz.

Durch diesen Vorgang wird der Sperrbereich von Sitzung A wie in Abbildung 7 dargestellt:

Okay, nächste Sitzung B. Um die Anweisung auszuführen Aktualisieren Sie t set col1 = 5, wobei col1 = 1, es kann in zwei Schritte unterteilt werden:

Einfügen des Datensatzes (col1=5, id=5);

Löschen (col1=1 , id=5) dieser Rekord. Der erste Schritt besteht darin, zu versuchen, Daten in (1,10) einzufügen, die der Lückensperre hinzugefügt wurden, sodass sie blockiert sind　　.

Das obige ist der detaillierte Inhalt vonWelche Regeln gelten für das Sperren von MySQL-Gap-Locks?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!