Zusammenfassung des Wissens zu MySQL-Sperren

Sperren in MySQL

Sperren sind ein Mittel, um Ressourcenkonkurrenz in einer parallelen Umgebung zu lösen. Unter ihnen sind optimistische Parallelitätskontrolle, pessimistische Parallelitätskontrolle und Multiversions-Parallelitätskontrolle die wichtigsten technischen Mittel, die für die Datenbank-Parallelitätskontrolle verwendet werden (Einzelheiten finden Sie in meinem vorherigen Artikel), und die Sperre in MySQL ist die pessimistische Parallelitätskontrolle.

Es gibt viele Arten von Sperren in MySQL, die wie folgt klassifiziert werden können.

Durch Lesen und Schreiben

Aus Sicht des Lesens und Schreibens in der Datenbank können Datenbanksperren in die folgenden Kategorien unterteilt werden:

• Exklusive Sperre: auch bekannt als exklusive Sperre, X-Sperre, Schreibsperre. X-Sperren sind nicht mit anderen Sperren kompatibel. Solange eine Transaktion eine Sperre zu den Daten hinzugefügt hat, können andere Transaktionen keine X-Sperren auf die Daten setzen. Gleichzeitig können andere Transaktionen, nachdem eine Transaktion eine X-Sperre gesetzt hat, keine weiteren Sperren hinzufügen. Nur Transaktionen, die exklusive Sperren erhalten, können die Daten lesen und ändern.
• Gemeinsam genutzte Sperre: auch als Lesesperre oder S-Sperre bekannt. Das S-Schloss ist mit dem S-Schloss kompatibel und kann gleichzeitig platziert werden.
• Update-Schloss: auch als Bügelschloss bekannt. Es ermöglicht das Hinzufügen von S-Sperren, erlaubt anderen Transaktionen jedoch nicht, U-Sperren oder X-Sperren anzuwenden. Wenn die gelesenen Daten aktualisiert werden sollen, wird die S-Sperre zu einer X-Sperre aufgewertet. Der Vorteil der U-Sperre besteht darin, dass sie Transaktion A das Lesen von Daten ermöglicht, ohne andere Transaktionen zu blockieren, und gleichzeitig sicherstellt, dass die Daten seit dem letzten Lesen durch Transaktion A nicht geändert wurden. Daher kann sie den Konflikt zwischen X-Sperren und S-Sperren verringern und Deadlocks vermeiden, die durch das Upgrade der S-Sperre auf eine X-Sperre verursacht werden. Beachten Sie, dass MySQL U Lock nicht unterstützt, sondern nur SQL Server.
  

Die Kompatibilitätsmatrix sieht wie folgt aus (+ steht für kompatibel, - steht für inkompatibel)

Nach Granularität

MySQL unterstützt verschiedene Sperrebenen und der Umfang der gesperrten Daten ist ebenfalls unterschiedlich. Dies wird oft als Granularität der Sperre bezeichnet. MySQL verfügt über drei Sperrebenen: Sperre auf Zeilenebene, Sperre auf Seitenebene und Sperre auf Tabellenebene. Verschiedene Speicher-Engines unterstützen unterschiedliche Sperrgranularitäten. Beispielsweise verwenden die Speicher-Engines MyISAM und MEMORY Sperren auf Tabellenebene, Sperren auf Seitenebene werden nur von der Speicher-Engine BDB unterstützt und die Speicher-Engine InnoDB unterstützt Sperren auf Zeilenebene und auf Tabellenebene. Sperren auf Zeilenebene werden standardmäßig verwendet.

Merkmale

Sperre auf Tabellenebene: geringer Overhead, schnelle Sperrung, kein Deadlock, hohe Sperrgranularität, höchste Wahrscheinlichkeit eines Sperrkonflikts und geringste Parallelität. Die Datenbank-Engine vermeidet Deadlocks, indem sie immer alle erforderlichen Sperren gleichzeitig und die Tabellensperren immer in der gleichen Reihenfolge erwirbt.
Sperre auf Zeilenebene: Hoher Overhead, langsame Sperrung; es kann zu Deadlocks kommen; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit eines Sperrkonflikts am geringsten und die Parallelität am höchsten. Zeilensperren werden immer inkrementell erworben, sodass es zu Deadlocks kommen kann.
Seitensperre: Der Overhead und die Sperrzeit liegen zwischen der Tabellensperre und der Zeilensperre. Es können Deadlocks auftreten. Die Sperrgranularität liegt zwischen der Tabellensperre und der Zeilensperre und die Parallelität ist durchschnittlich.

