Detaillierte Erläuterung des Prinzips des MySQL-Sperrmechanismus (2)

Sperre ist ein Mechanismus für Computer, um mehrere Prozesse oder Threads zu koordinieren, um gleichzeitig auf eine Ressource zuzugreifen. In einer Datenbank sind Daten neben dem traditionellen Wettbewerb um Rechenressourcen (wie CPU, RAM, I/O usw.) auch eine Ressource, die von vielen Benutzern gemeinsam genutzt wird. 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 für Datenbanken besonders wichtig und komplex. In diesem Kapitel konzentrieren wir uns auf die Eigenschaften des MySQL-Sperrmechanismus, häufige Sperrprobleme und einige Methoden oder Vorschläge zur Lösung von MySQL-Sperrproblemen.
Mysql verwendet viele solcher Sperrmechanismen, wie z. B. Zeilensperren, Tabellensperren, Lesesperren, Schreibsperren usw., die alle vor Operationen gesperrt werden. Diese Sperren werden zusammenfassend als pessimistische Sperren bezeichnet.

InnoDB-Sperre

Der größte Unterschied zwischen InnoDB und MyISAM besteht in zwei Punkten:

Erstens unterstützt es TRANSACTION

Das zweite ist die Verwendung von Sperren auf Zeilenebene. Es gibt viele Unterschiede zwischen Sperren auf Zeilenebene und Sperren auf Tabellenebene. Darüber hinaus bringt die Einführung von Transaktionen auch einige neue Probleme mit sich.

1. Transaktion und ihre ACID-Attribute
Eine Transaktion ist eine logische Verarbeitungseinheit, die aus einer Reihe von SQL-Anweisungen besteht. Eine Transaktion hat 4 Attribute, die normalerweise als ACID-Attribute bezeichnet werden Transaktion.

1. Atomarität: Eine Transaktion ist eine atomare Operationseinheit und alle Änderungen an den Daten werden entweder ausgeführt oder überhaupt nicht ausgeführt.

2. Konsistent: Daten müssen konsistent bleiben, wenn eine Transaktion gestartet und abgeschlossen wird. Dies bedeutet, dass alle relevanten Datenregeln auf Transaktionsänderungen angewendet werden müssen, um die Integrität am Ende der Transaktion aufrechtzuerhalten. Außerdem müssen alle internen Datenstrukturen (z. B. B-Tree-Indizes oder doppelt verknüpfte Listen) korrekt sein.

3. Isolation: Das Datenbanksystem bietet einen bestimmten Isolationsmechanismus, um sicherzustellen, dass Transaktionen in einer „unabhängigen“ Umgebung ausgeführt werden, die nicht durch externe gleichzeitige Vorgänge beeinträchtigt wird. Das bedeutet, dass Zwischenzustände während einer Transaktion für die Außenwelt nicht sichtbar sind und umgekehrt.

4. Dauerhaft: Nach Abschluss der Transaktion ist die Änderung der Daten dauerhaft und kann auch bei einem Systemausfall beibehalten werden.

2. Durch gleichzeitige Transaktionen verursachte Probleme
Im Vergleich zur seriellen Verarbeitung kann die gleichzeitige Transaktionsverarbeitung die Auslastung der Datenbankressourcen erheblich erhöhen und somit den Transaktionsdurchsatz des Datenbanksystems verbessern Unterstützung von mehr Benutzern. Die gleichzeitige Transaktionsverarbeitung bringt jedoch auch einige Probleme mit sich, darunter hauptsächlich die folgenden Situationen.

1. Verlorene Aktualisierung: Wenn zwei oder mehr Transaktionen dieselbe Zeile auswählen und die Zeile dann basierend auf dem ursprünglich ausgewählten Wert aktualisieren, da jede Transaktion die Existenz der anderen Transaktionen nicht kennt, liegt ein Problem mit der verlorenen Aktualisierung vor auftritt – die letzte Aktualisierung überschreibt Aktualisierungen, die von anderen Transaktionen vorgenommen wurden. Beispielsweise erstellen zwei Redakteure elektronische Kopien desselben Dokuments. Jeder Redakteur ändert seine Kopie selbstständig und speichert dann die geänderte Kopie, wobei das Originaldokument überschrieben wird. Der Bearbeiter, der zuletzt eine Kopie seiner Änderungen gespeichert hat, überschreibt die von einem anderen Bearbeiter vorgenommenen Änderungen. Dieses Problem kann vermieden werden, wenn ein Editor nicht auf dieselbe Datei zugreifen kann, bis ein anderer Editor die Transaktion abschließt und festschreibt.

