Wie wird die Transaktionsisolation von MySQL erreicht?

Update-, Delete- und Insert-Anweisungen fügen den betreffenden Daten automatisch exklusive Sperren hinzu. Select-Anweisungen fügen standardmäßig keine Sperren hinzu.

Unter welchen Umständen wird der Lesevorgang gesperrt?

• Wählen Sie ... Sperren im Freigabemodus, fügen Sie den gelesenen Datensätzen die S-Sperre hinzu
• Wählen Sie ... zum Aktualisieren, fügen Sie den gelesenen Datensätzen ein X-Lock hinzu
• Datensätze in einer Transaktion lesen und S-Sperren zu den gelesenen Datensätzen hinzufügen
• Wenn die Transaktionsisolationsebene SERIALIZABLE ist, wird den gelesenen Datensätzen eine S-Sperre hinzugefügt.

Es gibt drei Arten von Sperren in InnoDB:

• Datensatzsperre: Einen einzelnen Datensatz sperren
• Lückensperre: Lückensperre, sperrt die Lücke vor dem Datensatz und erlaubt kein Einfügen von Datensätzen
• Next-Key-Lock: sperrt sowohl die Daten als auch die Lücke vor den Daten, d. h. es dürfen keine Datensätze in die Daten oder die Lücke vor den Daten eingefügt werden.

Schreiben Sie eine Demo zur Demonstration

