Umfassende Analyse der Isolationsebenen in MySQL

Wenn die Datenbank gleichzeitig denselben Datenstapel hinzufügt, löscht und ändert, können eine Reihe von Problemen auftreten, beispielsweise Dirty Writes, Dirty Reads, nicht wiederholbare Lesevorgänge und Phantomlesevorgänge. MySQL bietet eine Reihe von Mechanismen zum Lösen von Transaktionsparallelitätsproblemen, z. B. Transaktionsisolierung, Sperrmechanismus und MVCC-Mechanismus zur Steuerung der Parallelität mehrerer Versionen. Lassen Sie uns heute den Transaktionsisolierungsmechanismus untersuchen.

Eine Transaktion ist eine logische Verarbeitungseinheit, die aus einer Gruppe von SQL-Anweisungen besteht. Schauen wir uns zunächst die ACID-Eigenschaften von Transaktionen an:

• Atomarität: Eine Transaktion ist eine atomare Operationseinheit, die Daten ändert und entweder vollständig oder überhaupt nicht ausgeführt wird. Es wird von der Ausführungsebene aus beschrieben.
• Konsistenz: Die Daten müssen beim Start und Abschluss einer Transaktion in einem konsistenten Zustand bleiben. Es wird aus der Perspektive der Ausführungsergebnisse beschrieben.
• Isolation: Das Datenbanksystem bietet einen bestimmten Isolationsmechanismus, um sicherzustellen, dass der Transaktionsausführungsprozess für die Außenwelt unsichtbar ist, unabhängig ausgeführt wird und nicht von der Außenwelt beeinflusst wird.
• Dauerhaft: Nach Abschluss einer Transaktion sind die Änderungen an den Daten dauerhaft und bleiben auch bei einem Systemausfall erhalten.
  

Auswirkungen gleichzeitiger Transaktionen:

• Dirty Write (verlorenes Update): Mehrere Transaktionen wählen dieselbe Zeile aus und sind sich der Existenz der jeweils anderen nicht bewusst, wodurch die Datenoperationen vorheriger Transaktionen überschrieben werden.
• Dirty Reads: Transaktion A liest Daten, die Transaktion B nicht festgeschrieben hat. Transaktion B führt ein Rollback durch und Transaktion A schreibt sie fest. Das Endergebnis entspricht nicht dem Konsistenzprinzip.
• Nicht wiederholbare Lesevorgänge: Dieselbe Transaktion und dieselbe Abfrageanweisung können bei mehrmaliger Ausführung inkonsistente Ergebnisse liefern. Dies kann durch Änderungen verursacht werden, die von externen Transaktionen vorgenommen wurden, und erfüllt nicht die Isolationsanforderungen.
• Phantom-Lesevorgänge: Transaktion A liest die von Transaktion B übermittelten neuen Daten, wodurch die Isolationsanforderung nicht erfüllt wird.
  

Transaktionsisolationsebene:

MySQL bietet die oben genannten vier Isolationsebenen. Je strenger die Isolation, desto weniger Probleme können auftreten, desto höher sind jedoch die Leistungseinbußen. Die Standardisolationsebene ist wiederholbares Lesen. Im Folgenden wird der Client verwendet, um Vorgänge zur Überprüfung durchzuführen.

Erstellen Sie zunächst eine Tabelle und Daten

CREATE TABLE `Konto` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `balance` int(11) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

INSERT INTO `Konto` (`ID`, `Guthaben`)
WERTE
  (1, 500),
  (2, 600),
  (3, 200);

Stellen Sie eine Verbindung zum Client her und überprüfen Sie die Isolationsstufe. Sie können sehen, dass es sich um ein wiederholbares Lesen handelt:

MySQL [test]> Variablen wie „tx_isolation“ anzeigen;
+---------------+-----------------+
| Variablenname | Wert |
+---------------+-----------------+
| tx_isolation | WIEDERHOLBARES LESEN |
+---------------+-----------------+

Nicht festgeschriebenen Test lesen:

Beide Clients AB führen „set tx_isolation='read-uncommitted'“ aus und setzen die Isolationsebene auf „Read Uncommitted“.

Kunde A startet eine Transaktion: starte Transaktion; fragt Daten ab: select * from account;

Client B startet eine Transaktion: Transaktion starten; aktualisiert Daten: Konto aktualisieren, Kontostand festlegen = Kontostand - 100, wobei ID = 1; die Transaktion wird zu diesem Zeitpunkt nicht bestätigt.

Kunde A fragt die Daten erneut ab: select * from account; jetzt sind die Daten aus den beiden Abfragen unterschiedlich.

A liest die von B aktualisierten Daten, bevor B die Daten festschreibt. Wenn B zu diesem Zeitpunkt ein Rollback durchführt, sind die Daten auf A fehlerhaft. Bei dieser Situation handelt es sich um einen Dirty Read, der durch nicht festgeschriebenes Lesen verursacht wird. Dies kann durch die Verwendung der Isolationsebene „Read Committed“ gelöst werden.

Verwenden Sie den Commit-Befehl, um die Transaktion des Clients AB festzuschreiben.

Eingereichte Tests lesen:

Beide Clients A und B führen „set tx_isolation='read-committed';“ aus, um die Isolationsebene auf „Read Committed“ festzulegen.

Kunde A startet eine Transaktion: starte Transaktion; fragt Daten ab: select * from account;

