Detaillierte Erläuterung der vier Transaktionsisolationsebenen in MySQL

Die Testumgebung dieses Experiments: Windows 10+cmd+MySQL5.6.36+InnoDB

1. Grundelemente von Transaktionen (ACID)

1. Atomarität: Nach dem Start einer Transaktion müssen alle Vorgänge abgeschlossen sein oder überhaupt nicht ausgeführt werden. Es ist unmöglich, dass die Transaktion mittendrin stecken bleibt. Tritt während der Ausführung einer Transaktion ein Fehler auf, wird der Vorgang auf den Zustand vor dem Beginn der Transaktion zurückgesetzt und alle Vorgänge werden so ausgeführt, als wäre nichts geschehen. Mit anderen Worten: Angelegenheiten sind ein unteilbares Ganzes, genau wie die Atome, die wir in der Chemie kennengelernt haben und die die Grundeinheiten der Materie darstellen.

2. Konsistenz: Die Integritätsbeschränkungen der Datenbank werden vor und nach dem Start und Ende der Transaktion nicht verletzt. Wenn A beispielsweise Geld an B überweist, ist es für A nicht möglich, das Geld abzuziehen, B erhält es jedoch nicht.

3. Isolation: Nur eine Transaktion darf gleichzeitig dieselben Daten anfordern und es gibt keine Interferenzen zwischen verschiedenen Transaktionen. Wenn A beispielsweise Geld von einer Bankkarte abhebt, kann B kein Geld auf diese Karte überweisen, bevor A den Abhebungsvorgang abgeschlossen hat.

4. Dauerhaftigkeit: Nachdem die Transaktion abgeschlossen ist, werden alle durch die Transaktion an der Datenbank vorgenommenen Aktualisierungen in der Datenbank gespeichert und können nicht zurückgesetzt werden.

Zusammenfassung: Atomarität ist die Grundlage der Transaktionsisolierung. Isolation und Persistenz sind Mittel. Das ultimative Ziel ist die Aufrechterhaltung der Datenkonsistenz.

2. Parallelitätsprobleme bei Transaktionen

1. Dirty Read: Transaktion A liest die von Transaktion B aktualisierten Daten und dann führt B den Vorgang zurück, sodass die von A gelesenen Daten schmutzige Daten sind

2. Nicht wiederholbares Lesen: Transaktion A liest dieselben Daten mehrmals. Während Transaktion A mehrere Daten liest, aktualisiert und speichert Transaktion B die Daten. Dies führt zu inkonsistenten Ergebnissen, wenn Transaktion A dieselben Daten mehrere Male liest.

3. Phantomlesen: Systemadministrator A ändert die Noten aller Studenten in der Datenbank von bestimmten Punktzahlen in ABCDE-Noten, aber Systemadministrator B fügt zu diesem Zeitpunkt einen Datensatz mit einer bestimmten Punktzahl ein. Wenn Systemadministrator A die Änderung abgeschlossen hat, stellt er fest, dass immer noch ein Datensatz vorhanden ist, der nicht geändert wurde, als ob eine Illusion aufgetreten wäre. Dies wird als Phantomlesen bezeichnet.

Zusammenfassung: Nicht wiederholbare Lesevorgänge und Phantom-Lesevorgänge können leicht verwechselt werden. Bei nicht wiederholbaren Lesevorgängen stehen Änderungen im Mittelpunkt, während bei Phantom-Lesevorgängen Hinzufügungen oder Löschungen im Mittelpunkt stehen. Um das Problem nicht wiederholbarer Lesevorgänge zu lösen, müssen Sie nur die Zeilen sperren, die die Bedingungen erfüllen. Um das Problem von Phantom-Lesevorgängen zu lösen, müssen Sie die Tabelle sperren.

4. Verwenden Sie Beispiele, um die Situation jeder Isolationsstufe zu veranschaulichen

1. Lesen Sie nicht festgeschrieben:

