Ausführliche Erläuterung der Probleme im Zusammenhang mit Sperren in MySQL

Wie kann ich gleichzeitig auf die Datenbank zugreifen? Die Antwort lautet 加锁.

Empfohlen: „MySQL-Video-Tutorial“

Als nächstes sprechen wir über den Sperrmechanismus der Datenbank. Welche Sperren gibt es in der Datenbank?

Zunächst einmal handelt es sich bei Sperren um eine Technologie zur Parallelitätskontrolle. Sperren werden zum Schutz von Daten verwendet, wenn mehrere Benutzer gleichzeitig auf dieselben Daten zugreifen.

Es gibt zwei grundlegende Sperrtypen:

Gemeinsame (S) Sperre: Mehrere Transaktionen können eine gemeinsam genutzte Seite blockieren. Normalerweise kann keine Transaktion die Seite ändern gelesen wird, wird die S-Sperre sofort aufgehoben. Wenn Sie die Select-Anweisung ausführen, müssen Sie dem Operationsobjekt (Tabelle oder einigen Datensätzen) eine gemeinsame Sperre hinzufügen. Bevor Sie die Sperre hinzufügen, müssen Sie jedoch prüfen, ob eine exklusive Sperre vorhanden ist. Wenn nicht, können Sie eine gemeinsame Sperre hinzufügen (Einem Objekt können N gemeinsame Sperren hinzugefügt werden), sonst funktioniert es nicht. Die gemeinsame Sperre wird normalerweise nach der Ausführung der SELECT-Anweisung freigegeben. Sie kann natürlich auch freigegeben werden, wenn die Transaktion endet (einschließlich normalem Ende und abnormalem Ende), was hauptsächlich von der von der Datenbank festgelegten Transaktionsisolationsstufe abhängt.

Exklusive (X)-Sperre: Nur eine Transaktion darf diese Seite blockieren. Jede andere Transaktion muss warten, bis die X-Sperre aufgehoben wird, bevor sie auf die Seite zugreifen kann . Beim Ausführen von Einfüge-, Aktualisierungs- und Löschanweisungen müssen Sie dem zu bearbeitenden Objekt eine exklusive Sperre hinzufügen. Bevor Sie eine exklusive Sperre hinzufügen, müssen Sie bestätigen, dass keine weiteren Sperren für das Objekt vorhanden sind Dem Objekt können keine weiteren Sperren hinzugefügt werden. Die exklusive Sperre wird normalerweise am Ende der Transaktion aufgehoben (natürlich gibt es Ausnahmen, das heißt, wenn die Isolationsstufe der Datenbanktransaktion auf „Nicht festgeschrieben lesen“ (nicht festgeschriebene Daten lesen) eingestellt ist, wird in diesem Fall die exklusive Sperre nach dem Ende der Transaktion aufgehoben Aktualisierungsvorgang abgeschlossen ist) freigegeben, nicht am Ende der Transaktion).

Sperrmechanismus

Da Schlösser verwendet werden, besteht die Möglichkeit eines Deadlocks.

Vier notwendige Bedingungen für einen Deadlock:

Bedingung des gegenseitigen Ausschlusses: Eine Ressource kann jeweils nur von einem Prozess verwendet werden.

Anforderungs- und Haltebedingungen: Wenn ein Prozess aufgrund der Anforderung von Ressourcen blockiert wird, behält er die erhaltenen Ressourcen.

Nichtentzugsbedingung: Ressourcen, die ein Prozess erhalten hat, können nicht gewaltsam entzogen werden, bevor sie aufgebraucht sind.

Schleifenwartebedingung: Mehrere Prozesse bilden eine Kopf-an-Ende-Schleife, die auf eine Ressourcenbeziehung wartet.

Solange im System ein Deadlock auftritt, müssen diese Bedingungen erfüllt sein, und solange eine der oben genannten Bedingungen nicht erfüllt ist, tritt kein Deadlock auf.

Deadlock verhindern

Um einen Deadlock zu verhindern, muss lediglich eine der vier notwendigen Bedingungen für einen Deadlock zerstört werden.

1) Zerstören Sie die gegenseitige Ausschlussbedingung

