Detaillierte Erläuterung der MySQL-Zeilensperren beim Auftreten zusammengesetzter Primärschlüssel und mehrspaltiger Indizes

Hintergrund

Als ich heute mit anderen Projektteams zusammenarbeitete, um Stresstests für das System durchzuführen, kam es im Verlauf des Prozesses gelegentlich zu Deadlock-Problemen. Nach der Analyse des Codes wurde festgestellt, dass eine Aktualisierung des zusammengesetzten Primärschlüssels erfolgte. Bei der Aktualisierung der zusammengesetzten Primärschlüsseltabelle wurde nur ein Feld aktualisiert. Gleichzeitig gab es innerhalb der Transaktion eine Einfügeoperation für die Tabelle, was zu gelegentlichen Deadlock-Problemen führte.

Beispielsweise hat die Tabelle t_lock_test zwei Primärschlüssel, die beide primary key(a,b) sind, aber beim Aktualisieren wird sie über update t_lock_test .. where a = ? , und anschließend erfolgt in der Transaktion insert into t_lock_test values(...) .

Der Sperralgorithmus in InnoDB ist Next-Key Locking. Es ist sehr wahrscheinlich, dass der Deadlock durch diesen Punkt verursacht wird. Wird Next-Key Locking jedoch unter einem zusammengesetzten Primärschlüssel ausgelöst? Wird Next-Key Locking unter einem mehrspaltigen gemeinsamen eindeutigen Index ausgelöst? Die Antwort steht nicht im Buch, daher muss sie in der Praxis getestet werden.

Sperren in InnoDB

Sperren sind ein wesentliches Merkmal, das Datenbanksysteme von Dateisystemen unterscheidet. Sperrmechanismen werden verwendet, um den gleichzeitigen Zugriff auf gemeinsam genutzte Ressourcen zu verwalten [Abbildung]. Die InnoDB-Speicher-Engine sperrt Tabellendaten auf Zeilenebene, was gut ist. Die Speicher-Engine InnoDB verwendet jedoch auch an vielen anderen Stellen innerhalb der Datenbank Sperren, um den gleichzeitigen Zugriff auf eine Vielzahl unterschiedlicher Ressourcen zu ermöglichen. Um beispielsweise die LRU-Liste im Pufferpool zu betreiben und Elemente in der LRU-Liste zu löschen, hinzuzufügen und zu verschieben, müssen Sperren eingesetzt werden, um die Konsistenz sicherzustellen. Datenbanksysteme verwenden Sperren, um den gleichzeitigen Zugriff auf gemeinsam genutzte Ressourcen zu unterstützen und Datenintegrität und -konsistenz sicherzustellen.

Da Sperren grundsätzlich unter der InnoDB-Speicher-Engine verwendet werden, überspringen wir MyISAM und besprechen direkt InnoDB.

Sperrtyp

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

• Gemeinsames Schloss (S Lock), das einer Transaktion das Lesen einer Datenzeile ermöglicht
• Exklusive Sperre (x lOCK), die es einer Transaktion ermöglicht, einen Datenteil zu löschen oder zu 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 das Lesen die Daten von r nicht ändert. Diese Situation wird als sperrenkompatibel bezeichnet. Wenn jedoch eine andere Transaktion T3 eine exklusive Sperre für Zeile r erhält, wartet sie, bis T1 und T2 die gemeinsame Sperre für Zeile r freigeben – diese Situation wird als Sperreninkompatibilität bezeichnet.

Kompatibilität von exklusiven Sperren und gemeinsam genutzten Sperren:

InnoDB generiert beim Aktualisieren von Daten Zeilensperren, und Zeilensperren können auch explizit hinzugefügt werden (auch als "pessimistische Sperren" bezeichnet).

select for update

Sperralgorithmus

InnoDB verfügt über drei Zeilensperralgorithmen:

Datensatzsperre: Eine Sperre für einen einzelnen Zeilendatensatz, die im wahrsten Sinne des Wortes eine Zeilensperre ist.

