PostgreSQL Transaktionsverwaltung

Einführung

In diesem Labor werden Sie die Transaktionsverwaltung von PostgreSQL erkunden, ein entscheidender Aspekt für die Sicherstellung der Datenintegrität. Sie lernen, wie Transaktionen gestartet und bestätigt werden, um eine Reihe von Operationen als eine einzige Arbeitseinheit zu behandeln. Sie lernen auch, fehlgeschlagene Transaktionen rückgängig zu machen, Isolationsstufen festzulegen und mit gleichzeitigen Aktualisierungen Sperren zu simulieren.

Transaktion starten und bestätigen

In diesem Schritt lernen Sie, wie Sie eine Transaktion in PostgreSQL starten und bestätigen. Transaktionen gewährleisten die Datenintegrität, indem sie eine Reihe von Operationen als eine einzige Arbeitseinheit behandeln. Fällt eine Operation innerhalb der Transaktion aus, wird die gesamte Transaktion rückgängig gemacht, wodurch teilweise Aktualisierungen verhindert und die Konsistenz erhalten bleibt.

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

sudo -u postgres psql

Sie sollten nun die Eingabeaufforderung postgres=# sehen.

Als Nächstes erstellen Sie 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);

Starten Sie nun eine Transaktion mit dem Befehl BEGIN.

BEGIN;

Überweisen Sie innerhalb der Transaktion 20,00 $ von Alice's Konto auf Bob's 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 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 Alice's Guthaben um 20,00 $ gesunken und Bob's Guthaben um 20,00 $ gestiegen ist.

Schließlich beenden Sie die psql-Shell.

\q

Rückgängigmachen einer fehlgeschlagenen Transaktion

In diesem Schritt lernen Sie, wie Sie eine fehlgeschlagene Transaktion in PostgreSQL rückgängig machen. 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 mit der PostgreSQL-Datenbank von dem vorherigen Schritt verbunden sein. Falls nicht, stellen Sie die Verbindung erneut her, indem Sie Folgendes verwenden:

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. Lassen Sie uns zunächst den nächsten verfügbaren id-Wert finden.

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 Fehler ERROR: duplicate key value violates unique constraint "accounts_pkey".

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

ROLLBACK;

Überprüfen Sie, ob das 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 Salden vom Ende von Schritt 1 enthält. Die fehlgeschlagene INSERT-Operation wurde erfolgreich rückgängig gemacht.

Schließlich beenden Sie die psql-Shell.

\q

Isolationsebenen einstellen und testen

In diesem Schritt lernen Sie die Transaktionsisolationsebenen in PostgreSQL kennen und wie Sie diese einstellen und testen. Isolationsebenen steuern den Grad, in dem gleichzeitige Transaktionen voneinander isoliert sind. Höhere Isolationsebenen bieten einen besseren Schutz vor Datenkorruption, können aber die Konsistenz reduzieren.

Sie sollten noch mit der PostgreSQL-Datenbank von dem vorherigen Schritt verbunden sein. Falls nicht, stellen Sie die Verbindung erneut her, indem Sie Folgendes verwenden:

sudo -u postgres psql

Öffnen Sie zwei separate Terminalfenster. Verbinden Sie sich in jedem Terminal als Benutzer postgres mit der PostgreSQL-Datenbank. Sie sollten in beiden Terminals die Eingabeaufforderung postgres=# sehen.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

Stellen Sie im Terminal 1 die Isolationsebene auf READ COMMITTED (obwohl dies der Standardwert ist, stellen wir ihn explizit für die Demonstration ein).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Beginnen Sie dann eine Transaktion.

BEGIN;

Lesen Sie im Terminal 1 den Kontostand von Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Notieren Sie den Kontostand. Beginnen Sie nun im Terminal 2 eine Transaktion und aktualisieren Sie den Kontostand von Alice.

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

Lesen Sie im Terminal 1 erneut den Kontostand von Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Da die Isolationsebene READ COMMITTED ist, sehen Sie den aktualisierten Kontostand (90,00), der von Terminal 2 bestätigt wurde.

Lassen Sie uns nun die Isolationsebene REPEATABLE READ testen. Machen Sie im Terminal 1 die aktuelle Transaktion rückgängig und stellen Sie die Isolationsebene auf REPEATABLE READ ein.

ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

Lesen Sie im Terminal 1 erneut den Kontostand von Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Notieren Sie den Kontostand. Beginnen Sie nun im Terminal 2 eine Transaktion und aktualisieren Sie erneut den Kontostand von Alice.

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

Lesen Sie im Terminal 1 erneut den Kontostand von Alice.