2. Dirty Reads: Eine Transaktion ändert einen Datensatz, und die Daten dieses Datensatzes befinden sich zu diesem Zeitpunkt in einem inkonsistenten Zustand Ohne Kontrolle liest die zweite Transaktion die „schmutzigen“ Daten und führt eine entsprechende Weiterverarbeitung durch, was zu nicht festgeschriebenen Datenabhängigkeiten führt. Dieses Phänomen wird treffend als „Dirty Reading“ bezeichnet.

3. Nicht wiederholbare Lesevorgänge: Eine Transaktion hat sich beim Lesen einiger Daten geändert oder einige Datensätze wurden gelöscht! Dieses Phänomen wird als „nicht wiederholbares Lesen“ bezeichnet.

4. Phantom-Lesevorgänge: Eine Transaktion liest zuvor abgerufene Daten gemäß den gleichen Abfragebedingungen erneut, stellt jedoch fest, dass andere Transaktionen neue Daten eingefügt haben, die ihre Abfragebedingungen erfüllen. Dieses Phänomen wird als „Phantom-Lesen“ bezeichnet ".

3. Transaktionsisolationsstufe
Unter den Problemen, die durch die gleichzeitige Transaktionsverarbeitung verursacht werden, sollte „Update-Verlust“ normalerweise vollständig vermieden werden. Das Verhindern von Aktualisierungsverlusten kann jedoch nicht allein durch den Datenbanktransaktionscontroller gelöst werden. Die Anwendung muss die erforderlichen Sperren für die zu aktualisierenden Daten hinzufügen. Daher sollte die Verhinderung von Aktualisierungsverlusten in der Verantwortung der Anwendung liegen.

„Dirty Read“, „Non-Repeatable Read“ und „Phantom Read“ sind eigentlich Datenbank-Lesekonsistenzprobleme, die durch die Bereitstellung eines bestimmten Transaktionsisolationsmechanismus durch die Datenbank gelöst werden müssen. Die Art und Weise, wie Datenbanken die Transaktionsisolation implementieren, kann grundsätzlich in die folgenden zwei Typen unterteilt werden.

1. Eine besteht darin, die Daten vor dem Lesen zu sperren, um zu verhindern, dass andere Transaktionen die Daten ändern.

2. Die andere besteht darin, über einen bestimmten Mechanismus einen konsistenten Daten-Snapshot (Snapshot) des Datenanforderungszeitpunkts zu generieren, ohne Sperren hinzuzufügen, und diesen Snapshot zu verwenden, um eine bestimmte Konsistenzebene (Anweisungsebene oder Transaktionsebene) bereitzustellen Lesen. Aus Benutzersicht scheint es, dass die Datenbank mehrere Versionen derselben Daten bereitstellen kann. Daher wird diese Technologie als Data Multi-Version Concurrency Control (kurz MVCC oder MCC) bezeichnet, die häufig auch als Multiversionsdatenbank bezeichnet wird.

In der MVCC-Parallelitätskontrolle können Lesevorgänge in zwei Kategorien unterteilt werden: Snapshot-Lesen und aktuelles Lesen. Beim Snapshot-Lesen wird die sichtbare Version des Datensatzes (bei der es sich möglicherweise um die historische Version handelt) ohne Sperre gelesen. Der aktuelle Lesevorgang liest die neueste Version des Datensatzes und der vom aktuellen Lesevorgang zurückgegebene Datensatz wird gesperrt, um sicherzustellen, dass dieser Datensatz nicht gleichzeitig von anderen Transaktionen geändert wird.
Welche Lesevorgänge sind in einem System, das die MVCC-Parallelitätskontrolle unterstützt, Snapshot-Lesevorgänge? Welche Operationen werden gerade gelesen? Nehmen Sie MySQL InnoDB als Beispiel:

Snapshot-Lesen: eine einfache Auswahloperation, die ein Snapshot-Lesen ist und keine Sperren erfordert. (Natürlich gibt es Ausnahmen)

select * from table where ?;

