Detaillierte Erläuterung des Sperrbereichs auf Zeilenebene der MySQL-Datenbank-InnoDB-Engine
- 小云云Original
- 2018-02-23 13:49:111867Durchsuche
Die MySQL-Datenbank-InnoDB-Engine unterstützt das Sperren auf Zeilenebene, was bedeutet, dass wir Sperrvorgänge für bestimmte Datenzeilen in der Tabelle durchführen können. Die Auswirkung des Sperrvorgangs ist: Wenn eine Sache einen Sperrvorgang für eine Zeile in der Tabelle ausführt Tabelle und eine andere Die Transaktion muss auch einen Sperrvorgang für dieselbe Zeile ausführen, sodass der Sperrvorgang der zweiten Transaktion möglicherweise blockiert wird. Nach dem Blockieren kann die zweite Transaktion nur warten, bis die erste Transaktion abgeschlossen ist (Festschreibung oder Rollback). oder Zeitüberschreitung.
In diesem Artikel werden hauptsächlich die Konzepte im Zusammenhang mit Zeilensperren in InnoDB vorgestellt, wobei der Schwerpunkt auf dem Sperrbereich von Zeilensperren liegt:
Welche Art von SQL-Anweisungen werden gesperrt?
Welche Art von Sperre soll ich hinzufügen?
Welche Zeilen werden durch die Sperranweisung gesperrt?
Hintergrundwissen
Oben haben wir kurz die Zeilenebenensperren von InnoDB vorgestellt. Um den anschließenden Verifizierungsteil zu verstehen, müssen wir etwas Hintergrundwissen hinzufügen. Wenn Sie die entsprechenden Kenntnisse sehr gut kennen, können Sie direkt zum Verifizierungsteil springen.
1. Arten von InnoDB-Sperren
Die InnoDB-Engine verwendet sieben Arten von Sperren:
Gemeinsame exklusive Sperre (Shared und exklusive Sperren)
Absichtssperren
Datensatzsperren
-
Lückensperren
-
Next-Key-Sperren
Absichtssperren einfügen
AUTO-INC-Sperren
Dieser Artikel befasst sich hauptsächlich mit Shared- und Exclusive-Sperren, Record-Sperren, Gap-Sperren und Next-Key-Sperren Wenn Sie sich für andere Arten von Schlössern interessieren, können Sie selbst mehr darüber erfahren. Ich werde hier nicht näher darauf eingehen.
1.1 Gemeinsame und exklusive Sperren
Die Konzepte von gemeinsam genutzten Sperren (S-Sperren) und exklusiven Sperren (X-Sperren) sind in vielen Programmiersprachen aufgetaucht. Beschreiben wir zunächst die Auswirkungen dieser beiden Sperren in MySQL:
Wenn eine Transaktion einer bestimmten Datenzeile eine S-Sperre hinzufügt, kann eine andere Transaktion auch eine S-Sperre zur entsprechenden Zeile hinzufügen . Sperre, aber die X-Sperre kann nicht zur entsprechenden Zeile hinzugefügt werden.
Wenn eine Transaktion einer bestimmten Datenzeile eine X-Sperre hinzufügt, kann eine andere Transaktion der entsprechenden Zeile weder eine S-Sperre noch eine X-Sperre hinzufügen.
Verwenden Sie eine klassische Matrixtabelle, um weiterhin die gegenseitige Ausschlussbeziehung zwischen gemeinsam genutzten Sperren und exklusiven Sperren zu veranschaulichen:
| S | X | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
|
0 | 1 | |||||||||
| X | 1 | 1 |
In der Abbildung steht S für eine gemeinsame Sperre und X für eine exklusive Sperre, 0 für Sperrenkompatibilität und 1 für Sperrenkonflikt ist nicht blockiert und Konflikt ist blockiert. Aus der Tabelle ist ersichtlich, dass andere Transaktionen auf etwaige Sperren warten müssen, sobald eine Transaktion eine exklusive Sperre hinzufügt. Mehrere gemeinsame Sperren blockieren sich nicht gegenseitig.
1.2 Datensatzsperren, Lückensperren, Next-Key-Sperren
Diese drei Arten von Sperren beschreiben alle den Umfang der Sperre und werden daher gemeinsam erläutert.
Die folgenden Definitionen stammen aus der offiziellen MySQL-Dokumentation
Datensatzsperren: Datensatzsperren sperren einen Datensatz im Index.
Lückensperren: Lückensperren sperren entweder den Wert in der Mitte des Indexdatensatzes oder den Wert vor dem ersten Indexdatensatz oder den Wert nach dem letzten Indexdatensatz.
Next-Key-Sperren: Die Next-Key-Sperre ist eine Kombination aus einer Datensatzsperre für den Indexdatensatz und einer Lückensperre vor dem Indexdatensatz. Indexdatensätze werden in den Definitionen von
erwähnt. Warum? Welche Beziehung besteht zwischen Zeilensperren und Indizes? Tatsächlich schließt InnoDB den Sperrvorgang ab, indem es den Index in der Tabelle durchsucht oder scannt und allen Indexdaten, auf die es trifft, eine gemeinsame Sperre oder eine exklusive Sperre hinzufügt. Daher können wir Sperren auf Zeilenebene (Sperren auf Zeilenebene) als Index-Datensatz-Sperren (Index-Datensatz-Sperren) bezeichnen, da Sperren auf Zeilenebene dem Index hinzugefügt werden, der der Zeile entspricht.
Die Schließbereiche der drei Schlosstypen sind unterschiedlich und werden sukzessive erweitert. Lassen Sie uns den Sperrbereich verschiedener Sperren anhand eines Beispiels kurz erläutern. Angenommen, die Indexspalte in Tabelle t enthält die vier numerischen Werte 3, 5, 8 und 9. Laut offiziellem Dokument beträgt der Sperrbereich Drei Sperren werden wie folgt bestimmt:
Der Sperrbereich der Datensatzsperre ist ein separater Indexdatensatz, bei dem es sich um die vier Datenzeilen 3, 5, 8 und 9 handelt.
Die Lückensperre sperrt die Lücke in der Zeile, die durch eine Menge als (-∞,3), (3,5), (5,8), (8) dargestellt wird ,9), (9,+∞).
Next-Key-Sperre ist eine Kombination aus der Index-Datensatzsperre und der Lückensperre vor der Index-Datensatzsperre. Sie wird durch einen Satz als (-∞,3], (3) dargestellt , 5], (5,8], (8,9], (9,+∞).
Abschließend müssen noch drei Punkte zur Lückensperre hinzugefügt werden:
-
Lückensperren verhindern, dass andere Transaktionen gleichzeitig Lückendaten einfügen, was das Phantomproblem effektiv lösen kann. Aus diesem Grund verwenden nicht alle Transaktionsisolationsstufen Die InnoDB-Engine verwendet Lückensperren nur auf der Isolationsebene Wiederholbares Lesen (Standard). Die Aufgabe von Lückensperren besteht lediglich darin, zu verhindern, dass andere Transaktionen Daten in Lücken einfügen verhindert, dass andere Transaktionen dieselbe Lückensperre haben. Dies bedeutet, dass
mit Ausnahme der Einfügeanweisung andere SQL-Anweisungen Lückensperren zur gleichen Zeile hinzufügen können - 🎜>Für das Sperrverhalten des eindeutigen Index ist die Lückensperre ungültig.
2 > Wir haben bereits eingeführt, dass InnoDB das Sperren durch das Scannen von Indexdatensätzen während der Ausführung von SQL-Anweisungen implementiert. Welche Anweisungen fügen welche Art von Sperren nacheinander hinzu:
select. .. from-Anweisung: Die InnoDB-Engine verwendet Multi-Version-Parallelitätskontrolle (MVCC), um nicht blockierendes Lesen zu implementieren, sodass InnoDB bei gewöhnlichen Select-Reading-Anweisungen nicht sperrt [Hinweis 1]
select ... from for update-Anweisung: Wie die obige Anweisung fügt InnoDB allen durchsuchten Indexdatensätzen eine Sperre für den nächsten Schlüssel hinzu. Wenn jedoch die eindeutige Zeile eines eindeutigen Index gescannt wird, wird der nächste Schlüssel auf eine Indexdatensatzsperre herabgestuft , aber wenn die eindeutige Zeile eines eindeutigen Index gescannt wird, wird der nächste Schlüssel auf eine Indexdatensatzsperre herabgestuft. [Anmerkung 2]
löschen ... wobei ... Aussage:. InnoDB fügt allen durchsuchten Indexdatensätzen Next-Key-Sperren hinzu. Wenn jedoch die eindeutige Zeile eines eindeutigen Index gescannt wird, wird der Next-Key auf eine Indexdatensatzsperre herabgestuft.
Anweisung einfügen: InnoDB legt nur eine exklusive Indexdatensatzsperre für die einzufügende Zeile fest.
Schließlich sind noch zwei Punkte hinzuzufügen:
Wenn eine Abfrage einen Hilfsindex verwendet und dem Indexdatensatz eine exklusive Sperre hinzufügt, wird InnoDB gesperrt den entsprechenden aggregierten Indexdatensatz.
Wenn Ihre SQL-Anweisung keinen Index verwenden kann, muss MySQL die gesamte Tabelle scannen, um die Anweisung zu verarbeiten. Das Ergebnis ist, dass jede Zeile der Tabelle gesperrt wird und andere Benutzer verhindert werden vom Zugriff auf alle Einfügungen in die Tabelle.
SQL-Anweisungsüberprüfung
Fahren wir ohne weitere Umschweife mit dem wichtigsten Teil der SQL-Anweisungsüberprüfung in diesem Artikel fort.
1. Testumgebung
Datenbank: MySQL 5.6.35
Transaktionsisolationsstufe: Wiederholbares Lesen
Datenbankzugriffsterminal: MySQL-Client
2
2.1 Szenario 1Tabelle erstellen:CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Daten einfügen:
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');Zuerst führen wir die Vorlage der SQL-Anweisung aus: | 步骤 | client 1 | client 2 |
|---|---|---|
| 1 | begin; | -- |
| 2 | SELECT * FROM user where name='e' for update; | -- |
| 3 | -- | begin; |
| 4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (10, #{name}); |
| 5 | rollback; | -- |
| 6 | -- | rollback; |
| name的值 | 执行结果 |
|---|---|
| a | 不阻塞 |
| b | 不阻塞 |
| d | 阻塞 |
| e | 阻塞 |
| f | 阻塞 |
| h | 不阻塞 |
| i | 不阻塞 |
SELECT * FROM user where name='e' for update eine Summe sperrt von drei Datensatzzeilen im Indexnamen ( Das c,e]-Intervall sollte die Next-Key-Sperre sein und das (e,h)-Intervall ist die Lücke nach dem Indexdatensatz e.
| 步骤 | client 1 | client 2 |
|---|---|---|
| 1 | begin; | -- |
| 2 | SELECT * FROM user where name='e' for update; | -- |
| 3 | -- | SELECT * FROM user where name=#{name} for update; |
| 5 | rollback; | -- |
| 6 | -- | rollback; |
| name的值 | 执行结果 |
|---|---|
| d | 不阻塞 |
| e | 阻塞 |
| f | 不阻塞 |
-Anweisung blockiert, die insert-Anweisung jedoch nicht. Wenn beide Anweisungen blockiert sind, handelt es sich um eine Indexdatensatzsperre. Wenn wir die Ausführungsergebnisse beobachten, können wir sehen, dass d und f die Lückensperre und e die Indexdatensatzsperre sind. Wir haben festgestellt, dass der Sperrbereich für den Hilfsindexnamen bei der Abfragebedingung select for update (c, e], (e,g) ist, wobei:
eine Indexdatensatzsperre hinzufügt [e] zum von der SQL-Anweisung gescannten Indexdatensatz e wird die Lücke vor e gesperrt, die Daten (c, e) zwischen c und e werden mit einer Lückensperre hinzugefügt where name='e'
- Die ersten beiden bilden das Next-Key-Schloss (c, e).
- Es ist erwähnenswert, dass die Lücke (e, g ) hinter e ist ebenfalls gesperrt.
- Aufmerksame Leser haben möglicherweise bemerkt, dass die Testdaten keine Lückengrenzen enthalten Wert: Die Vorlage zum Ausführen der SQL-Anweisung:
-
Ersetzen Sie die Werte von id und name in Schritt 5 und beobachten Sie die Ergebnisse:
id的值 name=c 执行结果 id的值 name=g 执行结果 -- -- -- -3 g 组塞 -- -- -- -2 g 阻塞 -1 c 不阻塞 -1 g 阻塞 1 c 不阻塞 1 g 不阻塞 2 c 不阻塞 2 g 阻塞 3 c 不阻塞 3 g 不阻塞 4 c 阻塞 4 g 阻塞 5 c 阻塞 5 g 阻塞 6 c 阻塞 6 g 阻塞 7 c 不阻塞 7 g 不阻塞 8 c 阻塞 8 g 不阻塞 9 c 不阻塞 9 g 不阻塞 10 c 阻塞 10 g 不阻塞 11 c 阻塞 - - - 12 c 阻塞 - - - Durch Beobachtung der obigen Ausführungsergebnisse haben wir festgestellt, dass, wenn name gleich c und e ist, die Das Ergebnis der
insert-Anweisung erhöht sich mit dem Wert von id. Anders als für eine Weile zu sperren und für eine Weile nicht zu sperren. Es muss sein, dass die ID-Spalte gesperrt ist, um ein solches Ergebnis zu verursachen.Wenn wir uns nicht zuerst die Ergebnisse der Datenzeile
id=5ansehen, finden wir ein Muster:Wenn
name=c,name=centsprichtid=3Die Lücken (3,5), (5,7), (7,9) und (9,∞) nach dem ID-Aggregatindexdatensatz sind alle gesperrt.Wenn
name=e,name=eden Lücken (5,7), (3,5), (1, 3), (-∞,1) entspricht, sind alle gesperrt.id=7- Wir können die
-Anweisung verwenden, um festzustellen, dass die zu den oben genannten Lücken hinzugefügten Sperren alle Lückensperren sind.
select * from user where id = x for update;
Vorlage zum Ausführen von SQL-Anweisungen:id=5
Ersetzen Sie die ID in Schritt 5. Wert , Beobachtung:步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name='e' for update; -- 3 -- SELECT * FROM user where id=#{id} for update; 5 rollback; -- 6 -- rollback; 通过观察执行结果可知，
id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。最终结论：
对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。
对辅助索引对应的聚合索引加上索引记录锁。
当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。
上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。
2.2 场景二
建表：
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意与场景一表user不同的是name列为唯一索引。
插入数据：
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a'); INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c'); INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e'); INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g'); INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');首先我们执行SQL语句的模板：
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name='e' for update; 3 -- begin; 4 -- INSERT INTO `user` (`id`, `name`) VALUES (10, #{name}); 5 rollback; -- 6 -- rollback; 替换步骤5中name的值，观察结果：
name的值 执行结果 a 不阻塞 b 不阻塞 c 不阻塞 d 不阻塞 e 阻塞 f 不阻塞 g 不阻塞 h 不阻塞 i 不阻塞 由测试结果可知，只有
name='e'这行数据被锁定。通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，
2.3 场景三
场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。
借用场景一的表和数据。
建表：
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a'); INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c'); INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e'); INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g'); INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');执行SQL语句的模板：
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- begin; 4 -- INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name}); 5 rollback; -- 6 -- rollback; 替换步骤5中name的值，观察结果：
name的值 执行结果 a 阻塞 b 阻塞 c 阻塞 d 阻塞 e 阻塞 f 阻塞 g 阻塞 h 阻塞 i 阻塞 这个结果是不是和你想象的不太一样，这个结果表明
where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？我们执行以下的SQL语句执行计划：
explain select * from user where name>'e' for update;
执行结果：
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | index_name | index_name | 26 | NULL | 5 | Using where; Using index | +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
如果你的结果与上面不同先执行一下
OPTIMIZE TABLE user;再执行以上语句。通过观察SQL语句的执行计划我们发现，语句使用了
name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。接下来我们再制造一组数据。
建表：CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');这张表和前表的区别是多了一列非索引列
age。我们再执行一下同样的SQL语句执行计划：
explain select * from user where name>'e' for update;
执行结果：
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | index_name | index_name | 26 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
是不是和第一次执行结果不同了，
rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。因为
select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：索引记录g在
name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。g和i对应
id列中的7和9加索引记录锁。当
name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。
接下来我们逐一测试：
首先测试验证了next-key锁范围，执行SQL语句的模板：
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- begin; 4 -- INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18'); 5 rollback; -- 6 -- rollback; 替换步骤5中name的值，观察结果：
name的值 执行结果 a 不阻塞 b 不阻塞 c 不阻塞 d 不阻塞 f 阻塞 g 阻塞 h 阻塞 i 阻塞 j 阻塞 k 阻塞 下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- SELECT * FROM user where name=#{name} for update; 5 rollback; -- 6 -- rollback; 替换步骤5中name的值，观察结果：
name的值 执行结果 e 不阻塞 f 不阻塞 g 阻塞 h 不阻塞 i 阻塞 j 不阻塞 接下来验证对
id列加索引记录锁，执行SQL语句的模板：步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- SELECT * FROM user where id=#{id} for update; 5 rollback; -- 6 -- rollback; 替换步骤5中id的值，观察结果：
id的值 执行结果 5 不阻塞 6 不阻塞 7 阻塞 8 不阻塞 9 阻塞 10 不阻塞 最后我们验证
name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- begin; 4 -- INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18'); 5 rollback; -- 6 -- rollback; 替换步骤5中id的值，观察结果：
id的值 执行结果 -1 不阻塞 1 不阻塞 2 不阻塞 3 不阻塞 4 不阻塞 5 不阻塞 6 阻塞 7 阻塞 8 阻塞 9 阻塞 10 阻塞 11 阻塞 12 阻塞 注意7和9是索引记录锁记录锁。
观察上面的所有SQL语句执行结果，可以验证
select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。2.4 场景四
我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。
建表：
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');和场景三表唯一不同是
name列为唯一索引。SQL语句
select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。我们通过SQL验证我们的结论，执行SQL语句的模板：
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- begin; 4 -- INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18'); 5 rollback; -- 6 -- rollback; Ersetzen Sie den Wert von name in Schritt 5 und beobachten Sie die Ergebnisse:
name的值 执行结果 a 不阻塞 b 不阻塞 c 不阻塞 d 不阻塞 f 阻塞 g 阻塞 h 阻塞 i 阻塞 j 阻塞 k 阻塞 Überprüfen Sie, welcher Teil der Next-Key-Sperre die Lückensperre und welcher Teil die Indexdatensatzsperre ist. Die Vorlage zum Ausführen der SQL-Anweisung:
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- SELECT * FROM user where name=#{name} for update; 5 rollback; -- 6 -- rollback; Ersetzen Sie den Wert von name in Schritt 5 und beobachten Sie die Ergebnisse:
name的值 执行结果 e 不阻塞 f 不阻塞 g 阻塞 h 不阻塞 i 阻塞 j 不阻塞 Durch die Überprüfungsergebnisse der beiden oben genannten SQL-Anweisungen haben wir Der Lock-Range-Trend unseres G- und i-Stacks hat die nächste Tastenkombination der beiden bewiesen.
Als nächstes überprüfen wir die Sperrübertragung auf den Aggregatindex nach dem Sperren des Hilfsindex und führen die Vorlage der SQL-Anweisung aus:
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- SELECT * FROM user where id=#{id} for update; 5 rollback; -- 6 -- rollback; Ersetzen Sie den Wert von id in Schritt 5 und Beobachten Sie die Ergebnisse:
id的值 执行结果 5 不阻塞 6 不阻塞 7 阻塞 8 不阻塞 9 阻塞 10 不阻塞 Aus den Ergebnissen ist ersichtlich, dass Indexdatensatzsperren zu den Spalten 7 und 9 im Gesamtindex hinzugefügt werden
nameentsprechend den Spalten g und i im Hilfsindexid.Bisher sind alle experimentellen Ergebnisse genau die gleichen wie bei Szenario drei, was auch leicht zu verstehen ist. Schließlich sind Szenario vier und Szenario drei nur unterschiedliche Indextypen von Hilfsindizes
name, einer ist ein eindeutiger Index und der andere ist ein normaler Index.Überprüfen Sie abschließend die Absicht, sperren Sie mit der nächsten Taste die Grenzdaten e und stellen Sie sicher, dass die Schlussfolgerung mit Szenario drei übereinstimmt.
Vorlage zum Ausführen von SQL-Anweisungen:
步骤 client 1 client 2 1 begin; -- 2 SELECT * FROM user where name>'e' for update; -- 3 -- begin; 4 -- INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18'); 5 rollback; -- 6 -- rollback; Ersetzen Sie den Wert von id in Schritt 5 und beobachten Sie die Ergebnisse:
id的值 执行结果 -1 不阻塞 1 不阻塞 2 不阻塞 3 不阻塞 4 不阻塞 5 不阻塞 6 不阻塞 7 阻塞 8 不阻塞 9 阻塞 10 不阻塞 11 不阻塞 12 不阻塞 Beachten Sie, dass 7 und 9 Index-Datensatzsperren sind .
Aus den Ergebnissen geht hervor, dass, wenn
nameals obere Grenze e des Indexdatensatzes aufgeführt ist, kein Sperrverhalten für die ID vorliegt, was sich von Szenario drei unterscheidet.Bereichsabfragen für eindeutige Indizes ähneln Bereichsabfragen für normale Indizes. Der einzige Unterschied besteht darin, dass Lückensperren nicht hinzugefügt werden, wenn der Hilfsindex dem Grenzwert des oberen und unteren Bereichs entspricht Primärschlüssel.
Eindeutiger Indexbereich-Abfrage-Sperrbereich:
Der Sperrbereich für gescannte Hilfsindexdatensätze ist eine Kombination aus mehreren Indexdatensätzen und einer Überlagerungskombination des Schlüsselbereichs.
Für den Sperrbereich des Aggregatindex (Primärschlüssel) werden Indexdatensatzsperren zu den Aggregatindexspalten hinzugefügt, die mehreren Hilfsindizes entsprechen.
Fazit
Die InnoDB-Engine fügt den gescannten Indexdatensätzen entsprechende Sperren hinzu. Durch „Szenario 1“ haben wir deutlich gemacht, dass das Scannen von a common Der Sperrbereich von Indexdatensätzen. Durch „Szenario 3“ können wir den Sperrbereich einer beliebigen Anzahl von Indexdatensätzen zum Scannen gewöhnlicher Indizes ableiten. Durch „Szenario 2“ haben wir den Sperrbereich für das Scannen eines eindeutigen Indexdatensatzes (oder Primärschlüssels) bestimmt. Durch „Szenario 4“ können wir den Sperrbereich einer beliebigen Anzahl von Scan-Indexdatensätzen (oder Primärschlüsseln) ableiten. Es kann in tatsächlichen Anwendungen flexibel verwendet werden, um festzustellen, ob zwei SQL-Anweisungen miteinander verbunden sind. Hierbei ist auch zu beachten, dass die Abfragebedingungen des Index nicht selbstverständlich sind. Sie entsprechen häufig nicht dem, was wir verstehen. Es ist notwendig, die Anzahl der vom Index letztendlich gescannten Datensätze zu beurteilen, andernfalls wird dies der Fall sein kann zu Abweichungen im Verständnis des Sperrbereichs führen.
Bemerkungen
Hinweis 1: Wenn die Transaktionsisolationsstufe SERIALIZABLE ist, fügen normale SELECT-Anweisungen auch Indizes hinzu, die während der Anweisungsausführung gescannt werden. Wenn die Anweisung einen eindeutigen Index durchsucht, wird die Next-Key-Sperre auf eine Indexdatensatzsperre herabgestuft.
Hinweis 2: Wenn eine Aktualisierungsanweisung einen aggregierten Indexdatensatz (Primärschlüssel) ändert, wird eine implizite Sperroperation für den betroffenen Hilfsindex durchgeführt. Wenn vor dem Einfügen eines neuen sekundären Indexdatensatzes eine Duplikatprüfung durchgeführt wird und wenn ein neuer sekundärer Indexdatensatz eingefügt wird, fügt der Aktualisierungsvorgang auch gemeinsame Sperren für die betroffenen sekundären Indexdatensätze hinzu.Verwandte Empfehlungen:
MySQL-Fehler beim Ausführen der SQL-Datei Fehler: Unbekannte Speicher-Engine'InnoDB So lösen Sie
Wann MySQL startet Was tun, wenn die InnoDB-Engine deaktiviert ist
Vergleich zwischen MySQL-Speicher-Engine MyISAM und InnoDB
Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des Sperrbereichs auf Zeilenebene der MySQL-Datenbank-InnoDB-Engine. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!