Client B startet eine Transaktion: Transaktion starten; aktualisiert Daten: Konto aktualisieren, Kontostand festlegen = Kontostand - 100, wobei ID = 1; die Transaktion wird zu diesem Zeitpunkt nicht bestätigt.

Client A fragt die Daten erneut ab: select * from account; Jetzt können wir sehen, dass die von Client A zweimal abgefragten Daten konsistent sind und kein Dirty Read auftritt

Zu diesem Zeitpunkt führt Client B die Transaktion aus: commit;

Client A fragt die Daten erneut ab: select * from account; Zu diesem Zeitpunkt können Sie sehen, dass sich die von Client A abgefragten Daten geändert haben. Dies ist ein nicht wiederholbarer Lesevorgang.

Wiederholbarer Lesetest:

Beide Clients AB führen „set tx_isolation='repeatable-read';“ aus, um die Isolationsebene auf wiederholbares Lesen einzustellen.

Kunde A startet eine Transaktion: starte Transaktion; fragt Daten ab: select * from account;

Client B startet eine Transaktion: Transaktion starten; aktualisiert Daten: Konto aktualisieren, Kontostand festlegen = Kontostand - 100, wobei ID = 1; führt die Transaktion aus

Client A fragt die Daten erneut ab: select * from account; Jetzt können wir sehen, dass die von Client A zweimal abgefragten Daten konsistent sind und die wiederholt gelesenen Daten konsistent sind.

Client A führt die Aktualisierungsanweisung aus: update account set balance = balance - 50 wobei ID = 1;

Client A fragt die Daten erneut ab: select * from account; Zu diesem Zeitpunkt sind die Daten mit der ID = 1 die Daten, nachdem Client B -50 aktualisiert hat, und die Datenkonsistenz wird nicht zerstört.

Client B öffnet die Transaktion erneut und fügt ein Datenelement ein: „In Konto (ID, Guthaben) Werte (4,1000) einfügen“, Transaktion festschreiben;

Eine Client-Abfrage, das Ergebnis ist das gleiche wie beim letzten Mal

Client A führt aus: update account set balance = balance - 100 where id = 4; um die neu eingefügten Daten von Client B zu aktualisieren. Die Ausführung ist erfolgreich. Wenn alle Daten erneut abgefragt werden, können die Daten mit der ID = 4 eingefügt werden und es erfolgt ein Phantom-Read.

# Ein Client-Ausführungsprozess: # Legen Sie die Wiederholbarkeit der Isolationsebene fest MySQL [test]> set tx_isolation='repeatable-read';
Abfrage OK, 0 Zeilen betroffen, 1 Warnung (0,00 Sek.)
# Transaktion starten MySQL [test]> start transaction;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
# Alle Daten abfragen MySQL [test]> select * from account;
+----+---------+
| ID | Guthaben |
+----+---------+
| 1 | 300 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
3 Zeilen im Satz (0,00 Sek.)
# Erneut abfragen, um zu überprüfen, ob die beiden Ergebnisse übereinstimmen MySQL [test]> select * from account;
+----+---------+
| ID | Guthaben |
+----+---------+
| 1 | 300 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
3 Zeilen im Satz (0,00 Sek.)
# Nachdem Client B Daten eingefügt hat, kann Client A MySQL dieses Mal nicht abfragen [test]> select * from account;
+----+---------+
| ID | Guthaben |
+----+---------+
| 1 | 150 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
3 Zeilen im Satz (0,00 Sek.)
# Client A aktualisiert die von Client B eingefügten Daten und stellt fest, dass die Aktualisierung erfolgreich ist. MySQL [test]> update account set balance = balance + 1000 where id = 4;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0
# Abfrage erneut, die Daten können abgefragt werden, aber es tritt ein Phantomlesen aufMySQL [test]> select * from account;
+----+---------+
| ID | Guthaben |
+----+---------+
| 1 | 400 |
| 2 | 600 |
| 3 | 200 |
| 4 | 2000 |
+----+---------+
4 Zeilen im Satz (0,00 Sek.)
# Transaktion festschreiben MySQL [test]> commit;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

# B-Client-Ausführungsprozess: Stellen Sie die Isolationsebene auf wiederholbares Lesen ein MySQL [test]> set tx_isolation='repeatable-read';
Abfrage OK, 0 Zeilen betroffen, 1 Warnung (0,00 Sek.)
# Transaktion starten MySQL [test]> start transaction;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
# Daten aktualisieren und direkt an MySQL übermitteln [test]> Konto aktualisieren, Guthaben festlegen = Guthaben – 100, wobei ID = 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

MySQL [Test]> Commit;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)
# Transaktion erneut starten MySQL [test]> start transaction;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
# Fügen Sie ein Datenstück ein MySQL [test]> insert into account(id,balance) values ​​​​(4,1000);
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)
MySQL [Test]> Commit;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Die letzte Serialisierungsart: set tx_isolation='serializable'; kann von Ihnen selbst überprüft werden und kann alle oben genannten Probleme lösen, wird aber im Allgemeinen nicht verwendet. Sie stellt zwar die Konsistenz sicher, führt jedoch zu einer erheblichen Leistungsminderung und extrem geringer Parallelität. Die Standardeinstellung ist wiederholbares Lesen.

Die Isolationsebene kann Probleme mit der Transaktionsparallelität bis zu einem gewissen Grad bewältigen. Darüber hinaus gibt es noch andere Möglichkeiten, die später noch einmal untersucht werden.

Das Obige ist eine umfassende Analyse der Isolationsstufen in MySQL. Weitere Informationen zu MySQL-Isolationsstufen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!