Wie funktioniert das Locking in PostgreSQL?

Question

Accepted Answer

PostgreSQL verwendet **Locks**, um gleichzeitigen Zugriff zu koordinieren und Konflikte zu verhindern. Dank **MVCC** benötigen Lesevorgänge in der Regel keine Locks (Leser blockieren Schreiber nicht), aber Schreibvorgänge erwerben Locks auf Zeilenebene, und verschiedene Lock-Typen koordinieren Operationen. Das Verständnis von Locking hilft, Contention und Deadlocks zu vermeiden.

## MVCC bedeutet, dass Lesevorgänge normalerweise nicht sperren

```text
Because of MVCC, plain SELECTs do NOT take row locks — they read a consistent snapshot.
→ Readers don't block writers, writers don't block readers (a key Postgres advantage).
Locking mainly comes into play for WRITES and explicit locking.
```

## Row-level Locks (von Schreibvorgängen und explizitem Locking)

```sql
-- UPDATE/DELETE automatically lock the affected ROWS (until the transaction ends)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- locks row 1

-- SELECT ... FOR UPDATE — explicitly lock rows you intend to update (prevent races)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- lock the row
-- ... compute, then UPDATE — no other transaction can modify it meanwhile
UPDATE accounts SET balance = ... WHERE id = 1;
COMMIT;
```

`SELECT ... FOR UPDATE` sperrt Zeilen, sodass gleichzeitige Transaktionen sie nicht ändern können — was Race Conditions in Read-Modify-Write-Operationen verhindert (z. B. sicheres Decrementieren von Bestandsmengen).

## Lock-Typen und Table Locks

```text
ROW-level → ACCESS via UPDATE/DELETE/SELECT FOR UPDATE — fine-grained
TABLE-level → some DDL (ALTER TABLE) takes heavier table locks → can block queries
→ Schema changes on busy tables can cause contention; plan migrations carefully
  (Postgres has gotten better — many ALTERs are now non-blocking, but not all).
```

## Lock-Probleme und Deadlocks vermeiden

```text
✓ Keep transactions SHORT (hold locks briefly → less contention)
✓ Acquire locks in a CONSISTENT ORDER across transactions → avoid DEADLOCKS
✓ Use FOR UPDATE deliberately for read-modify-write; consider FOR UPDATE SKIP LOCKED
  for queue-like patterns (skip already-locked rows)
✓ Watch for long-held locks blocking others (pg_locks, pg_stat_activity to diagnose)
✓ Be cautious with DDL on busy tables (can block)
```

## Warum es wichtig ist

Das Verständnis von PostgreSQL-Locking ist wichtig für die Erstellung von **korrekten und performanten Concurrent-Anwendungen**, daher ist es wertwolles Senior-Level-Wissen für Systeme mit erheblichem gleichzeitigem Datenbankzugriff.

Die grundlegende Erkenntnis ist, dass **MVCC bedeutet, dass Lesevorgänge normalerweise nicht sperren** (Leser blockieren Schreiber nicht — ein wichtiger Postgres-Concurrency-Vorteil), daher betrifft Locking in erster Linie **Schreibvorgänge und explizites Locking**.

Das Verständnis von **Row-Level Locks** (von UPDATE/DELETE erworben) und besonders **`SELECT ... FOR UPDATE`** (explizites Sperren von Zeilen, um Race Conditions in Read-Modify-Write-Operationen zu verhindern — wie das sichere Decrementieren von Bestandsmengen oder die Verarbeitung eines Kontostands, wobei gleichzeitige Transaktionen ohne Locking zu falschen Ergebnissen führen könnten) ist wichtig für die **Korrektheit** in Concurrent-Szenarien.

Das Wissen, dass **DDL/Schema-Änderungen schwerere Table Locks annehmen können** (potenziell Abfragen auf beschäftigten Tabellen blockieren — eine wichtige Überlegung bei der Planung von Migrationen, obwohl Postgres viele ALTERs non-blocking gemacht hat) hilft, Contention während Deployments zu vermeiden.

Das Verständnis, wie man **Lock-Probleme und Deadlocks vermeidet** — Transaktionen kurz halten (Locks nur kurz halten), Locks in konsistenter Reihenfolge erwerben (Deadlocks verhindern), `FOR UPDATE` gezielt einsetzen (und `SKIP LOCKED` für Queue-Muster), und Lock-Contention diagnostizieren (pg_locks, pg_stat_activity) — ist wertvoll für Performance und Zuverlässigkeit unter Concurrency.

Da Concurrency in echten Anwendungen inhärent ist, und da die richtige Verwendung von Locking (besonders `FOR UPDATE` für Korrektheit in Read-Modify-Write-Operationen und die Vermeidung von Contention/Deadlocks) das ist, was Concurrent-Datenbankcode korrekt und performant macht, ist das Verständnis von PostgreSQL-Locking — das MVCC-no-read-locks-Modell, explizites Row-Locking für Korrektheit, Table Locks von DDL und das Vermeiden von Deadlocks/Contention — wertwolles Senior-Level-Wissen für die Erstellung robuster Concurrent-Anwendungen und den Betrieb von Postgres unter Last, eine häufig relevante Herausforderung, die das Verständnis von Concurrency Control in Datenbanken demonstriert.