Pessimistic Locking mit SELECT FOR UPDATE

1. Was Pessimistic Locking ist

Pessimistic Locking geht davon aus, dass Schreibkonflikte wahrscheinlich sind, und sperrt eine Zeile deshalb bereits beim Lesen, nicht erst beim Schreiben. Der zentrale Befehl dafuer ist SELECT ... FOR UPDATE, der eine oder mehrere Zeilen fuer die Dauer der aktuellen Transaktion exklusiv reserviert. Jede andere Transaktion, die versucht, dieselbe Zeile ebenfalls mit FOR UPDATE zu lesen oder zu aendern, muss warten, bis die sperrende Transaktion committet oder zurueckrollt.

Der Unterschied zu Optimistic Locking mit Versionsspalten ist grundlegend: Statt einen Konflikt erst nachtraeglich beim Schreiben zu erkennen, verhindert Pessimistic Locking den Konflikt von vornherein, indem konkurrierende Transaktionen gar nicht erst gleichzeitig auf dieselbe Zeile zugreifen koennen. Das macht Pessimistic Locking besonders geeignet fuer Szenarien mit hoher Konfliktwahrscheinlichkeit, wo wiederholte Retries bei Optimistic Locking mehr Overhead verursachen wuerden als eine direkte Sperre.

Die folgenden Abschnitte zeigen die genaue Syntax von SELECT FOR UPDATE, den Unterschied zwischen Row-Level- und Table-Locks, das SKIP-LOCKED-Pattern fuer Queue-Verarbeitung und den Umgang mit Lock-Timeouts und Deadlocks.

2. SELECT FOR UPDATE: Syntax und Verhalten

Die Grundsyntax von SELECT ... FOR UPDATE ist im SQL-Standard verankert und wird von allen grossen relationalen Datenbanken unterstuetzt, wenn auch mit leicht unterschiedlichen Erweiterungen. Die Anweisung muss innerhalb einer expliziten Transaktion stehen, da die Sperre sonst sofort nach der einzelnen Anweisung wieder freigegeben wuerde und ihren Zweck verfehlt. Jede Zeile, die das SELECT zurueckgibt, wird exklusiv gesperrt, bis die Transaktion mit COMMIT oder ROLLBACK endet.

-- Grundlegendes Pessimistic Locking mit SELECT FOR UPDATE
BEGIN;

SELECT stock, price
FROM inventory
WHERE product_id = 42
FOR UPDATE;
-- Diese Zeile ist jetzt exklusiv gesperrt,
-- andere Transaktionen mit FOR UPDATE muessen warten

-- Berechnung im Anwendungscode basierend auf sicher aktuellen Werten
UPDATE inventory
SET stock = stock - 3
WHERE product_id = 42;

COMMIT;
-- Sperre wird beim COMMIT automatisch freigegeben

Ein haeufiges Missverstaendnis ist, dass ein einfaches SELECT ohne FOR UPDATE eine Zeile ebenfalls schuetzt. Das ist falsch: Ein normales SELECT liest die Daten konsistent gemaess dem aktuellen Isolation Level, verhindert aber nicht, dass eine parallele Transaktion dieselbe Zeile aendert. Nur FOR UPDATE erzwingt die exklusive Sperre, die andere schreibende und ebenfalls FOR-UPDATE-lesende Transaktionen blockiert.

3. Row-Level-Locks vs. Table-Locks

Moderne relationale Datenbanken implementieren Pessimistic Locking auf Zeilenebene, nicht auf Tabellenebene, was fuer die Nebenlaeufigkeit entscheidend ist. Ein SELECT FOR UPDATE auf eine einzelne Zeile blockiert nur Zugriffe auf genau diese Zeile, nicht auf die gesamte Tabelle. Andere Transaktionen koennen weiterhin andere Zeilen derselben Tabelle uneingeschraenkt lesen und aendern, solange sie nicht dieselbe gesperrte Zeile betreffen.

