Was sind Sperren auf Tabellenebene, Sperren auf Zeilenebene, exklusive Sperren und gemeinsame Sperren in MySQL?

Vorwort

Aufgrund der Komplexität unseres Geschäfts sind mehrere zusammengehörige SQL-Anweisungen erforderlich, aus denen eine Transaktion besteht. Lassen Sie uns zunächst erklären, was eine Transaktion ist. Eine Transaktion bezieht sich auf eine Gruppe von SQL-Anweisungen, die alle gemeinsam ausgeführt werden müssen, andernfalls ist ein teilweiser Erfolg oder ein teilweiser Fehler nicht zulässig. Eine Transaktion hat ACID-Merkmale:

• Atomizität: Entweder alle sind erfolgreich oder alle scheitern, um die Konsistenz der Transaktion sicherzustellen;

• Konsistenz: Zum Beispiel wird bei einer Banküberweisung das Geld einer Person von Ihnen abgezogen Wenn Sie einer anderen Person Geld hinzufügen müssen, können Sie es nicht einfach abziehen, ohne es hinzuzufügen, da es sonst zu Problemen im Unternehmen kommt und die Konsistenz der Daten zerstört wird wird zuerst in den Cache geschrieben. Unter anderem dauert es noch einige Zeit, bis die Daten im Cache auf die Festplatte geschrieben werden. Bei einem Stromausfall, einer Ausfallzeit oder einem Neustart verfügen wir über ein Redo-Log, um die Haltbarkeit der Datenbank sicherzustellen

• Isolation:

  In diesem Abschnitt kann erklärt werden, warum Transaktionen isoliert werden müssen, da Transaktionen gleichzeitig ausgeführt werden müssen und wir oft viele Unternehmen im Hintergrund haben müssen Wenn alle Transaktionen seriell ausgeführt werden, wird beim Schreiben eines Multithread-Programms nur ein Thread die Arbeit erledigen, was sehr ineffizient ist. Daher müssen Transaktionen gleichzeitig ausgeführt werden, aber die gleichzeitige Ausführung bringt einige Probleme mit sich: Sicherheit und Konsistenz von Transaktionen und

  Probleme mit der Parallelitätseffizienz
