PostgreSQL Transaktionsmanagement

Einführung

In diesem Lab werden Sie das Transaktionsmanagement von PostgreSQL erkunden, ein entscheidender Aspekt zur Gewährleistung der Datenintegrität. Sie lernen, wie Sie Transaktionen beginnen und bestätigen (commit), wobei Sie eine Reihe von Operationen als eine einzige Arbeitseinheit behandeln. Sie lernen auch, wie Sie fehlgeschlagene Transaktionen rückgängig machen (Rollback), Isolationsstufen (Isolation Levels) festlegen und Sperren (Locks) mit gleichzeitigen Aktualisierungen simulieren.

Skills Graph

Eine Transaktion beginnen und bestätigen (Commit)

In diesem Schritt lernen Sie, wie Sie eine Transaktion in PostgreSQL beginnen und bestätigen (commit). Transaktionen gewährleisten die Datenintegrität, indem sie eine Reihe von Operationen als eine einzige Arbeitseinheit behandeln. Wenn eine Operation innerhalb der Transaktion fehlschlägt, wird die gesamte Transaktion rückgängig gemacht (Rollback), wodurch Teilaktualisierungen verhindert und die Konsistenz gewahrt wird.

Verbinden Sie sich zunächst als Benutzer postgres mit der PostgreSQL-Datenbank. Öffnen Sie ein Terminal und verwenden Sie den folgenden Befehl:

sudo -u postgres psql

Sie sollten nun die Eingabeaufforderung postgres=# sehen.

Erstellen Sie als Nächstes eine Tabelle namens accounts, um Transaktionen zu demonstrieren.

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

Fügen Sie einige Anfangsdaten in die Tabelle accounts ein.

INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);

Beginnen wir nun eine Transaktion mit dem Befehl BEGIN.

BEGIN;

Übertragen Sie innerhalb der Transaktion 20 $ von Alices Konto auf Bobs Konto.

UPDATE accounts SET balance = balance - 20.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 20.00 WHERE name = 'Bob';

Um diese Änderungen dauerhaft zu machen, bestätigen (commit) Sie die Transaktion mit dem Befehl COMMIT.

COMMIT;

Überprüfen Sie, ob die Transaktion erfolgreich war, indem Sie die Tabelle accounts abfragen.

SELECT * FROM accounts;

Sie sollten sehen, dass Alices Guthaben um 20 $ gesunken ist und Bobs Guthaben um 20 $ gestiegen ist.

Beenden Sie abschließend die psql-Shell.

\q

Eine fehlgeschlagene Transaktion rückgängig machen (Rollback)

In diesem Schritt lernen Sie, wie Sie eine fehlgeschlagene Transaktion in PostgreSQL rückgängig machen (Rollback). Das Rückgängigmachen einer Transaktion ist entscheidend, wenn während einer Reihe von Datenbankoperationen ein Fehler auftritt, um sicherzustellen, dass die Datenbank in einem konsistenten Zustand bleibt.

Sie sollten noch aus dem vorherigen Schritt mit der PostgreSQL-Datenbank verbunden sein. Wenn nicht, verbinden Sie sich erneut mit:

sudo -u postgres psql

Beginnen wir eine neue Transaktion.

BEGIN;

Innerhalb dieser Transaktion werden wir eine Operation versuchen, die absichtlich fehlschlagen wird. Versuchen wir, einen doppelten Primärschlüssel einzufügen. Finden wir zunächst den nächsten verfügbaren id-Wert.

SELECT MAX(id) FROM accounts;

Nehmen wir an, das Ergebnis ist 2. Versuchen Sie nun, ein neues Konto mit id = 1 einzufügen, das bereits existiert.

INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);

Dieser Befehl führt zu einem ERROR: duplicate key value violates unique constraint "accounts_pkey" Fehler.

Da innerhalb der Transaktion ein Fehler aufgetreten ist, machen Sie die Transaktion rückgängig (Rollback), um alle vorgenommenen Änderungen zu verwerfen. Verwenden Sie den Befehl ROLLBACK.

ROLLBACK;

Überprüfen Sie, ob der ROLLBACK erfolgreich war, indem Sie die Tabelle accounts abfragen.

SELECT * FROM accounts;

Sie sollten sehen, dass die Tabelle immer noch nur die Konten von Alice und Bob mit ihren Guthaben vom Ende von Schritt 1 enthält. Die fehlgeschlagene INSERT-Operation wurde erfolgreich rückgängig gemacht (Rollback).

Beenden Sie abschließend die psql-Shell.

\q

Isolationslevel festlegen und testen

In diesem Schritt lernen Sie die Transaktionsisolationslevel (transaction isolation levels) in PostgreSQL kennen und erfahren, wie Sie diese festlegen und testen können. Isolationslevel steuern den Grad, in dem gleichzeitige Transaktionen voneinander isoliert sind. Höhere Isolationslevel bieten einen besseren Schutz vor Datenbeschädigung, können aber die Parallelität (concurrency) verringern.

Sie sollten noch aus dem vorherigen Schritt mit der PostgreSQL-Datenbank verbunden sein. Wenn nicht, verbinden Sie sich erneut mit:

sudo -u postgres psql

Öffnen Sie zwei separate Terminalfenster. Stellen Sie in jedem Terminal eine Verbindung zur PostgreSQL-Datenbank als Benutzer postgres her. Sie sollten zwei postgres=#-Eingabeaufforderungen haben.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

Setzen Sie in Terminal 1 das Isolationslevel auf READ COMMITTED (obwohl dies die Standardeinstellung ist, werden wir es zur Demonstration explizit festlegen).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Beginnen Sie dann eine Transaktion.

BEGIN;

Lesen Sie in Terminal 1 Alices Guthaben.

