Das heutige umfassende Verständnis der MySQL-Sperrtypen und Sperrprinzipien

Verwandte kostenlose Lernempfehlungen: MySQL-Tutorial

Vorwort

• MySQL-Index zugrunde liegende Datenstruktur und Algorithmus
• MySQL-Leistungsoptimierungsprinzipien – Teil 1
• MySQL-Leistungsoptimierung – Übungsteil 1
• MySQL-Leistung Optimierung – Übungsteil 2
• MySQL-Sperr- und Transaktionsisolationsstufe

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:

1. Unterteilen Sie nach der Granularität der Sperre: Zeilensperre, Tabellensperre, Seitensperre
2. Unterteilen Sie nach der Art und Weise, wie die Sperre verwendet wird: gemeinsame Sperre, exklusive Sperre (eine Implementierung von pessimistisch Sperre)
3. Es gibt auch zwei ideologische Sperren: pessimistische Sperre und optimistische Sperre.
4. In InnoDB gibt es mehrere Sperrtypen auf Zeilenebene: Datensatzsperre, Lückensperre, Next-Key-Sperre Sperre
5. Tabellensperre
6. Die Sperre auf Tabellenebene ist die detaillierteste Sperre unter den MySQL-Sperren. Dies bedeutet, dass der Ressourcenaufwand geringer ist als bei der Zeilensperre und es zu keinem Deadlock kommt. Die Wahrscheinlichkeit eines Sperrkonflikts ist jedoch sehr hoch
7. . Sowohl MyISAM als auch InnoDB werden von den meisten MySQL-Engines unterstützt und unterstützen Sperren auf Tabellenebene. InnoDB verwendet jedoch standardmäßig Sperren auf Zeilenebene.

Die Tabellensperre wird vom MySQL-Server implementiert. Im Allgemeinen wird die gesamte Tabelle gesperrt, wenn DDL-Anweisungen wie ALTER TABLE und andere Vorgänge ausgeführt werden. Beim Ausführen einer SQL-Anweisung können Sie auch explizit eine zu sperrende Tabelle angeben.

Die Tabellensperre verwendet die einmalige Sperrtechnologie. Das heißt, Sie verwenden den Sperrbefehl zu Beginn der Sitzung, um alle Tabellen zu sperren, die später verwendet werden. Bevor die Tabelle freigegeben wird, kann nur auf diese gesperrten Tabellen zugegriffen werden. Auf andere Tabellen kann erst dann zugegriffen werden, wenn alle Tabellensperren endgültig über „Unlock Tables“ aufgehoben werden.

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.

LOCK TABLE table_name [ AS alias_name ] READ复制代码

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.

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.

Da wir nun das Sperrprinzip der Aktualisierung einer einzelnen Datenzeile basierend auf dem Index verstehen, stellen wir uns die Frage, was passiert, wenn der Aktualisierungsvorgang mehrere Zeilen umfasst, wie zum Beispiel das folgende SQL-Ausführungsszenario.

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.

Gemeinsame Sperre/Exklusive Sperre

Gemeinsame Sperre (Gemeinsame Sperre)

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.

Verwendung

SELECT ... LOCK IN SHARE MODE;SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE

Fügen Sie 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 (eXklusive Sperre)

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.

Verwendung

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 Sperren

Wie 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.

Optimistische Parallelitätskontrolle (optimistisches Sperren) und pessimistische Parallelitätskontrolle (pessimistisches Sperren) sind die wichtigsten technischen Mittel zur Parallelitätskontrolle.

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 Sperre

• In relationalen Datenbankverwaltungssystemen ist die pessimistische Parallelitätskontrolle (auch bekannt als „pessimistische Sperre“, Pessimistic Concurrency Control, abgekürzt „PCC“) eine Methode der Parallelitätskontrolle. Es verhindert, dass eine Transaktion Daten in einer Weise verändert, die sich auf andere Benutzer auswirkt. Wenn eine von einer Transaktion ausgeführte Operation eine Sperre auf eine Datenzeile anwendet, können andere Transaktionen nur dann Vorgänge ausführen, die mit der Sperre in Konflikt stehen, wenn die Transaktion die Sperre aufhebt. Die pessimistische Parallelitätskontrolle wird hauptsächlich in Umgebungen mit intensiven Datenkonflikten und in Umgebungen verwendet, in denen die Kosten für die Verwendung von Sperren zum Schutz von Daten bei Auftreten eines Parallelitätskonflikts geringer sind als die Kosten für das Zurücksetzen von Transaktionen.
• Pessimistische Sperre bezieht sich, wie der Name schon sagt, auf eine konservative (pessimistische) Einstellung gegenüber Daten, die von der Außenwelt geändert werden (einschließlich anderer aktueller Transaktionen dieses Systems und der Transaktionsverarbeitung von externen Systemen). Verarbeitungsprozess werden die Daten gesperrt. Die Implementierung pessimistischer Sperren basiert häufig auf dem von der Datenbank bereitgestellten Sperrmechanismus (nur der von der Datenbankschicht bereitgestellte Sperrmechanismus kann die Exklusivität des Datenzugriffs wirklich garantieren. Andernfalls gibt es keinen Sperrmechanismus, selbst wenn er in diesem System implementiert ist garantieren, dass das externe System die Daten nicht ändert.)
• Der spezifische Prozess der pessimistischen Sperre

