MySQL-Fallanalyse der Transaktionsisolationsebene

Viele Freunde waren immer verwirrt über die Isolationsstufe von MySQL. Tatsächlich ist diese Frage überhaupt nicht schwierig. Der Schlüssel ist, wie man sie erklärt! Allein der Anblick der Theorie wird Ihnen sicherlich schwindelig machen, aber wenn wir es anhand von echtem SQL demonstrieren, werden Sie feststellen, wie einfach es ist!

Heute möchte ich das Problem der Transaktionsisolationsebene in MySQL anhand einiger einfacher Fälle demonstrieren.

1. Theorie

In MySQL gibt es die folgenden vier Typen von Transaktionsisolationsebenen:

• SERIALISIERBAR
• Wiederholbares Lesen
• LESEN SIE ENGAGIERT
• LESEN SIE UNVERBINDLICH

Die Bedeutung der vier verschiedenen Isolationsebenen ist wie folgt:

SERIALISIERBAR

Wenn die Isolationsebene serialisiert ist, führen Benutzer die aktuelle Transaktion sequenziell nacheinander aus. Diese Isolationsebene bietet maximale Isolation zwischen Transaktionen.

WIEDERHOLBARES LESEN

Auf der Isolationsebene für wiederholbares Lesen werden Transaktionen nicht als Sequenz betrachtet. Allerdings sind die Änderungen in der aktuell ausgeführten Transaktion nach wie vor nicht nach außen sichtbar. Das heißt, wenn der Benutzer dieselbe SELECT-Anweisung mehrmals in einer anderen Transaktion ausführt, ist das Ergebnis immer dasselbe. (Denn die durch die ausgeführte Transaktion erzeugten Datenänderungen sind von außen nicht sichtbar.)

LESEN SIE ENGAGIERT

Die Isolationsebene READ COMMITTED ist weniger sicher als die Isolationsebene REPEATABLE READ. Transaktionen auf der Ebene READ COMMITTED können von anderen Transaktionen an Daten vorgenommene Änderungen erkennen. Das heißt, wenn während der Transaktionsverarbeitung andere Transaktionen die entsprechenden Tabellen ändern, können mehrere SELECT-Anweisungen in der gleichen Transaktion unterschiedliche Ergebnisse zurückgeben.

LESEN SIE UNVERBINDLICH

READ UNCOMMITTED bietet minimale Isolation zwischen Transaktionen. Transaktionen auf dieser Isolationsebene sind nicht nur anfällig für Phantom-Lesevorgänge und nicht wiederholbare Lesevorgänge, sondern können auch Daten lesen, die von anderen Transaktionen noch nicht festgeschrieben wurden. Wenn diese Transaktion nicht festgeschriebene Änderungen von anderen Transaktionen als Grundlage für Berechnungen verwendet und diese nicht festgeschriebenen Änderungen von ihren übergeordneten Transaktionen rückgängig gemacht werden, führt dies zu einer großen Menge an Datenänderungen.

In der MySQL-Datenbank ist die Standardtransaktionsisolationsebene REPEATABLE READ

2. SQL-Praxis

Als Nächstes werden wir den Lesern die obige Theorie anhand einiger einfacher SQL-Anweisungen verifizieren.

2.1 Überprüfen Sie die Isolationsstufe

Sie können die standardmäßige globale Isolationsstufe der Datenbankinstanz und die Isolationsstufe der aktuellen Sitzung über das folgende SQL anzeigen:

Verwenden Sie vor MySQL 8 den folgenden Befehl, um die MySQL-Isolationsebene anzuzeigen:

WÄHLEN SIE @@GLOBAL.tx_isolation, @@tx_isolation;

Die Abfrageergebnisse lauten wie folgt:

Wie Sie sehen, ist die Standardisolationsebene REPEATABLE-READ, sowohl für die globale Isolationsebene als auch für die aktuelle Sitzungsisolationsebene.

Ab MySQL 8 verwenden Sie den folgenden Befehl, um die MySQL-Standardisolationsebene anzuzeigen:

Wählen Sie @@GLOBAL.transaction_isolation, @@transaction_isolation;

Lediglich die Schlagworte haben sich geändert, alles andere bleibt gleich.

Die Isolationsebene kann mit dem folgenden Befehl geändert werden (Entwicklern wird empfohlen, die aktuelle Sitzungsisolationsebene anstelle der globalen Isolationsebene zu ändern):

FESTLEGEN DER SITZUNG DER TRANSAKTION ISOLATION LESEN UNCOMMITTED

