Ausführliche Erläuterung der Konzepte und Verwendung von MySQL-Transaktionen

Dieser Artikel erläutert anhand von Beispielen das Konzept und die Verwendung von MySQL-Transaktionen. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Der Begriff der Affären

Eine MySQL-Transaktion besteht aus einer oder mehreren Datenbankoperationen, die entweder alle erfolgreich ausgeführt wurden oder alle fehlgeschlagen sind und zurückgesetzt wurden.

Transaktionen werden über Transaktionsprotokolle implementiert, die Redo- und Undo-Protokolle umfassen.

Der Status der Transaktion

aktiv

Wenn der der Transaktion entsprechende Datenbankvorgang ausgeführt wird, befindet sich die Transaktion in einem aktiven Zustand.

teilweise begangen

Wenn die letzte Operation in einer Transaktion ausgeführt wird, die Auswirkungen jedoch nicht auf die Festplatte geschrieben werden, weil alle Operationen im Speicher ausgeführt werden, bezeichnen wir die Transaktion als teilweise festgeschrieben.

fehlgeschlagen

Wenn sich eine Transaktion in einem aktiven oder teilweise festgeschriebenen Zustand befindet, können einige Fehler auftreten (Datenbankfehler, Betriebssystemfehler oder direkte Stromausfälle usw.) und die Ausführung kann nicht fortgesetzt werden, oder die Ausführung der aktuellen Transaktion wird manuell gestoppt. Wir sagen, dass sich die Transaktion in einem fehlgeschlagenen Zustand befindet.

abgebrochen

Wenn eine Transaktion während der Ausführung fehlschlägt, machen wir die Auswirkungen der fehlgeschlagenen Transaktion auf die aktuelle Datenbank rückgängig. Diesen Rückgängig-Prozess nennen wir Rollback.

Wenn der Rollback-Vorgang abgeschlossen ist, d. h. die Datenbank in den Zustand vor der Ausführung der Transaktion zurückversetzt wird, bezeichnen wir die Transaktion als abgebrochenen Zustand.

engagiert

Wenn eine Transaktion im teilweise festgeschriebenen Zustand alle geänderten Daten mit der Festplatte synchronisiert hat, können wir sagen, dass die Transaktion sich im festgeschriebenen Zustand befindet.

Wie Sie der Abbildung entnehmen können, gilt der Lebenszyklus einer Transaktion erst dann als beendet, wenn sich die Transaktion im festgeschriebenen oder abgebrochenen Zustand befindet. Bei einer festgeschriebenen Transaktion werden die von der Transaktion an der Datenbank vorgenommenen Änderungen dauerhaft wirksam. Bei einer Transaktion im abgebrochenen Zustand werden alle von der Transaktion an der Datenbank vorgenommenen Änderungen auf den Zustand vor der Ausführung der Transaktion zurückgesetzt.

Die Rolle von Transaktionen

Transaktionen dienen in erster Linie dazu, die Datenkonsistenz bei komplexen Datenbankoperationen sicherzustellen, insbesondere bei gleichzeitigem Datenzugriff.
MySQL-Transaktionen werden hauptsächlich zur Verarbeitung von Daten mit großen Operationsvolumina und hoher Komplexität verwendet.

Merkmale der Transaktionen

Atomarität (auch Unteilbarkeit genannt)

Die Datenoperationen einer Transaktion werden entweder alle erfolgreich ausgeführt oder alle sind fehlgeschlagen und werden auf den Zustand vor der Ausführung zurückgesetzt, als wäre die Transaktion nie ausgeführt worden.

Isolation (auch Unabhängigkeit genannt)

Mehrere Transaktionen sind voneinander isoliert und beeinflussen sich nicht gegenseitig. Die Datenbank erlaubt mehrere gleichzeitige Transaktionen zum gleichzeitigen Lesen, Schreiben und Ändern ihrer Daten. Durch Isolierung können Dateninkonsistenzen aufgrund plattformübergreifender Ausführung bei gleichzeitiger Ausführung mehrerer Transaktionen vermieden werden.

Vier Isolationszustände:
1. Lesen Sie ungebunden
2. Lesen Sie engagiert
3. Wiederholbares Lesen
4. Serialisierbar

Konsistenz

Vor und nach dem Transaktionsvorgang bleiben die Daten im gleichen Zustand und die Integrität der Datenbank wird nicht beeinträchtigt.
Atomarität und Isolation haben einen entscheidenden Einfluss auf die Konsistenz.

Haltbarkeit

