Ausführliche Erklärung des Sperrmechanismus in MySQL InnoDB

Vorne geschrieben

Eine Datenbank ist im Wesentlichen eine gemeinsam genutzte Ressource. Daher muss bei der Maximierung der Leistung bei gleichzeitigem Zugriff sichergestellt werden, dass jeder Benutzer die Daten auf konsistente Weise lesen und ändern kann. Zur Lösung derartiger Probleme ist eine Sperre die beste Waffe.

Erstellen Sie zunächst einen neuen Tabellentest mit der ID als Primärschlüssel, dem Namen als sekundärem Index und der Adresse als eindeutigem Index.

CREATE TABLE `test` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `name` int(11) NICHT NULL,
 `Adresse` int(11) NICHT NULL,
 Primärschlüssel (`id`),
 EINZIGARTIGER SCHLÜSSEL `idex_unique` (`Adresse`),
 SCHLÜSSEL `idx_index` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

Zeilensperren in INSERT-Methoden

Es ist ersichtlich, dass, wenn zwei Transaktionen nacheinander INSERT-Operationen an Zeilendatensätzen mit demselben Primärschlüssel ausführen, Transaktion B nur warten kann, bis die Zeilensperre nach dem Commit von Transaktion A freigegeben wird, da Transaktion A zuerst die Zeilensperre erhält. Wenn Sie Daten in die eindeutige Indexfeldadresse einfügen, müssen Sie ebenfalls eine Zeilensperre erwerben. Das Diagramm ähnelt dem Einfügen des Primärschlüssels und wird hier nicht wiederholt.

Wenn jedoch beide Transaktionen Daten in den Namen des Hilfsindexfelds einfügen, muss nicht auf die Sperre gewartet werden, da selbst wenn das Hilfsindexfeld denselben Wert hat, in der Datenbank unterschiedliche Zeilen bearbeitet werden und kein Konflikt auftritt.

Die Update-Methode hat ähnliche Ergebnisse wie die Insert-Methode.

Tabellensperren und Zeilensperren unter SELECT FOR UPDATE

Die SELECT FOR UPDATE-Anweisung von Transaktion A erhält die Tabellensperre des Tabellentests. Zu diesem Zeitpunkt wird Transaktion B beim Ausführen des Einfügevorgangs blockiert. Erst nachdem Transaktion A die Tabellensperre festgeschrieben und freigegeben hat, kann Transaktion B die entsprechende Zeilensperre erhalten und den Einfügevorgang ausführen.

Wenn auf die Anweisung SELECT FOR UPDATE der Transaktion A jedoch WHERE id = 1 folgt, erwirbt diese Anweisung nur eine Zeilensperre, keine Tabellensperre, und blockiert nicht die Änderungsvorgänge der Transaktion B an anderen Primärschlüsseln.

Lückensperren unter sekundären Indizes

Schauen Sie sich zunächst die Daten unter der Testtabelle an:

mysql> wähle * aus Test;
+----+------+---------+
| ID | Name | Adresse |
+----+------+---------+
| 3 | 1 | 3 |
| 6 | 1 | 2 |
| 7 | 2 | 4 |
| 8 | 10 | 5 |
+----+------+---------+
4 Zeilen im Satz (0,00 Sek.)

Gap Lock kann als eine Art Zeilensperre bezeichnet werden. Der Unterschied besteht darin, dass es Datensätze innerhalb eines Bereichs sperrt. Seine Funktion besteht darin, Phantom-Lesevorgänge zu vermeiden, d. h. die plötzliche Erhöhung oder Verringerung von Intervalldateneinträgen. Die wichtigsten Lösungen sind:

• Verhindert, dass neue Daten in die Lücke eingefügt werden; deshalb wird es als Lückensperre bezeichnet.
• Verhindern Sie, dass vorhandene Daten nach einem Aktualisierungsvorgang zu Daten in der Lücke werden (wenn beispielsweise das Namensfeld mit der ID = 7 auf 1 aktualisiert wird, ändert sich die Anzahl der Einträge mit dem Namen = 1 von 2 auf 3).

Die Bedingungen für die automatische Verwendung von Gap-Locks durch InnoDB sind:

• Isolationsebene „Repeatable Read“: Die Standardarbeitsebene von MySQL
• Die Suchbedingung muss einen Index haben (wenn kein Index vorhanden ist, wird ein vollständiger Tabellenscan durchgeführt, der alle Datensätze in der gesamten Tabelle sperrt)

Wenn InnoDB Indexdatensätze scannt, fügt es zuerst eine Zeilensperre zum ausgewählten Indexzeilendatensatz hinzu und fügt dann eine Lückensperre zu den Lücken auf beiden Seiten des Indexdatensatzes hinzu (durch Scannen nach links, um den ersten Wert zu finden, der kleiner als der angegebene Parameter ist, und durch Scannen nach rechts, um den ersten Wert zu finden, der größer als der angegebene Parameter ist, wodurch ein Intervall erstellt wird). Wenn eine Lücke durch Transaktion A gesperrt ist, kann Transaktion B keine Datensätze in diese Lücke einfügen.

Das „Gap Lock“, von dem wir hier sprechen, ist eigentlich nicht GAP LOCK, sondern RECORD LOCK + GAP LOCK, was in InnoDB NEXT_KEY LOCK genannt wird.

Schauen wir uns ein Beispiel an. Wenn wir eine Tabelle erstellen, geben wir die Spalte Name als Hilfsindex an. Derzeit sind die Werte dieser Spalte [1,2,10]. Die Lückenbereiche sind (-∞, 1], [1,1], [1,2], [2,10], [10, +∞)

Runde 1:

• Transaktion A SELECT ... WHERE name = 1 FOR UPDATE;
• Lückensperre hinzufügen zu (-∞, 2)
• Transaktion B INSERT ... Name = 1 Blöcke
• Transaktion B INSERT ... name = -100 blockiert
• Transaktion B INSERT ... name = 2 ist erfolgreich
• Transaktion B INSERT ... name = 3 ist erfolgreich

Runde 2:

• Transaktion A SELECT ... WHERE name = 2 FOR UPDATE;
• Lückensperre hinzufügen zu [1, 10)
• Transaktion B INSERT ... Name = 1 Blöcke
• Transaktion B INSERT ... Name = 9 Blöcke
• Transaktion B INSERT ... name = 10 Erfolg
• Transaktion B INSERT ... name = 0 Erfolg

Runde 3:

• Transaktion A SELECT ... WHERE name <= 2 FOR UPDATE;
• Lückensperre für (-∞, +∞) hinzufügen
• Transaktion B INSERT ... Name = 3 Blöcke
• Transaktion B INSERT ... name = 300 blockiert
• Transaktion B INSERT ... name = -300 blockiert

Zusammenfassung des InnoDB-Sperrmechanismus

Verweise

• „MySQL Technical Insider InnoDB Storage Engine“ 2. Ausgabe von Jiang Chengyao
• Über MySQL InnoDB's Lock

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.