Die obige SQL-Anweisung gibt an, dass die Datenbankisolationsstufe der aktuellen Sitzung auf READ UNCOMMITTED eingestellt ist. Nachdem die Einstellung erfolgreich war, wird die Isolationsstufe erneut abgefragt und festgestellt, dass sich die Isolationsstufe der aktuellen Sitzung geändert hat, wie in Abbildung 1-2 dargestellt:

Beachten Sie, dass, wenn Sie nur die Isolationsstufe der aktuellen Sitzung ändern, die Isolationsstufe nach dem Ändern einer Sitzung auf die Standardisolationsstufe zurückgesetzt wird. Beim Testen müssen wir daher nur die Isolationsstufe der aktuellen Sitzung ändern.

2.2 LESEN SIE UNCOMMITTED

2.2.1 Testdaten vorbereiten

READ UNCOMMITTED ist die niedrigste Isolationsstufe. Diese Isolationsstufe weist die Probleme Dirty Read, Non-Repeatable Read und Phantom Read auf. Schauen wir uns also zuerst diese Isolationsstufe an, damit Sie verstehen, was diese drei Probleme sind.

Sie werden im Folgenden einzeln vorgestellt.

Erstellen Sie zunächst eine einfache Tabelle und geben Sie zwei Daten wie folgt vor:

Die Daten in der Tabelle sind sehr einfach. Es gibt zwei Benutzer, javaboy und itboyhub, und jedes ihrer Konten verfügt über 1.000 RMB. Lassen Sie uns nun einen Übertragungsvorgang zwischen diesen beiden Benutzern simulieren.

Beachten Sie, dass bei Verwendung von Navicat unterschiedliche Abfragefenster unterschiedlichen Sitzungen entsprechen. Bei Verwendung von SQLyog entsprechen unterschiedliche Abfragefenster derselben Sitzung. Wenn Sie SQLyog verwenden, müssen Sie daher eine neue Verbindung öffnen und Abfragevorgänge in der neuen Verbindung durchführen.

2.2.2 Schmutziges Lesen

Wenn eine Transaktion Daten liest, die nicht von einer anderen Transaktion festgeschrieben wurden, wird dies als „Dirty Read“ bezeichnet. Die spezifischen Vorgänge sind wie folgt:

Öffnen Sie zunächst zwei SQL-Operationsfenster, angenommen, es sind A und B. Geben Sie das folgende SQL in Fenster A ein (nach der Eingabe nicht ausführen):

TRANSAKTION STARTEN;
UPDATE-Konto, setze Guthaben=Guthaben+100, wobei Name='Javaboy';
UPDATE-Kontosatz-Guthaben=Balance-100, wobei Name='itboyhub';
BEGEHEN;

Führen Sie das folgende SQL im Fenster B aus, um die Standardisolationsebene für Transaktionen wie folgt auf READ UNCOMMITTED zu ändern:

FESTLEGEN DER SITZUNG DER TRANSAKTION ISOLATION LESEN UNCOMMITTED

Geben Sie als Nächstes das folgende SQL in Fenster B ein. Führen Sie nach der Eingabe die erste Zeile aus, um die Transaktion zu starten (beachten Sie, dass nur eine Zeile ausgeführt werden muss):

TRANSAKTION STARTEN;
SELECT * vom Konto;
BEGEHEN;

Führen Sie als Nächstes die ersten beiden SQL-Anweisungen in Fenster A aus, um eine Transaktion zu starten und dem Javaboy-Konto 100 Yuan hinzuzufügen.

Rufen Sie Fenster B auf und führen Sie die zweite Abfrage SQL (SELECT * from user;) in Fenster B aus. Das Ergebnis ist wie folgt:

Es ist ersichtlich, dass, obwohl die Transaktion im Fenster A nicht festgeschrieben wurde, die relevanten Datenänderungen im Fenster B abgefragt werden können.

Dies ist das Dirty-Read- Problem.

2.2.3 Nicht wiederholbares Lesen

Ein nicht wiederholbarer Lesevorgang bedeutet, dass eine Transaktion denselben Datensatz zweimal liest, die zweimal gelesenen Daten jedoch unterschiedlich sind. Dies wird als nicht wiederholbarer Lesevorgang bezeichnet. Die einzelnen Schritte sind wie folgt (stellen Sie den Betrag auf beiden Konten wieder auf den Stand von 1000 vor der Operation her):