Wenn der Transaktionsvorgang abgeschlossen ist, werden die Daten zur dauerhaften Speicherung auf die Festplatte geschrieben und gehen auch bei einem Systemausfall nicht verloren.

Transaktionssyntax

Daten

#Erstellen Sie eine Datentabelle:
Tabelle „Konto“ erstellen(
  -> id int(10) auto_increment,
  -> Name varchar(30),
  -> Saldo int(10),
  ->Primärschlüssel (ID));
#Daten einfügen:
in Konto (Name, Guthaben) Werte einfügen (,Kontostand',100), (,Kontostand',10);

mysql> wähle * vom Konto aus;
+----+--------------+---------+
| ID | Name | Guthaben |
+----+--------------+---------+
| 1 | Lao Wangs Frau | 100 |
| 2 | Lao Wang | 10 |
+----+--------------+---------+

Lao Wangs Frau hat 100 Yuan auf ihrem WeChat-Konto, das sie verwendet, um Lao Wang jeden Monat Taschengeld zu geben. Je besser seine Leistung, desto mehr bekommt er. Lao Wang hat auch sein eigenes kleines Sparschwein. Er hat bereits 10 Yuan als Taschengeld gespart, hahaha.

beginnen

Transaktionsstartmodus 1

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> Transaktionsvorgang SQL......

Transaktion starten [Modifikator]

Modifikatoren:
1. read only //Nur lesen 2. read write //Lesen und Schreiben standardmäßig 3. WITH CONSISTENT SNAPSHOT //Konsistentes Lesen

Transaktionsstartmodus 2

mysql> Transaktion starten, nur lesen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> Transaktionsvorgang SQL......

#Wenn schreibgeschützt eingestellt ist, wird bei Änderung der Daten ein Fehler gemeldet:

mysql> Transaktion starten, nur lesen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Konto aktualisieren, Guthaben festlegen = Guthaben + 30, wobei ID = 2;
FEHLER 1792 (25006): Anweisung kann in einer NUR-LESE-Transaktion nicht ausgeführt werden.

begehen

Die Transaktion wird festgeschrieben und bei Erfolg auf die Festplatte geschrieben.

mysql> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Rollback

Die Transaktion wird zurückgesetzt und in den Zustand vor der Transaktionsoperation zurückversetzt.

mysql> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

Es sollte hier betont werden, dass die ROLLBACK-Anweisung nur von unseren Programmierern verwendet wird, um eine Transaktion manuell zurückzusetzen. Wenn während der Ausführung der Transaktion Fehler auftreten und die Ausführung nicht fortgesetzt werden kann, wird die Transaktion selbst automatisch zurückgesetzt.

Vollständiges Übermittlungsbeispiel

Im Januar erbrachte Lao Wang sehr gute Leistungen und seine Frau gab ihm als Belohnung 20 Yuan Taschengeld.

Ausführungsschritte:
1. Daten aus dem Konto von Lao Wangs Frau lesen
2. 20 Yuan vom Konto von Lao Wangs Frau abziehen
3. Daten aus Lao Wangs Konto lesen
4. Fügen Sie 20 Yuan zu Lao Wangs Konto hinzu
5. Die Ausführungsübermittlung ist erfolgreich
6. Zu diesem Zeitpunkt hat Wangs Frau nur 80 Yuan auf ihrem Konto, während Wang 30 Yuan auf seinem Konto hat. Wang ist sehr glücklich.

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> Konto aktualisieren, Guthaben festlegen=Balance-20, wobei ID = 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> Konto aktualisieren, Saldo festlegen=Saldo+20, wobei ID = 2;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> festschreiben;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

#Kontostand:
mysql> wähle * vom Konto aus;
+----+--------------+---------+
| ID | Name | Guthaben |
+----+--------------+---------+
| 1 | Lao Wangs Frau | 80 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

Vollständiges Rollback-Beispiel

Im Februar hatte Lao Wang sehr gute Leistungen gezeigt, bestand darauf, den Haushalt zu erledigen und mit dem Hund spazieren zu gehen. Lao Wangs Frau wollte ihm 25 Yuan Taschengeld geben, aber Lao Wang konnte das Lob einfach nicht ertragen. Als Lao Wangs Frau Lao Wang das Taschengeld überwies, sah sie plötzlich eine WeChat-Nachricht von einem kleinen Mädchen auf Lao Wangs Telefon auf dem Tisch: Lieber Bruder Wang … Lao Wangs Frau war sehr wütend und zog die Überweisung wütend zurück und stornierte das Taschengeld für diesen Monat.