CREATE TABLE `Mädchen` (
  `id` int(11) NICHT NULL,
  `Name` varchar(255),
  `Alter` int(11),
  PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

in Mädchenwerte einfügen
(1, 'Xi Shi', 20),
(5, 'Wang Zhaojun', 23),
(8, 'Diao Chan', 25),
(10, 'Yang Yuhuan', 26),
(12, 'Chen Yuanyuan', 20);

Datensatzsperre

Sperren eines einzelnen Datensatzes

Fügen Sie beispielsweise den Daten mit dem ID-Wert 8 eine Datensatzsperre hinzu, wie unten gezeigt:

Record Lock verfügt auch über S Lock und X Lock und die Kompatibilität ist die gleiche wie zuvor beschrieben.

Der während der SQL-Ausführung hinzugefügte Sperrtyp unterliegt vielen Bedingungen, wie z. B. der Transaktionsisolationsstufe und dem während der Ausführung verwendeten Index (z. B. gruppierter Index, nicht gruppierter Index usw.). Daher werden wir ihn nicht im Detail analysieren, sondern einige einfache Beispiele geben.

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ Verwenden Sie den Primärschlüssel für gleichwertige Abfragen. -- Fügen Sie dem Datensatz mit der ID=8 eine Datensatzsperre vom Typ S hinzu.
Wählen Sie * von Mädchen, wobei ID = 8 im Freigabemodus gesperrt ist;

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ Verwenden Sie den Primärschlüssel für Abfragen mit gleichen Werten. -- Fügen Sie dem Datensatz mit der ID=8 eine Datensatzsperre vom Typ X hinzu.
Wählen Sie * von Mädchen, wo ID = 8 für Update;

Lückensperre

Sperren Sie die Lücke vor dem Datensatz und lassen Sie keine Datensätze einfügen

MySQL kann das Phantomleseproblem durch MVCC und Sperren auf der wiederholbaren Leseisolationsebene lösen

Aktueller Lesevorgang: Gesperrter Snapshot-Lesevorgang: MVCC

Aber wie kann man es abschließen? Da diese Phantomdatensätze beim ersten Lesen nicht vorhanden sind, können wir keine Datensatzsperre hinzufügen. Zu diesem Zeitpunkt können wir das Problem lösen, indem wir eine Lückensperre hinzufügen, d. h. die Lücke sperren.

Wenn eine Transaktion beispielsweise dem Datensatz mit der ID = 8 eine Lückensperre hinzufügt, bedeutet dies, dass andere Transaktionen keine neuen Datensätze in die Lücke vor dem Datensatz mit der ID = 8 einfügen dürfen, d. h. Datensätze mit ID-Werten im Intervall (5, 8) dürfen nicht sofort eingefügt werden. Datensätze mit ID-Werten im Bereich (5, 8) können erst festgeschrieben werden, nachdem die Transaktion mit der Lückensperre festgeschrieben wurde.

Schauen wir uns den folgenden SQL-Sperrprozess an

-- REPEATABLE READ verwendet den Primärschlüssel für Abfragen mit gleichem Wert -- Der Primärschlüsselwert ist jedoch nicht vorhanden -- Fügen Sie dem Clustered-Index-Datensatz mit der ID=8 eine Lückensperre hinzu
SELECT * FROM girl WHERE id = 7 LOCK IN SHARE MODE;

Da der Datensatz mit der ID = 7 nicht vorhanden ist, müssen wir, um Phantomlesen zu verhindern (um den Datensatz mit der ID = 7 im Ergebnissatz zu vermeiden, der durch Ausführen derselben Anweisung in derselben Transaktion erhalten wurde), verhindern, dass andere Transaktionen Datensätze mit der ID = 7 einfügen, bevor die aktuelle Transaktion festgeschrieben wird. Zu diesem Zeitpunkt können wir dem Datensatz mit der ID = 8 eine Lückensperre hinzufügen, d. h. andere Transaktionen dürfen keine neuen Datensätze mit ID-Werten im Bereich (5, 8) einfügen.

Lassen Sie mich Ihnen eine Frage stellen. Gap Lock kann nur die Lücke vor dem Datensatz sperren. Wie sperren Sie also die Lücke nach dem letzten Datensatz?

Tatsächlich werden MySQL-Daten in Seiten gespeichert und jede Seite hat 2 Pseudodatensätze

• Infimum-Datensatz, der den kleinsten Datensatz auf der Seite angibt
• Upremum-Datensatz, der den größten Datensatz auf der Seite angibt

Um zu verhindern, dass andere Transaktionen Datensätze mit ID-Werten im Intervall (12, +∞) einfügen, können wir dem Supremum-Datensatz auf der Seite, auf der sich der Datensatz mit der ID = 12 befindet, eine Lückensperre hinzufügen. Dadurch wird verhindert, dass andere Transaktionen neue Datensätze mit ID-Werten im Intervall (12, +∞) einfügen.

Nächstes Schlüsselschloss

Sperren Sie die Daten und die Lücke vor den Daten gleichzeitig, d. h., es kann kein Datensatz in die Daten oder die Lücke vor den Daten eingefügt werden <br /> So können Sie Next-Key-Sperre=Datensatzsperre+Lückensperre folgendermaßen verstehen

-- REPEATABLE READ verwendet den Primärschlüssel für die Bereichsabfrage -- Fügen Sie dem Clustered-Index-Datensatz mit der ID=8 eine S-Typ-Datensatzsperre hinzu
-- S-Typ Next-Key Lock zu allen Clustered-Index-Datensätzen mit ID>8 hinzufügen (einschließlich Supremum-Pseudo-Datensätze)
SELECT * FROM girl WHERE id >= 8 LOCK IN SHARE MODE;

Um das Phantom-Read-Problem zu lösen, ist es notwendig, anderen Transaktionen das Einfügen von Datensätzen mit ID>=8 zu verbieten, also

• Fügen Sie dem Clustered-Index-Datensatz mit der ID=8 eine S-Typ-Datensatzsperre hinzu
• Fügen Sie allen Clustered-Index-Datensätzen mit IDs > 8 (einschließlich Supremum-Pseudodatensätzen) eine Next-Key-Sperre vom Typ S hinzu.

Tabellensperre

Tabellensperren können auch in S-Sperren und X-Sperren unterteilt werden

Beim Ausführen von Select-, Insert-, Update- oder Delete-Anweisungen für eine Tabelle fügt die InnoDB-Speicher-Engine der Tabelle keine S- oder X-Sperre auf Tabellenebene hinzu.

Beim Ausführen einiger DDL-Anweisungen wie ALTER TABLE und DROP TABLE auf einer Tabelle wird der Tabelle eine X-Sperre hinzugefügt, sodass andere Transaktionen, die Anweisungen wie SELECT INSERT UPDATE DELETE auf der Tabelle ausführen, blockiert werden.

Wenn die Systemvariablen autocommit = 0 und innodb_table_locks = 1 sind, holen Sie sich manuell die S- oder X-Sperre der Tabelle t, die von der InnoDB-Speicher-Engine bereitgestellt wird. Sie können schreiben

Fügen Sie der Tabelle t eine S-Sperre auf Tabellenebene hinzu

Sperrtabellen t lesen

Fügen Sie der Tabelle t eine X-Sperre auf Tabellenebene hinzu

Tabellen sperren t schreiben

Wenn eine Transaktion einer Tabelle eine S-Sperre hinzufügt, dann

• Andere Transaktionen können weiterhin die S-Sperre der Tabelle erhalten
• Andere Transaktionen können weiterhin S-Sperren für bestimmte Datensätze in der Tabelle erhalten.
• Andere Transaktionen können die X-Sperre der Tabelle nicht weiterhin erhalten.
• Andere Transaktionen können nicht weiterhin X-Sperren für bestimmte Datensätze in der Tabelle erhalten.

Wenn eine Transaktion einer Tabelle eine X-Sperre hinzufügt, dann

• Andere Transaktionen können die S-Sperre der Tabelle nicht weiterhin erhalten.
• Andere Transaktionen können nicht weiterhin S-Sperren für bestimmte Datensätze in der Tabelle erhalten.
• Andere Transaktionen können die X-Sperre der Tabelle nicht weiterhin erhalten.
• Andere Transaktionen können nicht weiterhin X-Sperren für bestimmte Datensätze in der Tabelle erhalten.

Daher müssen Sie beim Ändern von Online-Tabellen vorsichtig sein, da dadurch eine große Anzahl von Transaktionen blockiert wird . Es gibt viele ausgereifte Methoden zum Ändern von Online-Tabellen, die hier nicht im Detail beschrieben werden.

Isolationsstufe

Nicht festgeschriebenes Lesen: jedes Mal wird der neueste Datensatz gelesen, ohne dass eine spezielle Verarbeitung durchgeführt wird Serialisierung: Transaktionen werden seriell und ohne Parallelität ausgeführt

Deshalb konzentrieren wir uns auf die isolierte Implementierung von Read Committed und Repeatable Read !

Diese beiden Isolationsebenen werden durch MVCC (Multi-version Concurrency Control) implementiert. Im Wesentlichen speichert MySQL mehrere Versionen historischer Daten durch Undolog und liest Daten einer bestimmten historischen Version gemäß den Regeln. Auf diese Weise können Lesen und Schreiben ohne Sperren parallelisiert werden, was die Datenbankleistung verbessert.

Wie speichert Undolog Datensätze vor der Änderung?

Für Tabellen, die die Speicher-Engine InnoDB verwenden, enthält der Datensatz des gruppierten Index die folgenden zwei erforderlichen ausgeblendeten Spalten:

trx_id : Jedes Mal, wenn eine Transaktion einen gruppierten Indexdatensatz ändert, wird die Transaktions-ID der Transaktion der ausgeblendeten Spalte trx_id zugewiesen.

roll_pointer : Jedes Mal, wenn ein Clustered-Index-Datensatz geändert wird, wird die alte Version in das Undo-Protokoll geschrieben. Diese versteckte Spalte entspricht einem Zeiger, über den die Informationen vor der Änderung des Datensatzes gefunden werden können.

Wenn der Name eines Datensatzes von Diao Chan in Wang Zhaojun und Xi Shi geändert wird, gibt es die folgenden Datensätze. Mehrere Datensätze bilden eine Versionskette.

Um zu bestimmen, welche Version in der Versionskette für die aktuelle Transaktion sichtbar ist, hat MySQL das Konzept von ReadView entwickelt . Die 4 wichtigen Inhalte sind wie folgt

• m_ids : Eine Liste der aktiven Transaktions-IDs im aktuellen System beim Generieren einer ReadView
• min_trx_id : Beim Generieren eines ReadView die kleinste derzeit im System aktive Transaktions-ID, d. h. der Mindestwert in m_ids
• max_trx_id : Der Transaktions-ID-Wert, den das System der nächsten Transaktion beim Generieren einer ReadView zuweisen soll
• creator_trx_id : Die Transaktions-ID der Transaktion, die den ReadView generiert hat

Wenn Datensätze in einer Tabelle geändert werden oder Einfüge-, Lösch- oder Aktualisierungsanweisungen ausgeführt werden, wird der Transaktion eine eindeutige Transaktions-ID zugewiesen. Andernfalls ist der Standardwert der Transaktions-ID einer Transaktion 0.

max_trx_id ist nicht der Maximalwert in m_ids. Transaktions-IDs werden inkrementell zugewiesen. Beispielsweise gibt es drei Transaktionen mit den Transaktions-IDs 1, 2 und 3. Später wird die Transaktion mit der Transaktions-ID 3 festgeschrieben. Wenn eine neue Transaktion ReadView generiert, umfasst der Wert von m_ids 1 und 2, der Wert von min_trx_id ist 1 und der Wert von max_trx_id ist 4.

Der Ausführungsprozess ist wie folgt:

• Wenn die trx_id der aufgerufenen Version = creator_id ist, bedeutet dies, dass die aktuelle Transaktion auf den Datensatz zugreift, den sie geändert hat, sodass auf die Version von der aktuellen Transaktion zugegriffen werden kann.
• Wenn die trx_id der aufgerufenen Version kleiner als die min_trx_id ist, bedeutet dies, dass die Transaktion, die die Version generiert hat, festgeschrieben wurde, bevor die aktuelle Transaktion die ReadView generiert hat, sodass auf die Version von der aktuellen Transaktion zugegriffen werden kann.
• Die trx_id der aufgerufenen Version ist größer oder gleich max_trx_id, was darauf hinweist, dass die Transaktion, die diese Version generiert hat, geöffnet wurde, nachdem die aktuelle Transaktion ReadView generiert hat, und auf diese Version von der aktuellen Transaktion nicht zugegriffen werden kann
• Befindet sich die trx_id der aufgerufenen Version in der m_ids-Liste?
• 4.1 Ja, beim Erstellen der ReadView ist diese Version noch aktiv und kann nicht aufgerufen werden. Folgen Sie der Versionskette, um die nächste Version der Daten zu finden, und führen Sie die obigen Schritte weiter aus, um die Sichtbarkeit zu bestimmen. Wenn die letzte Version nicht sichtbar ist, bedeutet dies, dass der Datensatz für die aktuelle Transaktion vollständig unsichtbar ist.
• 4.2 Nein, wenn die ReadView erstellt wird, wurde die Transaktion, die die Version generiert hat, festgeschrieben und auf die Version kann zugegriffen werden
  

OK, jetzt kennen wir die Regeln zum Erhalten der Versionssichtbarkeit, aber wie implementieren wir das festgeschriebene Lesen und das wiederholbare Lesen?

Tatsächlich ist es sehr einfach, das heißt, der Zeitpunkt der Generierung von ReadView ist unterschiedlich

Erstellen Sie beispielsweise zunächst die folgende Tabelle

CREATE TABLE `Mädchen` (
  `id` int(11) NICHT NULL,
  `Name` varchar(255),
  `Alter` int(11),
  PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Lesen Sie Commitment

Read Committed (Read Committed), vor jedem Lesevorgang wird ein ReadView generiert

Unten sehen Sie den Vorgang der Ausführung von drei Transaktionen. Eine Linie stellt einen Zeitpunkt dar.

Analysieren Sie zunächst den Ausführungsprozess von select zum Zeitpunkt 5

• Im System werden zwei Transaktionen mit den Transaktions-IDs 100 und 200 ausgeführt.
• Beim Ausführen der Select-Anweisung wird ein ReadView generiert, mids=[100,200], min_trx_id=100, max_trx_id=201, creator_trx_id=0 (die Select-Transaktion führt keine Änderungsoperation aus und die Transaktions-ID ist standardmäßig auf 0 eingestellt)
• Die Namensspalte der neuesten Version ist Xishi. Der trx_id-Wert dieser Version ist 100. In der Mids-Liste erfüllt sie nicht die Sichtbarkeitsanforderungen. Springe gemäß roll_pointer zur nächsten Version
• Die nächste Version mit dem Namen ist Lie Wang Zhaojun. Der trx_id-Wert dieser Version ist 100, was ebenfalls in der Mids-Liste steht. Daher erfüllt sie die Anforderungen nicht. Weiter zur nächsten Version springen
• Die Namensspalte der nächsten Version ist Diao Chan. Der trx_id-Wert dieser Version ist 10, was kleiner ist als min_trx_id. Daher ist der am Ende zurückgegebene Namenswert Diao Chan.
  

Lassen Sie uns den Ausführungsprozess von select zum Zeitpunkt 8 noch einmal analysieren

• Im System wird eine Transaktion mit der Transaktions-ID 200 ausgeführt (Transaktion mit der Transaktions-ID 100 wurde festgeschrieben)
• Beim Ausführen der Select-Anweisung wird ein ReadView generiert, mids=[200], min_trx_id=200, max_trx_id=201, creator_trx_id=0
• Die Namensspalte der neuesten Version ist Yang Yuhuan. Der trx_id-Wert dieser Version ist 200. In der Mids-Liste erfüllt sie nicht die Sichtbarkeitsanforderungen. Springe gemäß roll_pointer zur nächsten Version
• Die Namensspalte der nächsten Version ist Xishi. Der trx_id-Wert dieser Version ist 100, was kleiner ist als min_trx_id. Daher ist der am Ende zurückgegebene Namenswert Xishi.
  

Wenn die Transaktion mit der Transaktions-ID 200 festgeschrieben ist, lautet die durch die Abfrage erhaltene Namensspalte Yang Yuhuan.

Wiederholbares Lesen

Repeatable Read generiert beim ersten Lesen von Daten eine ReadView.

Wiederholbares Lesen generiert ReadView nur beim ersten Lesen von Daten, sodass jedes Mal dieselbe Version gelesen wird, d. h. der Namenswert ist immer Diao Chan. Der spezifische Prozess wurde oben zweimal demonstriert, daher werde ich ihn hier nicht wiederholen. Ich glaube, Sie werden ihn selbst analysieren.

Referenzblog

[1] https://souche.yuque.com/bggh1p/8961260/gyzlaf
[2] https://zhuanlan.zhihu.com/p/35477890

Dies ist das Ende dieses Artikels über die Isolierung von MySQL-Transaktionen. Weitere Informationen zur Isolierung von MySQL-Transaktionen 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!