Detaillierte Diskussion der InnoDB-Sperren (Record-, Gap-, Next-Key-Sperre)

Die Datensatzsperre sperrt einen einzelnen Indexdatensatz. Die Datensatzsperre sperrt immer den Index, nicht den Datensatz selbst. Selbst wenn es keinen Index für die Tabelle gibt, erstellt InnoDB im Hintergrund einen versteckten Clustered Primary Key-Index, und dieser versteckte Clustered Primary Key-Index wird gesperrt. Wenn also einer SQL-Anweisung kein Index folgt, wird hinter jedem Clustered-Index eine X-Sperre hinzugefügt. Dies ähnelt einer Tabellensperre, sollte sich aber im Prinzip völlig von einer Tabellensperre unterscheiden.

Die Lückensperre sperrt die Lücke zwischen Indexdatensätzen oder sperrt vor oder nach einem Indexdatensatz, jedoch nicht den Indexdatensatz selbst. Der Gap-Lock-Mechanismus wird hauptsächlich verwendet, um das Phantomleseproblem im wiederholbaren Lesemodus zu lösen. Im Folgenden werden Phantomlesevorgänge und die Art und Weise, wie Gap-Locks Phantomlesevorgänge lösen, demonstriert. Lassen Sie uns zunächst einige Definitionen zu diesem Bereich geben

Momentaufnahme gelesen:

Einfacher Auswahlvorgang, ohne Sperre im Freigabemodus oder zum Aktualisieren. Beim Lesen des Snapshots werden keine Sperren hinzugefügt. Aufgrund des konsistenten Lesemechanismus ohne Sperren von MySQL wird kein Lesen des Snapshots blockiert. Wenn die Transaktionsisolationsebene jedoch SERIALIZABLE ist, wird der Snapshot-Lesevorgang auch mit einer gemeinsamen Next-Key-Sperre hinzugefügt. Dieser Artikel beschreibt die SERIALIZABLE-Isolationsebene nicht.

Derzeit lese ich:

Der Begriff in der offiziellen Dokumentation lautet „Locking Read“ (Lesen sperren), was „Einfügen“, „Aktualisieren“, „Löschen“, „Auswählen … im Freigabemodus“ und „Auswählen … zum Aktualisieren“ bedeutet. Der aktuelle Lesevorgang sperrt alle gescannten Indexdatensätze, unabhängig davon, ob die nachfolgende Where-Bedingung den entsprechenden Zeilendatensatz trifft. Der aktuelle Lesevorgang kann zu einem Deadlock führen.

Absichtssperre:

Die Intention-Sperre von Innodb wird hauptsächlich dann verwendet, wenn mehrere Granularitätssperren gleichzeitig bestehen. Wenn beispielsweise Transaktion A eine S-Sperre auf eine Tabelle anwenden möchte und eine Zeile in der Tabelle durch Transaktion B gesperrt wurde, sollte auch die Anwendung der Sperre blockiert werden. Wenn die Tabelle viele Daten enthält, ist der Aufwand für die zeilenweise Überprüfung des Sperrflags sehr hoch und beeinträchtigt die Systemleistung. Um dieses Problem zu lösen, kann auf Tabellenebene ein neuer Sperrtyp eingeführt werden, der den Sperrstatus der Zeile anzeigt, zu der er gehört, was zum Konzept der „Absichtssperre“ führt. Wenn eine Tabelle beispielsweise 100 Millionen Datensätze enthält und Transaktion A mehrere Zeilen davon gesperrt hat, muss Transaktion B der Tabelle eine Sperre auf Tabellenebene hinzufügen. Wenn keine beabsichtigte Sperre vorhanden ist, muss die Tabelle überprüft werden, um festzustellen, ob die 100 Millionen Datensätze gesperrt sind. Wenn eine Absichtssperre vorhanden ist und Transaktion A vor dem Aktualisieren eines Datensatzes eine Absichtssperre und dann eine X-Sperre hinzufügt, prüft Transaktion B zunächst, ob eine Absichtssperre für die Tabelle vorhanden ist und ob die vorhandene Absichtssperre mit der Sperre in Konflikt steht, die sie gerade hinzufügen möchte. Wenn ein Konflikt besteht, wartet sie, bis Transaktion A ihn freigibt, ohne jeden Datensatz einzeln prüfen zu müssen. Wenn Transaktion B die Tabelle aktualisiert, muss sie nicht wissen, welche Zeile gesperrt ist. Sie muss nur wissen, dass eine Zeile gesperrt ist.
Einfach ausgedrückt besteht die Hauptfunktion der Absichtssperre darin, den Widerspruch zwischen Zeilensperren und Tabellensperren zu behandeln und anzuzeigen, dass „eine Transaktion eine Sperre für eine Zeile hält oder im Begriff ist, eine Sperre zu halten“.