Wenn alle Systemressourcen gemeinsam genutzt werden dürfen, gerät das System nicht in einen Deadlock-Zustand. Auf einige Ressourcen kann jedoch nicht gleichzeitig zugegriffen werden. Kritische Ressourcen wie Drucker können nur gegenseitig genutzt werden. Daher ist es nicht möglich, gegenseitige Ausschlussbedingungen zu zerstören, um einen Stillstand zu verhindern, und in einigen Fällen sollte diese gegenseitige Ausschließlichkeit geschützt werden.

2) Zerstörung unveräußerlicher Bedingungen

Wenn ein Prozess, der einige unveräußerliche Ressourcen behalten hat, neue Ressourcen anfordert, aber nicht erfüllt werden kann, muss er alle behaltenen Ressourcen freigeben und sie dann erneut anwenden in Zukunft benötigt werden. Dies bedeutet, dass von einem Prozess bereits belegte Ressourcen vorübergehend freigegeben oder entzogen werden oder dadurch die unveräußerliche Bedingung verletzt wird.

Die Umsetzung dieser Strategie ist komplex und kann zum Scheitern der vorherigen Arbeitsphase führen. Die wiederholte Anwendung und Freigabe von Ressourcen erhöht den Systemaufwand und verringert den Systemdurchsatz. Diese Methode wird häufig für Ressourcen verwendet, deren Status leicht zu speichern und wiederherzustellen ist, z. B. CPU-Register und Speicherressourcen. Sie kann im Allgemeinen nicht für Ressourcen wie Drucker verwendet werden.

3) Zerstören Sie die Anforderungs- und Aufbewahrungsbedingungen

Verwenden Sie die vorstatische Zuweisungsmethode, das heißt, der Prozess wendet alle benötigten Ressourcen einmal an, bevor er ausgeführt wird , wird es nicht in Betrieb genommen. Sobald es in Betrieb genommen wird, sind diese Ressourcen immer Eigentum des Unternehmens und es werden keine weiteren Ressourcenanforderungen gestellt, wodurch sichergestellt wird, dass das System nicht blockiert.

Diese Methode ist einfach zu implementieren, aber die Mängel liegen auch auf der Hand. Einige dieser Ressourcen werden möglicherweise nur zu Beginn oder gegen Ende des Laufs verwendet überhaupt nicht genutzt. Es kommt auch zu einem „Hunger“-Phänomen, wenn eine bestimmte Ressource über einen längeren Zeitraum von anderen Prozessen belegt ist, was dazu führt, dass der Prozess, der auf die Ressource wartet, mit der Ausführung beginnt.

4) Zerstörung der Schleifenwartebedingung

Um die Schleifenwartebedingung zu zerstören, kann die Methode der sequentiellen Ressourcenzuweisung verwendet werden. Nummerieren Sie zunächst die Ressourcen im System und legen Sie fest, dass jeder Prozess Ressourcen in aufsteigender Reihenfolge anfordern muss und alle ähnlichen Ressourcen gleichzeitig angefordert werden können. Mit anderen Worten: Solange ein Prozess die Zuweisung von Ressourcen Ri anwendet, kann der Prozess in zukünftigen Ressourcenanwendungen nur Ressourcen mit einer Anzahl größer als Ri beantragen.

Das Problem bei dieser Methode besteht darin, dass die Nummerierung relativ stabil sein muss, was das Hinzufügen neuer Gerätetypen einschränkt, obwohl bei der Nummerierung der Ressourcen häufig die Reihenfolge berücksichtigt wird, in der die meisten Jobs diese Ressourcen tatsächlich nutzen Die Reihenfolge, in der Jobs Ressourcen verwenden, unterscheidet sich von der vom System angegebenen Reihenfolge. Darüber hinaus werden Ressourcen verschwendet. Außerdem führt diese Methode, Ressourcen in der angegebenen Reihenfolge zu beantragen, zwangsläufig zu Problemen bei der Programmierung des Benutzers.

Deadlock aufheben

1) Ressourcen aus dem Deadlock-Prozess entziehen

2) Einige oder alle Prozesse beenden; 🎜>MySQL-Sperrgranularität (d. h. Sperrebene)

