MySQL Transaktionen und Datenintegrität

Einführung

In diesem Lab lernen Sie, wie Sie MySQL-Transaktionen zur Gewährleistung der Datenintegrität verwenden. Transaktionen gruppieren eine Reihe von SQL-Operationen zu einer einzigen, alles-oder-nichts-Einheit der Arbeit. Dies ist unerlässlich für Aufgaben wie Finanztransfers, bei denen alle Schritte erfolgreich abgeschlossen werden müssen.

Sie üben das Starten einer Transaktion mit BEGIN, das dauerhafte Speichern von Änderungen mit COMMIT, das Rückgängigmachen von Änderungen mit ROLLBACK und das Durchführen von Teil-Rollbacks mit SAVEPOINT. Am Ende dieses Labs werden Sie verstehen, wie Sie Datenbankoperationen sicher und zuverlässig verwalten.

Datenbank und Tabellen einrichten

Bevor Sie mit Transaktionen arbeiten, müssen Sie eine Datenbank und eine Tabelle einrichten. In diesem Schritt erstellen Sie eine Datenbank namens bank_db und eine Tabelle accounts, um Bankoperationen zu simulieren.

Öffnen Sie zuerst das Terminal auf Ihrem Desktop.

Verbinden Sie sich als Benutzer root mit dem MySQL-Server. Diese Laborumgebung erfordert kein Passwort für den root-Benutzer.

sudo mysql -u root

Sobald Sie verbunden sind, sehen Sie die MySQL-Eingabeaufforderung (mysql>), die anzeigt, dass Sie nun SQL-Befehle ausführen können.

Erstellen Sie die Datenbank bank_db und wechseln Sie für die aktuelle Sitzung zu ihr. Die Klausel IF NOT EXISTS verhindert einen Fehler, falls die Datenbank bereits existiert.

CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

Erstellen Sie als Nächstes die Tabelle accounts. Diese Tabelle speichert Kontoinformationen, einschließlich einer ID, einer Kontonummer und eines Saldos.

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

Fügen Sie nun zwei Beispielkonten in die Tabelle accounts ein. Diese dienen als unsere Startdaten.

INSERT INTO accounts (account_number, balance) VALUES
('ACC001', 1000.00),
('ACC002', 500.00);

Um zu überprüfen, ob die Tabelle und die Daten korrekt erstellt wurden, führen Sie die folgende Abfrage aus:

SELECT * FROM accounts;

Sie sollten die beiden gerade eingefügten Konten sehen. Die Ausgabe wird wie folgt aussehen:

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         | 1000.00 |
|  2 | ACC002         |  500.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Nachdem die Datenbank und die Tabelle vorbereitet sind, sind Sie bereit, mehr über Transaktionen zu erfahren.

Erfolgreiche Transaktion mit BEGIN und COMMIT erstellen

Eine Transaktion ist eine Abfolge von Operationen, die als eine einzige logische Einheit ausgeführt werden. Um Änderungen dauerhaft zu machen, müssen Sie sie mit COMMIT bestätigen. In diesem Schritt simulieren Sie eine erfolgreiche Banküberweisung von ACC001 nach ACC002.

Sie sollten sich noch in der MySQL-Shell aus dem vorherigen Schritt befinden.

Starten Sie zuerst eine neue Transaktion mit dem Befehl BEGIN. Alle nachfolgenden Befehle sind Teil dieser Transaktion, bis Sie COMMIT oder ROLLBACK ausführen.

BEGIN;

Führen Sie nun die beiden Operationen für die Überweisung durch: ziehen Sie 100 $ von ACC001 ab und fügen Sie 100 $ zu ACC002 hinzu.

UPDATE accounts SET balance = balance - 100.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100.00 WHERE account_number = 'ACC002';

An diesem Punkt sind die Änderungen nur innerhalb Ihrer aktuellen Sitzung sichtbar. Sie können den temporären Zustand der Konten überprüfen:

SELECT * FROM accounts;

Die Ausgabe zeigt die aktualisierten Salden, aber sie sind noch nicht dauerhaft gespeichert.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Da beide Operationen erfolgreich waren, können Sie die Änderungen durch Bestätigen der Transaktion dauerhaft machen.

COMMIT;

Der Befehl COMMIT speichert alle während der Transaktion vorgenommenen Änderungen. Die Überweisung ist nun abgeschlossen und für alle anderen Datenbankverbindungen sichtbar. Sie können dies überprüfen, indem Sie die Tabelle erneut abfragen. Die Salden bleiben bei 900 $ und 600 $.

Transaktion mit ROLLBACK rückgängig machen

