SQLite Transaktionsverarbeitung

Einführung

In diesem Lab werden wir die Transaktionsverarbeitung von SQLite untersuchen, wobei der Schwerpunkt auf der Aufrechterhaltung der Datenintegrität durch die Verwendung von Transaktionen liegt. Wir werden lernen, wie man Transaktionen beginnt und abschließt (commit), um sicherzustellen, dass mehrere zusammengehörige Operationen als eine einzige, atomare Einheit behandelt werden.

Dieses Lab führt Sie durch den Prozess des Startens einer Transaktion mit BEGIN TRANSACTION, des Vornehmens von Änderungen an der Datenbank und des anschließenden dauerhaften Speicherns dieser Änderungen mit der COMMIT-Anweisung. Sie werden auch lernen, wie Sie Änderungen mit ROLLBACK rückgängig machen und SAVEPOINT für eine detailliertere Kontrolle verwenden können. Diese praktische Erfahrung wird Ihr Verständnis dafür festigen, wie Transaktionen Atomarität, Konsistenz, Isolation und Dauerhaftigkeit (ACID-Eigenschaften) in SQLite gewährleisten.

Erstellen einer Datenbank und Tabelle

In diesem ersten Schritt erstellen wir eine SQLite-Datenbank und eine Tabelle zum Speichern von Benutzerdaten. Dies bildet die Grundlage für die Untersuchung der Transaktionsverarbeitung in den folgenden Schritten.

Öffnen Sie zunächst Ihr Terminal in der LabEx VM. Ihr Standardpfad ist /home/labex/project.

Erstellen wir nun eine SQLite-Datenbank namens mydatabase.db. Führen Sie den folgenden Befehl aus, um die Datenbankdatei zu erstellen und das SQLite-Befehlszeilentool zu öffnen:

sqlite3 mydatabase.db

Sie sehen eine Eingabeaufforderung, die anzeigt, dass Sie sich jetzt in der SQLite-Shell befinden:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Erstellen Sie als Nächstes eine Tabelle namens users zum Speichern grundlegender Benutzerinformationen. Diese Tabelle hat drei Spalten: id (eine eindeutige Kennung), name und balance. Geben Sie den folgenden SQL-Befehl an der sqlite>-Eingabeaufforderung ein und drücken Sie die Eingabetaste:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

Dieser Befehl richtet die Tabelle users ein, wobei:

• id eine Ganzzahl (Integer) ist, die als Primärschlüssel (Primary Key) für jeden Benutzer dient.
• name ein Textfeld ist, das den Namen des Benutzers darstellt.
• balance eine reelle Zahl (Real Number) ist, die das Kontoguthaben des Benutzers darstellt.

Fügen Sie nun einige Anfangsdaten in die Tabelle users ein:

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

Diese Befehle fügen zwei Benutzer, Alice und Bob, mit einem Anfangsguthaben von 100.0 bzw. 200.0 hinzu.

Um zu bestätigen, dass die Daten korrekt hinzugefügt wurden, führen Sie diesen Befehl aus, um alle Datensätze in der Tabelle anzuzeigen:

SELECT * FROM users;

Erwartete Ausgabe (Expected Output):

1|Alice|100.0
2|Bob|200.0

Diese Ausgabe zeigt die id, name und balance für jeden Datensatz. Der Befehl SELECT * ruft alle Spalten aus der angegebenen Tabelle ab.

Eine Transaktion beginnen und bestätigen (Commit)

In diesem Schritt lernen Sie, wie Sie eine Transaktion beginnen und abschließen (commit). Transaktionen sind unerlässlich, um die Datenintegrität bei der Durchführung mehrerer zusammengehöriger Operationen sicherzustellen.

Um eine Transaktion zu starten, verwenden Sie den Befehl BEGIN TRANSACTION:

BEGIN TRANSACTION;

Dieser Befehl weist SQLite an, Änderungen zu verfolgen, diese aber erst dann dauerhaft auf die Datenbank anzuwenden, wenn Sie die Transaktion explizit abschließen (commit).

Übertragen wir nun 50.0 von Alice zu Bob:

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

Diese Befehle aktualisieren die Guthaben von Alice und Bob innerhalb der Transaktion. Das Guthaben von Alice wird um 50.0 verringert und das Guthaben von Bob um 50.0 erhöht.

Um die Änderungen zu speichern, verwenden Sie den Befehl COMMIT:

COMMIT;

Dieser Befehl schließt die Transaktion ab (commit), wodurch die Änderungen dauerhaft werden.

Überprüfen Sie die Änderungen, indem Sie die Tabelle erneut abfragen:

SELECT * FROM users;

Erwartete Ausgabe (Expected Output):

1|Alice|50.0
2|Bob|250.0

Diese Ausgabe bestätigt, dass die Transaktion erfolgreich abgeschlossen (committed) wurde und die Guthaben von Alice und Bob entsprechend aktualisiert wurden.

Eine Transaktion zurückrollen (Rollback)

In diesem Schritt lernen Sie, wie Sie den Befehl ROLLBACK verwenden, um Änderungen rückgängig zu machen, die während einer Transaktion vorgenommen wurden. Dies ist wichtig für die Fehlerbehandlung und die Gewährleistung der Datenintegrität.

Beginnen Sie zunächst eine neue Transaktion:

BEGIN TRANSACTION;

Versuchen wir nun, 100.0 von Alice zu Bob zu übertragen, aber simulieren wir diesmal eine Fehlersituation:

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