MySQL-Speicher-Engines verwenden drei Arten (Ebenen) von Sperrmechanismen: Sperren auf Zeilenebene, Sperren auf Seitenebene und Sperren auf Tabellenebene.

1. Sperre auf Tabellenebene: Sperren Sie die gesamte Tabelle direkt. Während Ihres Sperrzeitraums können andere Prozesse nicht in die Tabelle schreiben. Wenn Sie eine Schreibsperre haben, dürfen andere Prozesse nicht lesen. Merkmale: geringer Overhead, schnelles Sperren; größte Sperrgranularität, höchste Wahrscheinlichkeit von Sperrkonflikten und niedrigste Parallelität.

Die MyISAM-Speicher-Engine verwendet Sperren auf Tabellenebene.

Es gibt zwei Modi: gemeinsame Lesesperre für die Tabelle und exklusive Schreibsperre für die Tabelle. Der Befehl zum Hinzufügen einer Lesesperre: Tabelle sperren Tabellenname read; Der Befehl zum Entfernen der Sperre: Tabellen entsperren.

Gleichzeitiges Einfügen unterstützen: Unterstützt die gleichzeitige Ausführung von Abfrage- und Einfügevorgängen (gleichzeitiges Einfügen am Ende der Tabelle).

Sperrplanungsmechanismus: Schreibsperrpriorität. Ein Prozess fordert eine Lesesperre für eine MyISAM-Tabelle an, und gleichzeitig fordert ein anderer Prozess auch eine Schreibsperre für dieselbe Tabelle an. Wie geht MySQL damit um? Die Antwort ist, dass der Schreibprozess zuerst die Sperre erhält.

2. Sperre auf Zeilenebene: Sperren Sie nur den angegebenen Datensatz, sodass andere Prozesse weiterhin andere Datensätze in derselben Tabelle bearbeiten können. Merkmale: Hoher Overhead, langsame Sperren; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrkonflikten ist am höchsten und die Parallelität ist am höchsten.

Die InnoDB-Speicher-Engine unterstützt sowohl Sperren auf Zeilenebene als auch Sperren auf Tabellenebene, standardmäßig werden jedoch Sperren auf Zeilenebene verwendet.

3. Sperre auf Seitenebene: Sperren Sie jeweils eine Gruppe benachbarter Datensätze. Der Overhead und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren. Die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren, und die Parallelität ist durchschnittlich.

Die am häufigsten verwendete Methode zur Handhabung des gleichzeitigen Zugriffs durch mehrere Benutzer ist das Sperren. Wenn ein Benutzer ein Objekt in der Datenbank sperrt, können andere Benutzer nicht mehr auf das Objekt zugreifen. Die Auswirkung der Sperrung auf den gleichzeitigen Zugriff spiegelt sich in der Granularität der Sperre wider. Beispielsweise schränkt eine auf einer Tabelle platzierte Sperre den gleichzeitigen Zugriff auf die gesamte Tabelle ein. (Seitensperre) schränkt eine auf einer Datenseite platzierte Sperre den Zugriff auf die gesamte Datenseite ein Die Sperre beschränkt nur den gleichzeitigen Zugriff auf die Zeile.

Die Konzepte, Implementierungsmethoden und Nutzungsszenarien des optimistischen Sperrens und des pessimistischen Sperrens

Es gibt zwei Sperrmechanismen: pessimistisches Sperren und optimistisches Sperren.

Pessimistische Sperre ist, wie der Name schon sagt, pessimistisch in Bezug auf die Welt. Sie glaubt, dass die Wahrscheinlichkeit, dass andere auf die sich ändernden Daten zugreifen, sehr hoch ist, und sperrt daher die Daten, wenn sich die Daten zu ändern beginnen Erst dann erfolgt die Freigabe.

Ein typischer datenbankabhängiger pessimistischer Sperraufruf:

Wählen Sie * aus Konto, wobei Name="Erica" ​​für die Aktualisierung aus.