Aktueller Lesevorgang: Spezielle Lesevorgänge, Einfüge-/Aktualisierungs-/Löschvorgänge gehören zum aktuellen Lesevorgang und müssen gesperrt werden.
Bei den folgenden Anweisungen handelt es sich ausschließlich um aktuelle Lesevorgänge, bei denen die neueste Version des Datensatzes gelesen wird. Darüber hinaus muss nach dem Lesen sichergestellt werden, dass andere gleichzeitige Transaktionen den aktuellen Datensatz nicht ändern und den gelesenen Datensatz nicht sperren können. Mit Ausnahme der ersten Anweisung, die dem Lesedatensatz eine S-Sperre (gemeinsame Sperre) hinzufügt, fügen alle anderen Vorgänge eine X-Sperre (exklusive Sperre) hinzu.

Je strenger die Transaktionsisolation der Datenbank ist, desto geringer sind die Nebenwirkungen der Parallelität, aber desto höher ist der gezahlte Preis, da die Transaktionsisolation im Wesentlichen dazu führt, dass Transaktionen bis zu einem gewissen Grad „serialisiert“ werden, was offensichtlich im Widerspruch zu „“ steht. „Parallelität“ ist ein Oxymoron. Gleichzeitig stellen verschiedene Anwendungen unterschiedliche Anforderungen an die Lesekonsistenz und die Transaktionsisolation. Beispielsweise reagieren viele Anwendungen nicht empfindlich auf „nicht wiederholbare Lesevorgänge“ und „Phantomlesevorgänge“ und legen möglicherweise mehr Wert auf die Fähigkeit, gleichzeitig auf Daten zuzugreifen.

Um den Widerspruch zwischen „Isolation“ und „Parallelität“ aufzulösen, definiert ISO/ANSI SQL92 4 Transaktionsisolationsstufen. Jede Ebene hat einen unterschiedlichen Isolationsgrad und ermöglicht unterschiedliche Nebenwirkungen dass der Widerspruch zwischen „Isolation“ und „Parallelität“ durch die Wahl unterschiedlicher Isolationsstufen ausgeglichen werden soll. Die folgende Tabelle bietet eine gute Zusammenfassung der Merkmale dieser 4 Isolationsstufen.

InonoD-Zeilensperrkonflikt abrufen

Sie können das System analysieren, indem Sie die Statusvariable Row von InnoDB_row_lock überprüfen Sperrkonflikt:

mysql> show status like 'innodb_row_lock%';

Wenn Sie feststellen, dass der Sperrkonflikt schwerwiegend ist, z. B. die Werte von InnoDB_row_lock_waits und InnoDB_row_lock_time_avg relativ hoch sind, können Sie auch InnoDB festlegen Überwacht das Auftreten von Sperrenkonflikten in Tabellen, Datenzeilen usw. und analysiert die Ursachen von Sperrenkonflikten.

Zeilensperrmodus und Sperrmethode von InnoDB

InnoDB implementiert die folgenden zwei Arten von Zeilensperren.

Gemeinsame Sperre(n): wird auch Lesesperre genannt. Ermöglicht einer Transaktion das Lesen einer Zeile und verhindert so, dass andere Transaktionen eine exklusive Sperre für denselben Datensatz erhalten. Wenn Transaktion T eine S-Sperre zu Datenobjekt A hinzufügt, kann Transaktion T A lesen, A jedoch nicht ändern. Andere Transaktionen können nur eine S-Sperre zu A hinzufügen, aber keine X-Sperre hinzufügen, bis T die S-Sperre für A aufhebt. Dies garantiert, dass andere Transaktionen A lesen können, aber keine Änderungen an A vornehmen können, bis T die S-Sperre für A aufhebt.

Exklusive Sperre (X): auch Schreibsperre genannt. Erlauben Sie Transaktionen, die exklusive Sperren erwerben, das Aktualisieren von Daten und verhindern Sie, dass andere Transaktionen gemeinsame Lesesperren und exklusive Schreibsperren für denselben Datensatz erwerben. Wenn Transaktion T dem Datenobjekt A eine X-Sperre hinzufügt, kann Transaktion T A lesen oder ändern. Andere Transaktionen können keine weiteren Sperren zu A hinzufügen, bis T die Sperre für A aufhebt.
Möglicherweise haben Sie ein gutes Verständnis für gemeinsame Sperren, das heißt, mehrere Transaktionen können Daten nur lesen, aber nicht ändern.

对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表: 

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 
事务可以通过以下语句显式给记录集加共享锁或排他锁：

1、共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

2、排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示： 
创建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
mysql> alter table tab_with_index add index id(id);

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。 
在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

