Detaillierte Einführung in die vier Transaktionsisolationsstufen in MySQL (Bild und Text)

In diesem Artikel werden hauptsächlich die relevanten Informationen zu den vier Transaktionsisolationsstufen von MySQL im Detail vorgestellt, die einen gewissen Referenzwert haben. Interessierte Freunde können sich auf

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

1. Grundelemente der Transaktion (ACID)

　1. Atomizität: alle Vorgänge nach dem Start der Transaktion, entweder alles tun oder nicht Mach es überhaupt und es ist unmöglich, in der Mitte stecken zu bleiben. Wenn während der Transaktionsausführung ein Fehler auftritt, wird die Transaktion auf den Zustand vor Beginn der Transaktion zurückgesetzt und alle Vorgänge werden so ausgeführt, als ob sie nicht stattgefunden hätten. Das heißt, die Materie ist ein unteilbares Ganzes, genau wie die in der Chemie erlernten Atome, die die Grundeinheiten der Materie darstellen.

2. Konsistenz: Vor und nach Beginn und Ende der Transaktion wurde die Integrität der Datenbank Einschränkung nicht zerstört. Wenn A beispielsweise Geld an B überweist, ist es A unmöglich, das Geld abzuziehen, B es jedoch nicht erhält.

3. Isolation: Gleichzeitig darf nur eine Transaktion 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 der Abhebungsvorgang von A abgeschlossen ist.

4. Haltbarkeit: Nach Abschluss der Transaktion werden alle Aktualisierungen der Datenbank durch die Transaktion in der Datenbank gespeichert und können nicht zurückgesetzt werden.

Zusammenfassung: Atomarität ist die Grundlage der Transaktionsisolation, Isolation und Haltbarkeit sind Mittel, und das ultimative Ziel ist die Aufrechterhaltung der Datenkonsistenz.

2. Probleme mit der Parallelität von Transaktionen

1. Dirty Read: Transaktion A liest die von Transaktion B aktualisierten Daten, und dann macht B den Vorgang rückgängig, dann liest A Bei den empfangenen Daten handelt es sich um schmutzige Daten

2. Nicht wiederholbares Lesen: Transaktion A liest dieselben Daten mehrmals, und Transaktion B aktualisiert und übermittelt die Daten während der mehrfachen Lesevorgänge von Transaktion A, was zu einer Transaktion führt, wenn A liest Wenn dieselben Daten mehrmals verwendet werden, sind die Ergebnisse inkonsistent.

3. Phantomlesung: Systemadministrator A hat die Ergebnisse aller Schüler in der Datenbank von bestimmten Ergebnissen in ABCDE-Noten geändert, aber Systemadministrator B hat zu diesem Zeitpunkt einen Datensatz mit bestimmten Ergebnissen eingefügt Nachdem die Änderung abgeschlossen war, stellte Mitglied A fest, dass es noch einen Datensatz gab, der nicht geändert worden war. Es war, als hätte er eine Halluzination gehabt.

Zusammenfassung: Nicht wiederholbares Lesen und Phantomlesen können leicht verwechselt werden. Beim nicht wiederholbaren Lesen liegt der Schwerpunkt auf der Änderung, während sich das Phantomlesen auf das Hinzufügen oder Löschen konzentriert. Um das Problem nicht wiederholbarer Lesevorgänge zu lösen, müssen Sie nur Zeilen sperren, die die Bedingungen erfüllen. Um das Problem der Phantomlesevorgänge zu lösen, müssen Sie die Tabelle sperren

3. MySQL-Transaktionsisolationsstufe

Die Standard-Transaktionsisolationsstufe von MySQL ist wiederholbar-lesbar

4 Veranschaulichen Sie jede Isolationsstufe

1. Nicht festgeschrieben lesen:

(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf nicht festgeschriebenes Lesen ein ), Abfrage der -Tabelle. Anfangswert des Kontos:

(2) Bevor die Transaktion von Client A festgeschrieben wird, öffnen Sie einen anderen Client B und aktualisieren Sie das Tabellenkonto :

(3) Obwohl die Transaktion von Kunde B noch nicht übermittelt wurde, kann Kunde A zu diesem Zeitpunkt die aktualisierten Daten von B abfragen:

