Detaillierte MySQL-Erklärung verschiedener Sperrunterschiede und MVCC

MySQL scheint viele Sperren zu haben,

Welche Tabellensperren, Zeilensperren, Seitensperren

Gemeinsame Sperre, exklusive Sperre, Absichtssperre, Lesesperre, Schreibsperre

Pessimistische Sperre, optimistische Sperre. .

Ich möchte wirklich fragen: Gibt es ein goldenes Schloss? Ich habe immer noch Fan Bingbing. . .

Oh, warum fühlt es sich so chaotisch an? Also lasst es uns klären und zusammenfassen.

Es gibt auch ein Verständnis und Beispiele für MVCC unter Innodb. Wie kann ein effizienter und konsistenter Zugriff bei gleichzeitigem Zugriff aufrechterhalten werden? Es ist einfach und leicht zu verstehen. Ich habe von meinen Freunden gehört, dass große Unternehmen diese Frage auch gerne in Vorstellungsgesprächen stellen.

Tabellen-/Zeilen-/Seitensperre:

Sperrung auf Tabellenebene: MyISAM- und MEMORY-Speicher-Engines

Sperren auf Zeilenebene: InnoDB-Speicher-Engine

Sperren auf Seitenebene: BDB-Speicher-Engine

Sperre auf Tabellenebene: geringer Overhead, geringe Parallelität, schnelles Sperren; große Sperrgranularität, die Wahrscheinlichkeit eines Sperrkonflikts ist am höchsten und Parallelität ist ebenfalls vorhanden am niedrigsten .

Sperren auf Zeilenebene: hoher Overhead, hohe Parallelität, langsame Sperren können am geringsten sein, die Wahrscheinlichkeit von Sperrenkonflikten ist geringer am niedrigsten und der Grad der Parallelität auch am höchsten.

Seitensperre: Der Overhead und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren; die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren, und die Parallelität ist durchschnittlich.

Gemeinsame/exklusive Sperre

Eine gemeinsame Sperre, auch Lesesperre genannt, ist eine Sperre, die durch einen Lesevorgang erstellt wird. Andere Benutzer können die Daten gleichzeitig lesen, aber keine Transaktion kann die Daten ändern (eine exklusive Sperre für die Daten erwerben), bis alle gemeinsamen Sperren aufgehoben wurden.

Exklusive Sperre wird auch Schreibsperre genannt. Wenn Transaktion T eine exklusive Sperre zu Daten A hinzufügt, können andere Transaktionen keine Blockade zu A hinzufügen. Transaktionen mit exklusiven Sperren können Daten sowohl lesen als auch ändern.

Mysiam-Sperrmodus

MyISAM fügt automatisch Lesesperren für alle beteiligten Tabellen hinzu, bevor die Abfrageanweisung ausgeführt wird (SELECT). UPDATE, DELETE, INSERT usw.) werden den beteiligten Tabellen automatisch Schreibsperren hinzugefügt.

a. Lesevorgänge für MyISAM-Tabellen (Hinzufügen von Lesesperren) blockieren nicht die Leseanforderungen anderer Prozesse für dieselbe Tabelle, blockieren jedoch Schreibanforderungen für dieselbe Tabelle. Dies geschieht nur, wenn die Lesesperre aktiviert ist freigegeben. Schreibvorgänge aus anderen Prozessen ausführen.

b. Schreibvorgänge (Hinzufügen von Schreibsperren) zur MyISAM-Tabelle blockieren andere Prozesse von Lese- und Schreibvorgängen in derselben Tabelle. Nur wenn die Schreibsperre aufgehoben wird, werden andere Prozesse blockiert Lese- und Schreibvorgänge des Prozesses ausgeführt werden.

innodb-Sperrmodus

Die Absichtssperre wird von InnoDB automatisch hinzugefügt und erfordert keinen Benutzereingriff.