mysql> alter table tab_with_index drop index name;
1
Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 
Warnings: 0
mysql> insert into tab_with_index  values(1,'4');
1
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_with_index where id = 1;

InnoDB存储引擎使用相同索引键的阻塞例子 ：

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 
在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);
1Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 
Warnings: 0

InnoDB存储引擎的表使用不同索引的阻塞例子 ：

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 
比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

mysql> explain select * from tab_with_index where name = 1 \G
mysql> explain select * from tab_with_index where name = '1' \G

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。 
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！下面这个例子假设emp表中只有101条记录，其empid的值分别是1,2,……,100,101。 
InnoDB存储引擎的间隙锁阻塞例子 

小结

本文重点介绍了MySQL中MyISAM表级锁和InnoDB行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决办法。

Für MyISAM-Tabellensperren werden hauptsächlich die folgenden Punkte diskutiert:
(1) Gemeinsam genutzte Lesesperren (S) sind kompatibel, aber gemeinsam genutzte Lesesperren (S) und exklusive Schreibsperren (X) sind kompatibel und die exklusiven Die Schreibsperre (X) schließt sich gegenseitig aus, dh Lesen und Schreiben erfolgen seriell.
(2) Unter bestimmten Bedingungen ermöglicht MyISAM die gleichzeitige Ausführung von Abfragen und Einfügungen. Damit können wir das Sperrkonfliktproblem für Abfragen und Einfügungen in derselben Tabelle in Anwendungen lösen.
(3) Der Standard-Sperrplanungsmechanismus von MyISAM ist die Schreibpriorität, die nicht unbedingt für alle Anwendungen geeignet ist. Benutzer können den Lese-/Schreibsperrenkonflikt anpassen, indem sie den Parameter LOW_PRIORITY_UPDATES festlegen oder die Option LOW_PRIORITY in INSERT, UPDATE und angeben DELETE-Anweisungen verwenden.
(4) Aufgrund der großen Sperrgranularität von Tabellensperren und der Serialisierung zwischen Lesen und Schreiben kann es bei der MyISAM-Tabelle zu erheblichen Sperrwartezeiten kommen. Sie können die Verwendung von InnoDB-Tabellen in Betracht ziehen, um Sperrenkonflikte zu reduzieren .

Für InnoDB-Tabellen werden in diesem Artikel hauptsächlich die folgenden Inhalte behandelt:
(1) Die Zeilensperren von InnoDB basieren auf Indizes. Wenn auf Daten nicht über Indizes zugegriffen wird, verwendet InnoDB Tabellensperren.
(2) Stellt den InnoDB-Gap-Lock-Mechanismus (Next-Key) und die Gründe vor, warum InnoDB Gap-Locks verwendet.
Unter verschiedenen Isolationsstufen sind der Sperrmechanismus und die konsistente Lesestrategie von InnoDB unterschiedlich.

Nachdem Benutzer die Sperreigenschaften von InnoDB verstanden haben, können sie Sperrkonflikte und Deadlocks durch Design- und SQL-Anpassungen reduzieren, einschließlich:

Verwenden Sie so weit wie möglich eine niedrigere Isolationsstufe und entwerfen Sie Indizes sorgfältig Versuchen Sie, den Zugriff auf Daten mithilfe von Indizes genauer zu gestalten, wodurch die Wahrscheinlichkeit von Sperrkonflikten verringert wird. Wählen Sie eine angemessene Transaktionsgröße. Bei kleinen Transaktionen ist die Wahrscheinlichkeit von Sperrkonflikten geringer. Dies ist am besten um es auf einmal anzufordern. Ausreichender Sperrgrad. Wenn Sie beispielsweise Daten ändern möchten, ist es am besten, direkt eine exklusive Sperre zu beantragen, anstatt zuerst eine gemeinsame Sperre zu beantragen und dann beim Ändern eine exklusive Sperre anzufordern. Dies ist anfällig für Deadlocks, wenn verschiedene Programme auf eine Gruppe zugreifen Bei Tabellen sollten sie versuchen, den Zugriff in derselben Reihenfolge zu vereinbaren. Jede Tabelle greift so weit wie möglich auf die Zeilen in der Tabelle zu. Dies kann das Risiko eines Deadlocks erheblich verringern. Um die Auswirkungen von Lückensperren auf gleichzeitige Einfügungen zu vermeiden, werden bei Abfragen keine Sperren angezeigt ; Für einige bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.

Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des Prinzips des MySQL-Sperrmechanismus (2). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!