Detaillierte Erläuterung der MySQL-Transaktionsverarbeitung

1. MySQL-Transaktionskonzept

MySQL-Transaktionen werden hauptsächlich zur Verarbeitung von Daten mit großen Operationsvolumina und hoher Komplexität verwendet. Eine logische Ausführungseinheit besteht aus einer Abfolge von einer oder mehreren Datenbankoperationen. Diese Abfolge von Operationen wird entweder vollständig ausgeführt oder abgebrochen. In MySQL unterstützen nur Datenbanken oder Tabellen, die die Innodb-Datenbank-Engine verwenden, Transaktionen. Transaktionen werden zum Verwalten von Einfüge-, Aktualisierungs- und Löschanweisungen verwendet.

2. Transaktionsmerkmale: Atomarität, Konsistenz, Isolation und Dauerhaftigkeit. Diese vier Merkmale werden auch als ACID-Eigenschaften bezeichnet.

1. Atomarität: Eine Transaktion ist die kleinste Ausführungseinheit einer Anwendung, genauso wie ein Atom das kleinste Teilchen in der Natur ist und die Eigenschaft hat, unteilbar zu sein. Eine Transaktion ist die kleinste unteilbare logische Ausführungseinheit in einer Anwendung. Eine Gruppe von Transaktionen kann entweder erfolgreich sein oder abgebrochen werden.

2. Stabilität und Konsistenz: Das Ergebnis der Transaktionsausführung muss die Datenbank von einem konsistenten Zustand in einen anderen konsistenten Zustand ändern. Eine Datenbank befindet sich in einem konsistenten Zustand, wenn sie nur die Ergebnisse erfolgreich ausgeführter Transaktionen enthält. Die Konsistenz wird durch Atomizität sichergestellt. Bei ungültigen Daten (z.B. Fremdschlüsseleinschränkungen) wird die Transaktion zurückgezogen.

3. Isolierung: Die Ausführung der einzelnen Transaktionen stört sich nicht gegenseitig und die internen Vorgänge jeder Transaktion sind von anderen gleichzeitigen Transaktionen isoliert. Mit anderen Worten: Gleichzeitig ausgeführte Transaktionen können die Zwischenzustände der jeweils anderen nicht sehen und gleichzeitig ausgeführte Transaktionen können sich nicht gegenseitig beeinflussen. Transaktionen laufen unabhängig voneinander. Wenn das Ergebnis einer Transaktion andere Transaktionen beeinflusst, werden die anderen Transaktionen zurückgezogen. Eine 100-prozentige Isolierung der Transaktionen erfordert Einbußen bei der Geschwindigkeit.

4. Persistenz und Zuverlässigkeit: Persistenz wird auch Persistenz genannt, was bedeutet, dass nach der Durchführung einer Transaktion alle an den Daten vorgenommenen Änderungen im permanenten Speicher aufgezeichnet werden müssen, der normalerweise in einer physischen Datenbank gespeichert wird. Nach einem Software- oder Hardwareabsturz verwendet der InnoDB-Datentabellentreiber die Protokolldatei, um sie zu rekonstruieren und zu ändern. Zuverlässigkeit und hohe Geschwindigkeit können nicht gleichzeitig erreicht werden. Die Option innodb_flush_log_at_trx_commit bestimmt, wann Transaktionen im Protokoll gespeichert werden.

Hinweis: Die Speicher-Engine MyISAM unterstützt keine Transaktionen, aber die Speicher-Engine InnoDB unterstützt Transaktionen. Transaktionen sind nur für Anweisungen gültig, die Daten betreffen. Engines anzeigen Zeigen Sie die von MySQL Lock unterstützten Daten-Engines an.

3. Datenkonzept lesen

1. Dirty Reads: Dirty Reads sind das Lesen von schmutzigen Daten, und mit schmutzigen Daten sind nicht festgeschriebene Daten gemeint. Eine Transaktion ändert einen Datensatz. Bevor die Transaktion abgeschlossen und festgeschrieben ist, befinden sich die Daten in einem ausstehenden Zustand (sie können festgeschrieben oder zurückgesetzt werden). Zu diesem Zeitpunkt liest eine zweite Transaktion die nicht festgeschriebenen Daten und führt auf ihrer Grundlage weitere Verarbeitungen durch, wodurch eine nicht festgeschriebene Datenabhängigkeit erzeugt wird. Dieses Phänomen wird als „Dirty Read“ bezeichnet.