Manchmal schlägt eine Operation innerhalb einer Transaktion fehl, oder Sie entscheiden sich, sie abzubrechen. Der Befehl ROLLBACK verwirft alle Änderungen, die während der aktuellen Transaktion vorgenommen wurden, und stellt die Datenbank in ihren Zustand vor Beginn der Transaktion wieder her.

Beginnen wir eine neue Transaktion, um ein neues Konto hinzuzufügen.

BEGIN;

Fügen Sie nun ein neues Konto ACC003 in die Tabelle accounts ein.

INSERT INTO accounts (account_number, balance) VALUES ('ACC003', 150.00);

Überprüfen Sie die Tabelle, um das neue Konto innerhalb der Transaktion zu sehen.

SELECT * FROM accounts;

Sie sehen drei aufgelistete Konten.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
|  3 | ACC003         |  150.00 |
+----+----------------+---------+
3 rows in set (0.00 sec)

Stellen Sie sich nun vor, Sie stellen fest, dass dieses Konto nicht hätte hinzugefügt werden dürfen. Anstatt COMMIT auszuführen, können Sie die gesamte Transaktion mit ROLLBACK abbrechen.

ROLLBACK;

Dieser Befehl macht die INSERT-Operation rückgängig. Um dies zu überprüfen, fragen Sie die Tabelle accounts erneut ab.

SELECT * FROM accounts;

Das Konto ACC003 ist verschwunden, und die Tabelle ist wieder in dem Zustand, in dem sie sich befand, bevor Sie BEGIN eingegeben haben.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

ROLLBACK ist ein wichtiges Werkzeug zur Aufrechterhaltung der Datenintegrität bei Fehlern.

SAVEPOINT für partielle Rollbacks verwenden

Bei langen Transaktionen möchten Sie möglicherweise nur einen Teil der Arbeit rückgängig machen, anstatt die gesamte Transaktion. SAVEPOINT ermöglicht es Ihnen, innerhalb einer Transaktion eine Markierung zu setzen, zu der Sie später zurückrollen können.

Beginnen wir eine neue Transaktion.

BEGIN;

Aktualisieren Sie zuerst den Saldo von ACC001, indem Sie ihn um 50 $ erhöhen.

UPDATE accounts SET balance = balance + 50.00 WHERE account_number = 'ACC001';

Erstellen Sie nun einen Savepoint. Dies dient als Lesezeichen für den aktuellen Zustand der Transaktion.

SAVEPOINT after_update;

Führen Sie als Nächstes eine weitere Operation durch: Fügen Sie ein neues Konto ACC004 ein.

INSERT INTO accounts (account_number, balance) VALUES ('ACC004', 300.00);

Nehmen wir an, die Einfügung von ACC004 war ein Fehler, aber die Aktualisierung von ACC001 war korrekt. Sie können zum Savepoint zurückrollen, um nur die INSERT-Anweisung rückgängig zu machen.

ROLLBACK TO after_update;

Dieser Befehl setzt die Transaktion auf den Zustand zurück, in dem sie sich befand, als Sie den Savepoint after_update erstellt haben. Die Einfügung von ACC004 wird rückgängig gemacht, aber die Aktualisierung von ACC001 bleibt bestehen.

Fragen Sie die Tabelle ab, um dies zu bestätigen. ACC004 sollte verschwunden sein, aber der Saldo von ACC001 sollte aktualisiert sein.

SELECT * FROM accounts;

Die Ausgabe sollte ACC001 mit einem Saldo von 950,00 $ und kein ACC004 anzeigen.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  950.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Da Sie die Aktualisierung von ACC001 beibehalten möchten, können Sie nun den verbleibenden Teil der Transaktion bestätigen.

COMMIT;

Sie haben erfolgreich einen Savepoint verwendet, um ein partielles Rollback durchzuführen. Sie können die MySQL-Shell nun verlassen.

exit;

Zusammenfassung

In diesem Lab haben Sie die Grundlagen von MySQL-Transaktionen zur Aufrechterhaltung der Datenintegrität gelernt. Sie haben den vollständigen Transaktionslebenszyklus geübt, vom Starten einer Transaktion mit BEGIN bis zur Finalisierung mit COMMIT oder deren Abbruch mit ROLLBACK.

Sie haben erfolgreich eine Banküberweisung simuliert und sichergestellt, dass mehrere Operationen als eine einzige atomare Einheit behandelt wurden. Sie haben auch gelernt, wie Sie mit SAVEPOINT eine feinere Kontrolle über komplexe Transaktionen erlangen, indem Sie partielle Rollbacks ermöglichen. Diese Fähigkeiten sind entscheidend für die Entwicklung robuster und zuverlässiger Datenbankanwendungen, bei denen Datenkonsistenz von größter Bedeutung ist.