SELECT balance FROM accounts WHERE name = 'Alice';

Notieren Sie sich das Guthaben. Beginnen Sie nun in Terminal 2 eine Transaktion und aktualisieren Sie Alices Guthaben.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;

Lesen Sie in Terminal 1 Alices Guthaben erneut.

SELECT balance FROM accounts WHERE name = 'Alice';

Da das Isolationslevel READ COMMITTED ist, sehen Sie das aktualisierte Guthaben (90.00), das von Terminal 2 bestätigt (committed) wurde.

Testen wir nun das Isolationslevel REPEATABLE READ. Machen Sie in Terminal 1 die aktuelle Transaktion rückgängig (Rollback) und setzen Sie das Isolationslevel auf REPEATABLE READ.

ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

Lesen Sie in Terminal 1 Alices Guthaben erneut.

SELECT balance FROM accounts WHERE name = 'Alice';

Notieren Sie sich das Guthaben. Beginnen Sie nun in Terminal 2 eine Transaktion und aktualisieren Sie Alices Guthaben erneut.

BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;

Lesen Sie in Terminal 1 Alices Guthaben erneut.

SELECT balance FROM accounts WHERE name = 'Alice';

Da das Isolationslevel REPEATABLE READ ist, sehen Sie immer noch das ursprüngliche Guthaben vom Beginn der Transaktion, auch wenn Terminal 2 einen neuen Wert bestätigt (committed) hat.

Bestätigen (commit) Sie abschließend die Transaktion in Terminal 1.

COMMIT;

Wenn Sie nun Alices Guthaben in Terminal 1 erneut lesen, sehen Sie den zuletzt bestätigten (committed) Wert (100.00).

Beenden Sie beide psql-Shells.

\q

Sperren mit gleichzeitigen Aktualisierungen simulieren

In diesem Schritt simulieren Sie Sperren (locks) mit gleichzeitigen Aktualisierungen (concurrent updates) in PostgreSQL. Sperren sind Mechanismen, die verwendet werden, um zu verhindern, dass sich gleichzeitige Transaktionen gegenseitig beeinträchtigen, wodurch die Datenintegrität sichergestellt wird.

Sie sollten noch aus dem vorherigen Schritt mit der PostgreSQL-Datenbank verbunden sein. Wenn nicht, verbinden Sie sich erneut mit:

sudo -u postgres psql

Öffnen Sie zwei separate Terminalfenster. Stellen Sie in jedem Terminal eine Verbindung zur PostgreSQL-Datenbank als Benutzer postgres her. Sie sollten zwei postgres=#-Eingabeaufforderungen haben.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

Beginnen Sie in Terminal 1 eine Transaktion und aktualisieren Sie Alices Guthaben. Verwenden Sie unbedingt SELECT ... FOR UPDATE, um die Zeile zu sperren (lock).

BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;

Dieser Befehl ruft Alices Guthaben ab und setzt eine Sperre (lock) auf die Zeile, wodurch verhindert wird, dass andere Transaktionen diese ändern, bis diese Transaktion bestätigt (committed) oder rückgängig gemacht (rolled back) wird.

Beginnen Sie in Terminal 2 eine Transaktion und versuchen Sie, Alices Guthaben zu aktualisieren.

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';

Beachten Sie, dass dieser Befehl in Terminal 2 scheinbar hängen bleibt. Dies liegt daran, dass er auf die Sperre (lock) wartet, die von Terminal 1 gehalten wird, bis diese freigegeben wird.

Bestätigen (commit) Sie nun in Terminal 1 die Transaktion.

COMMIT;

Nachdem die Transaktion in Terminal 1 bestätigt (committed) wurde, wird der UPDATE-Befehl in Terminal 2 fortgesetzt.

Bestätigen (commit) Sie in Terminal 2 die Transaktion.

COMMIT;

Fragen Sie nun in einem der beiden Terminals die Tabelle accounts ab, um die Änderungen zu überprüfen.

SELECT * FROM accounts;

Sie sollten sehen, dass Alices Guthaben durch die Transaktion in Terminal 2 aktualisiert wurde, nachdem Terminal 1 die Sperre (lock) freigegeben hat.

Beenden Sie abschließend beide psql-Shells.

\q

Dieses Beispiel zeigt, wie SELECT ... FOR UPDATE verwendet werden kann, um Sperren (locks) zu simulieren und zu verhindern, dass sich gleichzeitige Aktualisierungen (concurrent updates) gegenseitig beeinträchtigen. Ohne die Sperre (lock) könnten beide Transaktionen dasselbe anfängliche Guthaben lesen und ihre Aktualisierungen basierend auf diesem Wert anwenden, was zu einer verlorenen Aktualisierung (lost update) führen würde.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Transaktionen in PostgreSQL verwaltet. Sie begannen mit der Verbindung zur PostgreSQL-Datenbank mit psql und der Erstellung einer Beispiel-Tabelle accounts mit initialen Daten.

Anschließend konzentrierten Sie sich auf die Demonstration der Verwendung von Transaktionen. Sie lernten, wie man eine Transaktion mit dem Befehl BEGIN initiiert, mehrere Datenbankoperationen durchführt (Aktualisierung von Alices und Bobs Guthaben) und dann die Transaktion mit dem Befehl COMMIT bestätigt (commit), um die Änderungen dauerhaft zu machen. Dies veranschaulichte das grundlegende Prinzip von Transaktionen: die Behandlung einer Reihe von Operationen als eine einzige atomare Einheit. Sie lernten auch, wie man Transaktionen rückgängig macht (roll back) und Isolationslevel (isolation levels) festlegt. Abschließend simulierten Sie Sperren (locks) mit gleichzeitigen Aktualisierungen (concurrent updates), um zu verstehen, wie man Datenbeschädigung verhindert.