Was sind Intent Shared Locks, Intent Exclusive Locks und Deadlocks von MySQL?

1. InnoDB-Sperren auf Tabellenebene

Sperren auf Zeilenebene sollten normalerweise verwendet werden, um die Integrität von Transaktionen sicherzustellen. Dies ist auch einer der häufigsten Gründe für die Wahl der InnoDB-Engine. In Einzelfällen können jedoch auch Sperren auf Tabellenebene erforderlich sein.

Die Transaktion muss die meisten oder alle Daten aktualisieren und die Tabelle ist relativ groß. Wenn die Standardzeilensperre verwendet wird, ist nicht nur die Effizienz der Transaktionsausführung gering, sondern es kann auch dazu führen, dass andere Transaktionen auf eine warten Lange Zeit und Sperrkonflikte. Die Transaktion umfasst mehrere Tabellen, was wahrscheinlich zu einem Deadlock und einer großen Anzahl von Transaktions-Rollbacks führt. Wenn wir eine Tabellensperre erhalten möchten, führen Sie den folgenden Befehl aus:

Bei der Verwendung von Tabellensperren treten folgende Effizienzprobleme auf:

Um eine Tabellensperre zu erhalten. Um eine gemeinsame Sperre S oder eine exklusive Sperre X für eine Tabelle zu verwenden, müssen Sie zunächst sicherstellen, dass diese Tabelle nicht erworben wurde andere Transaktionen. Angenommen, wir haben 10 Millionen Daten in dieser Tabelle. Wie kann dann ermittelt werden, welche Zeilen dieser 10 Millionen Daten X-Sperren haben?
Wenn Sie die S-Sperre der Tabelle erhalten möchten, müssen Sie ermitteln, welche Zeilen in der Tabelle über X-Sperren verfügen. Wenn einige Zeilen über X-Sperren verfügen, können Sie die S-Sperre oder X-Sperre dieser Tabelle nicht erhalten. Es gibt keinen besseren Weg, als einzeln zu prüfen, was zu Ineffizienz führt. Die Ineffizienz wird durch die Notwendigkeit verursacht, Tabellensperren hinzuzufügen und die Daten einzeln zu durchlaufen, um festzustellen, ob einige Daten Zeilensperren haben. Die Absichts-Gemeinschaftssperre und die Absichts-Exklusivsperre, die wir hier lernen, können gelöst werden

Wenn Sie die X-Sperre der Tabelle erhalten möchten, müssen Sie nicht überprüfen, welche Zeilensperren in der Tabelle von (X oder S) belegt sind. . Sie müssen nur schnell IX und IS überprüfen.

2. Absichtliche gemeinsame Sperre und beabsichtigte exklusive Sperre

Intention gemeinsame Sperre (IS-Sperre):

Der Transaktionsplan fügt dem Datensatz zunächst eine gemeinsame Sperre hinzu Eine Reihe von Datensätzen.

• Absichtliche exklusive Sperre (IX-Sperre): Der Transaktionsplan fügt dem Datensatz eine exklusive Sperre hinzu. Bevor die Transaktion einer Datensatzreihe eine exklusive Sperre hinzufügt Erhalten Sie die IX-Sperre der Tabelle

Bevor Sie die Zeilensperre hinzufügen, wird die IS- oder IX-Sperre der Tabelle von der InnoDB-Speicher-Engine hinzugefügt

• Die Absichtssperren sind alle kompatibel und verursacht keine Konflikte, hauptsächlich um anderen dabei zu helfen, die Effizienz beim Erwerb von Tabellensperren zu steigern

• Der Zweck von Absichtssperren besteht darin, Tabellensperren effizienter zu erwerben (X und S in der Tabelle beziehen sich auf Tabellensperren). , keine Zeilensperren!)

• Absichtssperren Es handelt sich um eine Sperre auf Tabellenebene,

  koordiniert die Koexistenz von Tabellensperren und Zeilensperren
. Der Hauptzweck besteht darin, anzuzeigen, dass eine Transaktion eine Zeile sperrt oder versucht, eine Zeile zu sperren.

• Analysieren Sie Transaktion 1, um die Zeilen-X-Sperre zu erhalten, und Transaktion 2, um die Tabellen-S-Sperre zu erhalten:

Wenn Transaktion 1 eine IX-Sperre hinzufügen muss. Wenn Transaktion 2 die S-Sperre der gesamten Tabelle erwerben möchte, stellt sie fest, dass eine andere Transaktion die IX-Sperre für diese Tabelle erworben hat. Dies bedeutet, dass in dieser Tabelle einige Daten vorhanden sein müssen, die zur X-Sperre hinzugefügt wurden In Transaktion 2 kann der gesamten Tabelle keine S-Sperre hinzugefügt werden. Zu diesem Zeitpunkt kann Transaktion 2 nur warten und die Tabellensperre

1 nicht erfolgreich erreichen. Dies liegt daran, dass MyISAM keine Transaktionen unterstützt Tabellensperren erhalten immer alle benötigten Sperren auf einmal. Entweder erfüllen sie alle oder warten, damit es nicht zu einem Deadlock kommt. In InnoDB wird die Sperre jedoch schrittweise erhalten, mit Ausnahme von Transaktionen, die aus einem einzelnen SQL bestehen. Das heißt, die Granularität der Sperre ist relativ gering, was dazu führt, dass in InnoDB ein Deadlock möglich ist. Wenn Sie mit mehreren Tabellen arbeiten, kann es natürlich trotzdem zu einem Deadlock kommen.