Record Lock sperrt den Indexdatensatz (beachten Sie, dass wir hier über den Index sprechen, da der Primärschlüsselindex in InnoDB die Daten sind). Wenn beim Erstellen der InnoDB-Speicher-Engine-Tabelle kein Index festgelegt ist, verwendet die InnoDB-Speicher-Engine den versteckten Primärschlüssel, um sie zu sperren.

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 den Datensatz selbst

Die Prinzipien der Sperrintervallaufteilung sind bei Gap Lock und Next-Key Lock gleich.

Wenn ein Index beispielsweise vier Werte hat: 10/11/13 und 20, sind die Intervalle, in die der Index unterteilt ist, wie folgt:

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

Die Schließtechnologie mit Next-Key Lock wird Next-Key Locking genannt. Sein Entwurfszweck besteht darin, das Phantomproblem zu lösen, das im nächsten Abschnitt vorgestellt wird. Bei dieser Sperrtechnologie wird nicht ein einzelner Wert, sondern ein Bereich gesperrt, was eine Verbesserung gegenüber der Prädikatssperre darstellt.

Wenn der abgefragte Index ein eindeutiges Attribut enthält (Primärschlüsselindex, eindeutiger Index), optimiert die InnoDB-Speicher-Engine die Next-Key-Sperre und stuft sie auf eine Datensatzsperre herab, was bedeutet, dass nur der Index selbst gesperrt ist, nicht der Bereich.

Schauen wir uns ein Sperrbeispiel unter einem sekundären Index (nicht eindeutiger Index) an:

TABELLE ERSTELLEN z (a INT, b INT, PRIMARY KEY(a), KEY(b));

EINFÜGEN IN z AUSWÄHLEN 1,1;
EINFÜGEN IN z SELECT 3,1;
EINFÜGEN IN z SELECT 5,3;
EINFÜGEN IN z AUSWÄHLEN 7,6;
EINFÜGEN IN z SELECT 10,8;

Spalte b der Tabelle z ist ein sekundärer Index. Wenn Transaktion A ausgeführt wird:

Wählen Sie * von z, wobei b = 3 für Update

Da es sich bei Spalte B um einen Hilfsindex handelt, wird zu diesem Zeitpunkt der Next-Key-Locking-Algorithmus verwendet und der Sperrbereich ist (1,3). Beachten Sie, dass InnoDB auch dem nächsten Wert des Hilfsindex eine Gap-Sperre hinzufügt, d. h. es gibt auch eine Sperre für den Hilfsindexbereich von (3,6). Wenn daher das folgende SQL in der neuen Transaktion B ausgeführt wird, wird es blockiert:

1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE; //S-Sperre 2. INSERT INTO z SELECT 4,2;
3. EINFÜGEN IN z SELECT 6,5;

Die erste SQL-Anweisung kann nicht ausgeführt werden, da die in Transaktion A ausgeführte SQL-Anweisung bereits eine X-Sperre zum Wert der Spalte a=5 im gruppierten Index hinzugefügt hat und die Ausführung daher blockiert wird.

Die zweite SQL-Anweisung fügt 4 in den Primärschlüssel ein. Es gibt kein Problem. Der eingefügte sekundäre Indexwert 2 liegt jedoch im gesperrten Bereich (1,3], sodass die Ausführung ebenfalls blockiert ist.

Im dritten SQL ist der eingefügte Primärschlüssel 6 nicht gesperrt und 5 liegt nicht im Bereich (1,3). Der eingefügte Wert 5 in Spalte b liegt jedoch im nächsten Gap-Lock-Bereich (3,6) und muss daher ebenfalls warten.

Die folgende SQL-Anweisung wird nicht blockiert, da sie nicht im Next-Key Lock- und Gap Lock-Bereich liegt und sofort ausgeführt werden kann:

EINFÜGEN IN z AUSWÄHLEN 8,6;
EINFÜGEN IN z SELECT 2,0;
EINFÜGEN IN z SELECT 6,7;