Bevor Sie einen Datensatz ändern, versuchen Sie, dem Datensatz eine exklusive Sperre hinzuzufügen Wird geändert, dann kann die Abfrage derzeit warten oder eine Ausnahme auslösen. Die spezifische Antwortmethode wird vom Entwickler basierend auf den tatsächlichen Anforderungen festgelegt.

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. 🎜

Optimistische Sperre

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.

Vor- und Nachteile des optimistischen Sperrens

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.

Intention Shared Lock/Intention Exclusive Lock

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 (Intention Shared Lock)

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 (Exklusive Sperre)

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

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.

Lückensperre

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复制代码

案例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. (-infinity, 1]
2. (1, 5]
3. (5, 7]
4. (7, 11]
5. (11, +infinity]

只使用记录锁（行锁），不会产生间隙锁

/* 开启事务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;复制代码

从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

结论

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁（行锁）和间隙锁，如果记录存在，则只会产生记录锁（行锁）；
2. 对于查找某一范围内的查询语句，会产生间隙锁。

案例2：普通索引的间隙锁

示例表：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. (-infinity, 1]
2. (1, 3]
3. (3, 8]
4. (8, 12]
5. (12, +infinity]

测试1

我们执行以下的事务（事务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) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。

测试2

我们再进行以下测试，这里将数据还原成初始化那样

/* 开启事务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;复制代码

查看表中的数据；

这里有一个奇怪的现象：

• 事务3 添加 id = 6，number = 8 的数据，阻塞了；
• 事务4 添加 id = 8，number = 8 的数据，正常执行了；
• 事务7 将 id = 11，number = 12 的数据修改为 id = 11， number = 5 的操作，给阻塞了。

这是为什么？我们来看看下面的图：

从图中库看出，当 number 相同时，会根据主键 id 来排序

1. 事务 3 添加的 id = 6，number = 8，这条数据是在 (3,8) 的区间里边，所以会阻塞；
2. 事务 4 添加的 id = 8，number = 8，这条数据实在 (8,12) 区间里边，所以不会阻塞；
3. 事务 7 的修改语句相当于 在 (3,8) 的区间里边插入一条数据，所以也被阻塞了。

结论

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通普通索引排序，再根据唯一索引排序。

临键锁（Next-key Locks）

临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 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，临键锁则也会失效。

插入意向锁（Insert Intention Locks）

插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

插入意向锁的作用：

1. 为来唤起等待。由于该间隙已经有锁，插入时必须阻塞，插入意向锁的作用具有阻塞功能；
2. 插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？间隙锁直接不相互排斥。不可以阻塞即唤起等待，会造成幻读。
3. 为什么不实用记录锁（行锁）或 临键锁？申请了记录锁或临键锁，临键锁之间可能相互排斥，即影响 insert 的并发性。

自增锁（Auto-inc Locks）

AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

自增操作

使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：

• auto_increment_offset 表示起始数字
• auto_increment_increment 表示调动幅度（即每次增加n个数字，2就代表每次+2）

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();复制代码

自增锁

首先insert大致上可以分成三类：

1. simple insert 如insert into t(name) values('test')
2. bulk insert 如load data | insert into ... select .... from ....
3. mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：

• 0 ：traditonal (每次都会产生表锁)
• 1 ：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）
• 2 ：interleaved （不会锁表，来一个处理一个，并发最高）

  MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。

 show variables like 'innodb_autoinc_lock_mode';复制代码

traditonal

innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。

• 它提供来一个向后兼容的能力
• 在这一模式下，所有的 insert 语句（“insert like”）都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁。注意，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句；
• 它能保证值分配的可预见性、可连续性、可重复性，这个也就是保证了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值（它保证了基于语句复制的安全）；
• 由于在这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响了并发的插入。

consecutive

innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。

• 这一模式下对 simple insert 做了优化，由于 simple insert 一次性插入的值的个数可以立马得到确定，所以 MyQL 可以一次生成几个连续的值，用于这个 insert 语句。总得来说这个对复制也是安全的（它保证了基于语句复制的安全）；
• 这一模式也是MySQL的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值就可以提前释放锁。

interleaved

innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。

• 由于这个模式下已经没有了 auto_inc 锁，所以这个模式下的性能是最好的，但是也有一个问题，就是对于同一个语句来说它所得到的 auto_incremant 值可能不是连续的。

如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。

Zusammenfassung

Eigenschaften von InnoDB-Sperren

1. Bei Abfragen ohne Indexbedingungen verwendet InnoDB Tabellensperren!
2. Da die Zeilensperre von MySQL für den Index und nicht für den Datensatz gilt, kommt es bei Verwendung desselben Indexschlüssels zu einem Sperrkonflikt, obwohl auf Datensätze verschiedener Zeilen zugegriffen wird.
3. Wenn eine Tabelle über mehrere Indizes verfügt, können verschiedene Transaktionen unterschiedliche Indizes verwenden, um verschiedene Zeilen zu sperren. Unabhängig davon, ob ein Primärschlüsselindex, ein eindeutiger Index oder ein gewöhnlicher Index verwendet wird, verwendet InnoDB Zeilensperren, um Daten zu sperren.
4. Selbst wenn in der Bedingung ein Indexfeld verwendet wird, bestimmt MySQL anhand der Kosten verschiedener Ausführungspläne, ob der Index zum Abrufen von Daten verwendet werden soll, wenn MySQL der Ansicht ist, dass der vollständige Tabellenscan effizienter ist, z. B. für einige Bei kleinen Tabellen werden keine Indizes verwendet. In diesem Fall verwendet InnoDB Tabellensperren anstelle von Zeilensperren. Vergessen Sie daher bei der Analyse von Sperrkonflikten nicht, den SQL-Ausführungsplan (Explain-Ansicht) zu überprüfen, um zu bestätigen, ob der Index tatsächlich verwendet wird.

Sperrmodus

Die Sperrmodi sind: Leseabsichtssperre, Schreibabsichtssperre, Lesesperre, Schreibsperre und automatische Inkrementsperre (auto_inc).

Kompatibilitätsmatrix für verschiedene Schlösser KI

Kompatibel KompatibelS kompatibel Zusammenfassend gibt es folgende Punkte: Es ist in Next-Key-SperreDatensatzsperre . Verschiedene Sperren sperren unterschiedliche Positionen. Beispielsweise sperrt die Datensatzsperre nur den entsprechenden Datensatz, während die Lückensperre die Lücke zwischen den Datensätzen sperrt und die Next-Key-Sperre die Lücke zwischen dem Datensatz und dem vorherigen Datensatz sperrt. Die Schließbereiche verschiedener Schlosstypen sind in etwa wie in der folgenden Abbildung dargestellt. Kompatibilitätsmatrix für verschiedene Arten von SchlössernRECORDGAP RECORD Kompatibel

	IS				Kompatibel
		kompatibel
	AI	Kompatibel	Kompatibel
	Absichtssperren stehen nicht in Konflikt miteinander;	S-Sperre Es ist nur mit S/IS-Sperren kompatibel und kollidiert mit andere Sperren;	Tabellensperre und Zeilensperre unterteilt. Die Zeilensperre kann je nach Szenario weiter unterteilt werden, einschließlich	, Lückensperre, Lückensperre,	und Intention-GAP-Sperre einfügen
NEXT-KEY	II GAP

• Kompatibel
• GAP

Kompatibel Kompatibel KompatibelKompatibel

NEXT -KEY

kompatibel

Unter diesen stellt die erste Zeile die vorhandene Sperre dar und die erste Spalte stellt die hinzuzufügende Sperre dar. Das Einfügen von Absichtssperren ist etwas Besonderes, daher erstellen wir zunächst eine Zusammenfassung des Einfügens von Absichtssperren wie folgt:

• Das Einfügen von Absichtssperren hat keine Auswirkungen auf andere Transaktionen und andere Sperren. Mit anderen Worten: Eine Transaktion hat die Einfügeabsichtssperre erworben und hat keine Auswirkungen auf andere Transaktionen.
• Die Einfügeabsichtssperre steht im Konflikt mit der Lückensperre und der Next-Key-Sperre. Das heißt, wenn eine Transaktion die Einfügungsabsichtssperre erwerben möchte und eine andere Transaktion bereits eine Lückensperre oder eine Next-Key-Sperre hinzugefügt hat, wird sie blockiert.

Die Regeln für andere Arten von Sperren sind relativ einfach:

• Lückensperren stehen nicht in Konflikt mit anderen Sperren (mit Ausnahme von Einfügungssperren);

• Datensatzsperren stehen im Konflikt mit Datensatzsperren, Next-Key-Sperren stehen im Konflikt mit Next- Tastensperren, Aufnahmesperre und Next-Key-Sperrkonflikt

	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!