(4) Sobald 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 schmutzige Daten:

(5) Führen Sie den Update-Kontoauszug „Update Account Set Balance = Balance - 50“ aus, wobei die ID = 1 auf Client A. Lileis Kontostand nicht 350, sondern tatsächlich 400 beträgt. Ist das nicht seltsam? Datenkonsistenz, die ich nicht gefragt habe , Wenn Sie das glauben, sind Sie zu naiv. Wir werden 400-50 = 350 verwenden, und wir wissen nicht, dass andere Sitzungen zurückgesetzt wurden. Um dieses Problem zu lösen, können Sie Folgendes lesen: übermittelt Isolationsstufe

2. Festgeschrieben lesen

(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf festgeschrieben lesen (nicht senden) ein lesen), den Anfangswert des Tabellenkontos abfragen:

(2) Bevor die Transaktion von Client A festgeschrieben wird, öffnen Sie einen anderen Client B und aktualisieren Sie das Tabellenkonto:

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

(4) Transaktionseinreichung von Client B

(5) Client A führt dieselbe Abfrage wie im vorherigen Schritt aus, und das Ergebnis ist Der vorherige Schritt ist inkonsistent, was zu einem nicht wiederholbaren Leseproblem führt. Unter der Annahme, dass wir uns in der Sitzung von Client A befinden, fragen wir ab, dass der Kontostand von lilei 450 beträgt, andere Transaktionen jedoch den Kontostand von lilei auf 400 ändern. Wir wissen nicht, dass es ein Problem geben wird, wenn Sie den Wert 450 für andere Vorgänge verwenden, aber die Wahrscheinlichkeit ist sehr gering. Um dieses Problem zu vermeiden, können Sie die wiederholbare Leseisolationsstufe

3. Wiederholbares Lesen

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

(2) Bevor die Transaktion von Client A übermittelt wird, öffnen Sie einen anderen Client B, aktualisieren Sie das Tabellenkonto und senden Sie es. Die Transaktion von Client B kann tatsächlich die Transaktionsabfrage von Client A ändern. Die erreichten Zeilen, d. h. Das wiederholbare Lesen von MySQL sperrt die von der Transaktion abgefragten Zeilen nicht. Wenn die Transaktionsisolationsstufe im SQL-Standard wiederholbar ist, müssen die Zeilen von MySQL gesperrt werden. also ging ich raus. Achten Sie darauf, Zeilen in der Anwendung zu sperren, da Sie sonst den Saldo von Lilie von 400 in Schritt (1) als Zwischenwert für andere Vorgänge verwenden

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

(4) Führen Sie Schritt (1) aus, Lileis Guthaben beträgt immer noch 400, was mit dem Abfrageergebnis von Schritt ( übereinstimmt) 1) Es gibt kein nicht wiederholbares Leseproblem. Führen Sie dann die Aktualisierung von Balance = Balance - 50 aus, wobei die ID = 1 ist es ist 300. Die Konsistenz der Daten wurde nicht zerstört. Vielleicht ist es eine Funktion von MySQL

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  400 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(5) Starten Sie a Transaktion auf Client A, fragen Sie den Anfangswert des Tabellenkontos ab

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(6) Starten Sie die Transaktion auf Client B, fügen Sie ein neues Datenelement hinzu, in dem die Der Wert des Saldofelds ist 600, und senden Sie

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(4,'lily',600);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

(7) Berechnen Sie die Summe des Saldos auf Client A, der Wert ist 300+16000+2400=18700, der Der Wert von Kunde B ist nicht enthalten. Nachdem Kunde A ihn eingereicht hat, wurde der Restbetrag berechnet und es stellte sich heraus, dass er 19300 betrug. Dies liegt daran, dass die 600 von Kunde B in die Berechnung einbezogen wurden. Aus Kundensicht kann der Kunde dies nicht sehen Kunde B, und er wird denken, dass der Kuchen verloren ist, 600 Yuan mehr. Aus Sicht des Entwicklers ist die Konsistenz der Daten nicht zerstört. In der Anwendung kann unser Code jedoch 18700 an den Benutzer senden. Wenn Sie diese Situation mit geringer Wahrscheinlichkeit vermeiden müssen, müssen Sie die unten eingeführte Transaktionsisolationsstufe „Serialisierung“ übernehmen

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 18700 |
+--------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 19300 |
+--------------+
1 row in set (0.00 sec)