1. Öffnen Sie zunächst zwei Abfragefenster A und B und stellen Sie die Datenbanktransaktionsisolationsebene von B auf READ UNCOMMITTED ein. Das spezifische SQL finden Sie oben und wird hier nicht wiederholt.
2. Geben Sie das folgende SQL in Fenster B ein und führen Sie dann nur die ersten beiden SQLs aus, um die Transaktion zu starten und das Javaboy-Konto abzufragen:

TRANSAKTION STARTEN;
SELECT * vom Konto, wobei Name = "Javaboy" ist;
BEGEHEN;

Die Ausführungsergebnisse der ersten beiden SQL-Anweisungen sind wie folgt:

Führen Sie das folgende SQL in Fenster A aus, um dem Javaboy-Konto 100 Yuan hinzuzufügen, und zwar wie folgt:

TRANSAKTION STARTEN;
UPDATE-Konto, setze Guthaben=Guthaben+100, wobei Name='Javaboy';
BEGEHEN;

4. Kehren Sie erneut zu Fenster B zurück und führen Sie die zweite SQL-Anweisung in Fenster B aus, um das Konto von javaboy anzuzeigen. Das Ergebnis ist wie folgt:

Das Javaboy-Konto hat sich geändert, d. h. die Ergebnisse der zweimaligen Überprüfung des Javaboy-Kontos sind inkonsistent. Dies ist ein nicht wiederholbarer Lesevorgang.

Der Unterschied zwischen Dirty Read und nicht wiederholbarem Lesen besteht darin, dass beim Dirty Read die Daten angezeigt werden, die von anderen Transaktionen nicht festgeschrieben wurden, während beim nicht wiederholbaren Lesen die Daten angezeigt werden, die von anderen Transaktionen festgeschrieben wurden (da sich das aktuelle SQL auch in einer Transaktion befindet, möchte es möglicherweise die Daten nicht sehen, die von anderen Transaktionen festgeschrieben wurden).

2.2.4 Phantom-Lesen

Phantomlesen ist dem nicht wiederholbaren Lesen sehr ähnlich und schon der bloße Blick auf den Namen lässt darauf schließen, dass es eine Illusion erzeugt.

Lassen Sie mich Ihnen ein einfaches Beispiel geben.

Geben Sie in Fenster A das folgende SQL ein:

TRANSAKTION STARTEN;
in Konto (Name, Guthaben) Werte ('zhangsan', 1000) einfügen;
BEGEHEN;

Geben Sie dann das folgende SQL in Fenster B ein:

TRANSAKTION STARTEN;
SELECT * vom Konto;
Löschen aus Konto mit Name='zhangsan';
BEGEHEN;

Wir führen folgende Schritte durch:

• Führen Sie zunächst die ersten beiden Zeilen von Fenster B aus, um eine Transaktion zu starten und gleichzeitig die Daten in der Datenbank abzufragen. Zu diesem Zeitpunkt werden nur die Daten javaboy und itboyhub abgefragt.
• Führen Sie die ersten beiden Zeilen in Fenster A aus, um der Datenbank einen Benutzer namens zhangsan hinzuzufügen. Beachten Sie, dass Sie die Transaktion nicht bestätigen müssen.
• Führen Sie die zweite Zeile von Fenster B aus. Aufgrund des Dirty-Read-Problems kann der Benutzer zhangsan zu diesem Zeitpunkt gefunden werden.
• Führen Sie die dritte Zeile von Fenster B aus, um den Datensatz mit dem Namen „zhangsan“ zu löschen. Beim Löschen tritt zu diesem Zeitpunkt ein Problem auf. Obwohl „zhangsan“ in Fenster B zu finden ist, wurde dieser Datensatz nicht festgeschrieben. Er wird aufgrund eines Dirty Reads angezeigt und kann daher nicht gelöscht werden. In diesem Moment trat eine Illusion auf. Es gab eindeutig einen Zhangsan, aber er konnte nicht gelöscht werden.

Das ist Phantomlesen.

Nach dem Lesen der oben genannten Fälle sollte jeder verstehen, was Dirty Reads, nicht wiederholbare Reads und Phantom Reads bedeuten.

2.3 VERPFLICHTET LESEN

Im Vergleich zu READ UNCOMMITTED löst READ COMMITTED hauptsächlich das Problem des Dirty Read, jedoch nicht das Problem des nicht wiederholbaren Lesens und des Phantomlesens.