Beim Einfügen, Aktualisieren und Löschen fügt InnoDB den beteiligten Daten automatisch exklusive Sperren (X) hinzu. Bei allgemeinen Select-Anweisungen fügt InnoDB keine Sperren hinzu und die Transaktion kann Dies kann wie folgt erfolgen: Die Anweisung fügt der Anzeige eine gemeinsame Sperre oder eine exklusive Sperre hinzu.

Gemeinsame Sperre: AUSWÄHLEN ... SPERRE IM TEILUNGSMODUS;

Exklusive Sperre: SELECT ... FOR UPDATE;

MVCC (Multiversion Concurrency Control)

Ein schwer zu verstehendes Konzept, Ich habe viele Informationen und Blogs konsultiert und hier ist eine einfache und leicht verständliche Erklärung.

Szenariosimulation:

Unter der Prämisse einer hohen Parallelität müssen Sie diese Prämisse beachten.

Transaktion L1 ändert den Schlüsselwert von D in einer Tabelle und wurde noch nicht übermittelt.

Transaktion L2 ändert auch den Schlüsselwert von D und übermittelt es. Dann übermittelt L1.

Was ist passiert?

L1 liest den Wert 100, der dem Schlüssel:123 entspricht, aus D

L2 liest 100 entsprechend Schlüssel:123 aus D

L1 addiert 1 zum Wert und aktualisiert Schlüssel:123 auf 100 + 1

Erhöhen Sie den L2-Paarwert um 2 und aktualisieren Sie den Schlüssel:123 auf 100 + 2

Wenn L1 und L2 seriell ausgeführt werden, ist der Wert, der Schlüssel: 123 entspricht, 103, aber der Ausführungseffekt von L1 in der oben genannten gleichzeitigen Ausführung wird vollständig von L2 abgedeckt, und der tatsächliche Wert entspricht key:123 wird zu 102. Nur weil die L1-Transaktion nicht übermittelt wurde, kam L2 erneut.

Wie gehe ich damit um?

Methode 1:

Sperre hinzufügen. Reden wir nicht alle schon einmal über dieses Sperrproblem? Fügen Sie eine Schreibsperre hinzu und warten Sie, bis die Ausführung von L1 abgeschlossen ist, bevor Sie L2 ausführen. Es ist möglich, aber es kommt zu Warteschlangen und die Parallelität sinkt. Dies ist eine Art Pessimismus. Maschinen zur Steuerung der Parallelität auf Sperrenbasis werden im Allgemeinen als pessimistische Mechanismen bezeichnet.

Methode 2:

Um Serialisierbarkeit zu erreichen und gleichzeitig den Sperrmechanismus zu vermeiden Bei verschiedenen bestehenden Problemen können wir einen sperrfreien Parallelitätsmechanismus verwenden, der auf der Idee der Multiversion-Parallelitätskontrolle (MVCC) basiert , und schließlich wird das, was ich sagen möchte, herausgestellt! Im Allgemeinen werden sperrenbasierte Parallelitätskontrollmaschinen als pessimistischer Mechanismus (pessimistische Sperre) bezeichnet, während Mechanismen wie MVCC als optimistischer Mechanismus (optimistische Sperre) bezeichnet werden . Ein Mechanismus zum Hinzufügen einer Versionsnummer. Bei jeder Aktualisierung der Daten wird die Versionsnummer erhöht, um Transaktionskonsistenz und Probleme mit hoher Parallelität effizienter zu verwalten.

Da der Sperrmechanismus präventiv ist, blockiert das Lesen das Schreiben, und das Schreiben blockiert auch das Lesen. Wenn die Sperrgranularität größer und die Zeit länger ist ist Parallelität. Die Leistung wird nicht sehr gut sein. Das Lesen blockiert das Lesen nicht, und das Schreiben blockiert das Lesen nicht Da es keine Sperre gibt, blockieren sich lesende Schreibvorgänge nicht gegenseitig, wodurch die Parallelitätsleistung erheblich verbessert wird. Das Obige ist die detaillierte Erklärung der MySQL-verschiedenen Sperrunterschiede und MVCC. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!