Diese SQL-Anweisung sperrt alle Konten im Konto Tabelle Datensätze, die den Suchkriterien entsprechen (name="Erica"). Bevor diese Transaktion festgeschrieben wird (die Sperre während der Transaktion wird aufgehoben, wenn die Transaktion festgeschrieben wird), kann die Außenwelt diese Datensätze nicht ändern. Diese Anweisung wird verwendet, um bestimmte Zeilen zu sperren (wenn eine Where-Klausel vorhanden ist, erfüllen diese Zeilen die Where-Bedingung). Wenn diese Zeilen gesperrt sind, können andere Sitzungen diese Zeilen auswählen, sie können diese Zeilen jedoch nicht ändern oder löschen, bis die Transaktion der Anweisung durch eine Commit-Anweisung oder eine Rollback-Anweisung beendet wird. Es ist zu beachten, dass select...for update im MySQL-Transaktionstyp begin und commit platziert werden muss, sonst funktioniert es nicht.

Pessimismus kann zu langen Sperrzeiten und schlechter Ausgabe führen, insbesondere zu langen Transaktionen, was sich auf die Gesamtleistung des Systems auswirkt.

Implementierungsmethode der pessimistischen Sperre:

Die pessimistische Sperre wird ebenfalls basierend auf dem Datenbanksperrmechanismus implementiert. In herkömmlichen relationalen Datenbanken werden viele solcher Sperrmechanismen verwendet, z. B. Zeilensperren, Tabellensperren, Lesesperren, Schreibsperren usw., die alle vor Operationen gesperrt werden.

Optimistisches Sperren, das relativ optimistisch in Bezug auf die Welt ist, geht davon aus, dass die Wahrscheinlichkeit, dass andere auf die sich ändernden Daten zugreifen, sehr gering ist, sodass die Daten erst gesperrt werden, wenn die Änderung abgeschlossen ist und die Änderung bereit ist Sie sperren das Objekt nicht, wenn Sie es lesen und ändern, und geben es nach Abschluss der Änderungen frei. Optimistisches Sperren kann das Problem schmutziger Lesevorgänge nicht lösen.

Die Sperrzeit des optimistischen Sperrens ist kürzer als die des pessimistischen Sperrens, was die Gesamtleistung des Systems bei großer Parallelität erheblich verbessert.

So implementieren Sie optimistisches Sperren:

1. Die meisten von ihnen basieren auf dem Aufzeichnungsmechanismus der Datenversion (Version), der das Hinzufügen einer Versionskennung zu jeder Datenzeile erfordert (d. h. ein weiteres Feld für jede Datenzeile) Version), jedes Mal, wenn die Daten aktualisiert werden, muss die entsprechende Versionsnummer +1 aktualisiert werden.

Funktionsprinzip: Lesen Sie beim Lesen von Daten diese Version zusammen und fügen Sie bei einer späteren Aktualisierung eins zu dieser Versionsnummer hinzu. Zu diesem Zeitpunkt werden die Versionsinformationen der übermittelten Daten mit den aktuellen Versionsinformationen des entsprechenden Datensatzes in der Datenbanktabelle verglichen. Wenn die Versionsnummer der übermittelten Daten größer als die aktuelle Versionsnummer der Datenbanktabelle ist Andernfalls gelten die Daten als abgelaufen und müssen erneut gelesen werden.

2. Verwenden Sie einen Zeitstempel zum Implementieren

Es dient auch dazu, der Tabelle ein Feld hinzuzufügen, das eine optimistische Sperrsteuerung erfordert. Der Feldtyp verwendet einen Zeitstempel, ähnlich wie Bei der oben genannten Version wird auch der Zeitstempel der Daten in der aktuellen Datenbank überprüft und mit dem Zeitstempel verglichen, der vor dem Update erhalten wurde. Wenn sie konsistent sind, ist dies in Ordnung, andernfalls handelt es sich um einen Versionskonflikt.

Anwendbare Szenarien für pessimistische Sperren und optimistische Sperren:

Wenn der Umfang der Parallelität nicht groß ist, können Sie pessimistische Sperren verwenden, um das Parallelitätsproblem zu lösen Der Grad der Parallelität im System ist sehr groß. Pessimismus führt zu sehr großen Leistungsproblemen. Daher müssen wir die optimistische Sperrmethode wählen. Die meisten Anwendungen sollten jetzt optimistisch gesperrt sein.

Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung der Probleme im Zusammenhang mit Sperren in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!