Nicht wiederholbares Lesen:

Dies bedeutet, dass in derselben Transaktion die gelesenen Datensätze für mehrere aufeinanderfolgende Snapshot-Lesevorgänge identisch sein sollten.

Die Demonstration des nicht wiederholbaren Lesens ist relativ einfach und wird in diesem Artikel nicht erläutert.

Phantomlesung:

Dies bedeutet, dass in einer Transaktion A ein aktueller Lesevorgang ausgeführt wird und eine andere Transaktion B einen Datensatz innerhalb des betroffenen Bereichs der Transaktion A einfügt. Wenn zu diesem Zeitpunkt Transaktion A einen weiteren aktuellen Lesevorgang ausführt, werden Phantomzeilen angezeigt. Der Hauptunterschied zwischen diesem und dem nicht wiederholbaren Lesen besteht darin, dass es sich bei Transaktion A einmal um einen Snapshot-Lesen und einmal um einen aktuellen Lesevorgang handelt; und bei Transaktion B einmal um einen beliebigen DML-Vorgang und einmal nur um ein Einfügen. Beispielsweise ist in A der Ergebnissatz von „select * from test where id<10 lock in share mode“ (1,2,3). Zu diesem Zeitpunkt wird ein Datensatz 4 in die Testtabelle in B eingefügt. Dann ist der Ergebnissatz der erneuten Abfrage in A (1,2,3,4), was nicht mit dem Ergebnissatz der ersten Abfrage in Transaktion A übereinstimmt. Die 4 ist hier eine Phantomzeile.

Demonstrationsbedingungen: In der Isolationsstufe „Reread“ wird standardmäßig Next-Key Locks verwendet, eine Kombination aus Record Lock und Gap Lock. Mit anderen Worten, zusätzlich zur Sperrung des Datensatzes selbst werden auch die Lücken zwischen den Indizes gesperrt. Daher ist dieser Gap-Lock-Mechanismus standardmäßig eingeschaltet und erzeugt keine Phantomzeilen. Wenn wir Phantomzeilen demonstrieren möchten, können wir entweder die Isolationsstufe auf Read-Commited ändern oder die Gap Lock im REPEATABLE-READ-Modus deaktivieren. Hier verwenden wir die zweite Methode.

Die Demonstration des Phantomlesens führt vor der Demonstration den Parameter innodb_locks_unsafe_for_binlog ein, mit dem die Lückensperre deaktiviert werden kann.

innodb_locks_unsafe_for_binlog: statischer Parameter, der Standardwert ist 0, was bedeutet, dass die Lückensperre aktiviert ist. Wenn er auf 1 gesetzt ist, bedeutet dies, dass die Lückensperre deaktiviert ist. Derzeit verfügt MySQL nur über eine Datensatzsperre. Es ist jedoch zu beachten, dass die Lückensperre, die für die Überprüfung auf Duplikate von Fremdschlüsseln und eindeutigen Schlüsseln verwendet wird, auch dann gültig ist, wenn er auf 1 gesetzt ist. An diesem Punkt kann einfach verstanden werden, dass die Isolationsstufe der Transaktion zu einem wiederholbaren Lesevorgang degeneriert und es immer noch einen gewissen Unterschied zwischen den beiden geben sollte. Es wird empfohlen, diesen Parameter nicht einfach so festzulegen. Die Einstellung hier dient lediglich einer einfachen Phantom-Lese-Demonstration. In späteren MySQL-Versionen wird dieser Parameter möglicherweise nicht mehr verwendet.

Sitzung 1 fügt zuerst einen aktuellen Lesevorgang zu den Datensätzen mit myid>95 hinzu

