Die umfassendste Erklärung des Sperrmechanismus in MySQL

Vorwort

Differenzierung nach Sperrgranularität

• Globale Sperre
• Tabellensperre
• Zeilensperre
  • Datensatzsperre
  • Lückensperre
  • Pro-Tastensperre

Je nach Sperrszenario

• Optimistisches Sperren
• Pessimistische Sperre

Globale Sperre

Das Sperrobjekt ist: die gesamte Datenbankinstanz

Tabellen mit Lesesperre leeren (FTWRL) - macht die gesamte Datenbank schreibgeschützt

Anwendungsszenario: Erstellen Sie eine logische Sicherung der gesamten Datenbank

Vollständige logische Datenbanksicherung

Warum benötigen wir zum Sichern von Daten eine globale Sperre?

Beispielsweise habe ich für Verkäufe eine Tabelle, um Lieferungen aufzuzeichnen, und eine andere Tabelle, um Abzüge aufzuzeichnen. Als Ergebnis habe ich die Tabelle mit den Lieferdaten gesichert. Zu diesem Zeitpunkt hat jemand etwas gekauft, aber nur die Zahlung wurde abgezogen, aber es gab keine Lieferdaten. Das ist offensichtlich nicht akzeptabel.

Das offizielle logische Backup-Tool ist mysqldump. Wenn mysqldump den Parameter –single-transaction verwendet, wird vor dem Importieren der Daten eine Transaktion gestartet, um eine konsistente Ansicht zu gewährleisten. Dank der Unterstützung von MVCC können die Daten während dieses Vorgangs wie gewohnt aktualisiert werden. Dies ist jedoch transaktionsbasiert und für die MyISAM-Daten-Engine nicht verfügbar. In diesem Fall ist es möglich, dass einige Tabellen nicht auf der InnoDB-Daten-Engine basieren.

Wenn es sich bei allen um Tabellen der InnoDB-Daten-Engine handelt, ist es natürlich besser, den Standard-MySQLDump zu verwenden und den Parameter –single-transaction hinzuzufügen, um eine globale logische Sicherung durchzuführen.

Unterschied zwischen FTWRL und globalem „readonly=true“ festlegen

1. In anderer Logik wird „Nur lesen“ verwendet (verschiedene Systeme haben unterschiedliche)
2. ftwrl kann die Sperre automatisch aufheben, wenn die Clientverbindung getrennt wird, um Deadlock-Probleme zu vermeiden

Tabellensperre

Befehl: Tabelle {tableName} lesen/schreiben sperren (Schreiben ist mächtiger als Lesen, wer schreiben kann, kann auch lesen), Tabelle entsperren

Die gesperrte Ressource erlaubt nur dem aktuellen Thread, die entsprechende Operation auszuführen. Und der aktuelle Thread kann die entsprechende Operation nur an der gesperrten Tabelle ausführen.

Beispiel: Sperrtabelle t1 lesen, der aktuelle Thread kann nur lesen, aber nicht schreiben, und andere Threads können weder lesen noch schreiben

MDL-Sperre

Es ist keine explizite Verwendung erforderlich, es wird automatisch beim Zugriff auf die Tabelle hinzugefügt (um Probleme durch Änderungen in der Tabellenstruktur zu vermeiden)

Wenden Sie eine MDL-Lesesperre an, wenn Sie eine Tabelle hinzufügen, löschen, ändern oder überprüfen, und wenden Sie eine MDL-Schreibsperre an, wenn Sie die Struktur einer Tabelle ändern.

• MDL-Lesesperre (gemeinsame Sperre), Sperren schließen sich nicht gegenseitig aus. Daher können mehrere Threads gleichzeitig hinzugefügt, gelöscht, geändert und abgefragt werden.
• MDL-Schreibsperre (exklusive Sperre), diese Sperre schließt sich gegenseitig mit anderen Lese-/Schreibsperren aus. Das heißt, die aktuelle Datenänderung, Abfrage oder Strukturänderung muss warten, bis andere MDL-Schreibsperren freigegeben werden, bevor sie ausgeführt werden kann

Zeilensperre

Zeilensperren werden von jeder Engine auf Engine-Ebene implementiert (MyISAM unterstützt keine Zeilensperren, daher kann die Engine Aktualisierungsvorgänge immer nur für einen Thread gleichzeitig ausführen).

Bei einer Transaktion: Zeilensperren werden hinzugefügt, wenn eine oder mehrere Datenzeilen benötigt werden, aber alle Zeilensperren werden erst freigegeben, wenn die Transaktion festgeschrieben ist. Mit anderen Worten: Wenn andere Threads auf die Zeilendaten zugreifen müssen, müssen sie warten, bis die Transaktion des Threads festgeschrieben ist, bevor sie darauf zugreifen können.