Ausführungsschritte:
1. Daten aus dem Konto von Lao Wangs Frau lesen
2. Ziehen Sie 25 Yuan vom Konto von Lao Wangs Frau ab
3. Daten aus Lao Wangs Konto lesen
4. Fügen Sie 25 Yuan zu Lao Wangs Konto hinzu
5. Zu diesem Zeitpunkt zieht Lao Wangs Frau die vorherige Operation zurück
6. Zu diesem Zeitpunkt sind die Kontostände von Herrn Wang und seiner Frau dieselben wie vor der Operation.

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Konto aktualisieren, Guthaben festlegen=Balance-25, wobei ID = 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> Konto aktualisieren, Saldo festlegen=Saldo+25, wobei ID = 2;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> Rollback;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

#Kontostand:
mysql> wähle * vom Konto aus;
+----+--------------+---------+
| ID | Name | Guthaben |
+----+--------------+---------+
| 1 | Lao Wangs Frau | 80 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

Transaktionsunterstützte Speicher-Engines

1. InnoDB
2. NDB

Bei Speicher-Engines, die keine Transaktionen unterstützen, wie etwa MyISAM, werden die Transaktionen nicht wirksam und die SQL-Anweisungen werden automatisch festgeschrieben. Daher ist ein Rollback bei Speicher-Engines, die keine Transaktionen unterstützen, ungültig.

Tabelle tb1 erstellen(
  -> id int(10) auto_increment,
  -> Name varchar(30),
  -> Primärschlüssel (ID)
  ->)Engine=Myisam-Zeichensatz=utf8mb4;
  
mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> einfügen in tb1(Name) Werte('Tom');
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> wähle * aus tb1;
+----+------+
| Ich würde | Name |
+----+------+
| 1 | Tom |
+----+------+
1 Zeile im Satz (0,00 Sek.)

mysql> rollback; //Rollback ist ungültig. Abfrage OK, 0 Zeilen betroffen, 1 Warnung (0,00 Sek.)

mysql> wähle * aus tb1;
+----+------+
| Ich würde | Name |
+----+------+
| 1 | Tom |
+----+------+
1 Zeile im Satz (0,00 Sek.)

Festlegen und Anzeigen von Transaktionen

# Überprüfen Sie den Status der Transaktionseröffnung:
mysql> VARIABLEN WIE „autocommit“ ANZEIGEN;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| Autocommit | EIN |
+---------------+-------+

Standardmäßig werden Transaktionen automatisch festgeschrieben, und jede SQL-Anweisung wird automatisch festgeschrieben.

Wenn Sie zu diesem Zeitpunkt eine Transaktion ausführen müssen, müssen Sie diese explizit starten (beginnen oder Transaktion starten) und festschreiben (commit) oder zurücksetzen (rollback).

Wenn die Einstellung auf „AUS“ gesetzt ist, wird die Transaktion erst dann tatsächlich ausgeführt, wenn ein Commit- oder Rollback-Vorgang durchgeführt wird.

Deaktivieren des automatischen Übermittlungsmodus

Der erste

Starten Sie eine Transaktion explizit mit der Anweisung START TRANSACTION oder BEGIN.

Der Zweite

Setzen Sie den Wert der Systemvariablen „Autocommit“ auf „OFF“.

Setzen Sie Autocommit auf OFF.

Implizite Unterwerfung

Wenn wir eine Transaktion mit der Anweisung START TRANSACTION oder BEGIN starten oder den Wert der Systemvariable autocommit auf OFF setzen, wird die Transaktion nicht automatisch festgeschrieben. Wenn wir jedoch bestimmte Anweisungen eingeben, wird die Transaktion stillschweigend festgeschrieben, genau wie wir die Anweisung COMMIT eingegeben haben. Diese Situation, in der die Transaktion aufgrund einiger spezieller Anweisungen festgeschrieben wird, wird als implizites Festschreiben bezeichnet.

Die Datendefinitionssprache (DDL) wird zum Definieren oder Ändern von Datenbankobjekten verwendet.

Die sogenannten Datenbankobjekte beziehen sich auf Datenbanken, Tabellen, Ansichten, gespeicherte Prozeduren usw. Wenn wir CREATE, ALTER, DROP und andere Anweisungen verwenden, um diese sogenannten Datenbankobjekte zu ändern, wird die Transaktion, zu der die vorherige Anweisung gehört, implizit festgeschrieben.

BEGINNEN;
SELECT ... # Eine Anweisung in einer Transaktion UPDATE ... # Eine Anweisung in einer Transaktion ... # Andere Anweisungen in einer Transaktion CREATE TABLE ... # Diese Anweisung führt implizit ein Commit für die Transaktion aus, zu der die vorherige Anweisung gehört.

