Kategorien:	credativ® Inside

Wer mit PostgreSQL® arbeitet, stößt früher oder später auf das Thema Sperren. Sobald mehrere Transaktionen gleichzeitig auf dieselben Daten zugreifen, braucht das Datenbanksystem einen Mechanismus, der Konflikte verhindert und Datenkonsistenz sicherstellt. Genau hier kommen Locks ins Spiel. Dieser Artikel führt Sie Schritt für Schritt durch das PostgreSQL-Lock-System: von den Grundlagen über die verschiedenen Lock-Typen bis hin zur praktischen Analyse von Konflikten.

Was sind Locks in PostgreSQL und wozu dienen sie?

Ein Lock, auf Deutsch auch Sperre genannt, ist ein Mechanismus, mit dem PostgreSQL® den gleichzeitigen Zugriff mehrerer Transaktionen auf gemeinsame Ressourcen koordiniert. Ohne Sperren könnten zwei Transaktionen dieselbe Zeile gleichzeitig verändern, was zu inkonsistenten oder korrumpierten Daten führen würde.

PostgreSQL verwendet Locks, um die sogenannten ACID-Eigenschaften zu gewährleisten, insbesondere Isolation und Konsistenz. Eine Transaktion, die eine Ressource sperrt, signalisiert damit anderen Transaktionen: „Diese Ressource wird gerade verwendet, warte oder passe dein Verhalten an.“

Ein einfaches Alltagsbeispiel: Stellen Sie sich eine Tabelle mit Lagerbeständen vor. Wenn zwei Bestellprozesse gleichzeitig denselben Artikel reservieren wollen, muss sichergestellt sein, dass nicht beide denselben Bestand doppelt buchen. Locks verhindern genau dieses Szenario.

Wie das Lock-System von PostgreSQL aufgebaut ist

Das Lock-System von PostgreSQL® ist in mehrere Ebenen unterteilt, die jeweils unterschiedliche Granularitäten abdecken. Diese Hierarchie ermöglicht es, Sperren so gezielt wie möglich zu setzen, um unnötige Blockierungen zu vermeiden.

Die drei wichtigsten Ebenen sind:

Table-Level Locks: Sperren auf Tabellenebene, die den Zugriff auf eine gesamte Tabelle steuern
Row-Level Locks: Sperren auf Zeilenebene, die einzelne Datensätze schützen
Advisory Locks: Anwendungsdefinierte Sperren, die von der Datenbanklogik selbst nicht erzwungen werden, sondern von der Anwendung gesteuert werden

PostgreSQL setzt viele dieser Sperren automatisch im Hintergrund, abhängig davon, welche SQL-Befehle ausgeführt werden. Ein SELECT-Statement setzt beispielsweise einen anderen Lock-Typ als ein UPDATE oder ein ALTER TABLE. Das Verständnis dieser Automatismen ist entscheidend, um das Verhalten der Datenbank unter Last vorherzusagen.

Table-Level Locks: Arten und Kompatibilitäten

Table-Level Locks in PostgreSQL® steuern, welche Operationen gleichzeitig auf einer Tabelle ausgeführt werden dürfen. PostgreSQL kennt acht verschiedene Table-Level-Lock-Modi, die sich in ihrer Restriktivität stark unterscheiden.

Die wichtigsten Lock-Modi im Überblick

ACCESS SHARE: Wird von SELECT gesetzt. Sehr schwach, kompatibel mit fast allen anderen Modi.
ROW SHARE: Wird von SELECT FOR UPDATE und SELECT FOR SHARE gesetzt.
ROW EXCLUSIVE: Wird von INSERT, UPDATE und DELETE gesetzt.
SHARE UPDATE EXCLUSIVE: Wird von VACUUM, ANALYZE und bestimmten ALTER TABLE-Varianten gesetzt.
SHARE: Wird von CREATE INDEX (ohne CONCURRENTLY) gesetzt.
SHARE ROW EXCLUSIVE: Verhindert gleichzeitige Datenänderungen und SHARE-Locks.
EXCLUSIVE: Erlaubt nur noch ACCESS-SHARE-Locks, also einfache Lesezugriffe.
ACCESS EXCLUSIVE: Der stärkste Lock. Wird von DROP TABLE, TRUNCATE oder LOCK TABLE gesetzt und blockiert alle anderen Zugriffe.

Kompatibilitätskonflikte entstehen, wenn zwei Transaktionen Lock-Modi anfordern, die sich gegenseitig ausschließen. Ein ACCESS EXCLUSIVE-Lock zum Beispiel, der bei einem ALTER TABLE gesetzt wird, blockiert selbst einfache Lesezugriffe und kann in produktiven Systemen zu spürbaren Wartezeiten führen.

Row-Level Locks und ihre Rolle bei gleichzeitigen Transaktionen

Aufbauend auf dem Verständnis der Table-Level Locks gehen Row-Level Locks einen Schritt tiefer: Sie sperren nicht die gesamte Tabelle, sondern einzelne Zeilen. Dies ermöglicht eine deutlich höhere Parallelität, da verschiedene Transaktionen gleichzeitig unterschiedliche Zeilen bearbeiten können.

PostgreSQL kennt vier Row-Level-Lock-Modi:

FOR UPDATE: Sperrt die Zeile exklusiv. Andere Transaktionen können die Zeile weder lesen (mit FOR UPDATE) noch ändern.
FOR NO KEY UPDATE: Ähnlich wie FOR UPDATE, aber weniger restriktiv in Bezug auf Fremdschlüsselprüfungen.
FOR SHARE: Erlaubt anderen Transaktionen das Lesen, verhindert aber Änderungen.
FOR KEY SHARE: Der schwächste Row-Level Lock. Verhindert nur Änderungen, die Primärschlüsselwerte betreffen.