Nachfolgend finden Sie eine ausführliche Einführung in Zeilensperren und Tabellensperren. Seitensperren werden hier nicht vorgestellt, da sie selten verwendet werden.

Zeilensperre

Daten zeilenweise sperren. InnoDB-Zeilensperren werden implementiert, indem die Indexelemente im Index gesperrt werden. InnoDB muss einen gruppierten Index haben, und Zeilensperren fallen schließlich auf den gruppierten Index. Bei Abfragen über einen nicht gruppierten Index wird zuerst der nicht gruppierte Index gesperrt und dann der gruppierte Index. Wenn eine Where-Anweisung sowohl einen gruppierten Index als auch einen sekundären Index enthält, wird zuerst der gruppierte Index und dann der sekundäre Index gesperrt. Da die Sperrung schrittweise erfolgt, kann es zu einem Deadlock kommen.

MySQL-Zeilensperren nehmen präzisere Unterteilungen von S- und X-Sperren vor, wodurch die Granularität der Zeilensperren feiner wird und Konflikte reduziert werden. Dies ist die Kompatibilitätsmatrix, die als „präziser Modus“ bezeichnet wird. (Diese Matrix erscheint nicht in der offiziellen Dokumentation, sondern wurde aus dem MySQL-Quellcode lock0lock.c:lock_rec_has_to_wait abgeleitet.)

Zeilensperren-Kompatibilitätsmatrix

• Lückensperre: Sperrt nur die Lücke, das erste und das zweite Öffnungsintervall (a, b), sperrt die Lücke des Index, um zu verhindern, dass andere Transaktionen Daten einfügen.
• Datensatzsperre: sperrt nur Datensätze, bestimmte Datensatzzeilen.
• Next-Key Lock: Sperrt den Datensatz und die Lücke gleichzeitig, indem das erste Intervall geöffnet und das letzte Intervall (a, b) geschlossen wird.
• Einfügeabsichtssperre: Eine Sperre, die beim Einfügen verwendet wird. Im Code fügt das Einfügen einer Absichtssperre der GAP-Sperre tatsächlich ein LOCK_INSERT_INTENTION-Tag hinzu.

S Lock und S Lock sind vollständig kompatibel. Daher ist es nicht erforderlich, die genauen Modi zu vergleichen, um die Kompatibilität zu bestimmen. Genaue Moduserkennung, wird zwischen S,X und X,X verwendet. Aus dieser Matrix lassen sich mehrere Charakteristika ableiten:

• Es gibt keine Konflikte zwischen INSERT-Operationen: Sie fügen Ihre ein, ich füge meine ein.
• GAP und Next-Key verhindern das Einfügen: Die eingefügten Daten liegen genau innerhalb des Intervalls und das Einfügen ist nicht zulässig.
• GAP, Record und Next-Key stehen nicht im Konflikt
• Es besteht ein Konflikt zwischen Record, Record und Next-Key.
• Eine vorhandene Einfügesperre blockiert kein hinzugefügtes Schloss.
• Eine Lückensperre (egal ob S oder X) blockiert nur Einfügevorgänge.
  

Notiz

• Bei Datensatzsperren muss es sich bei der Spalte um eine eindeutige Indexspalte oder eine Primärschlüsselspalte handeln und die Abfrageanweisung muss eine exakte Übereinstimmung, wie beispielsweise "=", aufweisen, da die Datensatzsperre sonst zu einer temporären Schlüsselsperre degeneriert.
• Lückensperren und Sperren für benachbarte Schlüssel basieren auf nicht eindeutigen Indizes. Lückensperren und Sperren für benachbarte Schlüssel existieren nicht für eindeutige Indexspalten.
  

