Einführung
In diesem Lab erkunden Sie die Transaktionsverwaltung in PostgreSQL, einen entscheidenden Aspekt für die Sicherstellung der Datenintegrität. Sie lernen, Transaktionen zu beginnen und zu bestätigen, sodass eine Reihe von Vorgängen als eine Arbeitseinheit behandelt wird. Außerdem erfahren Sie, wie Sie fehlgeschlagene Transaktionen zurückrollen, Isolationsstufen festlegen und Sperren mit gleichzeitigen Aktualisierungen simulieren.
Eine Transaktion beginnen und bestätigen
In diesem Schritt lernen Sie, wie Sie eine Transaktion in PostgreSQL beginnen und bestätigen. Transaktionen sichern die Datenintegrität, indem sie eine Reihe von Vorgängen als eine Arbeitseinheit behandeln. Schlägt ein Vorgang innerhalb der Transaktion fehl, wird die gesamte Transaktion zurückgerollt. So werden teilweise Änderungen verhindert und die Konsistenz bleibt erhalten.
Stellen Sie zunächst als Benutzer postgres eine Verbindung zur PostgreSQL-Datenbank her. Öffnen Sie ein Terminal und führen Sie den folgenden Befehl aus:
sudo -u postgres psql
Jetzt sollte die Eingabeaufforderung postgres=# angezeigt werden.
Erstellen Sie anschließend eine Tabelle namens accounts, um Transaktionen zu veranschaulichen.
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2)
);
Fügen Sie einige Ausgangsdaten in die Tabelle accounts ein.
INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);
Beginnen Sie nun mit dem Befehl BEGIN eine Transaktion.
BEGIN;
Überweisen 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';
Übernehmen Sie die Änderungen dauerhaft, indem Sie die Transaktion mit dem Befehl COMMIT bestätigen.
COMMIT;
Prüfen Sie mit einer Abfrage der Tabelle accounts, ob die Transaktion erfolgreich war.
SELECT * FROM accounts;
Alices Kontostand sollte um $20 gesunken und Bobs Kontostand um $20 gestiegen sein.
Beenden Sie abschließend die psql-Shell.
\q
Eine fehlgeschlagene Transaktion zurückrollen
In diesem Schritt lernen Sie, wie Sie eine fehlgeschlagene Transaktion in PostgreSQL zurückrollen. Ein Rollback ist wichtig, wenn bei einer Reihe von Datenbankvorgängen ein Fehler auftritt. So bleibt die Datenbank in einem konsistenten Zustand.
Sie sollten noch mit der PostgreSQL-Datenbank aus dem vorherigen Schritt verbunden sein. Falls nicht, stellen Sie die Verbindung mit folgendem Befehl erneut her:
sudo -u postgres psql
Beginnen Sie eine neue Transaktion.
BEGIN;
Versuchen Sie innerhalb dieser Transaktion absichtlich, einen Vorgang auszuführen, der fehlschlägt: Fügen Sie einen doppelten Primärschlüssel ein. Ermitteln Sie zunächst den nächsten verfügbaren Wert für id.
SELECT MAX(id) FROM accounts;
Nehmen wir an, das Ergebnis ist 2. Versuchen Sie nun, ein neues Konto mit id = 1 einzufügen. Diese ID ist bereits vorhanden.
INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);
Dieser Befehl führt zu folgendem Fehler: ERROR: duplicate key value violates unique constraint "accounts_pkey".
Da innerhalb der Transaktion ein Fehler aufgetreten ist, rollen Sie die Transaktion mit dem Befehl ROLLBACK zurück, um alle vorgenommenen Änderungen zu verwerfen.
ROLLBACK;
Prüfen Sie durch Abfragen der Tabelle accounts, ob das ROLLBACK erfolgreich war.
SELECT * FROM accounts;
Die Tabelle sollte weiterhin nur die Konten von Alice und Bob mit den Kontoständen vom Ende von Schritt 1 enthalten. Der fehlgeschlagene INSERT-Vorgang wurde zurückgerollt.
Beenden Sie abschließend die psql-Shell.
\q
Isolationsstufen festlegen und testen
In diesem Schritt lernen Sie die Isolationsstufen von Transaktionen in PostgreSQL kennen und erfahren, wie Sie diese festlegen und testen. Isolationsstufen bestimmen, in welchem Maß gleichzeitige Transaktionen voneinander isoliert sind. Höhere Isolationsstufen bieten besseren Schutz vor Datenbeschädigung, können aber die Nebenläufigkeit verringern.
Sie sollten noch mit der PostgreSQL-Datenbank aus dem vorherigen Schritt verbunden sein. Falls nicht, stellen Sie die Verbindung mit folgendem Befehl erneut her:
sudo -u postgres psql
Öffnen Sie zwei separate Terminalfenster. Stellen Sie in jedem Terminalfenster als Benutzer postgres eine Verbindung zur PostgreSQL-Datenbank her. Es sollten zwei Eingabeaufforderungen postgres=# angezeigt werden.
Terminal 1:
sudo -u postgres psql
Terminal 2:
sudo -u postgres psql
Beginnen Sie in Terminal 1 eine Transaktion und legen Sie anschließend die Isolationsstufe READ COMMITTED fest (die Standardeinstellung). SET TRANSACTION muss innerhalb der Transaktion und vor der ersten Abfrage ausgeführt werden.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Lesen Sie in Terminal 1 Alices Kontostand aus.
SELECT balance FROM accounts WHERE name = 'Alice';
Notieren Sie sich den Kontostand. Beginnen Sie nun in Terminal 2 eine Transaktion und aktualisieren Sie Alices Kontostand.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;
Lesen Sie Alices Kontostand in Terminal 1 erneut aus.
SELECT balance FROM accounts WHERE name = 'Alice';
Da die Isolationsstufe READ COMMITTED ist, wird der von Terminal 2 bestätigte neue Kontostand (90.00) angezeigt.
Testen Sie nun die Isolationsstufe REPEATABLE READ. Rollen Sie in Terminal 1 die aktuelle Transaktion zurück, beginnen Sie eine neue Transaktion und legen Sie REPEATABLE READ fest, bevor Sie Daten auslesen.
ROLLBACK;
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Lesen Sie Alices Kontostand in Terminal 1 erneut aus.
SELECT balance FROM accounts WHERE name = 'Alice';
Notieren Sie sich den Kontostand. Beginnen Sie nun in Terminal 2 eine Transaktion und aktualisieren Sie Alices Kontostand erneut.
BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;
Lesen Sie Alices Kontostand in Terminal 1 erneut aus.
SELECT balance FROM accounts WHERE name = 'Alice';
Da die Isolationsstufe REPEATABLE READ ist, wird weiterhin 90.00 angezeigt. Das ist der Kontostand aus dem Snapshot, der durch die erste Abfrage in dieser Transaktion erstellt wurde, obwohl Terminal 2 den Wert 100.00 bestätigt hat.
Bestätigen Sie abschließend die Transaktion in Terminal 1.
COMMIT;
Lesen Sie Alices Kontostand anschließend in Terminal 1 erneut aus. Nach Abschluss der Transaktion wird der neueste bestätigte Wert (100.00) angezeigt.
SELECT balance FROM accounts WHERE name = 'Alice';
Beenden Sie beide psql-Shells.
\q
Sperren mit gleichzeitigen Aktualisierungen simulieren
In diesem Schritt simulieren Sie Sperren mit gleichzeitigen Aktualisierungen in PostgreSQL. Sperren verhindern, dass gleichzeitige Transaktionen einander beeinträchtigen, und sichern so die Datenintegrität.
Sie sollten noch mit der PostgreSQL-Datenbank aus dem vorherigen Schritt verbunden sein. Falls nicht, stellen Sie die Verbindung mit folgendem Befehl erneut her:
sudo -u postgres psql
Öffnen Sie zwei separate Terminalfenster. Stellen Sie in jedem Terminalfenster als Benutzer postgres eine Verbindung zur PostgreSQL-Datenbank her. Es sollten zwei Eingabeaufforderungen postgres=# angezeigt werden.
Terminal 1:
sudo -u postgres psql
Terminal 2:
sudo -u postgres psql
Beginnen Sie in Terminal 1 eine Transaktion und aktualisieren Sie Alices Kontostand. Verwenden Sie unbedingt SELECT ... FOR UPDATE, um die Zeile zu sperren.
BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
Dieser Befehl liest Alices Kontostand aus und sperrt die Zeile. Dadurch können andere Transaktionen die Zeile erst ändern, wenn diese Transaktion bestätigt oder zurückgerollt wurde.
Beginnen Sie in Terminal 2 eine Transaktion und versuchen Sie, Alices Kontostand zu aktualisieren.
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';
Beachten Sie, dass der Befehl in Terminal 2 scheinbar hängen bleibt. Er wartet darauf, dass Terminal 1 die Sperre freigibt.
Bestätigen Sie nun die Transaktion in Terminal 1.
COMMIT;
Sobald Sie die Transaktion in Terminal 1 bestätigt haben, wird der UPDATE-Befehl in Terminal 2 ausgeführt.
Bestätigen Sie die Transaktion in Terminal 2.
COMMIT;
Fragen Sie nun in einem der beiden Terminals die Tabelle accounts ab, um die Änderungen zu überprüfen.
SELECT * FROM accounts;
Alices Kontostand sollte durch die Transaktion in Terminal 2 aktualisiert worden sein, nachdem Terminal 1 die Sperre freigegeben hat.
Beenden Sie abschließend beide psql-Shells.
\q
Dieses Beispiel zeigt, wie Sie mit SELECT ... FOR UPDATE Sperren simulieren und verhindern können, dass sich gleichzeitige Aktualisierungen gegenseitig beeinträchtigen. Ohne die Sperre könnten beide Transaktionen denselben ursprünglichen Kontostand lesen und ihre Aktualisierungen auf Grundlage dieses Werts durchführen. Dadurch ginge eine Aktualisierung verloren.
Zusammenfassung
In diesem Lab haben Sie gelernt, Transaktionen in PostgreSQL zu verwalten. Zu Beginn haben Sie mit psql eine Verbindung zur PostgreSQL-Datenbank hergestellt und eine Beispieltabelle accounts mit Ausgangsdaten erstellt.
Anschließend haben Sie den Einsatz von Transaktionen kennengelernt. Sie haben mit dem Befehl BEGIN eine Transaktion gestartet, mehrere Datenbankvorgänge ausgeführt (die Kontostände von Alice und Bob aktualisiert) und die Änderungen mit dem Befehl COMMIT dauerhaft übernommen. Damit haben Sie das Grundprinzip von Transaktionen kennengelernt: Eine Reihe von Vorgängen wird als eine atomare Einheit behandelt. Außerdem haben Sie gelernt, Transaktionen zurückzurollen und Isolationsstufen festzulegen. Zum Schluss haben Sie Sperren mit gleichzeitigen Aktualisierungen simuliert, um zu verstehen, wie sich Datenbeschädigung verhindern lässt.