Beispiel:

Thread A macht folgendes
beginnen;
Aktualisiere t1, setze a=1, wobei id=1 ist;

Aktualisiere t2, setze b=2, wobei id=2 ist;

begehen

Zu diesem Zeitpunkt sperrt Thread A die ID von t1 = 1 bzw. die ID von t2 = 2. Wenn Thread B zu diesem Zeitpunkt auf die ID von t1 = 1 zugreift, kann er nicht darauf zugreifen. Selbst wenn die erste Anweisung ausgeführt wurde, kann Thread B die Daten erst abrufen, nachdem Thread A eine Commit-Operation ausgeführt hat.

Daher müssen wir auf Folgendes achten: Wenn bei der Durchführung von Transaktionsvorgängen die Aktualisierung nicht sequenziell erfolgt, versuchen Sie, die Anweisung mit den meisten Zugriffen zuletzt auszuführen (da die Sperrung sequenziell erfolgt, das Aufheben der Sperrung jedoch gemeinsam erfolgt).

Deadlock

In vielen Situationen kommt es zu Deadlocks, die meisten davon sind auf Probleme mit Datenbankoperationen zurückzuführen. Zum Beispiel

Thread A und Thread B ändern beide id=1 und id=2 und starten Transaktionen

Thread A ändert zuerst id=1, wodurch id=1 von Thread A gesperrt wird.

Thread B ändert id=2, wodurch id=2 von County B gesperrt wird

Zu diesem Zeitpunkt muss Thread A auf die ID=2 warten, um die Sperre freizugeben, und dann den Vorgang für die ID=2 ausführen, während Thread B auf die ID=1 warten muss, um die Sperre freizugeben, und dann den Vorgang für die ID=1 ausführen muss.

Dies führt zu einer zyklischen Deadlock-Situation.

Es gibt zwei Strategien, um mit diesem Problem umzugehen:

1. Gehen Sie direkt zum Warten über, bis die Zeit abgelaufen ist. Dieses Timeout kann durch den Parameter innodb_lock_wait_timeout eingestellt werden (Standard 50 s). Dieser Wert kann nicht zu kurz eingestellt werden. Was ist, wenn es sich nicht um einen Deadlock handelt?
2. Deadlock-Erkennung einleiten. Wenn ein Deadlock erkannt wird, führen Sie einen aktiven Rollback einer Transaktion in der Deadlock-Kette durch, damit andere Transaktionen weiter ausgeführt werden können. Wenn Sie den Parameter innodb_deadlock_detect auf „on“ setzen (der Standardwert ist „on“), wird diese Logik aktiviert.

Datensatzsperre

Ein Fall von Zeilensperre

Es zielt darauf ab, bestimmte Datensatzinformationen zu erfassen, die durch die Transaktion nach dem Sperren gesperrt wurden.

Auslösende Bedingung: Die Abfragebedingung ist genau erfüllt und das übereinstimmende Bedingungsfeld ist eindeutig

Beispiel: update t1 set name="张三" where id=12138

Funktion: Wenn ein Datensatz von der aktuellen Transaktion verwaltet wird, wird er nach dem Sperren nicht von anderen Transaktionen abgerufen, was zu Problemen wie "wiederholtem Lesen" und "Daten-Dirty-Reads" führt

Lückensperre

Ein Fall von Zeilensperre

Mit der Lücke sind die Daten dazwischen gemeint

Es gibt mehrere leere Daten in der Index-ID des Primärschlüssels. Wenn es zu diesem Zeitpunkt zwei Threads A und B gibt, A Daten zwischen 0 und 10 abfragt und B Daten in ID = 3 einfügt, führt dies zu einem Problem beim Lesen von Daten mit fehlerhaftem Lesen.

Wenn Sie eine Transaktion in einem Bereich wie „zwischen“ durchführen, wird daher zur Einschränkung eine Lückensperre hinzugefügt.

Pro-Tastensperre

Die temporäre Schlüsselsperre sperrt die abgefragten Datensätze und sperrt außerdem alle Lücken innerhalb der Bereichsabfrage. Anschließend sperrt sie auch das nächste angrenzende Intervall.

(Lin bedeutet benachbart)

Optimistisches und pessimistisches Sperren

Zusammenfassen

Dies ist das Ende dieses Artikels über den Sperrmechanismus in MySQL. Weitere Informationen zum Sperrmechanismus in MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!