Ausführliche Erklärung der MySQL-Sperren

Sperre ist im wirklichen Leben ein Werkzeug, das wir nutzen, wenn wir uns vor der Außenwelt verstecken wollen. In Computern handelt es sich um einen Mechanismus zum Koordinieren des gleichzeitigen Zugriffs mehrerer Prozesse oder Landkreise auf eine Ressource. In einer Datenbank sind Daten neben der Konkurrenz um herkömmliche Rechenressourcen (CPU, RAM, I/O usw.) auch eine Ressource, die von vielen Benutzern gemeinsam genutzt wird und auf die sie zugreifen. Die Gewährleistung der Konsistenz und Wirksamkeit des gleichzeitigen Zugriffs auf Daten ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der sich auf die Leistung des gleichzeitigen Zugriffs auf Datenbanken auswirkt. Aus dieser Sicht sind Sperren insbesondere für Datenbanken wichtig.

1. Sperren in MySQL

In der Datenbank können beide als „Lock“ bezeichnet werden. „lock“, aber die beiden haben völlig unterschiedliche Bedeutungen.

Riegel wird im Allgemeinen als Riegel (leichtes Schloss) bezeichnet, da die erforderliche Verriegelungszeit sehr kurz sein muss. Wenn es lange anhält, ist die Leistung der Anwendung sehr schlecht. In der InnoDB-Engine kann Latch in Mutex (Mutex) und RWlock (Lese-/Schreibsperre) unterteilt werden. Sein Zweck besteht darin, die Korrektheit gleichzeitiger Threads sicherzustellen, die kritische Ressourcen betreiben. Normalerweise gibt es keinen Mechanismus zur Erkennung von Deadlocks.

Das Objekt von Lock ist eine Transaktion und wird zum Sperren von Objekten in der Datenbank wie Tabellen, Seiten und Zeilen verwendet. Und im Allgemeinen werden Sperrobjekte erst nach dem Festschreiben oder Zurücksetzen der Transaktion freigegeben (die Freigabezeit kann für verschiedene Transaktionsisolationsstufen unterschiedlich sein).

Eine ausführlichere Erklärung von Latch finden Sie unter: In Bezug auf die eingehende Analyse und Beurteilung von MySQL-Latch-Konflikten konzentriert sich dieser Artikel hauptsächlich auf Lock-Sperren.

Arten von Sperren

Es gibt eigentlich nur zwei Vorgänge für Daten, nämlich Lesen und Schreiben, und die Datenbank führt dies auch aus, wenn sie Sperren implementiert Zwei Operationen verwenden unterschiedliche Sperren. InnoDB implementiert Standardsperren auf Zeilenebene, nämlich Shared Lock und Exclusive Lock.

• Gemeinsame Sperre (Lesesperre), die es einer Transaktion ermöglicht, eine Datenzeile zu lesen.

• Exklusive Sperre (Schreibsperre), die es einer Transaktion ermöglicht, eine Datenzeile zu löschen oder zu aktualisieren.

Und ihre Namen implizieren auch ein weiteres Merkmal der einzelnen Sperren, während Mutex-Sperren mit allen anderen Sperren nicht kompatibel sind:

Wenn Sie ein wenig über ihre Verwendung nachdenken, können Sie herausfinden, warum sie auf diese Weise konzipiert sind. Da gemeinsam genutzte Sperren Lesevorgänge und Mutex-Sperren Schreibvorgänge darstellen, können wir sie in der Datenbank parallelisieren Nur so können wir sicherstellen, dass keine Thread-Konkurrenz auftritt und Thread-Sicherheit erreicht werden kann.

Sperrgranularität

Sperrsperren werden je nach Granularität hauptsächlich in Tabellensperren, Seitensperren und Zeilensperren unterteilt. Verschiedene Speicher-Engines haben unterschiedliche Sperrgranularitäten.

Tabellensperre

Die Sperrung auf Tabellenebene ist der detaillierteste Sperrmechanismus unter den MySQL-Speicher-Engines. Das größte Merkmal dieses Sperrmechanismus besteht darin, dass die Implementierungslogik sehr einfach ist und nur minimale negative Auswirkungen auf das System hat. Das Erlangen und Freigeben von Sperren geht also sehr schnell. Da Sperren auf Tabellenebene die gesamte Tabelle auf einmal sperren, kann das Deadlock-Problem, das uns plagt, vermieden werden.