Aus dem obigen Beispiel können wir erkennen, dass der Zweck von Gap Lock darin besteht, mehrere Transaktionen zu organisieren, um Daten in denselben Bereich einzufügen, was zu Phantomproblemen führt. Im Beispiel hat Transaktion A den Datensatz mit b=3 gesperrt. Wenn zu diesem Zeitpunkt keine Gap-Sperre (3,6] vorhanden ist, können andere Transaktionen Datensätze mit dem Index b, Spalte 3 einfügen, was dazu führt, dass der Benutzer in Transaktion A beim erneuten Ausführen derselben Abfrage andere Datensätze zurückgibt, was zu Phantom-Lesevorgängen führt.

Benutzer können Gap Lock auch auf die folgenden zwei Arten explizit deaktivieren (wird jedoch nicht empfohlen):

• Setzen Sie die Transaktionsisolationsebene auf READ COMMITED
• Setzen Sie den Parameter innodb_locks_unsafe_for_binlog auf 1

In InnoDB wird bei Einfügevorgängen geprüft, ob der nächste einzufügende Datensatz gesperrt ist. Wenn dies der Fall ist, ist das Einfügen nicht zulässig. Im obigen Beispiel hat Transaktion A den Datensatz mit b = 3 in Tabelle z gesperrt, d. h. sie hat den Bereich (1,3) gesperrt. Wenn zu diesem Zeitpunkt der folgende Einschub in anderen Transaktionen ausgeführt wird, führt dies ebenfalls zu einer Blockierung:

INSERT INTO z SELECT 2,0

Denn wenn man einen Datensatz mit dem Wert 2 in die Hilfsindexspalte b einfügt, wird erkannt, dass der nächste Datensatz 3 indexiert wurde. Nach der Änderung des Wertes der Spalte b kann er ausgeführt werden

INSERT INTO z SELECT 2,0

Phantomproblem

Phantomlesen bedeutet, dass die zweimalige Ausführung derselben SQL-Anweisung hintereinander in derselben Transaktion zu unterschiedlichen Ergebnissen führen kann. Die zweite SQL-Anweisung kann Zeilen zurückgeben, die vorher nicht vorhanden waren.

Unter der Standardtransaktionsisolationsebene (REPEATABLE READ) verwendet die InnoDB-Speicher-Engine den Next-Key-Locking-Mechanismus, um Phantomleseprobleme zu vermeiden.

Zusammengesetzter (gemeinsamer) Primärschlüssel und Schloss

Die obige Einführung in den Sperrmechanismus (Auszug aus „MySQL Technology Insider InnoDB Storage Engine 2. Ausgabe“) gilt nur für Hilfsindizes und Clusterindizes. Was ist also die Manifestation von zusammengesetzten Primärschlüssel-Abwärtssperren? Im Buch habe ich die Antwort nicht gefunden und es deshalb in der Praxis ausprobiert.

Erstellen Sie zunächst eine Tabelle mit einem zusammengesetzten Primärschlüssel

Tabelle `composite_primary_lock_test` erstellen (
 `id1` int(255) NICHT NULL,
 `id2` int(255) NICHT NULL,
 PRIMÄRSCHLÜSSEL (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (10, 10);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 8);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 6);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 6);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 3);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 1);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 1);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (7, 1);

Transaktion A fragt zuerst die Spalte mit id2=6 ab und fügt eine Zeilensperre hinzu

select * from composite_primary_lock_test where id2 = 6 Sperre im Share-Modus

Wird die Sperre zu diesem Zeitpunkt auf eine Datensatzsperre herabgestuft? Transaktion B aktualisiert ein Datenelement im Next-Key Lock-Bereich (id1=1,id2=8). Beweisen Sie Folgendes:

UPDATE `composite_primary_lock_test` SE, wobei `id1` = 1 und `id2` = 8;

Das Ergebnis ist, dass UPDATE blockiert ist. Versuchen wir also, beim Sperren beide Primärschlüssel in der Where-Anweisung hinzuzufügen:

select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode

UPDATE ausführen

UPDATE `composite_primary_lock_test` SE, wobei `id1` = 1 und `id2` = 8;

Das Ergebnis ist, dass UPDATE nicht blockiert ist

Die oben gesperrten Daten mit ID2=6 haben mehr als einen Datensatz. Versuchen Sie dann, die eindeutigen Daten mit ID2=8 basierend auf nur einem Primärschlüssel zu sperren. Wird die Sperre auf Zeilenebene herabgestuft?

