In der Spalte „MySQL-Tutorial“ wird vorgestellt, wie Sperren das Phantomleseproblem lösen.
Sofern nicht anders angegeben, verwendet dieser Artikel die Standard-InnoDB-Engine. Falls andere Engines oder Datenbanken beteiligt sind, wird darauf ausdrücklich hingewiesen.
In Bezug auf die Sperrgranularität können wir Sperren in Tabellensperren und Zeilensperren unterteilen.
Tabellensperre
Die Sperrmethode der Tabellensperre lautet:
LOCK TABLE 表名 READ;--锁定后表只读 UNLOCK TABLE; --解锁复制代码
Zeilensperre
Die normale Idee ist: Nach dem Sperren einer Datenzeile können andere Transaktionen nicht auf diese Daten zugreifen. Dann stellen wir uns vor, dass Transaktion A, wenn sie auf ein Datenelement zugreift, es einfach zum Lesen herausnimmt und es nicht ändern möchte Kommt es vor, dass ich bei Transaktion B auch auf diese Daten zugreifen möchte, sie nur herausnehmen und lesen möchte und sie nicht ändern möchte. Wenn sie zu diesem Zeitpunkt blockiert sind, wäre das eine kleine Verschwendung der Leistung. Um dieses Datenleseszenario zu optimieren, unterteilen wir Zeilensperren in zwei Haupttypen:
gemeinsame Sperren und exklusive Sperren.
Gemeinsame Sperre, auch Lesesperre oder S-Sperre genannt, bedeutet, dass nach dem Hinzufügen eines Datenelements mit einer S-Sperre auch andere Transaktionen die Daten lesen und eine Sperre teilen können.
Wir können eine gemeinsame Sperre durch die folgende Anweisung hinzufügen:select * from test where id=1 LOCK IN SHARE MODE;复制代码Nach dem Sperren wird die Sperre aufgehoben, bis die gesperrte Transaktion endet (Commit oder Rollback). Exklusive SperreExklusive Sperre, Exklusive Sperre, auch Schreibsperre, X-Sperre genannt. Das heißt, nachdem einem Datenelement eine X-Sperre hinzugefügt wurde, können andere Transaktionen, die auf diese Daten zugreifen möchten, nur blockieren und auf die Freigabe der Sperre warten, was exklusiv ist.
MySQL fügt automatisch eine exklusive Sperre hinzu, wenn wir die Daten ändern, z. B. Einfügen, Aktualisieren, Löschen. Ebenso können wir manuell eine exklusive Sperre über die folgende SQL-Anweisung hinzufügen:
select * from test where id=1 for update;复制代码
In der InnoDB-Engine handelt es sich um Zeilensperren und Tabellensperren dürfen gleichzeitig existieren.
Intention Lock
Intention Lock ist eine Tabellensperre, die in zwei Typen unterteilt ist: Intention Shared Lock und Intention Exclusive Lock. Diese beiden Sperren können als IS-Sperren bzw. IX-Sperren bezeichnet werden.
Absichtssperren werden von MySQL selbst verwaltet und Benutzer können Absichten nicht manuell hinzufügen.
Es gibt zwei wichtige Sperrregeln für Absichtssperren:
Wenn es erforderlich ist, einer Datenzeile eine S-Sperre hinzuzufügen, fügt MySQL zunächst eine IS-Sperre zur Tabelle hinzu. Wenn es erforderlich ist, einer Datenzeile eine X-Sperre hinzuzufügen, fügt MySQL zunächst eine IX-Sperre zur Tabelle hinzu.In diesem Fall lässt sich das obige Problem leicht lösen. Wenn Sie eine Tabelle sperren müssen, müssen Sie nur prüfen, ob die Tabelle über eine entsprechende Absichtssperre verfügt, ohne die gesamte Tabelle zu durchlaufen.
Kompatibilität verschiedener Schlösser
Teilen. |
S | „sich gegenseitig ausschließend“ re | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Teilen |
事务A | 事务B |
---|---|
BEGIN; | |
SELECT * FROM test WHERE id=1 FOR UPDATE; | |
SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞 |
|
SELECT * FROM test WHERE id=5 FOR UPDATE; 加锁成功 |
|
COMMIT; (释放锁) |
|
SELECT * FROM test WHERE id=1 FOR UPDATE; 加锁成功 |
举例2(操作test2表):
事务A | 事务B |
---|---|
BEGIN; | |
SELECT * FROM test2 WHERE id=1 FOR UPDATE; | |
SELECT * FROM test2 WHERE id=1 FOR UPDATE; 阻塞 |
|
SELECT * FROM test2 WHERE id=5 FOR UPDATE; 阻塞 |
|
COMMIT; (释放锁) |
|
SELECT * FROM test2 WHERE id=1 FOR UPDATE; 加锁成功 |
从上面两个例子我们可以发现,test表好像确实是锁住了id=1这一行的记录,而test2表好像不仅仅是锁住了id=1这一行记录,实际上经过尝试我们就知道,test2表是被锁表了,所以其实MySQL中InnoDB锁住的是索引,当没有索引的时候就会锁表。
接下来再看一个场景:
事务A | 事务B |
---|---|
BEGIN; | |
SELECT * FROM test WHERE name=‘张1’ FOR UPDATE; | |
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞 |
|
SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞 |
|
COMMIT; (释放锁) |
|
SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功 |
这个例子中我们是把name索引锁住了,然后我们在事务B中通过主键索引只查id,这样就用到name索引了,但是最后发现也被阻塞了。所以我们又可以得出下面的结论,MySQL索引不但锁住了辅助索引,还会把辅助索引对应的主键索引一起锁住。
到这里,可能有人会有怀疑,那就是我把辅助索引锁住了,但是假如加锁的时候,只用到了覆盖索引,然后我再去查主键会怎么样呢?
接下来让我们再验证一下:
事务A | 事务B |
---|---|
BEGIN; | |
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; | |
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞 |
|
SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞 |
|
SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞 |
|
COMMIT; (释放锁) |
|
SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功 |
Wir können sehen, dass MySQL auch dann den Primärschlüsselindex sperrt, wenn nur die Hilfsindexsperre verwendet wird, und der B+-Baumblattknoten des Primärschlüsselindex die gesamten Daten speichert, sodass jedes abgefragte Feld gesperrt wird.
An diesem Punkt können wir eindeutig eine Schlussfolgerung darüber ziehen, was die Sperre ist:
In der InnoDB-Engine ist der Index gesperrt:
Als wir im vorherigen Artikel Transaktionen eingeführt haben, haben wir erwähnt, dass MySQL Phantom-Lesevorgänge verhindert durch Sperren, aber wenn die Zeilensperre nur eine Zeile von Datensätzen sperrt und das Phantomlesen nicht zu verhindern scheint, ist die Zeilensperre nur einer der Fälle. Tatsächlich gibt es drei Algorithmen für Zeilensperren: Datensatzsperre. Lückensperre und Next-Key-Sperre (Next-Key-Sperre), und der Grund, warum sie das Phantomlesen verhindern können, ist genau die Rolle der Next-Key-Sperre.
Datensatzsperre wird oben eingeführt. Wenn unsere Abfrage einen Datensatz treffen kann, verwendet InnoDB die Datensatzsperre, um die Trefferzeile der Datensätze zu sperren.
Wenn unsere Abfrage den Datensatz nicht erreicht, fügt InnoDB zu diesem Zeitpunkt eine Lückensperre hinzu.
Transaktion A | Transaktion B |
---|---|
BEGIN; | |
SELECT * FROM test WHERE id=1 FOR UPDATE; |
|
In den Test einfügen VALUE (2,'Zhang 2'); Blocking |
|
INSERT INTO test VALUE (3,'Zhang 3'); Blocking |
|
SELECT * FROM test WHERE id= 2 FÜR UPDATE; Erfolgreich sperren |
|
COMMIT; (Sperre freigeben) |
Aus dem obigen Beispiel können wir die Schlussfolgerung ziehen:
Die Testtabelle enthält 5 Datensätze und die Primärschlüsselwerte sind: 1,5,8,10,20. Dann gibt es die folgenden sechs Lücken:
(-∞,1),(1,5),(5,8),(8,10),(10,20),(20,+∞)
Und Wenn der Primärschlüssel nicht vom Typ int ist, wird er in ASCII-Code umgewandelt und anschließend die Lücke ermittelt.
Next-Key Lock ist eine Kombination aus Record Lock und Gap Lock. Wenn wir eine Bereichsabfrage durchführen und nicht nur einen oder mehrere Datensätze treffen, sondern auch Lücken einschließen, wird die temporäre Schlüsselsperre als Standardalgorithmus für Zeilensperren in InnoDB verwendet.
Hinweis: Für die RC-Isolationsstufe werden zusätzlich zu Fremdschlüsseleinschränkungen und Eindeutigkeitsbeschränkungen natürlich keine Lückensperren hinzugefügt Die auf der RC-Ebene hinzugefügten Sperren sind alle Datensatzsperren. Wenn kein Datensatz getroffen wird, wird keine Sperre gesperrt. Daher löst die RC-Ebene das Problem des Phantomlesens nicht.
Die temporäre Tastensperre wird unter den folgenden zwei Bedingungen auf eine Lückensperre oder eine Datensatzsperre herabgestuft:Transaktion B | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
blocking | |||||||||||||
Blockieren | |||||||||||||
blocking | |||||||||||||
blocking | |||||||||||||
| INSERT INTO test VALUE (9,'Zhang 9');Einfügung erfolgreich | ||||||||||||
(Sperre freigeben) |
事务A | 事务B |
---|---|
BEGIN; | |
SELECT * FROM test WHERE id=10 FOR UPDATE; | |
BEGIN; | |
SELECT * FROM test WHERE id=20 FOR UPDATE; | |
SELECT * FROM test WHERE id=20 FOR UPDATE; | |
SELECT * FROM test WHERE id=10 FOR UPDATE; | |
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | |
查询出结果 |
Wir können sehen, dass ein Deadlock sofort zurückgesetzt wird, anstatt 50 Sekunden lang ziellos darauf zu warten, dass die Transaktion abläuft, bevor die Transaktion zurückgesetzt wird. Woher weiß MySQL, dass ein Deadlock aufgetreten ist, und wie macht es das? Den Deadlock erkennen? Was ist mit der Sperre passiert?
Derzeit verwenden die meisten Datenbanken die Wartediagramm-Methode (Wartediagramm), um Deadlocks zu erkennen. Die InnoDB-Engine verwendet diese Methode auch, um Deadlocks zu erkennen. In der Datenbank werden zwei Arten von Informationen aufgezeichnet:
INNODB_TRX
Spaltenname | Bedeutung: RUNNING, LOCK WAIT, ROLLING ZURÜCK, COMMIT TING |
---|---|
Die Startzeit der Transaktion | |
Die Sperr-ID der wartenden Transaktion. Wenn trx_state nicht LOCK WAIT ist, ist sie null | |
Die Zeit, die die Transaktion auf den Start wartet | |
transactional Die Gewichtung spiegelt die Anzahl der von einer Transaktion geänderten und gesperrten Zeilen wider. Wenn ein Deadlock auftritt, wählt InnoDB die Transaktion mit dem kleinsten Wert zum Zurücksetzen aus. | |
Die Thread-ID in MySQL kann abgefragt werden über SHOW PROCESSLIST | |
SQL-Anweisungen, die von der Transaktion ausgeführt werden | |
Der aktuelle Betriebsstatus der Transaktion, wenn nicht NULL | |
Die Anzahl der von der verwendeten Tabellen In der aktuellen Transaktion ausgeführte SQL-Anweisungen | |
Die Anzahl der gesperrten Tabellen (da Zeilensperren verwendet werden). Obwohl eine Tabelle als gesperrt angezeigt wird, kann es sein, dass nur eine oder einige Zeilen gesperrt sind, sodass andere Zeilen gesperrt werden können auf die noch andere Transaktionen zugreifen können) | |
Die Anzahl der von der aktuellen Transaktion beibehaltenen Sperren | |
Die Größe der Indexstruktur der aktuellen Transaktion im Speicher | |
Die ungefähre Zahl Anzahl der Sperren in der aktuellen Transaktion. Anzahl der Zeilen, einschließlich derjenigen, die gelöscht wurden. Markieren Sie Löschmarkierungen und andere Daten, die physisch vorhanden sind, aber für die aktuelle Transaktion unsichtbar sind. Trx_rows_modified. Die Anzahl der Zeilen, die durch die aktuelle Transaktion geändert oder eingefügt wurden | trx_concurrency_tickets |
trx_isolation_level | |
trx_unique_checks | |
trx_foreign_key_checks | |
trx_last_foreign_key_error | |
trx_adaptive_hash_latched | |
trx_adaptive_hash_timeout | |
trx_is_read_only | |
trx_autocommit_non_locking | |
zeichnet Informationen für jede Sperre auf, die eine Transaktion angefordert, aber nicht erhalten hat, und Informationen für jede Sperre, die eine Transaktion hielt, aber eine andere Transaktion blockierte. | |
Bedeutung | |
lock_id | Die ID der Sperre (obwohl LOCK_ID derzeit TRX_ID enthält, kann sich das Datenformat in LOCK_ID jederzeit ändern. Schreiben Sie keine Anwendungen, die LOCK_ID-Werte analysieren ) |
Transaktions-ID der vorherigen Tabelle
Sperrmodus: S, Zeilensperre | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
lock_ index | |||||||||||||
lock_data | |||||||||||||
Spaltenname | Bedeutung |
---|---|
lock_id | Die ID der Sperre (obwohl LOCK_ID derzeit TRX_ID enthält, kann sich das Datenformat in LOCK_ID jederzeit ändern. Schreiben Sie keine Anwendungen, die LOCK_ID-Werte analysieren ) |
requesting_trx_id | Die Transaktions-ID der angeforderten Sperrressource |
requested_lock_id | Die ID der angeforderten Sperre |
blocking_trx_id | Die blockierte Transaktions-ID |
blocking_lock _id | Die ID des blockierten Schlosses |
Weitere verwandte kostenlose Lernempfehlungen: MySQL-Tutorial(Video)
Das obige ist der detaillierte Inhalt vonVerstehen Sie, was eine Sperre ist und wie Sie das Phantomleseproblem in MySQL lösen können. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!