Da Alice nur 50.0 hat, führt das erste Update zu einem negativen Guthaben. Obwohl SQLite standardmäßig keine Einschränkungen (Constraints) erzwingt, simulieren wir das Auftreten eines Fehlers nach dieser Operation.

Um die Änderungen rückgängig zu machen, verwenden Sie den Befehl ROLLBACK:

ROLLBACK;

Dieser Befehl macht die Transaktion rückgängig (rollback), wodurch alle Änderungen seit der Anweisung BEGIN TRANSACTION rückgängig gemacht werden.

Überprüfen Sie, ob die Änderungen rückgängig gemacht wurden, indem Sie die Tabelle users abfragen:

SELECT * FROM users;

Erwartete Ausgabe (Expected Output):

1|Alice|50.0
2|Bob|250.0

Diese Ausgabe bestätigt, dass die Transaktion erfolgreich rückgängig gemacht (rolled back) wurde und die Guthaben von Alice und Bob unverändert bleiben.

Savepoints implementieren

In diesem Schritt lernen Sie, wie Sie Speicherpunkte (Savepoints) innerhalb von Transaktionen verwenden. Speicherpunkte ermöglichen es Ihnen, Zwischenpunkte innerhalb einer Transaktion zu erstellen, zu denen Sie ein Rollback durchführen können, ohne die gesamte Transaktion rückgängig zu machen.

Beginnen Sie zunächst eine neue Transaktion:

BEGIN TRANSACTION;

Erstellen Sie einen Speicherpunkt namens savepoint1:

SAVEPOINT savepoint1;

Übertragen wir nun 20.0 von Alice zu Bob:

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

Erstellen Sie einen weiteren Speicherpunkt namens savepoint2:

SAVEPOINT savepoint2;

Fügen wir einen neuen Benutzer namens 'Charlie' mit einem Anfangsguthaben von 300.0 hinzu:

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

Nehmen wir nun an, wir entscheiden, dass das Hinzufügen von Charlie ein Fehler war. Wir können ein Rollback zu savepoint1 durchführen, wodurch die INSERT-Anweisung und die Übertragung von 20.0 zwischen Alice und Bob rückgängig gemacht werden:

ROLLBACK TO SAVEPOINT savepoint1;

Überprüfen Sie die Änderungen nach dem Rollback zu savepoint1:

SELECT * FROM users;

Erwartete Ausgabe (Expected Output):

1|Alice|50.0
2|Bob|250.0

Sie sollten sehen, dass Charlie nicht in der Tabelle ist und die Guthaben von Alice und Bob wieder ihre Werte vor savepoint1 haben.

Übertragen wir nun 10.0 von Alice zu Bob:

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

Schließen Sie schließlich die Transaktion ab (commit):

COMMIT;

Einschränkung für negative Salden hinzufügen

In diesem Schritt fügen Sie der Tabelle users eine Einschränkung hinzu, um negative Salden zu verhindern. Dies trägt zur Datenintegrität bei, indem ungültige Transaktionen verhindert werden.

Da SQLite keine Einschränkungen an bestehende Tabellen mithilfe von ALTER TABLE ADD CONSTRAINT hinzufügen unterstützt, müssen wir die Tabelle neu erstellen. Beginnen Sie zunächst eine Transaktion, um sicherzustellen, dass alle Operationen atomar sind:

BEGIN TRANSACTION;

Erstellen Sie eine neue Tabelle mit der gleichen Struktur, aber mit einer CHECK-Einschränkung:

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL CHECK (balance >= 0)
);

Kopieren Sie alle Daten aus der alten Tabelle in die neue Tabelle:

INSERT INTO users_new SELECT * FROM users;

Löschen Sie die alte Tabelle:

DROP TABLE users;

Benennen Sie die neue Tabelle in den ursprünglichen Namen um:

ALTER TABLE users_new RENAME TO users;

Bestätigen Sie die Transaktion, um die Änderungen dauerhaft zu machen:

COMMIT;

Jetzt verfügt die Tabelle users über eine CHECK-Einschränkung, die sicherstellt, dass die Spalte balance immer größer oder gleich 0 ist.

Versuchen wir nun, 1000 von Alice an Bob zu überweisen, was die Einschränkung verletzen wird:

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';

Dieses Mal wird der UPDATE-Befehl einen Fehler auslösen, da die CHECK-Einschränkung verletzt wird. Sie erhalten eine Fehlermeldung wie: Fehler: CHECK-Einschränkung verletzt: balance >= 0.

Rückgängigmachen der Transaktion:

ROLLBACK;

Überprüfen Sie, ob die Änderungen rückgängig gemacht wurden:

SELECT * FROM users;

Erwartetes Ergebnis:

1|Alice|40.0
2|Bob|260.0

Dies bestätigt, dass der ROLLBACK die Datenbank erfolgreich in ihren ursprünglichen Zustand vor der fehlerhaften Transaktion zurückgesetzt hat.

Zusammenfassung

In diesem Lab haben Sie die Grundlagen der Transaktionsverarbeitung (transaction handling) in SQLite kennengelernt. Sie haben behandelt, wie man Transaktionen beginnt und abschließt (commit), Änderungen rückgängig macht (rollback), Speicherpunkte (savepoints) für eine detailliertere Steuerung implementiert und Beschränkungen (constraints) hinzufügt, um die Datenintegrität sicherzustellen. Diese Fähigkeiten sind entscheidend für die Entwicklung robuster und zuverlässiger Datenbankanwendungen.