Tabellen in der MySQL-Datenbank implizit verwenden oder ändern

Verwenden oder ändern Sie implizit Tabellen in der MySQL-Datenbank.

Wenn wir Anweisungen wie ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD usw. verwenden, wird auch die Transaktion, zu der die vorherige Anweisung gehört, implizit festgeschrieben.

Transaktionssteuerungs- oder Sperranweisungen

Transaktionssteuerungs- oder Sperranweisungen.

Wenn wir die Anweisung START TRANSACTION oder BEGIN verwenden, um eine andere Transaktion zu starten, bevor eine Transaktion festgeschrieben oder zurückgesetzt wird, wird die vorherige Transaktion implizit festgeschrieben.

BEGINNEN;
SELECT ... # Eine Anweisung in einer Transaktion UPDATE ... # Eine Anweisung in einer Transaktion ... # Andere Anweisungen in einer Transaktion BEGIN; # Diese Anweisung führt implizit ein Commit für die Transaktion aus, zu der die vorherige Anweisung gehört.

Oder wenn der aktuelle Wert der Systemvariablen „Autocommit“ AUS ist und wir ihn manuell auf EIN stellen, wird die Transaktion, zu der die vorherige Anweisung gehört, ebenfalls implizit festgeschrieben.

Alternativ dazu wird durch die Verwendung von Sperranweisungen wie LOCK TABLES und UNLOCK TABLES implizit die Transaktion festgeschrieben, zu der die vorherige Anweisung gehört.

Anweisung zum Laden von Daten

Wenn wir beispielsweise die Anweisung LOAD DATA verwenden, um Daten stapelweise in die Datenbank zu importieren, wird auch die Transaktion, zu der die vorherige Anweisung gehört, implizit festgeschrieben.

Einige Aussagen zur MySQL-Replikation

Bei Verwendung von Anweisungen wie START SLAVE, STOP SLAVE, RESET SLAVE und CHANGE MASTER TO wird auch die Transaktion, zu der die vorherige Anweisung gehört, implizit festgeschrieben.

Einige andere Aussagen

Durch die Verwendung von Anweisungen wie ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE und RESET wird auch implizit die Transaktion festgeschrieben, zu der die vorherige Anweisung gehört.

Transaktions-Sicherungspunkte

Konzept

Indem wir in die der Transaktion entsprechende Datenbankanweisung einige Punkte einfügen, können wir angeben, zu welchem ​​Punkt beim Aufruf der ROLLBACK-Anweisung gerollt werden soll, anstatt zum ursprünglichen Ursprung zurückzukehren.

Mit Transaktionsspeicherpunkten müssen wir uns bei der Durchführung komplexer Transaktionsvorgänge keine Gedanken über die Rückkehr zum ursprünglichen Zustand machen, wenn ein Fehler auftritt, als würden wir über Nacht in die Zeit vor der Befreiung zurückkehren.

Syntax verwenden

1. SAVEPOINT Sicherungspunktname; // Sicherungspunkt markieren
2. ROLLBACK TO [SAVEPOINT] savepoint name; //Zurück zu einem Savepoint
3. RELEASE SAVEPOINT Savepoint-Name; // löschen

mysql> beginnen;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Konto aktualisieren, Guthaben festlegen=Balance-20, wobei ID = 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> Sicherungspunktaktion1;
Abfrage OK, 0 Zeilen betroffen (0,02 Sek.)

mysql> wähle * vom Konto aus;
+----+--------------+---------+
| ID | Name | Guthaben |
+----+--------------+---------+
| 1 | Lao Wangs Frau | 60 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

mysql> Konto aktualisieren, Saldo festlegen=Saldo+30, wobei ID = 2;
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0

mysql> Rollback zu Aktion1; //Zurück zum Sicherungspunkt von Aktion1. Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle * vom Konto aus;
+----+--------------+---------+
| ID | Name | Guthaben |
+----+--------------+---------+
| 1 | Lao Wangs Frau | 60 |
| 2 | Lao Wang | 30 |
+----+--------------+---------+

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Zusammenfassung der Kenntnisse zu MySQL-Transaktionsvorgängen“, „Zusammenfassung der Kenntnisse zu MySQL-Indexvorgängen“, „Zusammenfassung der häufig verwendeten MySQL-Funktionen“, „Zusammenfassung der Kenntnisse zu MySQL-Protokollvorgängen“, „Zusammenfassung der Kenntnisse zu MySQL-gespeicherten Prozeduren“ und „Zusammenfassung der Kenntnisse zu MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.