2. Nicht wiederholbare Lesevorgänge: Eine Transaktion liest denselben Datensatz zweimal, aber die zweimal gelesenen Daten sind unterschiedlich. Wir nennen dies einen nicht wiederholbaren Lesevorgang. Das heißt, die Daten werden zwischen zwei Lesevorgängen dieser Transaktion durch andere Transaktionen geändert.

3. Phantom-Lesevorgänge: Eine Transaktion liest zuvor abgerufene Daten unter denselben Abfragebedingungen erneut, stellt jedoch fest, dass andere Transaktionen neue Daten eingefügt haben, die ihren Abfragebedingungen entsprechen. Dieses Phänomen wird als Phantom-Lesevorgänge bezeichnet.

4. Transaktionsisolationsebene

Ändern Sie die Syntax der Transaktionsisolationsebene:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {LESEN UNCOMMITTED | LESEN COMMITTED | WIEDERHOLBARES LESEN | SERIALIZABLE}

1. Nicht festgeschriebenes Lesen (unbefugtes Lesen, nicht festgeschriebenes Lesen): Dies ist die niedrigste Isolationsebene, die es anderen Transaktionen ermöglicht, nicht festgeschriebene Daten anzuzeigen. Diese Ebene kann zu Dirty Reads führen. Wenn eine Transaktion mit dem Schreiben von Daten begonnen hat, darf eine andere Transaktion nicht gleichzeitig schreiben, andere Transaktionen dürfen diese Datenzeile jedoch lesen. Diese Isolationsstufe kann durch eine exklusive Schreibsperre erreicht werden. Dadurch wird ein Verlust von Aktualisierungen vermieden, es kann jedoch zu fehlerhaften Lesevorgängen kommen. Das heißt, Transaktion B liest die Daten, die Transaktion A nicht festgeschrieben hat. Die SELECT-Anweisung wird ohne Sperren ausgeführt, sodass das Lesen fehlerhafter Daten möglich ist. Die Isolationsstufe ist die niedrigste.

SET-Sitzungstransaktionsisolationsebene: Lesen nicht festgeschrieben;
SETZEN Sie die globale Transaktionsisolationsebene auf „Read Uncommitted“;/*Globale Empfehlung, sie nicht zu verwenden*/
WÄHLEN SIE @@global.tx_isolation;
Wählen Sie @@session.tx_isolation;
WÄHLEN SIE @@tx_isolation;

Erstellen Sie eine einfache Studententabelle, legen Sie die Felder „ID“, „Name“ und „Num“ fest, starten Sie Transaktion 1, fügen Sie der Tabelle eine gespeicherte Prozedur hinzu und führen Sie die Transaktion nicht aus. Überprüfen Sie den aktuellen Datenbanktransaktionsstatus und Sie können eine Datentransaktion mit der Transaktionsebene „READ UNCOMMITTED“ sehen:

Tabelle löschen, wenn Student vorhanden ist;
Tabelle „Student“ erstellen (
id int Primärschlüssel auto_increment Kommentar 'id',
Name varchar(100) Kommentar 'Name',
Zahl int
);
Prozedur löschen, falls proc_on_sw existiert;
Trennzeichen;;
Prozedur proc_on_sw() erstellen
beginnen
Transaktion starten;
einfügen in Student (Name, Nummer) Wert ('aaa', 1);
Wählen Sie * aus information_schema.INNODB_TRX;
Ende
;;
Trennzeichen;;
rufen Sie proc_on_sw() auf;

Erstellen Sie eine neue Transaktion 2 und fragen Sie die Studententabelle ab. Auf der Ebene READ UNCOMMITTED können wir die nicht festgeschriebenen Daten anderer Transaktionen sehen: Überprüfen Sie den Status der Datenbanktransaktion erneut und wir werden sehen, dass der Status normal ist.

Transaktion starten;
wähle * vom Studenten;
begehen;
Wählen Sie * aus information_schema.INNODB_TRX;

2. Read Committed (autorisiertes Lesen, Read Committed): Die Transaktion, die Daten liest, ermöglicht anderen Transaktionen den weiteren Zugriff auf die Datenzeile, die nicht festgeschriebene Schreibtransaktion verhindert jedoch, dass andere Transaktionen auf die Zeile zugreifen. Diese Isolationsebene vermeidet fehlerhafte Lesevorgänge, es können jedoch nicht wiederholbare Lesevorgänge auftreten. Transaktion A hat die Daten im Voraus gelesen, Transaktion B hat die Daten sofort aktualisiert und die Transaktion festgeschrieben, und als Transaktion A die Daten erneut gelesen hat, hatten sich die Daten geändert.