Technisch verwalten Datenbanken diese Row-Level-Locks meist ueber eine Lock-Tabelle im Speicher, die auf die physische Position der Zeile verweist, nicht ueber eine Markierung in der Zeile selbst. Bei Bereichsabfragen mit FOR UPDATE, etwa SELECT ... WHERE status = 'pending' FOR UPDATE, sperrt die Datenbank alle zurueckgegebenen Zeilen einzeln. MySQL/InnoDB kann bei bestimmten Konstellationen zusaetzlich Gap-Locks auf Luecken zwischen Indexwerten setzen, um Phantom Reads bei nachfolgenden INSERTs zu verhindern, was die effektive Sperrenreichweite ueber die tatsaechlich zurueckgegebenen Zeilen hinaus erweitern kann.

4. SKIP LOCKED fuer parallele Queue-Verarbeitung

Das Pattern FOR UPDATE SKIP LOCKED loest ein haeufiges Problem bei Queue-basierten Systemen: Mehrere parallele Worker-Prozesse wollen jeweils die naechste verfuegbare Aufgabe aus einer Tabelle holen, ohne sich gegenseitig zu blockieren oder dieselbe Aufgabe doppelt zu verarbeiten. Ohne SKIP LOCKED wuerde ein Worker, der versucht, eine bereits von einem anderen Worker gesperrte Zeile zu lesen, einfach warten, was bei mehreren parallelen Workern zu unnoetigen Wartezeiten fuehrt.

-- Queue-Verarbeitung mit SKIP LOCKED, mehrere Worker sicher parallel
BEGIN;

-- Jeder Worker holt sich die naechste NICHT gesperrte Aufgabe
SELECT job_id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Bereits von anderen Workern gesperrte Zeilen werden übersprungen,
-- statt dass dieser Worker darauf wartet

UPDATE job_queue
SET status = 'processing', worker_id = 'worker-7'
WHERE job_id = 1042;

COMMIT;
-- Nach erfolgreicher Verarbeitung: status auf 'done' setzen
-- Bei Fehler: status zurueck auf 'pending' setzen fuer Retry

SKIP LOCKED wird von PostgreSQL seit Version 9.5, von MySQL/InnoDB seit Version 8.0 und von Oracle seit langem unterstuetzt. Microsoft SQL Server bietet keine direkte SKIP-LOCKED-Klausel, erreicht ein aehnliches Verhalten aber ueber den Hint READPAST. Dieses Pattern ist der Standardansatz fuer selbstgebaute Job-Queues auf Basis einer relationalen Tabelle, ohne dass ein zusaetzliches Message-Queue-System eingefuehrt werden muss.

5. NOWAIT und Lock-Timeout-Handling

Standardmaessig wartet eine Transaktion, die auf eine bereits gesperrte Zeile mit SELECT FOR UPDATE zugreifen will, bis die sperrende Transaktion die Sperre freigibt, oder bis ein konfigurierter Timeout erreicht ist. Fuer Anwendungsfaelle, in denen Warten keine akzeptable Option ist, etwa bei einer Benutzeraktion, die sofortiges Feedback braucht, bietet die Klausel NOWAIT eine Alternative: Statt zu warten, wirft die Datenbank sofort einen Fehler, wenn die Zeile bereits gesperrt ist.

-- NOWAIT: sofortiger Fehler statt Warten
BEGIN;
SELECT * FROM seats
WHERE seat_id = 15 AND event_id = 200
FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row, wenn bereits gesperrt
-- Anwendungscode faengt den Fehler ab und zeigt sofort
-- "Sitzplatz wird gerade von jemand anderem reserviert" an
COMMIT;

-- Alternative: begrenzte Wartezeit statt sofortigem Fehler (PostgreSQL)
SET LOCAL lock_timeout = '3s';
BEGIN;
SELECT * FROM seats
WHERE seat_id = 15 AND event_id = 200
FOR UPDATE;
-- Wartet maximal 3 Sekunden, dann Fehler statt unbegrenztem Warten
COMMIT;

-- MySQL: Timeout global oder pro Session konfigurieren
SET SESSION innodb_lock_wait_timeout = 5;