Ein praktisches Beispiel: Wenn eine Buchungsanwendung eine Zeile mit SELECT … FOR UPDATE liest, um sie anschließend zu aktualisieren, stellt sie sicher, dass keine andere Transaktion dieselbe Zeile in der Zwischenzeit verändert. Dieses Muster nennt sich „pessimistisches Locking“ und eignet sich besonders für Szenarien mit hoher Konfliktwahrscheinlichkeit.

Advisory Locks gezielt für Anwendungslogik einsetzen

Advisory Locks sind ein besonderer PostgreSQL-Lock-Typ, der nicht an Datenbankobjekte gebunden ist, sondern frei von der Anwendung definiert wird. Sie dienen dazu, anwendungsseitige Prozesse zu koordinieren, die keinen direkten Datenbankbezug haben.

Advisory Locks werden über numerische Schlüssel identifiziert, die die Anwendung selbst festlegt. PostgreSQL erzwingt keine Semantik hinter diesen Schlüsseln, das ist Aufgabe der Anwendungslogik. Zwei gängige Funktionen sind pg_advisory_lock(key) für exklusive Sperren und pg_advisory_lock_shared(key) für geteilte Sperren.

Ein typischer Anwendungsfall: Ein Hintergrundjob soll sicherstellen, dass er nur einmal gleichzeitig läuft, auch wenn mehrere Instanzen der Anwendung aktiv sind. Durch einen Advisory Lock auf einen festen Schlüsselwert kann die erste Instanz den Lock setzen, und alle weiteren erkennen, dass der Job bereits läuft, ohne dass dafür eine eigene Sperrtabelle in der Datenbank angelegt werden muss.

Advisory Locks können entweder sitzungsbasiert (sie bleiben bis zum Ende der Verbindung aktiv) oder transaktionsbasiert (sie werden am Ende der Transaktion automatisch freigegeben) sein. Diese Unterscheidung ist wichtig für die Wahl der richtigen Strategie im PostgreSQL-Umfeld.

Deadlocks erkennen und Lock-Konflikte analysieren

Ein Deadlock entsteht, wenn zwei oder mehr Transaktionen gegenseitig auf die Freigabe von Locks warten, die die jeweils andere Transaktion hält. Keiner der Beteiligten kann fortfahren, das System steckt fest.

PostgreSQL erkennt Deadlocks automatisch und löst sie auf, indem es eine der beteiligten Transaktionen abbricht und einen Fehler zurückgibt. Die Anwendung muss diese Situation behandeln und die abgebrochene Transaktion bei Bedarf erneut ausführen.

Lock-Konflikte analysieren

Für die Analyse von Lock-Konflikten bietet PostgreSQL® mehrere nützliche Systemsichten:

pg_locks: Zeigt alle aktuell gehaltenen und wartenden Locks im System.
pg_stat_activity: Gibt Auskunft über aktive Datenbankverbindungen und deren aktuellen Status.
pg_blocking_pids(): Eine Funktion, die direkt anzeigt, welche Prozesse eine bestimmte Sitzung blockieren.

Durch die Kombination dieser Sichten lässt sich schnell nachvollziehen, welche Transaktion welchen Lock hält und welche Sitzungen darauf warten. Zum Beispiel kann folgende Abfrage wartende Sitzungen identifizieren:

SELECT pid, wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE wait_event_type = ‚Lock‘;

Häufige Lock-Konflikte sind oft ein Hinweis auf suboptimale Transaktionsdesigns, zu lange offene Transaktionen oder fehlende Indizes, die dazu führen, dass unnötig viele Zeilen gesperrt werden. Eine systematische Analyse ist daher der erste Schritt zur langfristigen Stabilität Ihrer Datenbankumgebung.

Wie credativ® beim PostgreSQL-Lock-Management unterstützt

Das Verständnis von PostgreSQL-Lock-Typen ist eine Sache. Die Übertragung dieses Wissens auf produktive, hochverfügbare Datenbankumgebungen ist eine andere. Genau hier setzt unser PostgreSQL® Kompetenz-Center an.

Wir unterstützen Sie konkret in folgenden Bereichen:

Performance-Analyse: Wir identifizieren Lock-Konflikte und Deadlock-Muster in Ihrer produktiven Umgebung und leiten gezielte Optimierungsmaßnahmen ab.
Transaktionsdesign: Wir beraten Sie bei der Gestaltung effizienter Transaktionen, die unnötige Sperren vermeiden und die Parallelität maximieren.
Monitoring: Wir richten maßgeschneiderte Monitoring-Umgebungen ein, die Lock-Wartezeiten und Blockierungen in Echtzeit sichtbar machen.
Schulungen und Workshops: Wir vermitteln Ihrem Team das nötige Wissen, um Lock-Probleme selbstständig zu erkennen und zu beheben.
Operative Unterstützung: Von der einmaligen Beratung bis zur vollständigen Übernahme Ihrer PostgreSQL-Umgebung bieten wir flexible Servicemodelle an.

Haben Sie Fragen zu Lock-Konflikten oder möchten Sie Ihre PostgreSQL-Umgebung auf ein stabiles Fundament stellen? Kontaktieren Sie uns und wir besprechen gemeinsam, wie wir Ihnen am besten helfen können.

PostgreSQL® ist eine Marke der PostgreSQL Community Association of Canada. credativ® ist Competence Center für PostgreSQL®. Die Nennung dient ausschließlich der sachlichen Beschreibung von Dienstleistungen von credativ®. Es besteht keine geschäftliche Verbindung zu den genannten Markeninhabern über die beschriebene Partnerschaft hinaus.

Welche Lock-Typen gibt es in PostgreSQL?