Beispiele für optimistisches und pessimistisches Sperren in MySQL

Die Aufgabe der Parallelitätskontrolle in einem Datenbankverwaltungssystem besteht darin, sicherzustellen, dass mehrere Transaktionen, die gleichzeitig auf dieselben Daten in der Datenbank zugreifen, die Isolation und Einheit der Transaktionen und die Einheit der Datenbank nicht zerstören.

Die wichtigsten technischen Mittel zur Parallelitätskontrolle von optimistischer und pessimistischer Sperre

Pessimistische Sperre

In relationalen Datenbankverwaltungssystemen ist die pessimistische Parallelitätskontrolle (PCC) eine Methode zur Parallelitätskontrolle. Dadurch wird verhindert, dass durch eine Transaktion Daten in einer Weise geändert werden, die Auswirkungen auf andere Benutzer hat. Wenn eine Transaktion eine Sperre auf jede Datenzeile in einer Operation anwendet, können andere Transaktionen Operationen, die mit der Sperre in Konflikt stehen, nur dann ausführen, wenn die Transaktionssperre aufgehoben wird.

Die pessimistische Parallelitätskontrolle wird hauptsächlich in Umgebungen mit starken Datenkonflikten verwendet, in denen die Kosten für die Verwendung von Sperren zum Schutz der Daten bei einem Parallelitätskonflikt geringer sind als die Kosten für das Zurücksetzen der Transaktion.

Pessimistische Sperrung bezeichnet eine konservative (pessimistische) Haltung gegenüber Daten, die von der Außenwelt geändert werden (einschließlich anderer aktueller Transaktionen im System und Transaktionsverarbeitung von externen Systemen). Daher sind die Daten während des gesamten Sommerverarbeitungsprozesses gesperrt. Die Implementierung der pessimistischen Sperre basiert im Allgemeinen auf dem von der Datenbank bereitgestellten Sperrmechanismus (empfohlenes Tutorial: MySQL-Tutorial).

In der Datenbank sieht der pessimistische Sperrprozess wie folgt aus

• Versuchen Sie, vor dem Ändern eines Datensatzes eine exklusive Sperre für den Datensatz zu erhalten.
• Wenn die Sperre fehlschlägt, bedeutet dies, dass der Datensatz geändert wird und die aktuelle Abfrage möglicherweise warten muss oder eine Ausnahme auslöst.
• Wenn die Sperre erfolgreich erworben wurde, kann der Datensatz geändert werden und die Sperre wird nach Abschluss der Transaktion entsperrt.
• Wenn andere Vorgänge vorhanden sind, um den Datensatz zu ändern oder exklusive Sperren hinzuzufügen, warten sie darauf, dass wir ihn entsperren, oder werfen direkt eine Ausnahme.

Verwenden pessimistischer Sperren in MySQL InnoDB

Um die pessimistische Sperre zu verwenden, müssen Sie die Autocommit-Eigenschaft der MySQL-Datenbank deaktivieren, da MySQL standardmäßig den Autocommit-Modus verwendet. Dies bedeutet, dass MySQL das Ergebnis sofort festschreibt, wenn Sie einen Aktualisierungsvorgang durchführen.

//Transaktion starten begin;/Arbeit beginnen;/Transaktion starten; (eine der drei Optionen auswählen)

Wählen Sie den Status aus t_goods, wobei id=1 für die Aktualisierung ist;

//Eine Bestellung basierend auf den Produktinformationen erstellen. In t_orders (id,goods_id) Werte einfügen (null,1);

//Ändern Sie den Produktstatus auf 2

Aktualisiere t_Goods, setze Status=2;

//Transaktions-Commit durchführen;/Arbeits-Commit durchführen;

In der obigen Abfrageanweisung wird die Methode „select…for update“ verwendet und die pessimistische Sperre durch das Öffnen einer exklusiven Sperre implementiert. Der entsprechende Datensatz wird gesperrt und andere Transaktionen müssen warten, bis diese Transaktion festgeschrieben ist, bevor sie ausgeführt werden können.

Wir verwenden select ... für update, um die Daten zu sperren, müssen dabei aber auf die Sperrebene achten. MySQL InnoDB verwendet standardmäßig Sperren auf Zeilenebene. Sperren auf Zeilenebene basieren alle auf Indizes. Wenn eine SQL-Anweisung keinen Index verwendet, wird keine Sperre auf Zeilenebene verwendet, und eine Sperre auf Tabellenebene wird verwendet, um die gesamte Tabelle zu sperren.

Merkmale

Gewährleistet die Sicherheit der Datenverarbeitung

Aus Effizienzgründen verursacht der Sperrmechanismus zusätzlichen Overhead für die Datenbank und erhöht die Wahrscheinlichkeit eines Deadlocks.

Da bei schreibgeschützten Transaktionen kein Konflikt auftritt, müssen keine Sperren verwendet werden, was die Systemlast erhöhen und die Parallelität verringern würde.

Optimistisches Sperren

Auch die optimistische Parallelitätskontrolle ist eine Methode zur Parallelitätskontrolle.

Gehen Sie davon aus, dass mehrere gleichzeitige Transaktionen sich während der Verarbeitung nicht gegenseitig beeinflussen und jede Transaktion den von ihr betroffenen Teil der Daten verarbeiten kann, ohne Sperren zu generieren. Vor dem Senden von Datenaktualisierungen prüft jede Transaktion zunächst, ob andere Transaktionen die Daten geändert haben, nachdem die Transaktion die Daten gelesen hat. Wenn dies der Fall ist, wird die gesendete Transaktion zurückgesetzt.

Optimistisches Sperren unterscheidet sich vom pessimistischen Sperren. Es wird davon ausgegangen, dass es zu keinen Datenkonflikten kommt. Daher wird beim Senden und Aktualisieren von Daten formal geprüft, ob es zu Datenkonflikten kommt. Wenn ein Konflikt gefunden wird, wird dem Benutzer eine Fehlermeldung zurückgegeben, die es ihm ermöglicht, zu entscheiden, was zu tun ist.

Beim optimistischen Sperren wird im Allgemeinen eine Datensatzversionsnummer verwendet, um den Daten eine Versionskennung hinzuzufügen, und die Versionskennung wird aktualisiert, wenn die Daten aktualisiert werden.

erreichen

Wenn Sie Versionsnummern verwenden, können Sie beim Initialisieren der Daten eine Versionsnummer angeben und jeder Aktualisierungsvorgang für die Daten führt einen +1-Vorgang für die Versionsnummer aus. Und bestimmen Sie, ob die aktuelle Versionsnummer die neueste Versionsnummer der Daten ist

1. Produktinformationen abfragen

Wählen Sie (Status, Status, Version) aus t_goods, wobei id = # {id}

2. Bestellungen auf Basis von Produktinformationen generieren

3. Ändern Sie den Produktstatus auf 2

t_goods aktualisieren

setze Status=2,Version=Version+1

wobei id=#{id} und version=#{version};

Merkmale

Bei der optimistischen Parallelitätssteuerung wird davon ausgegangen, dass die Wahrscheinlichkeit von Datenkonflikten zwischen Transaktionen gering ist. Daher versucht sie, so direkt wie möglich vorzugehen und sperrt nicht, bis die Transaktion festgeschrieben ist, sodass keine Sperren oder Deadlocks auftreten.