Ein pauschales, unbegrenztes Warten auf Sperren ist in interaktiven Anwendungen fast immer die falsche Wahl, weil ein Benutzer nicht endlos auf eine haengende Anfrage warten sollte. NOWAIT eignet sich fuer Faelle, in denen ein sofortiges Feedback wichtiger ist als ein automatischer Erfolg, waehrend ein moderates Lock-Timeout von wenigen Sekunden einen guten Kompromiss fuer die meisten anderen Faelle darstellt.

6. Deadlocks: Ursache, Erkennung, Vermeidung

Ein Deadlock entsteht, wenn zwei Transaktionen sich gegenseitig blockieren: Transaktion A haelt eine Sperre auf Zeile 1 und wartet auf Zeile 2, waehrend Transaktion B gleichzeitig Zeile 2 haelt und auf Zeile 1 wartet. Keine der beiden Transaktionen kann jemals fortfahren, ohne dass eine externe Instanz eingreift. Relationale Datenbanken erkennen diese Situation automatisch durch einen internen Deadlock-Detector und brechen eine der beiden Transaktionen zwangsweise mit einem Fehler ab, damit die andere fortfahren kann.

-- Deadlock-Szenario: unterschiedliche Zugriffsreihenfolge
-- Transaktion A:
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- ... etwas Zeit vergeht ...
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
-- wartet, weil Transaktion B account_id 2 bereits haelt

-- Transaktion B, zur gleichen Zeit:
BEGIN;
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
-- ... etwas Zeit vergeht ...
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- wartet auf Transaktion A, klassischer Deadlock

-- Die Datenbank erkennt den Zyklus und bricht eine Transaktion ab:
-- ERROR: deadlock detected
-- Anwendungscode muss diesen Fehler abfangen und die
-- betroffene Transaktion komplett neu starten

-- VERMEIDUNG: konsistente Zugriffsreihenfolge in der ganzen Anwendung
-- Immer nach aufsteigender account_id sperren, niemals gemischt
SELECT * FROM accounts WHERE account_id IN (1, 2)
ORDER BY account_id FOR UPDATE;

Die zuverlaessigste Strategie gegen Deadlocks ist eine konsistente, applikationsweite Zugriffsreihenfolge: Wenn jede Transaktion Zeilen immer in derselben Reihenfolge sperrt, etwa aufsteigend nach Primary Key, kann der zirkulaere Wartezyklus, der einen Deadlock ausmacht, gar nicht erst entstehen. Ergaenzend sollte jede Transaktion, die SELECT FOR UPDATE nutzt, auf einen Deadlock-Fehler vorbereitet sein und die gesamte Transaktion automatisch neu starten, aehnlich der Retry-Logik bei Serialization Failures unter SERIALIZABLE.

7. Pessimistic Locking je Datenbank im Vergleich

Obwohl SELECT FOR UPDATE Teil des SQL-Standards ist, unterscheiden sich die Erweiterungen und das Detailverhalten zwischen den grossen Datenbanken spuerbar. Die folgende Tabelle fasst die wichtigsten Unterschiede zusammen.

8. Wann Pessimistic statt Optimistic Locking

Pessimistic Locking ist die richtige Wahl, wenn die Konfliktwahrscheinlichkeit auf einer Zeile hoch ist und die Transaktionsdauer kurz gehalten werden kann. Klassische Beispiele sind Queue-Verarbeitung mit vielen parallelen Workern, Sitzplatzreservierungen bei begrenztem Kontingent kurz vor Verkaufsstart, oder Finanztransaktionen, bei denen ein Kontostand innerhalb einer einzigen kurzen Transaktion gelesen, geprueft und aktualisiert wird.

Ungeeignet ist Pessimistic Locking dagegen, wenn zwischen Lesen und Schreiben eine lange, von einer Benutzerinteraktion abhaengige Zeitspanne liegt, etwa ein Formular, das ein Benutzer minutenlang offen laesst. Eine ueber diese Zeit gehaltene Zeilensperre wuerde andere Nutzer unnoetig blockieren und im schlimmsten Fall zu einem faktischen Systemstillstand fuehren, wenn viele Benutzer gleichzeitig Formulare offen lassen. Fuer solche Faelle ist Optimistic Locking mit einer Versionsspalte die deutlich bessere Wahl, wie im vertiefenden Beitrag zu Optimistic Locking beschrieben.