Deadlock-Probleme werden im Allgemeinen von uns selbst verursacht. Ähnlich wie bei der Multi-Thread-Programmierung werden die meisten davon durch die unterschiedliche Reihenfolge verursacht, in der unsere mehreren Threads mehrere Sperrressourcen erhalten. Wenn wir daher unterschiedliche Codesegmente verwenden, um mehrere Tabellen in der Datenbank zu aktualisieren, sollten diese Tabellen in derselben Reihenfolge aktualisiert werden, um zu verhindern, dass Sperrkonflikte Deadlock-Probleme verursachen.

2. Deadlock-Szenarien und -Lösungen

Die Szenarien, in denen ein Deadlock auftritt, sind wie folgt:

Transaktion 1 erhält erfolgreich Zeilensperre 1Transaktion 2 erhält erfolgreich Zeilensperre 2

…

Transaktion 1 kann Zeilensperre 2 nicht erwerben. Während sie blockiert ist, gibt es keine Möglichkeit, Commit/Rollback auszuführen und Zeilensperre 1 kann nicht freigegeben werden

Transaktion 2 kann Zeilensperre 1 nicht erwerben. Während sie blockiert ist, gibt es keine Möglichkeit, Commit/Rollback auszuführen und Zeilensperre 2 kann nicht freigegeben werden

Alle Transaktionen sind blockiert. Das bedeutet, dass alle Threads im Prozess blockiert sind, was zu einem Deadlock-Problem führt.

Methoden zur Lösung von Deadlocks: Wenn mehrere Transaktionen/Threads mehrere gleiche Ressourcensperren erwerben, sollten sie Ressourcensperren in derselben Reihenfolge erwerben.

Die Transaktion ist blockiert oder blockiert, mit einem Timeout für die Transaktionsblockierung Lange Zeit schlägt die Transaktionsverarbeitung nach einer Zeitüberschreitung fehl und die aktuell gehaltene Sperre wird automatisch aufgehoben.

3. Vorgang

Manuelle Festschreibung und wiederholbare Leseisolationsstufen festlegen und Transaktionen öffnen

#🎜🎜 # Fragen Sie die von MVCC bereitgestellten Snapshot-Lesevorgänge in der wiederholbaren Leseisolationsstufe ab, und es gibt keine exklusive Sperre 7. Transaktion 2 erhält die exklusive Sperre mit der ID = 8

#🎜 🎜#

Transaktion 1 erhält dann die exklusive Sperre mit der ID=8, die Blockierung erfolgt#🎜 🎜#

Transaktion 2 erhält die exklusive Sperre mit id=7 erneut und blockiert.

#🎜 🎜#Zu diesem Zeitpunkt erkannte MySQL Server, dass ein Deadlock aufgetreten war, also entsperrte er Transaktion 1 und setzte Transaktion 1 zurück , und gab die von ihr belegte Zeilensperre frei, sodass Transaktion 2 erfolgreich die exklusive Sperre mit der ID=7 erhalten hat

3 Vorschläge zur Sperrenoptimierung

Unter der Voraussetzung, dass das Geschäft korrekt abgeschlossen werden kann,

versuchen Sie, eine niedrigere Isolationsstufe zu verwenden (Dirty Reads müssen vermieden werden)

#🎜🎜, um die Effizienz sicherzustellen #

Entwerfen Sie vernünftige Indizes und

versuchen Sie, Indizes für den Zugriff auf Daten zu verwenden,

um das Sperren genauer zu machen, das Risiko von Sperrkonflikten zu verringern und die Parallelität zu verbessern

• #🎜🎜 #

  Wählen Sie eine angemessene Transaktionsgröße. Die Wahrscheinlichkeit von Sperrenkonflikten ist bei kleinen Transaktionen gering (je größer die Transaktion, desto mehr SQL enthält sie. Möglicherweise sind mehr Sperren für Tabellenressourcen und Zeilenressourcen enthalten, was die Wahrscheinlichkeit von Sperrenkonflikten erhöht ) Wenn verschiedene Programme auf eine Gruppe von Tabellen zugreifen, sollten sie versuchen, auf jede Tabelle in der gleichen Reihenfolge zuzugreifen. Versuchen Sie bei einer Tabelle, so oft wie möglich auf Zeilen in einer Tabelle in einer festen Reihenfolge zuzugreifen. Dies kann die Wahrscheinlichkeit eines Deadlocks erheblich verringern 🎜#(Tatsächlich werden bei gleichwertigen Abfragen auch Lückensperren hinzugefügt.) Beantragen Sie keine Sperrstufe, die über den tatsächlichen Bedarf hinausgeht Anzeigesperre beim Abfragen, da MVCC unter den Isolationsstufen „Read-Commit“ und „Repeatable-Read“ einen Lesemechanismus ohne manuelle Sperre bereitgestellt hat

Das obige ist der detaillierte Inhalt vonWas sind Intent Shared Locks, Intent Exclusive Locks und Deadlocks von MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!