SELECT balance FROM accounts WHERE name = 'Alice';

Da die Isolationsebene REPEATABLE READ ist, sehen Sie immer noch den ursprünglichen Kontostand von Beginn der Transaktion, obwohl Terminal 2 einen neuen Wert bestätigt hat.

Schließlich bestätigen Sie im Terminal 1 die Transaktion.

COMMIT;

Wenn Sie nun erneut den Kontostand von Alice im Terminal 1 abfragen, sehen Sie den zuletzt bestätigten Wert (100,00).

Beenden Sie beide psql-Shells.

\q

Simulation von Sperren bei gleichzeitigen Aktualisierungen

In diesem Schritt simulieren Sie Sperren bei gleichzeitigen Aktualisierungen in PostgreSQL. Sperren sind Mechanismen, die verhindern, dass sich gleichzeitige Transaktionen gegenseitig beeinträchtigen und so die Datenintegrität gewährleisten.

Sie sollten noch mit der PostgreSQL-Datenbank von dem vorherigen Schritt verbunden sein. Falls nicht, stellen Sie die Verbindung erneut her, indem Sie Folgendes verwenden:

sudo -u postgres psql

Öffnen Sie zwei separate Terminalfenster. Verbinden Sie sich in jedem Terminal als Benutzer postgres mit der PostgreSQL-Datenbank. Sie sollten in beiden Terminals die Eingabeaufforderung postgres=# sehen.

Terminal 1:

sudo -u postgres psql

Terminal 2:

sudo -u postgres psql

Beginnen Sie im Terminal 1 eine Transaktion und aktualisieren Sie den Kontostand von Alice. Wichtig ist, dass Sie SELECT ... FOR UPDATE verwenden, um die Zeile zu sperren.

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

Dieser Befehl ruft den Kontostand von Alice ab und sperrt die Zeile. Dadurch wird verhindert, dass andere Transaktionen sie ändern, bis diese Transaktion bestätigt oder rückgängig gemacht wird.

Beginnen Sie im Terminal 2 eine Transaktion und versuchen Sie, den Kontostand von Alice zu aktualisieren.

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

Beachten Sie, dass dieser Befehl im Terminal 2 scheinbar hängen bleibt. Dies liegt daran, dass er auf die Freigabe der Sperre wartet, die von Terminal 1 gehalten wird.

Bestätigen Sie nun im Terminal 1 die Transaktion.

COMMIT;

Nach der Bestätigung der Transaktion im Terminal 1 wird der UPDATE-Befehl im Terminal 2 fortgesetzt.

Bestätigen Sie im Terminal 2 die Transaktion.

COMMIT;

Fragen Sie nun in jedem Terminal die Tabelle accounts ab, um die Änderungen zu überprüfen.

SELECT * FROM accounts;

Sie sollten sehen, dass der Kontostand von Alice durch die Transaktion in Terminal 2 aktualisiert wurde, nachdem Terminal 1 die Sperre freigegeben hat.

Schließlich beenden Sie beide psql-Shells.

\q

Dieses Beispiel zeigt, wie SELECT ... FOR UPDATE verwendet werden kann, um Sperren zu simulieren und zu verhindern, dass sich gleichzeitige Aktualisierungen gegenseitig beeinträchtigen. Ohne die Sperre könnten beide Transaktionen möglicherweise denselben Anfangsbestand lesen und ihre Aktualisierungen basierend auf diesem Wert anwenden, was zu einem verlorenen Update führen würde.

Zusammenfassung

In diesem Labor haben Sie gelernt, wie man Transaktionen in PostgreSQL verwaltet. Sie haben begonnen, eine Verbindung zur PostgreSQL-Datenbank über psql herzustellen und eine Beispieltabelle accounts mit Anfangsdaten zu erstellen.

Anschließend haben Sie sich auf die Demonstration der Verwendung von Transaktionen konzentriert. Sie haben gelernt, wie man eine Transaktion mit dem Befehl BEGIN startet, mehrere Datenbankoperationen durchführt (Aktualisierung der Kontenstände von Alice und Bob) und die Transaktion dann mit dem Befehl COMMIT bestätigt, um die Änderungen dauerhaft zu machen. Dies verdeutlichte das grundlegende Prinzip von Transaktionen: eine Reihe von Operationen als eine einzige atomare Einheit zu behandeln. Sie haben auch gelernt, wie man Transaktionen rückgängig macht und Isolationsebenen festlegt. Schließlich haben Sie Sperren bei gleichzeitigen Aktualisierungen simuliert, um zu verstehen, wie man Datenkorruption verhindert.