Die größte negative Auswirkung einer großen Sperrgranularität besteht natürlich darin, dass die Wahrscheinlichkeit eines Konflikts um Sperrressourcen am höchsten ist, was die Parallelität erheblich verringert.

Sperren auf Tabellenebene wird hauptsächlich von nicht-transaktionalen Speicher-Engines wie MyISAM, MEMORY und CSV verwendet.

Die Syntax der Tabellensperre ist sehr einfach:

# 获取表锁
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
 
lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
 
# 释放表锁
UNLOCK TABLES

MyISAM führt automatisch Tabellensperr- und Entsperrvorgänge durch, bevor die Abfrage ausgeführt wird. Im Allgemeinen muss der Benutzer die Tabelle nicht manuell hinzufügen und entsperren , aber es gibt Es muss auch im gesperrten Zustand angezeigt werden. Beispiel: Rufen Sie die Anzahl der Daten in der Tabelle zu einem bestimmten Zeitpunkt t1 und t2 ab.

LOCK TABLE t1 read, t2 read;
select count(t1.id1) as 'sum' from t1;
select count(t2.id1) as 'sum' from t2;
UNLOCK TABLES;

Seitensperre

Sperren auf Seitenebene ist eine einzigartige Sperrstufe in MySQL und kommt in anderer Datenbankverwaltungssoftware nicht allzu häufig vor. Das Merkmal der Sperrung auf Seitenebene besteht darin, dass die Sperrgranularität zwischen der Sperrung auf Zeilenebene und der Sperrung auf Tabellenebene liegt, sodass der zum Erhalten der Sperre erforderliche Ressourcenaufwand und die gleichzeitige Verarbeitungsfähigkeit, die sie bereitstellen kann, ebenfalls zwischen den beiden oben genannten liegen. Darüber hinaus führen Sperren auf Seitenebene und Sperren auf Zeilenebene zu einem Deadlock.

Im Prozess der Ressourcensperrung in der Datenbank wird mit abnehmender Granularität der gesperrten Ressourcen immer mehr Speicher verbraucht, um die gleiche Datenmenge zu sperren, und auch der Implementierungsalgorithmus wird immer komplexer . Je komplexer es ist. Da jedoch die Granularität gesperrter Ressourcen abnimmt, verringert sich auch die Wahrscheinlichkeit, dass Anwendungszugriffsanforderungen auf Sperrwartezeiten stoßen, und die allgemeine Parallelität des Systems nimmt ebenfalls zu.

Die Hauptanwendung des Sperrens auf Seitenebene ist die BerkeleyDB-Speicher-Engine.

Zeilensperre

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

总结

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

2、InnoDB中的锁

意向锁

上节提到InnoDB 支持多种粒度的锁，也就是行锁和表锁。为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock）。

那什么是意向锁呢？我们在这里可以举一个例子：如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

与上一节中提到的两种锁的种类相似的是，意向锁也分为两种：

• 意向共享锁（IS）：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁。

• 意向互斥锁（IX）：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

随着意向锁的加入，锁类型之间的兼容矩阵也变得愈加复杂：

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

行锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁。

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。例如有一个索引有10，11，13和20这4个值，那么该索引可能被Next-Key Locking的区间为：

除了Next-Key Locking，还有Previous-Key Locking技术。同样上述的值，使用Previous-Key Locking技术，那么可锁定的区间为：

但是不是所有索引都会加上Next-key Lock的，在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock。

接下来，我们来通过一个例子解释一下。

CREATE TABLE z (
    a INT,
    b INT,
    PRIMARY KEY(a),    // a是主键索引
    KEY(b)    // b是普通索引
);
INSERT INTO z select 1, 1;
INSERT INTO z select 3, 1;
INSERT INTO z select 5, 3;
INSERT INTO z select 7, 6;
INSERT INTO z select 10, 8;

这时候在会话A中执行 SELECT * FROM z WHERE b = 3 FOR UPDATE ，索引锁定如下：

这时候会话B执行的语句落在锁定范围内的都会进行waiting

SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4, 2;
INSERT INTO z SELECT 6, 5;

用户可以通过以下两种方式来显示的关闭Gap Lock：

• 将事务的隔离级别设为 READ COMMITED。

• 将参数innodb_locks_unsafe_for_binlog设置为1。

从上面的例子可以看出来，Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，设计它的目的是用来解决Phontom Problem（幻读问题）。在MySQL默认的隔离级别（Repeatable Read）下，InnoDB就是使用它来解决幻读问题。

幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL可能会返回之前不存在的行，也就是第一次执行和第二次执行期间有其他事务往里插入了新的行。

一致性非锁定读

一致性非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过多版本控制（MVCC）的方式来读取当前执行时间数据库中行的数据。如果读取的这行正在执行DELETE或UPDATE操作，这时读取操作不会向XS锁一样去等待锁释放，而是会去读一个快照数据。MVCC相关的知识我已经在另外一篇文章中阐述了，这里就不做过多原理的分析了。地址：谈谈MySQL InnoDB存储引擎事务的ACID特性

在事务隔离级别RC和RR下，InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义却不同，在RC级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在RR级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

下面我们通过一个例子来看看大家是否对MVCC理解了。

可以看到，第1步和第2步是非常容易理解的，而在第3步事务B插入一条新的数据后，在第4步事务A还是查不到，也就是利用了MVCC的特性来实现。当事务B提交后，第5步的查询在RC和RR隔离级别下的输出是不同的，这个的原因在另一篇博客中也说到了，是因为他们创建ReadView的时机不同。

但是很诡异的是在第6步的时候，事务A更新了一条它看不见的记录，然后查询就能够查询出来了。这里很多人容易迷惑，不可见不代表记录不存在，它只是利用了可见性判断忽略了而已。更新成功之后，事务A顺其自然的记录了这条记录的Undo log，在随后的查询中，因为它能够看见自己的改动这一个可见性的判断，自然就能够查询出来了。这里很多名词需要去深入读一下此文：谈谈MySQL InnoDB存储引擎事务的ACID特性

一致性锁定读

前面说到，在默认隔离级别RR下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作。

• SELECT … FOR UPDATE （X锁）

• SELECT … LOCK IN SHARE MODE （S锁）

3、锁带来的问题

通过锁定机制可以实现事务隔离性要求，使得事务可以并发的工作。锁提高了并发，但是却会带来潜在的问题。不过好在有事务隔离性的要求，不同的隔离级别解决的锁的问题也不同，这里只进行简单的介绍，不进行举例分析了。

InnoDB存储引擎在RR级别就已经解决了所有问题，但是它和Serializable的区别在哪里呢？区别就在于RR级别还存在一个丢失更新问题，而SERIALIZABLE无论对于查询还是更新都会进行锁定操作。

Wie in der Abbildung gezeigt, beträgt der ursprüngliche Betrag des Benutzers 100. Wenn die Beurteilung der Übertragung und Einzahlung im Programm darin besteht, zuerst abzufragen und dann zu aktualisieren, besteht das Problem, dass Aktualisierungen verloren gehen, d. h. die späteren Aktualisierungen werden verloren gehen überschreibt die vorherigen Updates. Wenn Sie dieses Problem vermeiden möchten, können Sie den Betrag bei jeder Aktualisierung nur auf den neuesten Wert in der Tabelle stützen. Wenn Sie zuerst eine Abfrage durchführen und dann aktualisieren müssen, können Sie die Menge in den Aktualisierungsbedingungen bestimmen (optimistische Sperre) oder SERIALIZABLE mit der höchsten Isolationsstufe verwenden.

4. Deadlock

Deadlock bedeutet, dass zwei oder mehr Transaktionen während des Ausführungsprozesses um Sperrressourcen konkurrieren Hier ist ein Deadlock-Problem, das in einem früheren Projekt aufgetreten ist, und eine ausführliche Analyse: Auf die Analyse des durch ein Online-Problem verursachten MySQL-Deadlock-Problems werde ich hier nicht näher eingehen.

Empfohlenes Tutorial: „MySQL-Tutorial“

Das obige ist der detaillierte Inhalt vonAusführliche Erklärung der MySQL-Sperren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!