mysql> anzeigen, Tabelle erstellen, test_gap_lock\G
*************************** 1. Reihe ***************************
Tabelle: test_gap_lock
Tabelle erstellen: CREATE TABLE `test_gap_lock` (
`id` int(11) NICHT NULL,
`name` varchar(100) DEFAULT NULL,
`myid` int(11) DEFAULT NULL,
Primärschlüssel (`id`),
EINZIGARTIGER SCHLÜSSEL `uniq_name` (`name`),
SCHLÜSSEL `idex_myid` (`myid`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)
mysql> beginnen;
mysql> wähle * aus test_gap_lock, wobei myid>95 für Update;
+----+------------+------+
| ID | Name | meine ID |
+----+------------+------+
| 1 | jiang | 99 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 100 |
+----+------------+------+
3 Zeilen im Satz (0,00 Sek.)

Sitzung 2: Zu diesem Zeitpunkt fügt Sitzung 2 den Datensatz mit myid=98 erfolgreich ein.

in test_gap_lock-Werte einfügen (6, „jiang2“, 98);

Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

Bei einer erneuten Prüfung durch Sitzung 1 wird festgestellt, dass der Datensatz mit myid=98 bereits vorhanden ist. Dieser Datensatz ist die Phantomzeile.

mysql> wähle * aus test_gap_lock, wobei myid>95 für Update;
+----+------------+------+
| ID | Name | meine ID |
+----+------------+------+
| 1 | jiang | 99 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 100 |
| 6 | jiang2 | 98 |
+----+------------+------+
4 Zeilen im Satz (0,00 Sek.)

Gap-Lock-Mechanismus löst Phantom-Read-Problem Demonstrationsbedingungen: Wir ändern den Wert innodb_locks_unsafe_for_binlog zurück auf den Standardwert 0 und tx_isolation ist

REPEATABLE-READ, erklären Sie es unbedingt während der Demonstration, um sicherzustellen, dass SQL den nicht eindeutigen Index idx_myid verwendet (denn wenn die Testdaten klein sind, scannt der Optimierer möglicherweise direkt die gesamte Tabelle, was dazu führt, dass alle Datensätze gesperrt werden und es unmöglich wird, eine Lückensperre zu simulieren).

Demonstrationsbeispiel 1 (nicht eindeutiger Index + aktuell gelesener Bereich)mysql> show create table test_gap_lock\G

*************************** 1. Reihe ***************************
Tabelle: test_gap_lock
Tabelle erstellen: CREATE TABLE `test_gap_lock` (
`id` int(11) NICHT NULL,
`name` varchar(100) DEFAULT NULL,
`myid` int(11) DEFAULT NULL,
Primärschlüssel (`id`),
EINZIGARTIGER SCHLÜSSEL `uniq_name` (`name`),
SCHLÜSSEL `idex_myid` (`myid`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

Sitzung 1 erklärt zunächst, dass das aktuelle Lese-SQL der Sitzung den Index idx_myid ausführt.

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> erläutern Sie „select * from test_gap_lock where myid>100 für Update;“
+----+----------+--------------+-------+---------------+-----------+-----------+-----------+---------+------+------------+-----------+------------+---------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+--------------+-------+---------------+-----------+-----------+-----------+---------+------+------------+-----------+------------+---------------------------+
| 1 | SIMPLE | test_gap_lock | Bereich | idex_myid | idex_myid | 5 | NULL | 2 | Indexbedingung verwenden |
+----+----------+--------------+-------+---------------+-----------+-----------+-----------+---------+------+------------+-----------+------------+---------------------------+
1 Zeile im Satz (0,00 Sek.)
mysql> wähle * aus test_gap_lock, wobei myid>100 für Update;
+----+------------+------+
| ID | Name | meine ID |
+----+------------+------+
| 5 | hubingmei4 | 101 |
| 98 | Prüfung | 105 |
+----+------------+------+
2 Zeilen im Satz (0,00 Sek.)

Sitzung 2 fügt myid=56 zuerst erfolgreich ein, da die gesperrte Lücke myid>100 ist und 56 nicht in diesem Bereich liegt. Beim Einfügen von myid=109 bleibt es hängen, bis Sitzung 1 festschreibt, zurückrollt oder die Wartezeit für die Sperre abläuft. Bevor die Wartezeit für die Sperre abläuft, wird in Sitzung 1 dasselbe SQL ausgeführt und das Ergebnis ist immer noch nur der Datensatz mit der ID=5,98, wodurch das Phantomleseproblem vermieden wird.

mysql> in test_gap_lock-Werte einfügen (999, 'test2', 56);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
mysql> in test_gap_lock-Werte einfügen (123, 'test4', 109);
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.

Demonstrationsbeispiel 2 (nicht eindeutiger Index + gleiches aktuelles Lesen)mysql> select * from test_gap_lock;

+-----+------------+------+
| ID | Name | meine ID |
+-----+------------+------+
| 1 | jiang | 98 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 101 |
| 6 | jiang2 | 100 |
| 7 | jiang22 | 70 |
| 67 | jiang222 | 80 |
| 98 | Prüfung | 105 |
| 123 | Test4 | 109 |
| 999 | Test2 | 56 |
+-----+------------+------+
9 Zeilen im Satz (0,00 Sek.)
Sitzung 1
mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> erkläre das Löschen aus test_gap_lock, wo myid=100;
+----+----------+--------------+-------+---------------+---------------+-----------+-----------+----------+----------+----------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+--------------+-------+---------------+---------------+-----------+-----------+----------+----------+----------+
| 1 | SIMPLE | test_gap_lock | Bereich | idex_myid | idex_myid | 5 | const | 2 | Verwenden von where |
+----+----------+--------------+-------+---------------+---------------+-----------+-----------+----------+----------+----------+
1 Zeile im Satz (0,00 Sek.)
mysql> löschen aus test_gap_lock, wo myid=100;
Abfrage OK, 2 Zeilen betroffen (0,00 Sek.)

Sitzung 2 fügt den Datensatz mit myid=99 ein, der aufgrund einer Lückensperre immer noch blockiert ist; das Einfügen des Datensatzes mit myid=97 ist erfolgreich

mysql> in test_gap_lock-Werte einfügen (676, „Lücke aufgezeichneter Test“, 99);
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.
mysql> in test_gap_lock-Werte einfügen (675, 'Lücke aufgezeichnet Test1', 97);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

Beispiel 3 (Primärschlüsselindex + aktuell gelesener Bereich)

mysql> wähle * aus test_gap_lock;
+-----+------------+------+
| ID | Name | meine ID |
+-----+------------+------+
| 1 | jiang | 98 |
| 2 | hubingmei | 98 |
| 5 | hubingmei4 | 100 |
| 6 | jiang2 | 100 |
| 7 | jiang22 | 70 |
| 67 | jiang222 | 80 |
| 98 | Prüfung | 105 |
| 123 | Test4 | 109 |
| 999 | Test2 | 56 |
+-----+------------+------+
9 Zeilen im Satz (0,00 Sek.)
Sitzung 1
mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> erläutern Sie „select * from test_gap_lock“, wobei ID > 100 für Update ist;
+----+----------+---------------+-------+---------------+---------+---------+---------+------+---------+---------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------------+-------+---------------+---------+---------+---------+------+---------+---------+
| 1 | SIMPLE | test_gap_lock | Bereich | PRIMARY | PRIMARY | 4 | NULL | 2 | Verwenden von „where“ |
+----+----------+---------------+-------+---------------+---------+---------+---------+------+---------+---------+
1 Zeile im Satz (0,00 Sek.)
mysql> wähle * aus test_gap_lock, wobei ID > 100 für Update;
+-----+-------+------+
| ID | Name | meine ID |
+-----+-------+------+
| 123 | Test4 | 109 |
| 999 | Test2 | 56 |
+-----+-------+------+
2 Zeilen im Satz (0,00 Sek.)

Sitzung 2 (id=3 kann eingefügt werden; id=108 kann aufgrund einer Lückensperre nicht eingefügt werden; der Datensatz mit id=123 kann im Freigabemodus nicht ausgewählt werden, da eine Datensatzsperre auf dem Datensatz besteht; id=125 kann im Freigabemodus ausgewählt und aktualisiert werden, was ziemlich seltsam ist. Dies sollte auch als aktuelles Lesen betrachtet werden. Ich habe jedoch später die offizielle Dokumentation überprüft und festgestellt, dass die Lückensperre nur den Einfügevorgang blockiert, da sich in der Lücke keine Datensätze befinden. Mit Ausnahme des Einfügevorgangs sollten die Ergebnisse anderer Vorgänge denen von keinen Vorgängen entsprechen, sodass MySQL sie nicht blockiert.)

mysql> in test_gap_lock-Werte einfügen (108, 'Gap Lock Test3', 123);
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.
mysql> in test_gap_lock-Werte einfügen (3, 'Gap Lock Test3', 123);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
mysql> select * from test_gap_lock where id=125 Sperre im Freigabemodus;
Leerer Satz (0,00 Sek.)
mysql> erläutern Sie „select * from test_gap_lock where id=125 lock in share mode“;
+----+-----------+------+---------+---------------+------+------+------+------+------+------+----------------------------------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+------+---------+---------------+------+------+------+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Unmögliches WHERE nach dem Lesen von const-Tabellen bemerkt |
+----+-----------+------+---------+---------------+------+------+------+------+------+------+----------------------------------------------------+
1 Zeile im Satz (0,00 Sek.)
mysql> aktualisiere test_gap_lock, setze myid=12345, wobei id=125;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 0 Geändert: 0 Warnungen: 0

Internes Sperrprinzip der Gap-Sperre. Voraussetzungen für die Gap-Sperre: 1. Die Transaktionsisolationsstufe ist REPEATABLE-READ, der Parameter innodb_locks_unsafe_for_binlog ist 0 und der von SQL verwendete Index ist ein nicht eindeutiger Index.

2 Die Transaktionsisolationsstufe ist REPEATABLE-READ, der Parameter innodb_locks_unsafe_for_binlog ist 0 und SQL ist ein aktueller Lesevorgang eines Bereichs. In diesem Fall wird eine Lückensperre hinzugefügt, auch wenn es sich nicht um einen nicht eindeutigen Index handelt.

Lückenschloss-Verriegelungsschritte

Die obigen Beispiele 1 (nicht eindeutiger Index + aktueller Lesebereich) und 3 (Primärschlüsselindex + aktueller Lesebereich) sind leicht zu verstehen. Warum erzeugt Beispiel 2 (nicht-Primärschlüsselindex + gleicher aktueller Lesebereich) auch eine Lückensperre? Dies sollte vom Prinzip des B-Baum-Index ausgehen. Wir alle wissen, dass der B-Baum-Index geordnet ist und InnoDB einen gruppierten Primärschlüsselindex hat. Meine Zeichenfähigkeiten sind begrenzt. Ich habe den Sperrvorgang von Beispiel 2 als Beispiel analysiert. Der handschriftliche Sperrvorgang ist wie folgt

Aus der Reihenfolge der Datenorganisation in der Abbildung können wir erkennen, dass es zwei Datensätze mit myid = 100 gibt. Wenn eine Lückensperre hinzugefügt wird, werden drei Lücken generiert, nämlich gap1 (98, 100), gap2 (100, 100) und gap3 (100, 105). Die myid-Werte in diesen drei offenen Intervallen (wenn ich mich richtig an die Mathematik aus der High School erinnere) können nicht eingefügt werden. Offensichtlich hat gap1 auch (myid = 99, id = 3) (myid

=99,id=4), gap2 hat keine tatsächliche Lücke und gap3 hat Datensätze wie (myid=101,id=7). Zusätzlich wird eine Datensatzsperre auf die beiden Datensätze mit myid=100 gesetzt, was bedeutet, dass diese beiden Datendienste nicht von anderen Sitzungen gelesen werden können (siehe Beispiel 3).

Next-Key-Schlösser

Standardmäßig ist die Transaktionsisolationsebene von MySQL auf wiederholbares Lesen eingestellt und der Parameter innodb_locks_unsafe_for_binlog ist 0. Dann werden standardmäßig Next-Key-Sperren verwendet. Bei den sogenannten Next-Key Locks handelt es sich um eine Kombination aus Record Lock und Gap Lock, das heißt, dass neben der Sperrung des Datensatzes selbst auch die Lücken zwischen den Indizes gesperrt werden.

Als nächstes analysieren wir, wie die meisten SQL-Typen gesperrt werden, wobei wir davon ausgehen, dass die Transaktionsisolationsebene „repeatable read“ ist.

wählen Sie .. aus

Kein Schloss jeglicher Art

Auswählen...aus der Sperre im Freigabemodus

Für alle gescannten Indexdatensätze wird eine gemeinsame Next-Key-Sperre sowie für den gruppierten Primärschlüsselindex eine exklusive Sperre vergeben.

Auswählen...von zum Aktualisieren

Fügen Sie eine exklusive Next-Key-Sperre für alle gescannten Indexdatensätze und eine exklusive Sperre für den Primärschlüssel-Clusterindex hinzu.

aktualisieren..wo löschen von..wo

Fügen Sie allen gescannten Indexdatensätzen eine Next-Key-Sperre und dem Clustered-Index des Primärschlüssels eine exklusive Sperre hinzu.

einfügen in..

Ein einfaches Einfügen fügt dem Indexdatensatz, der der eingefügten Zeile entspricht, eine exklusive Sperre hinzu. Dies ist eine Datensatzsperre und es gibt keine Lücke, sodass andere Sitzungen nicht daran gehindert werden, Datensätze in die Lücke einzufügen. Vor dem Einfügevorgang wird jedoch eine Sperre hinzugefügt. Das offizielle Dokument nennt sie die Einfügeabsichtslückensperre, die die absichtliche Lückensperre ist. Der Zweck dieser absichtlichen Lückensperre besteht darin, anzuzeigen, dass beim gleichzeitigen Einfügen mehrerer Transaktionen in dieselbe Lücke die eingefügten Datensätze, solange sie sich nicht an derselben Position in der Lücke befinden, abgeschlossen werden können, ohne auf andere Sitzungen warten zu müssen, sodass für den Einfügevorgang keine echte Lückensperre hinzugefügt werden muss. Stellen Sie sich vor, eine Tabelle hat den Index idx_test und enthält die Datensätze 1 und 8. Dann kann jede Transaktion jeden Datensatz zwischen 2 und 7 einfügen und fügt nur dem aktuell eingefügten Datensatz eine Datensatzsperre hinzu. Andere Sitzungen werden jedoch nicht daran gehindert, andere Datensätze als die eigenen einzufügen, da keine Konflikte vorliegen.

Vorausgesetzt, es liegt ein Fehler bei der Verletzung eines eindeutigen Schlüssels vor, wird für den doppelten Indexdatensatz eine Lesesperre eingerichtet. Wenn mehrere Sitzungen gleichzeitig denselben Zeilendatensatz einfügen und eine andere Sitzung eine exklusive Sperre für die Zeile erhalten hat, tritt ein Deadlock auf.

Demonstration des durch Einfügen verursachten Deadlock-Phänomens 1

mysql> anzeigen, Tabelle erstellen t1\G
*************************** 1. Reihe ***************************
Tabelle: t1
Tabelle erstellen: CREATE TABLE `t1` (
`i` int(11) NICHT NULL STANDARD '0',
PRIMÄRSCHLÜSSEL (`i`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

Sitzung 1

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> IN t1-WERTE EINFÜGEN(1);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

Sitzung 2 steckt zu diesem Zeitpunkt fest

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> IN t1-WERTE EINFÜGEN(1);

Sitzung 3 steckt zu diesem Zeitpunkt ebenfalls fest

mysql> beginnen;

Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> IN t1-WERTE EINFÜGEN(1);

Sitzung 1 Zu diesem Zeitpunkt setzen wir Sitzung 1 zurück

mysql> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Es stellt sich heraus, dass das Einfügen von Sitzung 2 erfolgreich ist, Sitzung 3 jedoch einen Deadlock erkennt und ein Rollback durchführt.

Sitzung 2, Abfrage OK, 1 Zeile betroffen (28,87 Sek.)

Sitzung 3 FEHLER 1213 (40001): Beim Versuch, eine Sperre zu erhalten, wurde ein Deadlock festgestellt. Versuchen Sie, die Transaktion neu zu starten.

Analyse der Deadlock-Ursachen:

Zuerst fügt Sitzung1 einen Datensatz ein und erhält eine exklusive Sperre für den Datensatz. Zu diesem Zeitpunkt erkennen sowohl Sitzung2 als auch Sitzung3 Primärschlüsselkonfliktfehler, aber da Sitzung1 nicht festgeschrieben wurde, wird Sitzung1 nicht als erfolgreich beim Einfügen des Datensatzes angesehen und kann daher keinen Fehler direkt melden. Daher beantragen sowohl Sitzung2 als auch Sitzung3 eine gemeinsame Sperre für den Datensatz, aber zu diesem Zeitpunkt haben sie die gemeinsame Sperre noch nicht erhalten und befinden sich in der Warteschlange. Zu diesem Zeitpunkt wird Sitzung1 zurückgesetzt und die exklusive Sperre für den Zeilendatensatz freigegeben. Anschließend erhalten sowohl Sitzung2 als auch Sitzung3 die gemeinsame Sperre für die Zeile. Wenn Sitzung2 und Sitzung3 Datensätze einfügen möchten, müssen sie exklusive Sperren erhalten. Da sie jedoch beide über gemeinsame Sperren verfügen, können sie niemals exklusive Sperren erhalten, sodass ein Deadlock auftritt. Wenn Sitzung1 zu diesem Zeitpunkt festgeschrieben und nicht zurückgesetzt wird, melden sowohl Sitzung2 als auch Sitzung3 direkt einen Primärschlüsselkonfliktfehler. Überprüfen Sie das Deadlock-Protokoll und Sie sehen das Ergebnis auf einen Blick

Deadlock durch Insert 2 verursacht

Ein weiterer ähnlicher Deadlock besteht darin, dass Sitzung1 den Datensatz mit der ID = 1 löscht und nicht festschreibt. Zu diesem Zeitpunkt fügen Sitzung2 und Sitzung3 den Datensatz mit der ID = 1 ein. Zu diesem Zeitpunkt wird Sitzung1 festgeschrieben. Wenn Sitzung2 und Sitzung3 etwas einfügen müssen, müssen sie exklusive Sperren erhalten, und dann kommt es zu einem Deadlock. Wenn Sitzung1 zurückgesetzt wird, melden Sitzung2 und Sitzung3 Primärschlüsselkonfliktfehler. Keine Demonstration mehr hier.

EINFÜGEN ... BEIM UPDATE DES DOPPELTEN SCHLÜSSELS

Der Unterschied zwischen dieser Art von SQL und der Einfügesperre besteht darin, dass bei Erkennung eines Schlüsselkonflikts direkt eine exklusive Sperre anstelle einer gemeinsamen Sperre beantragt wird.

ersetzen

Wenn die Ersetzungsoperation keinen Schlüsselkonflikt erkennt, ist ihre Sperrstrategie ähnlich der der Einfügeoperation. Wenn ein Schlüsselkonflikt erkannt wird, beantragt sie ebenfalls direkt eine exklusive Sperre.

EINFÜGEN IN T, AUSWÄHLEN … VON S, WO …

Die Sperrstrategie für die T-Tabelle ist die gleiche wie für normale Einfügungen. Zusätzlich wird den zugehörigen Datensätzen in der S-Tabelle eine gemeinsame Next-Key-Sperre hinzugefügt. (Wenn es sich um den wiederholbaren Lesemodus handelt, wird er nicht gesperrt.)

CREATE TABLE ... SELECT ... fügt der ausgewählten Tabelle eine gemeinsame Next-Key-Sperre hinzu

Sperrstrategie für die automatische ID-Inkrementierung

Wenn ein Feld in einer Tabelle eine Auto-Increment-Spalte ist, fügt InnoDB am Ende des Indexes eine exklusive Sperre hinzu. Um auf diesen Autoinkrementwert zugreifen zu können, ist eine Sperre auf Tabellenebene erforderlich. Die Dauer dieser Sperre auf Tabellenebene entspricht jedoch nur dem aktuellen SQL, nicht der gesamten Transaktion. Das heißt, die Sperre auf Tabellenebene wird freigegeben, nachdem das aktuelle SQL ausgeführt wurde. Andere Sitzungen können keine Datensätze einfügen, solange diese Sperre auf Tabellenebene aufrechterhalten wird.

Sperrstrategie zur Fremdschlüsselerkennung

Wenn eine Fremdschlüsseleinschränkung vorhanden ist, wird bei jedem Einfüge-, Aktualisierungs- oder Löschvorgang die Einschränkung überprüft und eine gemeinsame Datensatzsperre für den entsprechenden Datensatz eingerichtet, unabhängig davon, ob ein Fremdschlüsselkonflikt vorliegt.

Der obige Artikel behandelt ausführlich InnoDB-Sperren (Record-, Gap- und Next-Key-Sperre). Das ist alles, was ich mit Ihnen teilen möchte. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.