Beispiel-Tutorial zur MySQL-Transaktionsisolationsstufe

Die Testumgebung für das Experiment dieses Artikels: Windows 10+cmd+MySQL5.6.36+InnoDB

1. Grundelemente von Transaktionen (ACID)

1. Atomarität: Nach der Transaktion Wenn der Vorgang beginnt, sind alle Vorgänge entweder abgeschlossen oder nicht abgeschlossen, und es ist unmöglich, in der Mitte zu stagnieren. 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 werden die Integritätsbeschränkungen der Datenbank nicht verletzt. 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 die anfordern Bei gleichen Transaktionsdaten stören sich unterschiedliche Transaktionen nicht gegenseitig. 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 durchgeführt Wird in der Datenbank gespeichert und kann nicht zurückgesetzt werden.

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

2. Parallelitätsprobleme von Transaktionen

1. Dirty Read: Transaktion A liest die von Transaktion B aktualisierten Daten, und dann macht B den Vorgang rückgängig, dann sind die von A gelesenen Daten Dirty Data

2. Nicht wiederholbares Lesen: Transaktion A liest dieselben Daten mehrmals und Transaktion B liest dieselben Daten mehrmals in Transaktion A . Die Daten wurden aktualisiert und übermittelt, was zu inkonsistenten Ergebnissen führte, wenn Transaktion A dieselben Daten mehrmals las.

3. Phantomlesen: Systemadministrator A ändert die Ergebnisse aller Schüler in der Datenbank von bestimmten Ergebnissen Es ist ABCDE-Ebene, aber Systemadministrator B hat zu diesem Zeitpunkt einen Datensatz mit einer bestimmten Punktzahl eingefügt. Als Systemadministrator A die Änderung abschloss, stellte er fest, dass noch ein Datensatz vorhanden war, der nicht geändert wurde sogenannte Phantomlesung.

Zusammenfassung: Nicht wiederholbares Lesen und Phantomlesen sind leicht zu verwechseln, nicht wiederholbares Lesen konzentriert sich auf

Ändern, Phantomlesung konzentriert sich auf Hinzufügen oder Löschen. Um das Problem des nicht wiederholbaren Lesens zu lösen, müssen Sie nur Zeilen sperren, die die Bedingungen erfüllen, und um das Problem des Phantomlesens zu lösen, müssen Sie die Tabelle sperren 3. MySQL-Transaktionsisolationsstufe

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MySQLs standardmäßige Transaktionsisolationsstufe ist wiederholbares Lesen

4. Veranschaulichen Sie jede Isolationsstufe anhand von Beispielen

1. Lesen Sie unverbindlich:

(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf nicht festgeschriebenes Lesen (nicht festgeschriebenes Lesen) ein. Fragen Sie den Anfangswert des Tabellenkontos ab:

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

(3) Zu diesem Zeitpunkt kann Client A, obwohl die Transaktion von Client B noch nicht übermittelt wurde, die aktualisierten Daten von B abfragen:

(4) Sobald die Transaktion von Client B zurückgesetzt wurde Aus irgendeinem Grund werden alle Vorgänge rückgängig gemacht, und die von Client A abgefragten Daten sind tatsächlich schmutzige Daten:

(5) Führen Sie die Aktualisierungsabrechnung aus. Aktualisieren Sie den Kontostand = Saldo - 50, wobei id =1 auf dem Kontostand von Kunde A. lilei nicht 350, sondern tatsächlich 400 geworden ist. Ist es nicht seltsam? Die Konsistenz der Daten wurde nicht gefragt. Wenn Sie das denken, sind Sie zu naiv. In der Anwendung verwenden wir 400-50=350 und wissen nicht, dass andere Sitzungen zurückgesetzt werden. Um dieses Problem zu lösen, können Sie die Lese-Committed-Isolationsstufe

2. Gesendet lesen

(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf „Festgeschrieben“ (nicht festgeschriebenes Lesen) ein, fragen Sie den Anfangswert ab Tabellenkonto:

(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) Transaktionsübermittlung von Client B

(5) Client A führt dasselbe aus wie im vorherigen Schritt. Das Ergebnis der Abfrage stimmt nicht mit dem vorherigen Schritt überein, was dazu führt Ein nicht wiederholbares Leseproblem. Unter der Annahme, dass wir uns in der Sitzung von Client A befinden, stellt die Abfrage fest, dass der Kontostand von Lilei 450 beträgt, der Wert von Lilei jedoch auf 400 geändert wird Ich weiß nicht, dass es ein Problem geben wird, wenn wir 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 und stellen Sie den aktuellen Transaktionsmodus auf wiederholbares Lesen ein, fragen Sie den Anfangswert des Tabellenkontos ab :

(2) Bevor die Transaktion von Kunde A übermittelt wird, öffnen Sie einen anderen Kunden B, aktualisieren Sie das Tabellenkonto und senden Sie es ab. Die Transaktion von Kunde B kann die tatsächlich ändern Das heißt, dass der wiederholbare Lesevorgang von MySQL die von der Transaktion abgefragten Zeilen nicht sperrt. Die Transaktionsisolationsstufe im SQL-Standard ist beim wiederholbaren Lesen erforderlich und Schreibvorgänge, aber MySQL hat keine Sperren. Achten Sie darauf, Zeilen in der Anwendung zu sperren, da Sie andernfalls Lileis Saldo von 400 in Schritt (1) als Zwischenwert für andere Operationen verwenden

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

(4) Führen Sie Schritt (1) aus. Lileis Guthaben beträgt immer noch 400, was mit dem Abfrageergebnis von Schritt (1) übereinstimmt, und es gibt kein nicht wiederholbares Leseproblem. Führen Sie dann update balance = balance - 50 aus, wobei id = 1 ist. Der Kontostand wird nicht zu 400-50 = 350. Der Kontostandswert von Lilei wird in Schritt (2) mit 350 berechnet, sodass er 300 beträgt, und die Konsistenz der Daten ist gewährleistet wird nicht zerstört. , das ist ein bisschen magisch, vielleicht ist es eine Funktion von MySQL

mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     400 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 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: 0mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     300 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 rows in set (0.00 sec)

　 (5) Starten Sie eine Transaktion auf Client A und fragen Sie den Anfangswert ab des Tabellenkontos

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 |+------+--------+---------+3 rows in set (0.00 sec)

(6) Öffnen Sie die Transaktion auf Kunde B, fügen Sie ein neues Datenelement hinzu, in dem der Saldofeldwert 600 beträgt, 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 Guthabens von Kunde A. Der Wert von Kunde B ist nicht enthalten Der Saldo wird berechnet und ergibt 19300. Dies liegt daran, dass die 600 von Client B enthalten sind

, aus der Sicht des Kunden kann der Kunde Client B nicht sehen, und es wird das Gefühl haben, dass die Welt dies getan hat hat seinen Kuchen verloren, mit zusätzlichen 600 Yuan. Das ist Phantom-Lesen, das aus der Sicht eines Entwicklers 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üssen, müssen Sie die unten eingeführte Transaktionsisolationsstufe „Serialisierung“ übernehmen

mysql> Saldo) vom Konto;
+------------+
| sum(saldo) |
+-- ------------ +
|. 18700 |
+------------+
1 Zeile im Satz (0,00 Sek.)

MySQL> OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> select sum(balance) from account;

+-------- ------+
|. Saldo) |
+-------------+
| 19300 |
+----- ----------+
1 Zeile im Satz (0,00 Sek.)

　