Wir verwenden diese beiden Dinge als Referenzpunkte, um die verschiedenen Ebenen der Parallelität/Isolation von MySQL zu erhalten, falls dies der Fall ist Nicht isolieren, wenn Transaktionen gleichzeitig ausgeführt werden, kann es zu „Dirty Reading“ kommen (Transaktion B liest die nicht festgeschriebenen Daten von Transaktion A und verwendet dann die nicht festgeschriebenen Daten von Transaktion A, um Berechnungen durchzuführen, und erhält viele andere Ergebnisse und dann Transaktion A Rollt diese Daten zurück, dann sind die von Transaktion B berechneten Daten alle problematische Daten, schmutziges Lesen wird definitiv Probleme verursachen),
• nicht wiederholbares Lesen

  (Entfernen Sie ein Datenelement unter den gleichen Bedingungen und dann Als ich erneut zur Abfrage ging Ich habe festgestellt, dass sich der Wert der Daten geändert hat. Das nicht wiederholbare Lesen ist in einigen Geschäftsszenarien zulässig Phantom Read (das Datenvolumen der Ergebnisse zweier Abfragen vor und nach den gleichen Bedingungen in der Transaktion ist unterschiedlich) löst diese Probleme. Um die bei der gleichzeitigen Ausführung von Transaktionen auftretenden Probleme zu lösen, geben wir die Isolationsstufe von Transaktionen an: Serialisierung: Die Serialisierung wird vollständig mithilfe von Sperren implementiert und alle Transaktionen werden durch Sperren sortiert Damit ist die Datensicherheit hoch, die Parallelitätseffizienz jedoch gering. Im Allgemeinen werden wir dies nicht tun.

Uncommitted Read Für das von uns geschriebene Multithread-Programm gibt es keine Parallelitätskontrolle für das kritische Abschnittscodesegment, aber die Datensicherheit ist auch sehr gering . Dies ist problematisch, daher sollten niemals nicht festgeschriebene Lesevorgänge verwendet werden. Serialisierung und nicht festgeschriebenes Lesen werden in tatsächlichen Projekten nicht verwendet. Im Allgemeinen arbeiten Datenbank-Engines standardmäßig mit festgeschriebenem Lesen und wiederholbarem Lesen. Diese beiden Isolationsstufen kombinieren Datensicherheit und -konsistenz mit Daten-Parallelitätseffizienz. Diese beiden werden durch die MVCC-Multifunktion realisiert. Versions-Parallelitätskontrollmechanismus.

• Lesen eingereicht, Oracles Standardarbeitsebene. Das Lesen nicht festgeschriebener Daten ist weiterhin nicht zulässig. Nicht wiederholbare Lesevorgänge und virtuelle Lesevorgänge sind weiterhin möglich.

• Wiederholbares Lesen, MySQL-Standardarbeitsebene. Es ist garantiert, dass beim erneuten Lesen der Transaktion immer noch dieselben Daten abgerufen werden, wodurch das virtuelle Lesen teilweise gelöst wird. Das virtuelle Lesen erfolgt jedoch weiterhin. Je mehr Leistung aufgewendet wird, um Konflikte zu vermeiden, desto geringer ist die Effizienz.

Auf der Ebene des „wiederholbaren Lesens“ können tatsächlich einige der virtuellen Leseprobleme gelöst werden, die durch Aktualisierungen verursachten virtuellen Leseprobleme können jedoch nicht verhindert werden. Um das Auftreten virtueller Lesevorgänge zu verhindern, müssen Sie dennoch festlegen Isolationsstufe der Serialisierung.
Das Implementierungsprinzip der Transaktionsisolationsstufe:
• Lock + MVCC

  . Das zugrunde liegende Implementierungsprinzip der Serialisierung sind Sperren, einschließlich gemeinsam genutzter Sperren, gemeinsamer Absichtssperren, exklusiver Absichtssperren, Lückensperren und Deadlocks. Das zugrunde liegende Implementierungsprinzip des festgeschriebenen Lesens und des wiederholbaren Lesens: MVCC (Mehrfachversions-Parallelitätskontrolle). MVCC bietet eine Methode zum gleichzeitigen Lesen, einschließlich Snapshot-Lesen (die gleichen Daten haben mehrere Versionen), aktuelles Lesen, Rückgängig-Protokoll und Redo-Protokoll.

  MVCC ist das Prinzip des festgeschriebenen Lesens und des wiederholbaren Lesens, und Sperre ist das Prinzip der Serialisierung

Das Transaktionsprotokoll wird zur Implementierung von ACID-Funktionen verwendet, während gemeinsame Sperren, exklusive Sperren und MVCC zur Implementierung von Konsistenzfunktionen (I) verwendet werden. Das Transaktionsprotokoll ist in Rückgängig-Protokoll (Rollback-Protokoll) und Redo-Protokoll (Redo-Protokoll) unterteilt.

1. Sperre auf Tabellenebene und Sperre auf Zeilenebene

• Sperre auf Tabellenebene: Sperren Sie die gesamte Tabelle. Der Overhead ist gering (da Sie den Datensatz einer bestimmten Zeile in der Tabelle nicht finden müssen, um ihn zu sperren. Wenn Sie diese Tabelle ändern möchten, beantragen Sie direkt die Sperre dieser Tabelle), die Sperre ist schnell. und es wird keinen Deadlock geben;

  Die Sperrgranularität ist groß, die Wahrscheinlichkeit eines Sperrkonflikts ist hoch und die Parallelität ist gering.

• Sperre auf Zeilenebene:

  Sperren eines Zeilendatensatzes. Es ist teuer (Sie müssen die entsprechenden Datensätze in der Tabelle finden, und es gibt einen Prozess zum Durchsuchen der Tabelle und des Index), und es kommt zu langsamen Sperren. Die Sperrgranularität ist am geringsten, und die Wahrscheinlichkeit von Sperrkonflikten ist geringer am niedrigsten und die Parallelität ist hoch
Die MyISAM-Speicher-Engine unterstützt nur Sperren auf Tabellenebene, während InnoDB Transaktionsverarbeitung, Sperren auf Zeilenebene und bessere Parallelitätsfunktionen unterstützt

2. Exklusive Sperren Gemeinsame Sperren

Exklusive Sperre:

auch bekannt als Lesen und Schreiben (SX, SX), Schreiben und Schreiben (XX) schließen sich gegenseitig aus.

• 1 Testen Sie die Kompatibilität von exklusiven Sperren und gemeinsam genutzten Sperren zwischen verschiedenen Transaktionen

• Sehen Sie sich die Isolationsstufe an:

Öffnen Sie zunächst eine Transaktion A und fügen Sie eine exklusive Sperre zu den Daten mit der ID=7 hinzu:

Öffnen Sie Transaktion B auf einem anderen Client:

Unabhängig von der exklusiven Sperre oder der gemeinsamen Sperre ist die ID = 7 blockiert und kann nicht abgefragt werden, da Transaktion A den Daten in der Zeile mit der ID = 7 eine exklusive Sperre hinzugefügt hat, bei der es sich um eine Schreibsperre handelt. und andere können weder lesen noch schreiben.

Zusammenfassung: Für Datensperren zwischen verschiedenen Transaktionen können nur SS-Sperren koexistieren, und zwar XX, SX und im Indexbaum.

Jedes Mal, wenn Sie einen Test beenden, setzen Sie das zurück, was Sie gerade getan haben.

Verwenden Sie das nicht indizierte Feld der Tabelle als Filterbedingung

Jetzt ruft Transaktion 2 die Datensätze verschiedener Zeilen ab. chenwei

InnoDB unterstützt Zeilensperren, wenn die Primärschlüssel-ID als Filter verwendet wurde Bedingung: Transaktion 1 und Transaktion 2 können erfolgreich Sperren für verschiedene Zeilen erhalten. Jetzt stellen wir jedoch fest, dass wir die exklusive Sperre namens Chenwei nicht erhalten können. Lassen Sie uns erklären:

Die Zeilensperre von InnoDB wird durch das Sperren von Indexelementen und nicht durch das Sperren von Tabellenzeilendatensätzen implementiert.

Und wir verwenden den Namen als Filterbedingung, ohne den Index zu verwenden, daher werden natürlich keine Zeilensperren verwendet, Tabellensperren jedoch schon gebraucht. Dies bedeutet, dass InnoDB nur Sperren auf Zeilenebene verwendet, um Daten über Indizes abzurufen, andernfalls verwendet InnoDB Tabellensperren!!! Bedienung:

Wir haben festgestellt, dass nach dem Hinzufügen eines Index zum Namen zwei Transaktionen exklusive Sperren (zur Aktualisierung) für verschiedene Zeilen erhalten können, was erneut beweist, dass den Indexelementen die Zeilensperren von InnoDB hinzugefügt werden.

Da der Name jetzt den Index durchläuft, verwenden Sie zhangsan, um die ID des Zeilendatensatzes zu finden, in dem er sich im Hilfsindexbaum befindet 7, und gehen Sie dann zum Primärschlüssel-Indexbaum. Erhalten Sie die exklusive Sperre des entsprechenden Zeilendatensatzes (meine persönliche Vermutung ist, dass die entsprechenden Datensätze im Hilfsindexbaum und Primärschlüssel-Indexbaum gesperrt sind)

#🎜 🎜#3. Test der Serialisierungsisolationsstufe

# 🎜🎜# Alle Transaktionen in der Serialisierung verwenden gemeinsame Sperren oder exklusive Sperren, sodass sie nicht manuell hinzugefügt werden müssen. Auswählen erwirbt eine gemeinsame Sperre, und Einfügen, Löschen und Aktualisieren erwerben exklusive Sperren.

Serialisierungsisolationsstufe festlegen:

Zwei Transaktionen Gemeinsame Sperren können gleichzeitig erfasst werden (SS-Koexistenz:

Lassen Sie nun Transaktion 2 Daten einfügen;

Da durch Einfügen eine exklusive Sperre hinzugefügt werden muss, Transaktion 1 jedoch eine gemeinsame Sperre für die gesamte Tabelle hinzugefügt hat, kann Transaktion 2 die Tabelle nicht mehr erfolgreich sperren ( sx nicht koexistiert)

Rollback und Rollback aller Sperrenerfassungszustände:

Öffnen Sie zwei Transaktionen:

Da wir dem Namen einen Index hinzugefügt haben, ist die obige Auswahl äquivalent. Yu hat eine gemeinsame Zeile hinzugefügt Sperre für die Daten mit dem Namen zhangsan

Transaktion 2update;

#🎜 🎜#Transaktion 2 kann nicht aktualisiert werden, weil Die gesamte Tabelle wurde zu diesem Zeitpunkt durch die gemeinsame Sperre von Transaktion 1 gesperrt

Transaktion 2 sucht im Hilfsindexbaum nach Zhangsan, findet den entsprechenden Primärschlüsselwert und geht dann zum Primärschlüssel Der Indexbaum hat den entsprechenden Datensatz gefunden, aber festgestellt, dass diese Datensatzzeile durch eine gemeinsame Sperre gesperrt wurde. Transaktion 2 kann die gemeinsame Sperre erhalten, kann jedoch die exklusive Sperre nicht erhalten 🎜##🎜 🎜# Versuchen wir erneut, den Primärschlüsselindex zu verwenden, um zu sehen, ob die ID aktualisiert werden kann Hier verwenden wir jetzt ID anstelle von ID-Name, aber Name findet auch den entsprechenden Primärschlüssel über den Hilfsindexbaum und findet dann den entsprechenden Datensatz im Primärschlüssel-Indexbaum, und die Datensätze im Primärschlüssel-Indexbaum sind gesperrt #🎜 🎜#Wir aktualisieren die Daten mit id=8, erfolgreich. Da wir bei der Auswahl nur eine Zeilensperre zu den Daten mit id=7 hinzugefügt haben, können wir die Daten natürlich erfolgreich mit id=8# bearbeiten 🎜#

# 🎜🎜#

Wenn ein Index vorhanden ist, verwenden Sie eine Tabellensperre, wenn kein Index vorhanden ist Beziehen Sie sich auf die Art der Sperre. Unabhängig davon, ob es sich um eine Tabellensperre oder eine Zeilensperre handelt, wird zwischen gemeinsamer Sperre und exklusiver Sperre unterschieden Wenn Sie auf der wiederholbaren Leseebene keine Sperre verwenden, können wir InnoDB auch manuell sperren Wenn ein Indexelement in einer Abfrage verwendet wird, verwendet es eine Zeilensperre, um den Index zu sperren, anstatt einfach eine Datenzeile zu sperren.

Das obige ist der detaillierte Inhalt vonWas sind Sperren auf Tabellenebene, Sperren auf Zeilenebene, exklusive Sperren und gemeinsame Sperren in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!