Nach dem Ändern der Transaktionsisolationsebene READ COMMITTED und Wiederholen des obigen Tests im Fall des Dirty Read stellt sich heraus, dass das Dirty Read-Problem nicht mehr besteht. Nach Wiederholen des obigen Tests im Fall des nicht wiederholbaren Lesevorgangs stellt sich heraus, dass das Problem des nicht wiederholbaren Lesevorgangs weiterhin besteht.

Der obige Fall ist nicht für einen Phantomlesetest geeignet. Wechseln wir zu einem Phantomlesetestfall.

Immer noch zwei Fenster A und B, ändern Sie die Isolationsstufe von Fenster B READ COMMITTED ,

Geben Sie anschließend im Fenster A folgendes Test-SQL ein:

TRANSAKTION STARTEN;
in Konto (Name, Guthaben) Werte ('zhangsan', 1000) einfügen;
BEGEHEN;

Geben Sie das folgende Test-SQL in Fenster B ein:

TRANSAKTION STARTEN;
SELECT * vom Konto;
in Konto (Name, Guthaben) Werte ('zhangsan', 1000) einfügen;
BEGEHEN;

Die Testmethode ist wie folgt:

• Führen Sie zunächst die ersten beiden SQL-Zeilen in Fenster B aus, starten Sie eine Transaktion und fragen Sie Daten ab. Zu diesem Zeitpunkt werden nur zwei Benutzer gefunden: javaboy und itboyhub.
• Führen Sie die ersten beiden SQL-Zeilen in Fenster A aus, um einen Datensatz einzufügen, aber bestätigen Sie die Transaktion nicht.
• Führen Sie die zweite SQL-Zeile in Fenster B aus. Da jetzt kein Dirty-Read-Problem vorliegt, können die in Fenster A hinzugefügten Daten derzeit nicht gefunden werden.
• Führen Sie die dritte SQL-Zeile im Fenster B aus. Da das Namensfeld eindeutig ist, kann hier keine Einfügung vorgenommen werden. An diesem Punkt tritt eine Illusion auf. Auch wenn es keinen Benutzer mit dem Namen „zhangsan“ gibt, können Sie „zhangsan“ nicht einfügen.

2.4 WIEDERHOLBARES LESEN

Im Vergleich zu READ COMMITTED löst REPEATABLE READ das Problem nicht wiederholbarer Lesevorgänge, Phantomlesevorgänge werden jedoch nicht gelöst.

Der Phantomlesetest in REPEATABLE READ ist grundsätzlich derselbe wie im vorherigen Abschnitt. Der Unterschied besteht darin, dass Sie die Transaktion nach der Ausführung des Insert-SQL im zweiten Schritt festschreiben müssen.

Da REPEATABLE READ das Problem des nicht wiederholbaren Lesens gelöst hat, können die festgeschriebenen Daten im dritten Schritt nicht gefunden werden, selbst wenn die Transaktion im zweiten Schritt festgeschrieben wird, und wenn die Daten im vierten Schritt eingefügt werden, tritt ein Fehler auf.

Beachten Sie, dass REPEATABLE READ auch die Standardisolationsebene für Datenbanktransaktionen für die InnoDB-Engine ist.

2.5 SERIALISIERBAR

SERIALIZABLE bietet maximale Isolation zwischen Transaktionen. Auf dieser Isolationsebene werden Transaktionen sequenziell nacheinander ausgeführt und es kommt nicht zu Dirty Reads, nicht wiederholbaren Reads und Phantom Reads. Dies ist die sicherste Methode.

Wenn die aktuelle Transaktionsisolationsstufe auf SERIALIZABLE eingestellt ist, werden andere Transaktionen, wenn Sie sie zu diesem Zeitpunkt starten, blockiert und müssen warten, bis die aktuelle Transaktion festgeschrieben ist, bevor andere Transaktionen erfolgreich gestartet werden können. Daher treten die vorherigen Probleme mit Dirty Read, Non-Repeatable Read und Phantom Read hier nicht auf.

3. Fazit

Im Allgemeinen ist die Entsprechung zwischen Isolationsebenen und Dirty Reads, nicht wiederholbaren Reads und Phantom Reads wie folgt:

Das Leistungsverhältnis ist in der Abbildung dargestellt:

Okay, das ist alles für diesen Artikel. Vielleicht möchten Sie es ja mal ausprobieren, indem Sie ein paar Zeilen SQL schreiben.

Dies ist das Ende dieses Artikels über die MySQL-Fallanalyse der Transaktionsisolationsebene. Weitere relevante Inhalte zur MySQL-Transaktionsisolationsebene finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!