Missverständnisse über Tabellensperren und das Sperren von Tabellen

InnoDB verwendet Sperren auf Zeilenebene nur, wenn die Daten über Indexbedingungen korrekt abgerufen werden (es liegt kein Indexfehler vor). Andernfalls sperrt InnoDB alle Datensätze in der Tabelle, d. h. es sperrt die gesamte Tabelle. Beachten Sie, dass wir hier über das Sperren der gesamten Tabelle sprechen, Innodb jedoch keine Tabellensperren verwendet, um die Tabelle zu sperren. Stattdessen wird die unten vorgestellte Next-Key-Sperre verwendet, um die gesamte Tabelle zu sperren. Viele Aussagen im Internet besagen, dass Tabellensperren verwendet werden sollten, aber in Wirklichkeit ist dies nicht der Fall. Wir können dies anhand des folgenden Beispiels sehen.

Angenommen, wir haben die folgenden Daten (MySQL8):

mysql> wähle * aus Benutzern;
+----+------+-----+
| ID | Name | Alter |
+----+------+-----+
| 1 | ein | 1 |
| 2 | ein | 1 |
| 3 | ein | 1 |
| 4 | ein | 1 |
| 5 | ein | 1 |
+----+------+-----+

Methode 1:

Wir verwenden die Tabellensperre, um die Tabelle zu sperren und den Status der Engine zu überprüfen

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Tabellen sperren, die Benutzer schreiben;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Engine-InnoDB-Status anzeigen\G
...
------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 4863
Bereinigung für TRXs Nr. 0 < 4862 durchgeführt Rückgängig machen Nr. 0 < 0 Status: läuft, aber im Leerlauf
Länge der Verlaufsliste 911
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSAKTION 281479760456232, nicht gestartet
1 MySQL-Tabelle im Einsatz, 1 gesperrt ################Beachten Sie, dass 0 Sperrstruktur(en), Heap-Größe 1136, 0 Zeilensperre(n)
...

Anschließend sperren wir es durch Abfragen der nicht indizierten Felder und prüfen den Status der Engine.

## Entsperren Sie die letzte Tabellensperre. firstmysql> unlock tables;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle * von Benutzern, wobei Name = "a" für Update ist;

mysql> Engine-InnoDB-Status anzeigen\G
...
------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 4864
Bereinigung für TRXs Nr. 0 < 4862 durchgeführt Rückgängig machen Nr. 0 < 0 Status: läuft, aber im Leerlauf
Länge der Verlaufsliste 911
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSAKTION 4863, AKTIV 37 Sek.
2 Sperrstrukturen, Heap-Größe 1136, 6 Zeilensperren ################Hinweis hier...

Anschließend löschen wir die Daten mit der ID 2, 3 und 4 und sperren sie anschließend, indem wir das nicht indizierte Feld abfragen und den Status der Engine überprüfen.

mysql> löschen von Benutzern, bei denen die ID in (2,3,4) ist;
Abfrage OK, 3 Zeilen betroffen (0,00 Sek.)

mysql> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle * von Benutzern, wobei Name = "a" für Update ist;

mysql> Engine-InnoDB-Status anzeigen\G
...
------------
TRANSAKTIONEN
------------
Trx-ID-Zähler 4870
Bereinigung für TRXs Nr. 0 < 4869 durchgeführt, Rückgängigmachen Nr. 0 < 0 Status: läuft, aber im Leerlauf
Länge der Verlaufsliste: 914
LISTE DER TRANSAKTIONEN FÜR JEDE SITZUNG:
---TRANSAKTION 4869, AKTIV 9 Sek
2 Sperrstrukturen, Heap-Größe 1136, 3 Zeilensperren ################Hinweis hier...

Sie sehen, dass sich die Verwendung einer Tabellensperre hier vom Sperren der gesamten Tabelle unterscheidet, da Sie keinen Index verwenden können, um eine bestimmte Zeile zu sperren. Ab der zweiten und dritten Operation unterscheiden sich auch die gesperrten Zeilen. Dies liegt daran, dass die Anzahl der Lücken zwischen den beiden Operationen unterschiedlich ist. Daher ist ersichtlich, dass nicht die Next-Key-Sperre verwendet wird, sondern die Tabellensperre. Beim ersten Mal sperrte es (-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞], und beim zweiten Mal sperrte es (-∞,1],(1,5],(5,∞].

