MySQL-Sperren und Transaktionsisolationsstufen (Einführung)-MySQL-Tutorial-php.cn

Im heutigen Internet können Sie ohne eine Datenbank keine groß angelegte Multiplayer-App entwickeln. Wie sichergestellt werden kann, dass jeder mit hoher Parallelität lesen und schreiben kann, war schon immer ein schwieriges architektonisches Problem. Erstens wird die hohe Parallelität beseitigt, und die am häufigsten verwendete Methode zur Sicherstellung konsistenter Lese- und Schreibvorgänge ist die Transaktion und der Schlüsselpunkt der Realisierung Eine Transaktion ist ein Sperrmechanismus.

MySQL-Sperren und Transaktionsisolationsstufen (Einführung)

Heute stellen wir das Prinzip und die Implementierung vor, wie die InnoDB-Speicher-Engine den Sperrmechanismus bei hoher Parallelität implementiert, um konsistentes Lesen und Schreiben zu gewährleisten.

Sperre

Der Sperrmechanismus der Datenbank ist ein wesentliches Merkmal, das sie vom Dateisystem unterscheidet. Wird zur Verwaltung des gleichzeitigen Zugriffs auf freigegebene Ressourcen verwendet. InnoDB verwendet an vielen Stellen Sperrmechanismen, z. B. in Betriebsdatentabellen, LRU-Seitenlisten und Datenzeilen im Pufferpool. Um Konsistenz und Integrität sicherzustellen, ist ein Sperrmechanismus erforderlich.

Für verschiedene Datenbanken sind das Design und die Implementierung des Sperrmechanismus völlig unterschiedlich:

● MyISAM-Engine: Tabellensperrdesign, gleichzeitiges Lesen ist kein Problem, aber die Leistung beim gleichzeitigen Schreiben ist schlecht.

● Microsoft SQL Server: Unterstützt optimistische Parallelität und Sperren auf Zeilenebene. Wenn die Anzahl der Zeilensperren den Schwellenwert überschreitet, wird sie auf Tabellensperren umgestellt.

●InnoDB-Engine: unterstützt Zeilensperren und sorgt für konsistente, nicht sperrende Lesevorgänge. Zeilensperren verursachen keinen zusätzlichen Overhead und keine Leistungseinbußen.

●Oracle: Der InnoDB-Engine sehr ähnlich.

Zwei Arten von Schlössern: Schloss und Riegel

Sowohl Schlösser als auch Riegel in der Datenbank können als Schlösser bezeichnet werden, es gibt jedoch einen großen Unterschied .

Latch wird im Allgemeinen als Latch bezeichnet, der verwendet wird, um die Korrektheit kritischer Ressourcen sicherzustellen, die von gleichzeitigen Threads betrieben werden. Das Ziel ist eine Speicherdatenstruktur, die sehr kurz sein muss und keinen Deadlock verursacht erkannt. In der InnoDB-Engine ist es in Mutex (Mutex) und RWlock (Lese-/Schreibsperre) unterteilt.

Sperre wird zum Sperren von Objekten in der Datenbank verwendet, z. B. Tabellen, Seiten und Zeilen. Das Ziel ist eine Transaktion. Sie wird nach dem Festschreiben/Rollback freigegeben und ein Deadlock wird erkannt. Unterteilt in Zeilensperren, Tabellensperren und Absichtssperren.

Die Schlösser, die wir unten haben, beziehen sich auf Schlösser vom Typ Schloss.

Vier Sperrtypen

InnoDB unterstützt vier Sperren:

● Gemeinsame Sperre (S-Sperre): ermöglicht Transaktionen Lesen a Datenzeile

● Exklusive Sperre (X-Sperre): Ermöglicht einer Transaktion das Löschen oder Aktualisieren einer Datenzeile

● Gemeinsame Absichtssperre (Intention S-Sperre): Eine Transaktion möchte bestimmte Daten erhalten Zeilen in einer Tabelle Gemeinsame Sperre

● Absicht exklusive Sperre (Absicht) Die Zeilendaten werden nicht geändert, sodass Transaktion T2 auch direkt die gemeinsame Sperre von Zeile r erhalten kann, die als sperrkompatibel bezeichnet wird.

Wenn Transaktion T3 die exklusive Sperre von Zeile r erhalten möchte, um Daten zu ändern, muss sie warten, bis T1/T2 die gemeinsame Zeilensperre aufhebt. Dies wird als Sperrinkompatibilität bezeichnet.