4. Serialisierung

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

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 10000 |
| 2 | hanmei | 10000 |
| 3 | lucy | 10000 |
| 4 | lily | 10000 |
+------+--------+---------+
rows in set (0.00 sec)

(2) Öffnen Sie einen Client B und setzen Sie den aktuellen Transaktionsmodus auf „Serialisierbar“. Beim Einfügen eines Datensatzes wird ein Fehler gemeldet. Die Transaktion in MySQL ist gesperrt Serialisierbar, die Tabelle wird gesperrt, sodass keine Phantom-Lesevorgänge auftreten. Oftmals belegt eine Transaktion eine Tabelle und kann nur nach der Übermittlung verwendet werden wird in der Entwicklung selten verwendet.

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Ergänzende Informationen: Unterschiede

2. Wenn die Standard-Transaktionsisolationsstufe in MySQL wiederholbares Lesen ist, wird das Lesen Zeilen werden nicht gesperrt

3. Wenn die Transaktionsisolationsstufe Serialisierung ist, werden die Daten gelesen. Sperrt die gesamte Tabelle

4. Beim Lesen dieses Artikels aus der Sicht eines Entwicklers: Möglicherweise haben Sie das Gefühl, dass nicht wiederholbares Lesen und Phantomlesen kein logisches Problem darstellen und die endgültigen Daten immer noch konsistent sind. Ja, aber aus Sicht des Benutzers kann er normalerweise nur eine Transaktion sehen (nur Client A, ohne die Existenz dieser zu kennen). Undercover-Kunde B) berücksichtigt das Phänomen der gleichzeitigen Ausführung von Transaktionen nicht, wenn die Daten mehrmals mit unterschiedlichen Ergebnissen gelesen werden oder neue Datensätze aus dem Nichts auftauchen ein Problem mit der Benutzererfahrung.

5. Wenn eine Transaktion in MySQL ausgeführt wird, besteht für das Endergebnis kein Problem der Datenkonsistenz, da MySQL bei der Ausführung einer Operation nicht unbedingt das Zwischenergebnis der vorherigen Operation verwendet andere Die tatsächliche Situation gleichzeitiger Transaktionen wird verwendet, um damit umzugehen. Es scheint unlogisch, aber es stellt die Datenkonsistenz sicher, aber wenn die Transaktion in der Anwendung ausgeführt wird, wird das Ergebnis einer Operation von der nächsten Operation verwendet durchgeführt werden. Deshalb müssen wir beim wiederholbaren Lesen vorsichtig sein und Tabellen während der Serialisierung sperren, sonst wird die Konsistenz der Daten zerstört.

6. Wenn Transaktionen in MySQL ausgeführt werden, verarbeitet MySQL jede Transaktion umfassend entsprechend der tatsächlichen Situation, was dazu führt, dass die Konsistenz der Daten nicht zerstört wird, die Anwendung jedoch logischen Routinen folgt, was nicht der Fall ist So intelligent wie MySQL, werden unweigerlich Probleme mit der Datenkonsistenz auftreten.

7. Je höher die Isolationsstufe, desto vollständiger und konsistenter können die Daten sein, aber auch die Auswirkungen auf die Parallelitätsleistung sind größer. Bei den meisten Anwendungen können Sie der Isolationsstufe des Datenbanksystems Vorrang auf „Read Committed“ (Lesen festgeschrieben) einräumen, wodurch Dirty Reads vermieden werden können und eine bessere Parallelitätsleistung erzielt wird. Obwohl dies zu Parallelitätsproblemen wie nicht wiederholbaren Lesevorgängen und Phantom-Lesevorgängen führt, kann die Anwendung in einzelnen Situationen, in denen solche Probleme auftreten können, pessimistische Sperren oder optimistische Sperren verwenden, um diese zu steuern.

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in die vier Transaktionsisolationsstufen in MySQL (Bild und Text). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!