SET-Sitzungstransaktionsisolationsebene, Lesen festgeschrieben;
SET - Lesen der globalen Transaktionsisolationsebene festgeschrieben; /*Globale Empfehlung, nicht zu verwenden*/

Prozedur löschen, falls proc_on_up existiert;
Trennzeichen;;
Prozedur proc_on_up() erstellen
beginnen
setze Autocommit=0;
Aktualisiere den Studentensatznamen='cc', wobei ID=1 ist;
begehen;
setze Autocommit=1;
Ende
;;
Trennzeichen;;
rufen Sie proc_on_up() auf;
wähle * vom Studenten;

3. Wiederholbares Lesen: Wenn die Daten gelesen werden (Transaktion wird gestartet), sind Änderungsvorgänge nicht mehr zulässig. Wenn die Transaktion gestartet wird, sind UPDATE-Änderungsvorgänge anderer Transaktionen nicht zulässig. Nicht wiederholbares Lesen entspricht einer Änderung, d. h. einem UPDATE-Vorgang. Es können jedoch weiterhin Phantomleseprobleme auftreten. Weil das Phantom-Leseproblem der INSERT-Operation und nicht der UPDATE-Operation entspricht. Nicht wiederholbare Lesevorgänge und Dirty Reads werden vermieden, es kann jedoch manchmal zu Phantomlesevorgängen kommen. Dies kann mit „gemeinsamen Lesesperren“ und „exklusiven Schreibsperren“ erreicht werden.

set session transaction isolation level repeatable read;

4. Serialisierung und Serialisierung: Sorgen Sie für eine strikte Transaktionsisolierung. Dabei ist die seriell auszuführende Transaktion erforderlich. Transaktionen können nur nacheinander, nicht jedoch gleichzeitig ausgeführt werden. Wenn die Transaktionsserialisierung nicht allein durch „Sperren auf Zeilenebene“ erreicht werden kann, müssen andere Mechanismen verwendet werden, um sicherzustellen, dass auf die neu eingefügten Daten nicht von der Transaktion zugegriffen wird, die gerade den Abfragevorgang ausgeführt hat. Die Serialisierung ist die höchste Transaktionsisolierungsebene, hat aber auch die höchsten Kosten und eine sehr niedrige Leistung, weshalb sie selten verwendet wird. Auf dieser Ebene werden Transaktionen sequenziell ausgeführt, wodurch nicht nur Dirty Reads, nicht wiederholbare Lesevorgänge, sondern auch Phantom-Lesevorgänge vermieden werden.

set session transaction isolation level serializable;

Isolationsebene Dirty Read Nicht wiederholbares Lesen Phantom Read Nicht festgeschriebenes Lesen JA JA JA
Lesen Übernommen NEIN JA JA
Wiederholbares Lesen NEIN NEIN JA
Serialisierung NEIN NEIN NEIN

5. Vollständiges Beispiel inklusive Commit und Rollback vollständiges Beispiel

Prozedur löschen, falls pro_new existiert;
Trennzeichen;;
Prozedur pro_new erstellen (out rtn int)
beginnen
Deklariere err INT als Standard 0;
-- Wenn eine Ausnahme auftritt, wird sie automatisch verarbeitet und zurückgesetzt
Deklarieren Sie den Exit-Handler für SQLexception ROLLBACK. 
--Starten Sie die Transaktion mit dem Satz „Autocommit=0“.
Transaktion starten;
in Student (Name, Nummer) Werte (NULL, 2,3) einfügen;
-- set err = @@IDENTITY; -- = Holen Sie sich die Auto-Inkrement-ID der letzten Einfügung;
set err =last_insert_id(); -- Ruft die Auto-Increment-ID der letzten Einfügung ab
in Student einfügen (Name, Nummer) VALUEs ('ccc', Fehler);
-- Wenn beim Vorgang keine Anomalien vorliegen, führen Sie die Transaktion aus.
-- Setzen Sie den Rückgabewert auf 1
setze rtn=1;
setze Autocommit=1;
Ende
;;
Trennzeichen;;
setze @n=1;
rufe pro_new(@n) auf;
wähle @n;