(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf „Nicht festgeschrieben lesen“ ein. Fragen Sie den Anfangswert der Kontotabelle ab:

(2) Bevor die Transaktion des Kunden A festgeschrieben wird, wird ein weiterer Kunde B geöffnet, um die Kontotabelle zu aktualisieren:

(3) Obwohl die Transaktion von Client B noch nicht festgeschrieben wurde, kann Client A die von B aktualisierten Daten abfragen:

(4) Wenn die Transaktion von Client B aus irgendeinem Grund zurückgesetzt wird, werden alle Vorgänge rückgängig gemacht und die von Client A abgefragten Daten sind tatsächlich fehlerhafte Daten:

(5) Wenn Client A die Update-Anweisung „update account set balance = balance - 50“ ausführt, wobei ID = 1 ist, wird Lileis Kontostand nicht 350, sondern 400. Ist das nicht seltsam? Die Datenkonsistenz ist kein Problem. Wenn Sie das glauben, sind Sie zu naiv. In der Anwendung verwenden wir 400-50=350 und wissen nicht, dass andere Sitzungen zurückgesetzt wurden. Um dieses Problem zu lösen, können Sie die Isolationsstufe „Read Committed“ verwenden.

2. Eingereichte Beiträge lesen

(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf „Commit lesen“ ein. Fragen Sie den Anfangswert der Kontotabelle ab:

(2) Bevor die Transaktion des Kunden A festgeschrieben wird, wird ein weiterer Kunde B geöffnet, um die Kontotabelle zu aktualisieren:

(3) Zu diesem Zeitpunkt wurde die Transaktion von Client B noch nicht festgeschrieben, und Client A kann die von B aktualisierten Daten nicht abfragen, wodurch das Dirty-Read-Problem gelöst wird:

(4) Übermittlung der Transaktion durch Kunde B

(5) Client A führt dieselbe Abfrage wie im vorherigen Schritt aus, aber das Ergebnis stimmt nicht mit dem des vorherigen Schritts überein, was bedeutet, dass ein nicht wiederholbares Leseproblem auftritt. Angenommen, wir befinden uns in der Anwendung in der Sitzung von Client A und stellen fest, dass der Saldo von lilei 450 beträgt, aber andere Transaktionen den Saldowert von lilei auf 400 ändern. Wir wissen das nicht. Wenn wir den Wert von 450 verwenden, um andere Vorgänge auszuführen, treten Probleme auf. Diese Wahrscheinlichkeit ist jedoch sehr gering. Um dieses Problem zu vermeiden, können wir die wiederholbare Leseisolationsebene verwenden.

3. Wiederholbares Lesen

(1) Öffnen Sie einen Client A, stellen Sie den aktuellen Transaktionsmodus auf wiederholbares Lesen ein und fragen Sie den Anfangswert der Kontotabelle ab:

(2) Bevor die Transaktion von Client A festgeschrieben wird, wird ein anderer Client B geöffnet, die Kontotabelle wird aktualisiert und festgeschrieben. Die Transaktion von Client B kann tatsächlich die von der Transaktion von Client A abgefragten Zeilen ändern. Mit anderen Worten, MySQLs wiederholbares Lesen sperrt die von der Transaktion abgefragten Zeilen nicht. Das übersteigt meine Erwartungen. Gemäß dem SQL-Standard müssen Lese- und Schreibvorgänge Zeilen sperren, wenn die Transaktionsisolationsebene wiederholbares Lesen ist. MySQL hat sie nicht gesperrt. Oh mein Gott. Achten Sie bei der Anwendung darauf, die Zeile zu sperren, da Sie sonst den Saldo von 400 aus Schritt (1) als Zwischenwert für die Durchführung weiterer Operationen verwenden.

(3) Führen Sie die Abfrage aus Schritt (1) auf Client A aus:

(4) Führen Sie Schritt (1) aus. Lileis Kontostand beträgt immer noch 400, was mit dem Abfrageergebnis in Schritt (1) übereinstimmt. Es liegt kein nicht wiederholbares Leseproblem vor. Führen Sie dann update balance = balance - 50 aus, wobei id = 1 ist. Der Kontostand wird nicht 400-50=350. Lileis Kontostandwert wird in Schritt (2) mit 350 berechnet, beträgt also 300. Die Datenkonsistenz wird nicht zerstört. Das ist ein bisschen magisch. Vielleicht ist es eine Funktion von MySQL.

mysql> wähle * vom Konto aus;
+------+--------+---------+
| ID | Name | Guthaben |
+------+--------+---------+
| 1 | lilei | 400 |
| 2 | Hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
Zeilen im Set (0,00 Sek.)

mysql> Konto aktualisieren, Guthaben festlegen = Guthaben – 50, wobei ID = 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> wähle * vom Konto aus;
+------+--------+---------+
| ID | Name | Guthaben |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | Hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
Zeilen im Set (0,00 Sek.)

(5) Starten Sie eine Transaktion auf Client A und fragen Sie den Anfangswert der Kontotabelle ab

mysql> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle * vom Konto aus;
+------+--------+---------+
| ID | Name | Guthaben |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | Hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
Zeilen im Set (0,00 Sek.)

(6) Öffnen Sie eine Transaktion auf Client B, fügen Sie einen neuen Dateneintrag mit dem Saldofeldwert 600 hinzu und committen Sie

mysql> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> in Kontowerte einfügen (4, „Lily“, 600);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

(7) Wenn Client A die Summe der Salden berechnet, ergibt sich ein Wert von 300+16000+2400=18700. Der Wert von Client B wird dabei nicht berücksichtigt. Nachdem Client A die Anfrage übermittelt hat, wird die Summe der Salden erneut berechnet und ergibt überraschenderweise 19300. Dies liegt daran, dass die 600 von Client B berücksichtigt werden. Aus der Sicht des Clients kann der Client Client B nicht sehen und wird denken, dass es sich um ein Luftschloss mit 600 zusätzlichen Dollar handelt. Dies ist ein Phantom-Read. Aus der Sicht des Entwicklers wird die Datenkonsistenz nicht zerstört. In der Anwendung kann unser Code jedoch 18700 an den Benutzer senden. Wenn Sie diese Situation mit geringer Wahrscheinlichkeit vermeiden möchten, müssen Sie die unten eingeführte Transaktionsisolationsebene „Serialisierung“ übernehmen.

mysql> wähle Summe (Guthaben) vom Konto aus;
+--------------+
| Summe(Saldo) |
+--------------+
| 18700 |
+--------------+
1 Zeile im Satz (0,00 Sek.)

mysql> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle Summe (Guthaben) vom Konto aus;
+--------------+
| Summe(Saldo) |
+--------------+
| 19300 |
+--------------+
1 Zeile im Satz (0,00 Sek.)

4. Serialisierung

(1) Öffnen Sie einen Client A, setzen Sie den aktuellen Transaktionsmodus auf serialisierbar und fragen Sie den Anfangswert der Kontotabelle ab:

mysql> setze serialisierbare Isolationsebene für Sitzungstransaktionen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle * vom Konto aus;
+------+--------+---------+
| ID | Name | Guthaben |
+------+--------+---------+
| 1 | lilei | 10000 |
| 2 | hanmei | 10000 |
| 3 | Lucy | 10000 |
| 4 | Lilie | 10000 |
+------+--------+---------+
Zeilen im Set (0,00 Sek.)

(2) Öffnen Sie einen Client B und setzen Sie den aktuellen Transaktionsmodus auf serialisierbar. Beim Einfügen eines Datensatzes tritt ein Fehler auf. Die Tabelle wird gesperrt und das Einfügen schlägt fehl. Wenn die Transaktionsisolationsebene in MySQL serialisierbar ist, wird die Tabelle gesperrt, sodass keine Phantom-Lesevorgänge stattfinden. Diese Isolationsebene weist eine extrem geringe Parallelität auf. Oft belegt eine Transaktion eine Tabelle und Tausende anderer Transaktionen können nur hilflos zusehen. Sie müssen warten, bis die Transaktion abgeschlossen und festgeschrieben ist, bevor sie verwendet werden kann. Diese Isolationsebene wird in der Entwicklung selten verwendet.

mysql> setze serialisierbare Isolationsebene für Sitzungstransaktionen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Transaktion starten;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> in Kontowerte einfügen (5, „tom“, 0);
FEHLER 1205 (HY000): Wartezeit für Sperre überschritten; versuchen Sie, die Transaktion neu zu starten.

Auffüllen:

1. Die in der SQL-Spezifikation angegebenen Standards können von Datenbank zu Datenbank unterschiedlich sein.

2. Die Standardtransaktionsisolationsebene in MySQL ist wiederholbares Lesen, wodurch die gelesenen Zeilen nicht gesperrt werden.

3. Wenn die Transaktionsisolationsstufe serialisiert ist, wird durch das Lesen von Daten die gesamte Tabelle gesperrt

4. Wenn Sie diesen Artikel aus der Perspektive eines Entwicklers lesen, haben Sie möglicherweise das Gefühl, dass es bei nicht wiederholbaren Lesevorgängen und Phantomlesevorgängen kein logisches Problem gibt und die Daten am Ende immer noch konsistent sind. Aus der Perspektive der Benutzer können sie jedoch normalerweise nur eine Transaktion sehen (sie können nur Client A sehen und wissen nichts von der Existenz von Client B, dem Undercover) und berücksichtigen das Phänomen der gleichzeitigen Transaktionsausführung nicht. Sobald dieselben Daten mehrmals mit unterschiedlichen Ergebnissen gelesen werden oder neue Datensätze aus dem Nichts auftauchen, haben sie möglicherweise Zweifel. Dies ist ein Problem der Benutzererfahrung.

5. Wenn eine Transaktion in MySQL ausgeführt wird, weist das Endergebnis keine Probleme mit der Datenkonsistenz auf, da MySQL bei der Ausführung einer Operation während einer Transaktion möglicherweise nicht die Zwischenergebnisse der vorherigen Operation verwendet. Es verarbeitet sie basierend auf der tatsächlichen Situation anderer gleichzeitiger Transaktionen. Das scheint unlogisch, stellt jedoch die Datenkonsistenz sicher. Wenn jedoch eine Transaktion in einer Anwendung ausgeführt wird, wird das Ergebnis einer Operation von der nächsten Operation verwendet und es werden andere Berechnungen durchgeführt. Dabei müssen wir vorsichtig sein. Wir sollten Zeilen während wiederholbarer Lesevorgänge sperren und Tabellen während der Serialisierung sperren, da sonst die Konsistenz der Daten zerstört wird.

6. Wenn eine Transaktion in MySQL ausgeführt wird, verarbeitet MySQL sie umfassend basierend auf der tatsächlichen Situation jeder Transaktion, sodass die Konsistenz der Daten nicht zerstört wird. Das Anwendungsprogramm spielt jedoch gemäß der logischen Routine, die nicht so intelligent ist wie MySQL, sodass Datenkonsistenzprobleme unvermeidlich sind.

7. Je höher die Isolationsstufe, desto besser kann die Datenintegrität und -konsistenz sichergestellt werden, desto größer sind jedoch die Auswirkungen auf die Parallelitätsleistung. Sie können nicht beides haben. Für die meisten Anwendungen wird empfohlen, die Isolationsebene des Datenbanksystems auf „Read Committed“ einzustellen. Dadurch werden Dirty Reads vermieden und die Parallelitätsleistung verbessert. Obwohl es zu Parallelitätsproblemen wie nicht wiederholbaren Lesevorgängen und Phantomlesevorgängen kommen kann, kann die Anwendung in Einzelfällen, in denen solche Probleme auftreten können, pessimistische oder optimistische Sperren verwenden, um sie zu kontrollieren.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.