9. Performance-Auswirkungen und Best Practices

Jede mit SELECT FOR UPDATE gehaltene Sperre reduziert die effektive Nebenlaeufigkeit fuer genau die betroffenen Zeilen, weshalb die Transaktionsdauer bei Pessimistic Locking so kurz wie moeglich gehalten werden sollte. Alle langsamen Operationen, die nicht direkt die gesperrte Zeile betreffen, etwa Aufrufe externer APIs, sollten ausserhalb der Transaktion stattfinden, um die Sperrenzeit zu minimieren.

Eine bewaehrte Best Practice ist, Sperren immer in einer festen, applikationsweiten Reihenfolge zu erwerben, um Deadlocks strukturell auszuschliessen, statt sich auf den Deadlock-Detector der Datenbank als alleinige Absicherung zu verlassen. Zusaetzlich sollte jede Anwendung, die SELECT FOR UPDATE einsetzt, mit einem sinnvollen Lock-Timeout arbeiten und Deadlock-Fehler mit einer begrenzten Retry-Logik abfangen, statt sie ungehandhabt an den Benutzer durchzureichen.

-- Best-Practice-Kombination: Lock-Timeout plus Retry-Logik
-- Pseudocode fuer Anwendungslogik

-- function transferWithLock(fromId, toId, amount, maxAttempts = 3) {
--   for (let attempt = 1; attempt <= maxAttempts; attempt++) {
--     try {
--       db.execute("BEGIN");
--       db.execute("SET LOCAL lock_timeout = '2s'");
--
--       // Immer aufsteigend sperren, um Deadlocks strukturell zu vermeiden
--       const ids = [fromId, toId].sort();
--       db.query(
--         "SELECT balance FROM accounts WHERE account_id = ANY(?) " +
--         "ORDER BY account_id FOR UPDATE", [ids]
--       );
--
--       db.execute("UPDATE accounts SET balance = balance - ? WHERE account_id = ?", [amount, fromId]);
--       db.execute("UPDATE accounts SET balance = balance + ? WHERE account_id = ?", [amount, toId]);
--       db.execute("COMMIT");
--       return { success: true };
--     } catch (error) {
--       db.execute("ROLLBACK");
--       if ((error.code === 'DEADLOCK' || error.code === 'LOCK_TIMEOUT') && attempt < maxAttempts) {
--         continue;  // kompletter Neustart mit kurzem, randomisiertem Backoff
--       }
--       throw error;
--     }
--   }
-- }

10. Zusammenfassung

Pessimistic Locking mit SELECT FOR UPDATE sperrt eine Zeile bereits beim Lesen und verhindert damit Konflikte, statt sie erst nachtraeglich zu erkennen. SKIP LOCKED macht daraus ein zuverlaessiges Muster fuer parallele Queue-Verarbeitung, bei dem mehrere Worker sich nicht gegenseitig blockieren. NOWAIT und Lock-Timeouts verhindern unbegrenztes Warten in interaktiven Anwendungsfaellen, waehrend eine konsistente Zugriffsreihenfolge die zuverlaessigste Strategie gegen Deadlocks ist.

Pessimistic Locking eignet sich am besten fuer kurze Transaktionen mit hoher Konfliktwahrscheinlichkeit, waehrend Optimistic Locking bei laengeren, von Benutzerinteraktionen abhaengigen Zeitraeumen die bessere Wahl ist. Beide Ansaetze schliessen sich nicht gegenseitig aus, sondern lassen sich je nach Tabelle und Zugriffsprofil gezielt kombinieren, immer eingebettet in ein zum Anwendungsfall passendes Isolation Level.

11. FAQ: Pessimistic Locking mit SELECT FOR UPDATE