Tag 4: Transaktionen, MVCC und Sperren
Ziel: Du siehst, wie PostgreSQL Zeilenversionen verwaltet, wählst die passende Isolationsstufe, findest blockierende Sitzungen und erzeugst einen Deadlock, den du im Log wiederfindest. · Dauer: ca. 90 min · Voraussetzung: Tag 1
Briefing
- MVCC (Multi-Version Concurrency Control): Ein
UPDATEüberschreibt nicht, es legt eine neue Zeilenversion an. Jede Version trägtxmin(wer sie erzeugt hat) undxmax(wer sie gelöscht hat). - Jede Abfrage sieht einen Snapshot: welche Transaktionen schon fertig waren. Leser blockieren keine Schreiber und umgekehrt.
- Gesperrt wird trotzdem: zwei Schreiber auf derselben Zeile, und DDL
wie
ALTER TABLEgegen alles. - Heute brauchst du zwei Terminals auf
pg1, beide mitpsql -d tag04: „Sitzung A“ und „Sitzung B“. Für Abfragen nebenher ein drittes.
Sitzung A Sitzung B
BEGIN; UPDATE id=1 ----> UPDATE id=1 (wartet auf A)
COMMIT; ----> UPDATE 1 (läuft weiter)
Drill 1: BEGIN, COMMIT, ROLLBACK, SAVEPOINT
createdb tag04
psql -d tag04 <<'EOF'
CREATE TABLE konto (id int PRIMARY KEY, inhaber text, saldo numeric NOT NULL);
INSERT INTO konto VALUES (1, 'Anna', 100), (2, 'Ben', 100);
BEGIN;
UPDATE konto SET saldo = saldo - 30 WHERE id = 1;
SAVEPOINT vor_ben;
UPDATE konto SET saldo = saldo / 0 WHERE id = 2;
ROLLBACK TO SAVEPOINT vor_ben;
UPDATE konto SET saldo = saldo + 30 WHERE id = 2;
COMMIT;
SELECT * FROM konto ORDER BY id;
BEGIN;
DELETE FROM konto;
SELECT count(*) FROM konto;
ROLLBACK;
SELECT count(*) FROM konto;
EOFErwartet (gekürzt):
BEGIN
UPDATE 1
SAVEPOINT
ERROR: division by zero
ROLLBACK
UPDATE 1
COMMIT
id | inhaber | saldo
----+---------+-------
1 | Anna | 70
2 | Ben | 130
BEGIN
DELETE 2
count
-------
0
ROLLBACK
count
-------
2
Ohne Savepoint hätte der Fehler die ganze Transaktion abgebrochen
(current transaction is aborted, Tag 1).
ROLLBACK TO SAVEPOINT macht nur den Teil seit dem Savepoint
rückgängig, die Abbuchung bei Anna bleibt. Ohne BEGIN ist
jedes Statement seine eigene Transaktion (Autocommit).
Fortschritt speichern? Kostenlos registrieren
Drill 2: xmin, xmax und ctid sichtbar machen
Jede Tabelle hat versteckte Systemspalten. ctid ist die
physische Adresse (Seite, Position):
psql -d tag04 <<'EOF'
SELECT ctid, xmin, xmax, * FROM konto ORDER BY id;
BEGIN;
SELECT pg_current_xact_id();
UPDATE konto SET saldo = saldo + 1 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM konto ORDER BY id;
COMMIT;
EOFErwartet:
ctid | xmin | xmax | id | inhaber | saldo
-------+------+------+----+---------+-------
(0,3) | 892 | 894 | 1 | Anna | 70
(0,4) | 893 | 894 | 2 | Ben | 130
(2 rows)
BEGIN
pg_current_xact_id
--------------------
895
(1 row)
UPDATE 1
ctid | xmin | xmax | id | inhaber | saldo
-------+------+------+----+---------+-------
(0,5) | 895 | 0 | 1 | Anna | 71
(0,4) | 893 | 894 | 2 | Ben | 130
(2 rows)
COMMIT
xmax = 894 stammt vom zurückgerollten
DELETE aus Drill 1: Die Löschung ist vermerkt, aber
Transaktion 894 ist abgebrochen – also gilt die Zeile weiter. Das
UPDATE erzeugt eine neue Version an (0,5) mit
xmin = 895; die alte an (0,3) ist jetzt tot
und wartet auf VACUUM (Tag 6). Deine Transaktionsnummern
sind andere.
Jetzt mit zwei Sitzungen. Sitzung A:
BEGIN;
UPDATE konto SET saldo = 0 WHERE id = 2;
SELECT pg_current_xact_id();
SELECT ctid, xmin, xmax, saldo FROM konto WHERE id = 2;Sitzung B:
SELECT ctid, xmin, xmax, saldo FROM konto WHERE id = 2;Sitzung A:
ROLLBACK;Erwartet (A, dann B):
pg_current_xact_id
--------------------
896
ctid | xmin | xmax | saldo
-------+------+------+-------
(0,6) | 896 | 0 | 0
ctid | xmin | xmax | saldo
-------+------+------+-------
(0,4) | 893 | 896 | 130
Zwei Versionen derselben Zeile gleichzeitig: A sieht die eigene neue,
B die alte – mit xmax = 896, weil A sie löschen will, aber
noch nicht bestätigt hat. Niemand wartet.
Fortschritt speichern? Kostenlos registrieren
Drill 3: Read Committed gegen Repeatable Read
Standard ist READ COMMITTED: Jedes
Statement bekommt einen neuen Snapshot. Sitzung
A:
BEGIN;
SELECT saldo FROM konto WHERE id = 1;Sitzung B:
UPDATE konto SET saldo = 200 WHERE id = 1;Sitzung A:
SELECT saldo FROM konto WHERE id = 1;
COMMIT;Erwartet (A): erst 71, dann
200 – innerhalb derselben Transaktion.
Bei REPEATABLE READ gilt der Snapshot für die ganze
Transaktion. Sitzung A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT saldo FROM konto WHERE id = 1;Sitzung B:
UPDATE konto SET saldo = 300 WHERE id = 1;Sitzung A:
SELECT saldo FROM konto WHERE id = 1;
UPDATE konto SET saldo = saldo + 10 WHERE id = 1;
ROLLBACK;Erwartet (A, gekürzt):
saldo
-------
200
saldo
-------
200
ERROR: could not serialize access due to concurrent update
ROLLBACK
A sieht stabil 200, obwohl B längst 300
geschrieben hat. Will A die Zeile ändern, die ein anderer nach dem
Snapshot geändert hat, bricht PostgreSQL ab, statt Bs Änderung zu
überschreiben. Die Anwendung muss die Transaktion wiederholen.
Fortschritt speichern? Kostenlos registrieren
Drill 4: Serializable und der Serialisierungsfehler
Regel: Mindestens eine Ärztin oder ein Arzt muss Dienst haben. Beide prüfen, sehen zwei im Dienst und melden sich ab – jede für sich korrekt („Write Skew“):
psql -d tag04 -c 'CREATE TABLE dienst (arzt text PRIMARY KEY, im_dienst boolean)' \
-c "INSERT INTO dienst VALUES ('Anna', true), ('Ben', true)"Sitzung A und dann Sitzung B, jeweils:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM dienst WHERE im_dienst;Sitzung A:
UPDATE dienst SET im_dienst = false WHERE arzt = 'Anna';Sitzung B:
UPDATE dienst SET im_dienst = false WHERE arzt = 'Ben';Dann COMMIT; erst in A, dann in
B, und SELECT * FROM dienst;.
Erwartet (gekürzt):
count
-------
2
UPDATE 1
COMMIT
arzt | im_dienst
------+-----------
Anna | f
Ben | f
Beide COMMIT klappen – niemand hat Dienst. Die
Transaktionen ändern verschiedene Zeilen, REPEATABLE READ
merkt nichts. Jetzt alles zurück auf true
(UPDATE dienst SET im_dienst = true;) und dieselbe Abfolge
mit BEGIN ISOLATION LEVEL SERIALIZABLE; in beiden
Sitzungen.
Erwartet (B beim COMMIT):
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
SELECT * FROM dienst; zeigt danach Anna | f
und Ben | t. SERIALIZABLE verfolgt, wer was
gelesen hat, und bricht eine Transaktion ab, wenn kein serielles
Nacheinander dasselbe Ergebnis liefern könnte. Der Preis: mehr Abbrüche,
die die Anwendung mit einer Wiederholung (Retry) beantworten muss – das
sagt auch der HINT.
| Stufe | Snapshot | Typischer Fehler |
|---|---|---|
| Read Committed | pro Statement | keiner, aber nicht wiederholbare Lesevorgänge |
| Repeatable Read | pro Transaktion | could not serialize … concurrent update |
| Serializable | pro Transaktion + Prüfung | … read/write dependencies … |
READ UNCOMMITTED gibt es nur als Name; PostgreSQL
behandelt es wie Read Committed.
Fortschritt speichern? Kostenlos registrieren
Drill 5: Wer blockiert wen – pg_locks und pg_blocking_pids
log_lock_waits ist aus. Als Superuser darfst du es pro
Sitzung einschalten – ohne Reload, ohne andere zu stören.
Sitzung A:
BEGIN;
UPDATE konto SET saldo = saldo - 10 WHERE id = 1;
SELECT pg_backend_pid();Sitzung B:
SET log_lock_waits = on;
SELECT pg_backend_pid();
UPDATE konto SET saldo = saldo + 10 WHERE id = 1;B hängt. Im dritten Terminal:
psql -d tag04 <<'EOF'
SELECT pid, state, wait_event, pg_blocking_pids(pid) AS blockiert_von,
left(query, 25) AS query
FROM pg_stat_activity WHERE datname = 'tag04' AND pid <> pg_backend_pid();
SELECT l.pid, l.locktype, l.relation::regclass, l.transactionid AS xid, l.mode,
l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE a.datname = 'tag04' AND l.pid <> pg_backend_pid()
ORDER BY l.pid, l.granted DESC, l.locktype;
EOFErwartet:
pid | state | wait_event | blockiert_von | query
-------+---------------------+---------------+---------------+--------------------------
14984 | idle in transaction | ClientRead | {} | SELECT pg_backend_pid();
14985 | active | transactionid | {14984} | UPDATE konto SET saldo =
(2 rows)
pid | locktype | relation | xid | mode | granted
-------+---------------+------------+-----+------------------+---------
14984 | relation | konto_pkey | | RowExclusiveLock | t
14984 | relation | konto | | RowExclusiveLock | t
14984 | transactionid | | 915 | ExclusiveLock | t
14984 | virtualxid | | | ExclusiveLock | t
14985 | relation | konto_pkey | | RowExclusiveLock | t
14985 | relation | konto | | RowExclusiveLock | t
14985 | transactionid | | 916 | ExclusiveLock | t
14985 | tuple | konto | | ExclusiveLock | t
14985 | virtualxid | | | ExclusiveLock | t
14985 | transactionid | | 915 | ShareLock | f
(10 rows)
Zeilensperren stehen nicht in pg_locks, sondern im Tupel
selbst (als xmax). Wer auf eine Zeile wartet, wartet
deshalb auf die Transaktion des Sperrenden:
ShareLock auf xid 915, granted = f.
pg_blocking_pids() nimmt dir das Puzzeln ab. Jetzt
Sitzung A: COMMIT; – B meldet sofort
UPDATE 1. Das Log:
sudo grep -E 'still waiting|acquired ShareLock' -A1 \
/var/log/postgresql/postgresql-18-main.log | tail -5Erwartet (gekürzt):
... [14985] ubuntu@tag04 LOG: process 14985 still waiting for ShareLock on
transaction 915 after 1000.322 ms
... [14985] ubuntu@tag04 DETAIL: Process holding the lock: 14984. Wait queue: 14985.
--
... [14985] ubuntu@tag04 LOG: process 14985 acquired ShareLock on transaction 915
after 1674.208 ms
Gemeldet wird nach deadlock_timeout (Standard 1 s). In
Produktion gehört log_lock_waits = on in die Konfiguration
(Tag 7).
Fortschritt speichern? Kostenlos registrieren
Drill 6: Sperrwarteschlange, lock_timeout, statement_timeout
Ein harmloses SELECT in einer offenen Transaktion hält
eine schwache Sperre (AccessShareLock) bis zum Ende.
Sitzung A:
BEGIN;
SELECT saldo FROM konto WHERE id = 1;Sitzung B:
ALTER TABLE konto ADD COLUMN notiz text;Im dritten Terminal zuerst ein Leser, der im Hintergrund wartet, dann der Blick auf die Kette:
psql -d tag04 -c 'SELECT count(*) FROM konto' &
sleep 1
psql -d tag04 -c "SELECT pid, state, pg_blocking_pids(pid) AS blockiert_von,
left(query, 30) AS query FROM pg_stat_activity
WHERE datname = 'tag04' AND pid <> pg_backend_pid() ORDER BY pid"Erwartet:
pid | state | blockiert_von | query
-------+---------------------+---------------+--------------------------------
13486 | idle in transaction | {} | SELECT saldo FROM konto WHERE
13487 | active | {13486} | ALTER TABLE konto ADD COLUMN n
13489 | active | {13487} | SELECT count(*) FROM konto
(3 rows)
Das ALTER TABLE braucht AccessExclusiveLock
und wartet auf A. Jeder neue Leser stellt sich hinter
das ALTER TABLE – die Tabelle ist für alle zu, obwohl nur
ein SELECT und eine kleine Migration im Spiel sind.
Sitzung A: ROLLBACK; – B und der Leser
laufen durch.
Die Abhilfe für Migrationen: nicht ewig warten. Im dritten Terminal die Spalte wieder entfernen, solange niemand die Tabelle hält:
psql -d tag04 -c 'ALTER TABLE konto DROP COLUMN notiz'Dann wieder Sitzung A:
BEGIN; SELECT saldo FROM konto WHERE id = 1; und im dritten
Terminal:
psql -d tag04 -c "SET lock_timeout = '2s'" -c 'ALTER TABLE konto ADD COLUMN notiz text'
psql -d tag04 -c "SET statement_timeout = '1s'" -c 'SELECT pg_sleep(5)'Erwartet:
SET
ERROR: canceling statement due to lock timeout
SET
ERROR: canceling statement due to statement timeout
lock_timeout begrenzt nur das Warten auf Sperren,
statement_timeout die gesamte Laufzeit. Beides lässt sich
pro Sitzung, Rolle (ALTER ROLE … SET) oder Datenbank
setzen. Sitzung A: ROLLBACK;
Fortschritt speichern? Kostenlos registrieren
Drill 7: Deadlock erzeugen und im Log finden
Zwei Überweisungen in entgegengesetzter Reihenfolge. Sitzung A:
BEGIN;
UPDATE konto SET saldo = saldo - 5 WHERE id = 1;Sitzung B:
BEGIN;
UPDATE konto SET saldo = saldo - 5 WHERE id = 2;Sitzung A (wartet auf B):
UPDATE konto SET saldo = saldo + 5 WHERE id = 2;Sitzung B (wartet auf A – der Kreis ist geschlossen):
UPDATE konto SET saldo = saldo + 5 WHERE id = 1;Erwartet (nach etwa 1 s in einer der beiden Sitzungen):
ERROR: deadlock detected
DETAIL: Process 36079 waits for ShareLock on transaction 926; blocked by process
36080.
Process 36080 waits for ShareLock on transaction 925; blocked by process 36079.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,4) in relation "konto"
Die andere Sitzung meldet UPDATE 1. Beide mit
COMMIT; abschließen – die abgebrochene meldet
ROLLBACK. Nach deadlock_timeout prüft der
Wartende, ob ein Kreis existiert, und bricht sich selbst ab. Die
Statements beider Seiten stehen nur im Log:
sudo grep -A5 'deadlock detected' /var/log/postgresql/postgresql-18-main.log | tail -6
psql -d tag04 -Atc "SELECT deadlocks FROM pg_stat_database WHERE datname = 'tag04'"Erwartet (gekürzt):
... [36079] ubuntu@tag04 ERROR: deadlock detected
... [36079] ubuntu@tag04 DETAIL: Process 36079 waits for ShareLock on transaction
926; blocked by process 36080.
Process 36080 waits for ShareLock on transaction 925; blocked by process 36079.
Process 36079: UPDATE konto SET saldo = saldo + 5 WHERE id = 2;
Process 36080: UPDATE konto SET saldo = saldo + 5 WHERE id = 1;
... [36079] ubuntu@tag04 HINT: See server log for query details.
1
Deadlocks werden immer geloggt, auch ohne
log_lock_waits. Die Heilung liegt in der Anwendung: Zeilen
immer in derselben Reihenfolge sperren (etwa nach id
sortiert).
Fortschritt speichern? Kostenlos registrieren
Drill 8: idle in transaction
Eine offene Transaktion, die nichts tut, hält Sperren und – bei
REPEATABLE READ – einen alten Snapshot fest.
Sitzung A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT saldo FROM konto WHERE id = 1;Im dritten Terminal:
psql -d tag04 <<'EOF'
SELECT pid, state, now() - xact_start AS dauer, backend_xmin,
left(query, 20) AS query
FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' AND datname = 'tag04';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND datname = 'tag04';
EOFErwartet:
pid | state | dauer | backend_xmin | query
-------+---------------------+-----------------+--------------+----------------------
36146 | idle in transaction | 00:00:02.004769 | 927 | SELECT saldo FROM ko
(1 row)
pg_terminate_backend
----------------------
t
(1 row)
backend_xmin ist die älteste Transaktion, die diese
Sitzung noch sehen muss – solange sie offen ist, darf
VACUUM keine Zeilenversion entfernen, die danach tot wurde
(Tag 6). Tippt A jetzt etwas, kommt:
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
...
Automatisch geht es mit einem Timeout. In Sitzung A (psql verbindet sich neu):
SET idle_in_transaction_session_timeout = '2s';
BEGIN;
SELECT 1;Drei Sekunden warten, dann SELECT 2;.
Erwartet:
FATAL: terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
pg_cancel_backend(pid) bricht nur das laufende Statement
ab, pg_terminate_backend beendet die ganze Verbindung samt
Transaktion.
Fortschritt speichern? Kostenlos registrieren
Übung: Speed-Drills
- Zeige deine aktuelle Transaktionsnummer und deinen Snapshot. Zeitziel: 20 s
- Stelle für
tag04die Standard-Isolationsstufe aufrepeatable read, prüfe sie in einer neuen Sitzung und nimm es zurück. Zeitziel: 45 s - Zeige, wie viele Deadlocks es in
tag04bisher gab. Zeitziel: 20 s
Lösung
psql -d tag04 -c 'BEGIN' -c 'SELECT pg_current_xact_id(), pg_current_snapshot()' \
-c 'COMMIT'
psql -d tag04 -c "ALTER DATABASE tag04 SET default_transaction_isolation =
'repeatable read'"
psql -d tag04 -Atc 'SHOW default_transaction_isolation'
psql -d tag04 -c 'ALTER DATABASE tag04 RESET default_transaction_isolation'
psql -d tag04 -Atc "SELECT datname, deadlocks FROM pg_stat_database
WHERE datname = 'tag04'"Erwartet (gekürzt):
pg_current_xact_id | pg_current_snapshot
--------------------+---------------------
917 | 917:917:
ALTER DATABASE
repeatable read
ALTER DATABASE
tag04|1
Der Snapshot 917:917: heißt: alles unter 917 ist fertig,
ab 917 unsichtbar, keine Ausnahmen dazwischen.
pg_current_xact_id() vergibt die Nummer erst bei Bedarf –
ohne BEGIN hätte jeder Aufruf eine eigene verbraucht.
Fortschritt speichern? Kostenlos registrieren
Kontrollpunkt
psql -d tag04 -Atc 'SELECT id, saldo FROM konto ORDER BY id'Erwartet: 1|305 und 2|125
– oder 1|295 und 2|135, falls der Deadlock in
Drill 7 Sitzung B getroffen hat.
psql -d tag04 -Atc "SELECT count(*) FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%' AND datname = 'tag04'"Erwartet: 0 – keine Sitzung hängt
mehr.
psql -d tag04 -Atc "SELECT deadlocks > 0 FROM pg_stat_database
WHERE datname = 'tag04'"Erwartet: t
psql -d tag04 -Atc 'SHOW log_lock_waits'Erwartet: off – die Einstellung aus
Drill 5 galt nur für Sitzung B.
Challenge
Baue eine Sperrkette mit drei Sitzungen: A ändert Zeile 2 in einer offenen Transaktion, B und C wollen dieselbe Zeile ändern bzw. löschen. Finde mit einer Abfrage die Sitzung am Anfang der Kette und beende nur diese.
Hinweis: Gesucht ist eine PID, die in
pg_blocking_pids() anderer auftaucht, selbst aber von
niemandem blockiert wird.
Lösung
A:
BEGIN; UPDATE konto SET saldo = saldo + 1 WHERE id = 2; ·
B: UPDATE konto SET saldo = saldo + 1 WHERE id = 2; · C:
DELETE FROM konto WHERE id = 2;
psql -d tag04 <<'EOF'
SELECT pid, pg_blocking_pids(pid) AS blockiert_von, left(query, 30) AS query
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
AND cardinality(pg_blocking_pids(pid)) = 0;
EOFErwartet:
pid | blockiert_von | query
-------+---------------+--------------------------------
15587 | {15586} | UPDATE konto SET saldo = saldo
15588 | {15587} | DELETE FROM konto WHERE id = 2
(2 rows)
pg_terminate_backend
----------------------
t
(1 row)
C wartet auf B, nicht auf A: Wer zuerst auf eine Zeile wartet, steht
vorne in der Schlange. Nach dem Beenden von A laufen B
(UPDATE 1) und C (DELETE 1) durch – danach
gibt es Zeile 2 nicht mehr, der Kontrollpunkt oben gilt also vor der
Challenge.
Fortschritt speichern? Kostenlos registrieren
Aufräumen
Alle Sitzungen mit \q verlassen, dann:
dropdb tag04Interview-Notiz
- „Blockieren Leser in PostgreSQL Schreiber?“ – Nein, MVCC: Jeder Leser sieht seinen Snapshot, Schreiber legen neue Versionen an. Es blockieren nur Schreiber auf derselben Zeile und DDL.
- „Eine Migration hat die Produktion lahmgelegt, obwohl
ALTER TABLEschnell ist – warum?“ – Sie wartete auf eine lange Transaktion, und alle anderen standen hinter ihr in der Sperrschlange. Abhilfe:lock_timeoutund Wiederholen. - „Was tun bei Serialisierungsfehlern?“ – Erwartetes Verhalten bei
REPEATABLE READundSERIALIZABLE; die Anwendung wiederholt die ganze Transaktion (SQLSTATE40001, bei Deadlocks40P01).
Enterprise-Notiz
log_lock_waits = onundidle_in_transaction_session_timeout(z. B. 5–15 min) global; strengere Werte pro Anwendungsrolle.- Migrationswerkzeuge setzen
lock_timeout(wenige Sekunden) und wiederholen; große Änderungen laufen in kleinen Schritten (NOT VALID,CONCURRENTLY). statement_timeoutpro Rolle für Webanwendungen, nicht global – sonst brechen Wartungsjobs und Backups ab.- Alarme auf
pg_stat_activity: älteste offene Transaktion, Zahl der wartenden Sitzungen,pg_stat_database.deadlocks(Tag 7). SERIALIZABLEnur mit Retry-Logik im Treiber oder Framework – und mit Tests unter Last.