Verwandte kostenlose Lernempfehlungen: MySQL-Tutorial
Zuvor haben wir über die zugrunde liegende Datenstruktur und den Algorithmus der MySQL-Datenbank sowie einige Inhalte der MySQL-Leistungsoptimierung gesprochen. Und im vorherigen Artikel ging es um die Zeilensperren und Transaktionsisolationsstufen von MySQL. Dieser Artikel konzentriert sich auf Sperrtypen und Sperrprinzipien.
Unterteilen Sie zunächst die MySQL-Sperren:
Zusätzlich zur Verwendung von Entsperrtabellen zur Anzeige der Sperrenfreigabe werden durch das Ausführen der Sperrtabellenanweisung auch die zuvor von der Sitzung gehaltenen Sperren freigegeben, wenn die Sitzung andere Tabellensperren enthält die zuvor gehaltene Sperre. Verwendung gemeinsam genutzter Sperren bedeutet nur, die Zeile zu sperren wird derzeit operiert. Sperren auf Zeilenebene können Konflikte bei Datenbankvorgängen erheblich reduzieren. Seine Sperrgranularität ist am kleinsten, aber der Sperraufwand ist auch am größten. Es kann zu Deadlock-Situationen kommen.
Sperren auf Zeilenebene werden je nach Verwendungszweck in gemeinsame Sperren und exklusive Sperren unterteilt. Verschiedene Speicher-Engines haben unterschiedliche Zeilensperrimplementierungen. Wenn es später keine spezielle Erklärung gibt, bezieht sich die Zeilensperre speziell auf die von InnoDB implementierte Zeilensperre. Bevor Sie das Sperrprinzip von InnoDB verstehen, müssen Sie ein gewisses Verständnis seiner Speicherstruktur haben. InnoDB ist ein Clustered-Index, das heißt, die Blattknoten des B+-Baums speichern sowohl den Primärschlüsselindex als auch die Datenzeilen. Die Blattknoten des Sekundärindex von InnoDB speichern Primärschlüsselwerte. Wenn Sie also Daten über den Sekundärindex abfragen, müssen Sie den entsprechenden Primärschlüssel abrufen und ihn im Clustered-Index erneut abfragen. Ausführliche Informationen zu MySQL-Indizes finden Sie unter „Grundlegende Datenstruktur und Algorithmus des MySQL-Index“.
Im Folgenden wird die Ausführung von zwei SQLs als Beispiel verwendet, um das Sperrprinzip von InnoDB für Einzelzeilendaten zu erläutern. Das erste SQL verwendet den Primärschlüsselindex zum Abfragen, dann müssen Sie nur eine Schreibsperre für die Primärschlüsselindex-ID = 49 hinzufügen. LOCK TABLE table_name [ AS alias_name ] READ复制代码
Nach dem Login kopieren
Das zweite SQL verwendet den Sekundärindex zum Abfragen und fügt dann zuerst die Schreibsperre hinzu zum Primärschlüsselindex id = Tom Fügen Sie eine Schreibsperre für den Index hinzu. Da der InnoDB-Sekundärindex erneut basierend auf dem Primärschlüsselindex abgefragt werden muss, ist es auch erforderlich, eine Schreibsperre für den Primärschlüsselindex mit der ID hinzuzufügen = 49, wie in der Abbildung oben gezeigt. Das heißt, die Verwendung des Primärschlüsselindex erfordert das Hinzufügen einer Sperre, und die Verwendung des Sekundärindex erfordert das Hinzufügen einer Sperre für den Sekundärindex und den Primärschlüsselindex.
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码
Die Sperrenfreigabe in diesem Szenario ist komplizierter und es gibt viele Optimierungsmethoden, die ich noch nicht verstehe. Bitte hinterlassen Sie unten eine Nachricht zur Erklärung. Seitensperre
Sperre auf Seitenebene ist eine Sperre in MySQL, deren Sperrgranularität zwischen Sperre auf Zeilenebene und Sperre auf Tabellenebene liegt. Sperren auf Tabellenebene sind schnell, weisen jedoch viele Konflikte auf. Sperren auf Zeilenebene weisen wenige Konflikte auf, sind jedoch langsam. Also haben wir eine kompromittierte Seitenebene genommen und jeweils eine Gruppe benachbarter Datensätze gesperrt. BDB unterstützt Sperren auf Seitenebene. Eine gemeinsame Sperre, auch Lesesperre genannt, ist eine Sperre, die durch einen Lesevorgang erstellt wird. Andere Benutzer können die Daten gleichzeitig lesen, aber keine Transaktion kann die Daten ändern (eine exklusive Sperre für die Daten erwerben), bis alle gemeinsamen Sperren aufgehoben wurden.
Wenn Transaktion T eine gemeinsame Sperre zu Daten A hinzufügt, können andere Transaktionen nur gemeinsame Sperren zu A und keine exklusiven Sperren hinzufügen. Transaktionen, denen gemeinsame Sperren gewährt werden, können nur Daten lesen und keine Daten ändern.
SELECT ... LOCK IN SHARE MODE;SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
SELECT ... FOR UPDATE;
在查询语句后面增加FOR UPDATE
LOCK IN SHARE MODE nach der Abfrageanweisung hinzu, und MySQL fügt eine gemeinsame Sperre hinzu, wenn Kein anderer Thread verwendet eine exklusive Sperre für eine Zeile im Abfrageergebnissatz. Er kann erfolgreich eine gemeinsame Sperre beantragen, andernfalls wird er blockiert. Andere Threads können mithilfe gemeinsamer Sperren auch Tabellen lesen, und diese Threads lesen dieselbe Datenversion. Exklusive Sperre wird auch Schreibsperre genannt. Wenn Transaktion T eine exklusive Sperre zu Daten A hinzufügt, können andere Transaktionen keine weitere Sperre zu A hinzufügen der Blockade. Transaktionen mit exklusiven Sperren können Daten sowohl lesen als auch ändern.
SELECT ... FOR UPDATE;Fügen Sie FOR UPDATE nach der Abfrageanweisung hinzu, MySQL wird An Jeder Zeile im Abfrageergebnissatz wird eine exklusive Sperre hinzugefügt. Wenn kein anderer Thread eine exklusive Sperre für eine Zeile im Abfrageergebnissatz verwendet, kann er erfolgreich eine exklusive Sperre beantragen, andernfalls wird er blockiert. Optimistisches Sperren und pessimistisches SperrenWie im Sperrmechanismus der Datenbank eingeführt, besteht die Aufgabe der Parallelitätskontrolle im Datenbankverwaltungssystem (DBMS) darin, sicherzustellen, dass die Isolation von Transaktionen nicht zerstört wird, wenn mehrere Transaktionen auf dieselben Daten zugreifen in der Datenbank gleichzeitig und Einheit und Datenbankeinheit. Ob es sich um pessimistische oder optimistische Sperren handelt, es handelt sich um von Menschen definierte Konzepte, die als eine Art Gedanke betrachtet werden können. Tatsächlich gibt es in relationalen Datenbanksystemen nicht nur die Konzepte des optimistischen Sperrens und des pessimistischen Sperrens, sondern auch Memcache, Hibernate, Tair usw. haben ähnliche Konzepte. Für unterschiedliche Geschäftsszenarien sollten unterschiedliche Methoden zur Parallelitätskontrolle ausgewählt werden. Verstehen Sie daher optimistische Parallelitätskontrolle und pessimistische Parallelitätskontrolle nicht im engeren Sinne als Konzepte in DBMS und verwechseln Sie sie nicht mit den in den Daten bereitgestellten Sperrmechanismen (Zeilensperren, Tabellensperren, exklusive Sperren und gemeinsam genutzte Sperren). Tatsächlich wird in DBMS die pessimistische Sperre mithilfe des von der Datenbank selbst bereitgestellten Sperrmechanismus implementiert. Pessimistische SperreOptimistische Parallelitätskontrolle (optimistisches Sperren) und pessimistische Parallelitätskontrolle (pessimistisches Sperren) sind die wichtigsten technischen Mittel zur Parallelitätskontrolle.
Wenn die Sperre erfolgreich ist, kann der Datensatz geändert werden und wird nach Abschluss der Transaktion entsperrt.
🎜Wenn es andere Vorgänge gibt, um den Datensatz zu ändern oder eine exklusive Sperre hinzuzufügen, warten sie darauf, dass wir ihn entsperren oder direkt eine Ausnahme auslösen. 🎜🎜🎜Vor- und Nachteile der pessimistischen Sperre🎜🎜Die pessimistische Sperre verfolgt tatsächlich die Strategie „Erst die Sperre vor dem Zugriff erhalten“, was jedoch aufgrund des zusätzlichen Sperrmechanismus eine Garantie für die Sicherheit der Datenverarbeitung bietet , zusätzlicher Overhead und erhöht die Wahrscheinlichkeit eines Deadlocks. Und es reduziert die Parallelität; wenn ein Ding eine Datenzeile erhält, müssen andere Dinge auf die Übermittlung der Transaktion warten, bevor sie mit dieser Datenzeile arbeiten können. 🎜In relationalen Datenbankverwaltungssystemen ist die optimistische Parallelitätskontrolle (auch bekannt als „optimistische Sperre“, Optimistic Concurrency Control, Abkürzung „OCC“) eine Methode der Parallelitätskontrolle. Es wird davon ausgegangen, dass sich gleichzeitige Transaktionen mehrerer Benutzer während der Verarbeitung nicht gegenseitig beeinflussen und jede Transaktion den Teil der Daten verarbeiten kann, den sie betrifft, ohne eine Sperre zu erzeugen. Bevor Datenaktualisierungen festgeschrieben werden, prüft jede Transaktion zunächst, ob andere Transaktionen die Daten geändert haben, nachdem die Transaktion die Daten gelesen hat. Wenn für andere Transaktionen Aktualisierungen vorliegen, wird die festgeschriebene Transaktion zurückgesetzt.
Optimistisches Sperren Im Vergleich zum pessimistischen Sperren wird beim optimistischen Sperren davon ausgegangen, dass Daten im Allgemeinen keine Konflikte verursachen. Wenn also die Daten zur Aktualisierung übermittelt werden, wird der Konflikt der Daten offiziell erkannt. Wenn ein Konflikt gefunden wird, werden Fehlerinformationen angezeigt an den Benutzer zurückgegeben und der Benutzer kann entscheiden, was zu tun ist.
Im Vergleich zum pessimistischen Sperren verwendet das optimistische Sperren bei der Verarbeitung der Datenbank nicht den von der Datenbank bereitgestellten Sperrmechanismus. Die allgemeine Methode zum Implementieren einer optimistischen Sperre besteht darin, die Datenversion aufzuzeichnen.
Datenversion, eine den Daten hinzugefügte Versionskennung. Beim Lesen von Daten wird der Wert der Versionskennung zusammen gelesen. Bei jeder Aktualisierung der Daten wird gleichzeitig die Versionskennung aktualisiert. Wenn wir ein Update einreichen, vergleichen wir die aktuellen Versionsinformationen des entsprechenden Datensatzes in der Datenbanktabelle mit dem zum ersten Mal entnommenen Versionsidentifikationswert, wenn die aktuelle Versionsnummer der Datenbanktabelle mit dem entnommenen Versionsidentifikationswert übereinstimmt Aktualisieren Sie sie zum ersten Mal, andernfalls gelten sie als abgelaufene Daten.
Die optimistische Parallelitätskontrolle geht davon aus, dass die Wahrscheinlichkeit eines Datenwettlaufs zwischen Transaktionen relativ gering ist. Daher wird dies so direkt wie möglich durchgeführt und erst zum Zeitpunkt der Übermittlung gesperrt, sodass dies nicht der Fall ist Es kommt zu Sperren und Deadlocks. Wenn Sie dies jedoch einfach tun, kann es dennoch zu unerwarteten Ergebnissen kommen. Wenn beispielsweise zwei Transaktionen eine bestimmte Zeile der Datenbank lesen und diese nach der Änderung wieder in die Datenbank schreiben, treten Probleme auf.
Da Tabellensperren und Zeilensperren unterschiedliche Sperrbereiche haben, kommt es zu Konflikten zwischen ihnen. Wenn Sie also eine Tabellensperre hinzufügen möchten, müssen Sie zunächst alle Datensätze in der Tabelle durchlaufen, um festzustellen, ob eine exklusive Sperre hinzugefügt wird. Diese Durchlaufprüfungsmethode ist offensichtlich eine ineffiziente Methode, mit der MySQL Absichtssperren einführt, um Konflikte zwischen Tabellensperren und Zeilensperren zu erkennen.
Absichtssperren sind ebenfalls Sperren auf Tabellenebene und können auch in Leseabsichtssperren (IS-Sperren) und Schreibabsichtssperren (IX-Sperren) unterteilt werden. Wenn eine Transaktion eine Lese- oder Schreibsperre für einen Datensatz hinzufügen möchte, muss sie zunächst eine Absichtssperre für die Tabelle hinzufügen. Auf diese Weise lässt sich sehr einfach beurteilen, ob Datensätze in der Tabelle gesperrt sind. Überprüfen Sie einfach, ob eine Absichtssperre für die Tabelle vorliegt.
Absichtssperren stehen weder untereinander noch mit AUTO_INC-Tabellensperren in Konflikt. Sie blockieren nur Lesesperren auf Tabellenebene oder Schreibsperren auf Tabellenebene Blockieren Sie nur Lesesperren auf Tabellenebene oder Schreibsperren auf Tabellenebene. Wird mit Zeilensperren in Konflikt geraten.
Die Absichtssperre wird von InnoDB automatisch hinzugefügt und erfordert keinen Benutzereingriff.
Beim Einfügen, Aktualisieren und Löschen fügt InnoDB automatisch exklusive Sperren (X) zu den betreffenden Daten hinzu.
Bei allgemeinen Select-Anweisungen fügt InnoDB keine Sperren hinzu folgende Anweisungen oder Exklusivsperre.
Intention Shared Lock (IS): Zeigt an, dass die Transaktion das Hinzufügen einer Shared Lock zur Datenzeile vorbereitet, was bedeutet, dass vor dem Hinzufügen einer Shared Lock zu einer Datenzeile die IS Die Sperre der Tabelle muss zuerst erhalten werden.
Intention Exclusive Lock (IX): Ähnlich wie oben bedeutet dies, dass die Transaktion sich darauf vorbereitet, der Datenzeile eine exklusive Sperre hinzuzufügen, was darauf hinweist Die Transaktion muss zunächst die IX-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine exklusive Sperre hinzufügt.
Datensatzsperre ist die einfachste Zeilensperre, und dazu gibt es nichts zu sagen. Die oben beschriebene Sperre im InnoDB-Sperrprinzip ist die Datensatzsperre, die nur den Datensatz mit der ID = 49 oder dem Namen = „Tom“ sperrt.
Wenn die SQL-Anweisung den Index nicht verwenden kann, wird ein vollständiger Tabellenscan durchgeführt. Zu diesem Zeitpunkt fügt MySQL Datensatzsperren für alle Datenzeilen in der gesamten Tabelle hinzu und die MySQL-Serverschicht filtert sie dann. Wenn jedoch beim Filtern auf der MySQL-Server-Ebene festgestellt wird, dass die WHERE-Bedingung nicht erfüllt ist, wird die Sperre für den entsprechenden Datensatz aufgehoben. Dadurch wird sichergestellt, dass am Ende nur Sperren für Datensätze aufrechterhalten werden, die die Bedingungen erfüllen, der Sperrvorgang für jeden Datensatz jedoch nicht ausgelassen werden kann.
Der Aktualisierungsvorgang muss also basierend auf dem Index durchgeführt werden. Ohne einen Index werden nicht nur viele Sperrressourcen verbraucht und der Overhead der Datenbank erhöht, sondern auch die Parallelitätsleistung der Datenbank erheblich verringert.
Wenn wir Bereichsbedingungen anstelle von Gleichheitsbedingungen verwenden, um Daten abzurufen und gemeinsame oder exklusive Sperren anzufordern, sperrt InnoDB die Indexeinträge vorhandener Datensätze, die die Bedingungen erfüllen Innerhalb des Bedingungsbereichs, aber nicht vorhanden, sperrt InnoDB auch die „Lücke“. Dieser Sperrmechanismus ist die sogenannte Lückensperre.
间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。
要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog
show variables like 'innodb_locks_unsafe_for_binlog';复制代码
innodb_locks_unsafe_for_binlog：默认
值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。
# 在 my.cnf 里面的[mysqld]添加 [mysqld] innodb_locks_unsafe_for_binlog = 1复制代码
测试环境：
MySQL5.7，InnoDB，默认的隔离级别（RR）
示例表：
CREATE TABLE `my_gap` ( `id` int(1) NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码
在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：
/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。
恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：
/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。
恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？
/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。
示例表：id 是主键，在 number 上，建立了一个普通索引。
# 注意：number 不是唯一值CREATE TABLE `my_gap1` ( `id` int(1) NOT NULL AUTO_INCREMENT, `number` int(1) NOT NULL COMMENT '数字', PRIMARY KEY (`id`), KEY `number` (`number`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码
在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：
我们执行以下的事务（事务1最后提交），分别执行下面的语句：
/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码
我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：
这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。
我们再进行以下测试，这里将数据还原成初始化那样
/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码
查看表中的数据；
这里有一个奇怪的现象：
这是为什么？我们来看看下面的图：
从图中库看出，当 number 相同时，会根据主键 id 来排序
临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：
(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码
通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)
此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。
注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。
插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。
插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。
插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。
插入意向锁的作用：
AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：
使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：
通过使用last_insert_id()函数可以获得最后一个插入的数字
select last_insert_id();复制代码
首先insert大致上可以分成三类：
如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：
MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。
show variables like 'innodb_autoinc_lock_mode';复制代码
innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。
innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。
innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。
如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。
由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。
Die Sperrmodi sind: Leseabsichtssperre, Schreibabsichtssperre, Lesesperre, Schreibsperre und automatische Inkrementsperre (auto_inc).
| IS | KompatibelKompatibel | Kompatibel | |||
|---|---|---|---|---|---|
| kompatibel | kompatibel | ||||
| AI | Kompatibel | Kompatibel | |||
| Zusammenfassend gibt es folgende Punkte: | Absichtssperren stehen nicht in Konflikt miteinander; | S-Sperre Es ist nur mit S/IS-Sperren kompatibel und kollidiert mit andere Sperren; | Es ist in Tabellensperre und Zeilensperre unterteilt. Die Zeilensperre kann je nach Szenario weiter unterteilt werden, einschließlich | Next-Key-Sperre, Lückensperre, Lückensperre, | Datensatzsperre und Intention-GAP-Sperre einfügen | .
| NEXT-KEY | II GAP |
Kompatibel Kompatibel KompatibelKompatibel
| Kompatibel |
|---|
Das obige ist der detaillierte Inhalt vonDas heutige umfassende Verständnis der MySQL-Sperrtypen und Sperrprinzipien. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
MySQL ändert den Namen der Datentabelle
MySQL erstellt eine gespeicherte Prozedur
Der Unterschied zwischen Mongodb und MySQL
So überprüfen Sie, ob das MySQL-Passwort vergessen wurde
MySQL-Datenbank erstellen
MySQL-Standard-Transaktionsisolationsstufe
Der Unterschied zwischen SQL Server und MySQL
mysqlPasswort vergessen