S-Sperren und X-Sperren sind beide Zeilensperren, während IS-Sperren und IX-Sperren Absichtssperren sind und zu Tabellensperren gehören. Absichtssperren dienen dazu, die Art der Sperre offenzulegen, die für die nächste Zeile innerhalb einer Transaktion angefordert wird, d. h. eine Sperre mit der feineren Granularität einer Tabellensperre durchzuführen. Da InnoDB Tabellensperren unterstützt, blockieren Absichtssperren keine Anfragen außer vollständigen Tabellenscans.

Sperrkompatibilität:

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

Drei Tabellen zum Speichern von Transaktions- und SperrinformationenWir können

Befehl übergeben um die Informationen der aktuellen Sperranforderung im Transaktionsbereich anzuzeigen.

show engine innodb status Ab InnoDB1.0 wurden INNODB_TRX (Transaktionstabelle), INNODB_LOCKS (Sperrtabelle) und INNODB_LOCK_WAITS (Sperrwartetabelle) unter der INFORMATION_SCHEMA-Architektur hinzugefügt. Mithilfe dieser drei Tabellen können wir die aktuelle Situation überwachen Echtzeittransaktionen und analysieren mögliche Tischprobleme.

Die Definitionen der drei Tabellen sind:

`INNODB_LOCKS`
lock_id	锁ID
lock_trx_id	事务ID
lock_mode	锁的模式
lock_type	锁的类型，表锁或行锁
lock_table	要加锁的表
lock_index	锁住的索引
lock_space	锁对象的space id
lock_page	事务锁定页的数量，表锁时为NULL
lock_rec	事务锁定行的数量，表锁时为NULL
lock_data	事务锁定记录的主键值，表锁时为NULL

`INNODB_LOCK_WAITS`
requesting_trx_id	申请锁资源的事务ID
requesting_lock_id	申请的锁的ID
blocking_trx_id	阻塞的事务ID
blocking_lock_id	阻塞的锁的ID

Über INNODB_TRX können wir alle Transaktionen sehen, ob die Transaktion blockiert ist und wie die blockierte Sperr-ID lautet. Verwenden Sie nach
INNODB_LOCKS, um alle Sperrinformationen anzuzeigen. Nach
können Sie über INNODB_LOCK_WAITS die Warteinformationen und die Sperrbeziehung der Sperre anzeigen.

Über diese drei Tabellen können Sie den Transaktions- und Sperrstatus klarer anzeigen und auch gemeinsame Abfragen durchführen. In den folgenden Szenarien zeigen wir den Inhalt dieser drei Tabellen.

Isolationsstufe

Lassen Sie uns zunächst über die vier Transaktionsisolationsstufen der Datenbank sprechen:

●　READ UNCOMMITTED ( 0 ): Browsing-Zugriffsebene, es gibt Dirty Reads, nicht wiederholbare Lesevorgänge, Phantom Reads

　● READ COMMITTED (1): Cursorstabilitätsstufe, es gibt nicht wiederholbare Lesevorgänge, Phantom Reads

● REPEATABLE READ (2): Es gibt Phantom-Lesevorgänge

● SERIALIZABLE (3): Isolationsstufe, die Transaktionssicherheit gewährleistet, aber vollständig seriell und geringe Leistung

Diese vier Transaktionsisolationsstufen werden angegeben Gemäß dem SQL-Standard ist die Standardisolationsstufe von InnoDB REAPEATABLE READ. Im Gegensatz zu anderen Datenbanken verwendet es jedoch auch den Next-Key-Lock-Sperralgorithmus, der das Auftreten von Phantomlesevorgängen vermeiden kann, sodass die Transaktionsisolationsanforderungen vollständig erfüllt werden können Das heißt, es kann die Isolationsstufe SERIALIZABLE erreichen.

Je niedriger die Isolationsstufe, desto weniger Sperren werden von der Transaktion angefordert oder desto kürzer ist die Haltezeit der Sperre. Daher ist die Standardisolationsstufe der meisten Datenbanken READ COMMITED. Allerdings weisen relevante Analysen auch darauf hin, dass der Leistungsaufwand der Isolationsstufen nahezu gleich ist, sodass Benutzer die Isolationsstufe nicht anpassen müssen, um die Leistung zu verbessern.

Befehle zum Anzeigen und Ändern der Transaktionsisolationsstufe:

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

Nach dem Login kopieren

Im Beispiel wird die Transaktionsisolationsstufe dieser Sitzung geändert. Wenn Sie die globalen Parameter ändern müssen, können Sie die Sitzung ersetzen mit global. Wenn Sie dauerhafte Änderungen vornehmen möchten, müssen Sie die Konfigurationsdatei ändern:

[mysqld]
transaction-isolation = READ-COMMITED

Nach dem Login kopieren