Wählen Sie * aus Composite_Primary_Lock_Test, wobei ID2 = 8, Sperre im Freigabemodus;

UPDATE `composite_primary_lock_test` SE, wobei `id1` = 12 und `id2` = 10;

Auch das Ergebnis ist blockiert und das Experiment beweist:

Wenn bei einem zusammengesetzten Primärschlüssel beim Sperren nicht alle Primärschlüssel enthalten sind, verwendet InnoDB den Next-Key-Locking-Algorithmus. Wenn alle Primärschlüssel enthalten sind, wird dies als eindeutiger Index behandelt und auf Record Lock herabgestuft, wodurch nur der aktuelle Datensatz gesperrt wird.

Mehrspaltige Indizes (gemeinsame Indizes) und Sperren

Oben wird nur der Sperrmechanismus unter dem zusammengesetzten Primärschlüssel überprüft. Was ist mit mehrspaltigen Indizes? Wird dieser mit dem zusammengesetzten Indexmechanismus identisch sein? Was ist mit eindeutigen Indizes mit mehreren Spalten?

Erstellen Sie eine neue Testtabelle und initialisieren Sie die Daten

Tabelle `multiple_idx_lock_test` erstellen (
 `id` int(255) NICHT NULL,
 `idx1` int(255) NICHT NULL,
 `idx2` int(255) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`id`,`idx1`) MIT BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `multiple_idx_lock_test` 
EINDEUTIGEN INDEX `idx_multi`(`idx1`, `idx2`) UNTER VERWENDUNG VON BTREE HINZUFÜGEN;

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (1, 1, 1);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (5, 2, 2);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (7, 3, 3);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (4, 4, 4);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (2, 4, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (3, 5, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (8, 6, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (6, 6, 6);

Transaktion A fügt der Abfrage eine S-Sperre hinzu. Während der Abfrage wird nur die Spalte idx1 verwendet und das Prinzip „ganz links“ befolgt:

Wählen Sie * aus multiple_idx_lock_test, wobei idx1 = 6 Sperre im Freigabemodus ist;

Fügen Sie nun einen Datenabschnitt innerhalb des Next-Key Lock-Bereichs ein:

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

Das Ergebnis ist gesperrt. Versuchen Sie erneut, alle Felder im mehrspaltigen Index zu sperren:

Wählen Sie * aus multiple_idx_lock_test, wobei idx1 = 6 und idx2 = 6 im Freigabemodus sperren;

Fügen Sie einen Datenabschnitt in den Next-Key Lock-Bereich ein:

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

Das Ergebnis ist, dass es keine Blockierung gibt

Daraus ist ersichtlich, dass bei Verwendung eines eindeutigen Index mit mehreren Spalten zum Sperren die Angabe der zu sperrenden Zeilen (dh aller Spalten des beim Sperren verwendeten Index) erforderlich ist. Nur dann betrachtet InnoDB den Datensatz als eindeutigen Wert und die Sperre wird auf eine Datensatzsperre herabgestuft. Andernfalls wird der Next-Key Lock-Algorithmus verwendet, um die Daten innerhalb des Bereichs zu sperren.

Zusammenfassen

Seien Sie vorsichtig, wenn Sie Sperren in MySQL verwenden, insbesondere beim Aktualisieren/Löschen von Daten. Versuchen Sie, Aktualisierungen mit Primärschlüsseln zu verwenden. Wenn Sie eine Tabelle mit zusammengesetzten Primärschlüsseln aktualisieren, achten Sie darauf, alle Primärschlüssel zu aktualisieren, um Deadlocks und andere Probleme zu vermeiden, die durch einen größeren Sperrbereich verursacht werden.

Das ist alles für diesen Artikel. Ich hoffe, dass der Inhalt dieses Artikels für Ihr Studium oder Ihre Arbeit von gewissem Referenzwert ist. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.

siehe

„Mysql Technology Insider InnoDB-Speicher-Engine 2. Ausgabe“ – Jiang Chengyao