　4. Serialisierung

　(1) Öffnen Sie einen Client A und stellen Sie den aktuellen Transaktionsmodus auf serialisierbar ein, und Fragen Sie den Anfangswert des Tabellenkontos 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 |+------+--------+---------+4 rows in set (0.00 sec)

　(2) Öffnen Sie einen Client B und legen Sie den aktuellen Transaktionsmodus fest. Es ist serialisierbar und beim Einfügen von a wird ein Fehler gemeldet Datensatz. Die Tabelle ist gesperrt und das Einfügen schlägt fehl.

Wenn die Transaktionsisolationsstufe in MySQL serialisierbar ist, wird die Tabelle gesperrt, sodass keine Phantomlesevorgänge auftreten. Diese Isolationsstufe weist häufig eine A-Transaktion auf belegt eine Tabelle und Tausende anderer Transaktionen können nur inaktiv bleiben, bis sie abgeschlossen und übermittelt werden, bevor sie verwendet werden können. Dies 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

　1. Die in der SQL-Spezifikation festgelegten Standards und die spezifische Implementierung verschiedener Datenbanken können einige Unterschiede aufweisen

　2 Die Standardtransaktionsisolationsstufe in MySQL ist wiederholbares Lesen und die gelesenen Zeilen werden nicht gesperrt

3. Transaktionsisolation Wenn die Ebene Serialisierung ist, wird durch das Lesen von Daten die gesamte Tabelle gesperrt

4. Beim Lesen dieses Artikels , wenn Sie stehen Aus Sicht eines Entwicklers gibt es möglicherweise keine logischen Probleme mit nicht wiederholbaren Lesevorgängen und Phantom-Lesevorgängen, und die endgültigen Daten sind immer noch konsistent. Aus Sicht eines Benutzers können sie jedoch normalerweise nur eine Transaktion (nur eine) sehen (Transaktion sichtbar). Client A kennt die Existenz des Undercover-Clients B nicht und berücksichtigt nicht das Phänomen der gleichzeitigen Ausführung von Transaktionen, sobald dieselben Daten mehrmals mit unterschiedlichen Ergebnissen gelesen werden oder neue Datensätze aus dem Nichts auftauchen Luft, sie haben möglicherweise Zweifel. Dies ist ein Problem mit der Benutzererfahrung.

5. Wenn eine Transaktion in MySQL ausgeführt wird, weist das Endergebnis keine Datenkonsistenzprobleme auf, da es sich bei einer Transaktion um MySQL handelt Bei der Ausführung einer Operation werden möglicherweise nicht unbedingt die Zwischenergebnisse der vorherigen Operation verwendet. Sie wird auf der Grundlage der tatsächlichen Situation anderer gleichzeitiger Transaktionen verarbeitet, stellt jedoch die Konsistenz der Daten sicher Anwendung wird das Ergebnis einer Operation von der nächsten Operation verwendet und andere Berechnungen werden durchgeführt. 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 sie umfassend basierend auf der tatsächlichen Situation jeder Transaktion, was zu Nein führt Datenkonsistenz Zerstört, aber die Anwendung spielt Karten nach logischen Routinen, was nicht so intelligent ist wie MySQL, und es treten zwangsläufig Probleme mit der Datenkonsistenz auf.

　7

Je höher die Isolationsstufe, desto vollständiger und konsistenter können die Daten sein Die Parallelitätsleistung ist umso größer, je größer die Auswirkung ist, dass Sie Ihren Kuchen nicht gleichzeitig essen können. Bei den meisten Anwendungen können Sie der Isolationsstufe des Datenbanksystems Vorrang auf „Read Committed“ geben, 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 vonBeispiel-Tutorial zur MySQL-Transaktionsisolationsstufe. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!