Auf der Transaktionsisolationsstufe SERIALIZABLE fügt InnoDB nach jeder SELECT-Anweisung automatisch LOCK IN SHARE MODE hinzu, um eine gemeinsame Sperre hinzuzufügen Lesevorgang, sodass konsistente, nicht sperrende Lesevorgänge nicht mehr unterstützt werden.

Da InnoDB SERIALIZABLE auf der Isolationsstufe REPEATABLE READ erreichen kann, ist es im Allgemeinen nicht erforderlich, die höchste Isolationsstufe zu verwenden.

Konsistentes nicht sperrendes Lesen und Kontrolle der Parallelität mehrerer Versionen

Konsistentes nicht sperrendes Lesen (konsistentes nicht sperrendes Lesen) bezieht sich auf InnoDBs Mehrzeilige Versionskontrollmethode (Multi Version Concurrency Control, MVCC) zum Lesen der Zeilendaten in der Datenbank zum aktuellen Ausführungszeitpunkt.

Das heißt, wenn die gelesene Zeile einem Änderungsvorgang unterzogen wird, wartet der Lesevorgang nicht auf die Freigabe der Zeilensperre, sondern liest Snapshot-Daten der Zeile. Ein Snapshot bezieht sich auf historische Daten der Zeile, die durch den Rückgängig-Vorgang vervollständigt werden. Diese Methode verbessert die Parallelität der Datenbank erheblich, was auch die Standardeinstellung von InnoDB ist.

Ein Snapshot ist eine historische Version der aktuellen Zeile, es können jedoch mehrere Versionen der Zeilendaten vorhanden sein. Diese Technologie wird zur Zeilen-Multiversionstechnologie und die daraus resultierende Parallelitätskontrolle wird als Multiversion bezeichnet. Snapshot. Versions-Parallelitätskontrolle (MVCC). InnoDB verwendet nicht sperrende konsistente Lesevorgänge in den Isolationsstufen READ COMMITED und REPEATABLE READ, aber die in diesen beiden Isolationsstufen verwendeten Schnelldatendefinitionen sind unterschiedlich:

● READ COMMITED: Immer den neuesten Snapshot lesen

● WIEDERHOLBARES LESEN: Lesen Sie immer die Zeilendatenversion zu Beginn der Transaktion

Wir führen ein Beispiel aus:

td>

Konsistentes, nicht sperrendes Lesen

Zeit Sitzung A Sitzung B

BEGIN

`一致性非锁定读`
时间	会话A	会话B
1	BEGIN
2	select * from z where a = 3;
3		BEGIN
4		update z set b=2 where a=3;
5	select * from z where a = 3;
6		COMMIT;
7	select * from z where a = 3;
8	COMMIT;

2 select * from z where a = 3;

3 BEGIN

4 update z set b= 2 where a=3;

5 select * from z where a = 3;

6 COMMIT;

7 select * from z wobei a = 3;

8 COMMIT;

在这个例子中我们可以清晰的看到0、1、2三种隔离级别的区别：

#在事务开始前我们可以分别调整为0、1、2三种隔离级别，来查看不同的输出
mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where a = 3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update z set b=2 where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# A会话：T1事务，如果此时隔离级别是READ-UNCOMMITTED，因为此刻事务2可能会回滚，所以出现了脏读
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-UNCOMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# A会话：T1事务，如果此时隔离级别是READ-COMMITTED，因为数据和事务开始时读取的出现了不一致，因此称为不可重复读，能够读到其他事务的结果，违反了事务的隔离性
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-COMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Nach dem Login kopieren

一致性锁定读和SERIALIZABLE隔离

在默认的REPEATABLE READ隔离级别时，InnoDB使用的是一致性非锁定读。但有时我们也需要显示的指定使用一致性锁定读来保证读取操作时对数据进行加锁达到一致性。这要求数据库支持锁定读加锁语句：

　●　select ... for update: 读取时对行记录加X锁

　●　select ... lock in share mode：读取时对行记录加一个S锁

这两种锁必须在一个事务中，当事务提交后锁也就释放了，因此务必加上BEGIN, START TRANSACTION或者SET AUTOCOMMIT=0。

我们在前面隔离级别时也说过SERIALIZABLE隔离级别会对读操作自动加上LOCK IN SHARE MODE指令来加上一个共享锁，因此不再支持一致性的非锁定读。这也是隔离级别3的一大特性。

总结

由于锁的概念非常重要，这里先讲了锁的概念、锁的类型、锁的信息查看、事务的隔离级别和区别，后面我们会继续说锁的算法、锁的三种问题和幻读、死锁和锁升级。

推荐学习：MySQL教程

Das obige ist der detaillierte Inhalt vonMySQL-Sperren und Transaktionsisolationsstufen (Einführung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!