Methode 2:

Sie können auch die folgende Anweisung verwenden, um die Sperrinformationen anzuzeigen. Sie können auch erkennen, dass eine Zeilensperre verwendet wird und das Intervall (Daten können nicht eingefügt werden) und der Datensatz gesperrt sind. Es handelt sich also um eine Next-Key-Sperre.

mysql> wählen Sie ENGINE_TRANSACTION_ID, LOCK_TYPE, LOCK_MODE aus performance_schema.data_locks, wobei ENGINE_TRANSACTION_ID in (Ihrer Transaktions-ID) ist;
+----------------------+-----------+--------------+
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
+----------------------+-----------+--------------+
| 4889 | TABELLE | IX |
| 4889 | AUFZEICHNUNG | X |
| 4889 | AUFZEICHNUNG | X |
| 4889 | AUFZEICHNUNG | X |
+----------------------+-----------+--------------+
10 Zeilen im Satz (0,00 Sek.)

LOCK_TYPE: Für InnoDB sind die optionalen Werte RECORD (Zeilensperre), TABLE (Tabellensperre)

LOCK_MODE: Für InnoDB sind die möglichen Werte S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC und UNKNOWN. Mit Ausnahme von AUTO_INC und UNKNOWN umfassen alle anderen Sperrmodi die GAP-Sperre (sofern vorhanden).

Einzelheiten finden Sie in der MySQL-Dokumentation: https://dev.mysql.com/doc/ref...

Tabellensperre

Sperren Sie direkt die gesamte Tabelle, was sich auf alle Datensätze in der Tabelle auswirkt. Informationen zur Kompatibilität von Tabellenlesesperren und Tabellenschreibsperren finden Sie in der obigen Analyse.

Zusätzlich zu Tabellenlesesperren und Tabellenschreibsperren verfügt MySQL auch über eine spezielle Tabellensperre: die Absichtssperre, die dazu dient, die Kompatibilitätsbeurteilung von Sperren unterschiedlicher Granularität zu lösen.

Absichtssperre

Da die Sperrgranularität unterschiedlich ist, deckt der Umfang der Tabellensperre den Umfang der Zeilensperre ab, sodass es zu Konflikten zwischen der Tabellensperre und der Zeilensperre kommt. Beispiel: Transaktion A fügt einer Datenzeile in der Tabelle eine Zeilensperre hinzu, und dann möchte Transaktion B eine Tabellensperre hinzufügen. Normalerweise sollte es zu Konflikten kommen. Wenn nur eine Zeilensperre vorhanden ist, müssen wir jede Datenzeile durchlaufen, um festzustellen, ob ein Konflikt vorliegt, was nicht sehr effizient ist. Daher haben wir eine beabsichtigte Tabellensperre.

Der Hauptzweck von Absichtssperren besteht darin, die Koexistenz von Zeilensperren und Tabellensperren zu ermöglichen. Bevor eine Zeilensperre beantragt wird, muss eine Transaktion zunächst eine Absichtssperre für die Tabelle beantragen und dann nach Erfolg eine Zeilensperre beantragen. Hinweis: Die Aktion zum Beantragen einer Absichtssperre wird von der Datenbank ausgeführt und erfordert keinen Antrag des Entwicklers.

Absichtssperren sind Sperren auf Tabellenebene, sie zeigen jedoch an, dass eine Transaktion eine Datensatzzeile und nicht die gesamte Tabelle liest oder schreibt. Daher gibt es keine Konflikte zwischen Absichtssperren. Der eigentliche Konflikt wird beim Hinzufügen von Zeilensperren überprüft.

Absichtssperren werden in Absichtslesesperren (IS) und Absichtsschreibsperren (IX) unterteilt.

Kompatibilitätsmatrix für Tabellensperren

Oben finden Sie den detaillierten Inhalt der Zusammenfassung der relevanten Kenntnisse zu MySQL Lock. Weitere Informationen zu MySQL Lock finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!