SELECT ... FOR UPDATE
SELECT ... FOR UPDATE is a SQL statement in PostgreSQL that implements pessimistic row-level locking. It acquires exclusive locks on the rows returned by a query, preventing other concurrent transactions from modifying them or acquiring conflicting locks until the current transaction commits or rolls back. It suits high-contention scenarios such as financial ledgers.
Timeline (last 90 days)
SELECT ... FOR UPDATE reads the latest committed version of the rows (bypassing MVCC snapshot isolation rules) and acquires an exclusive lock on each matching physical row found by the query.
Pessimistic locking (SELECT ... FOR UPDATE) eliminates lost updates and race conditions by serializing access to contended rows, but it reduces throughput, increases latency, may exhaust the connection pool and introduces deadlock risk.
High-reliability ledgers use optimistic concurrency control (OCC) together with immutable append constraints, or use pessimistic row-level locking (SELECT ... FOR UPDATE) on account pointer records.
All Facts (3)
SELECT ... FOR UPDATE reads the latest committed version of the rows (bypassing MVCC snapshot isolation rules) and acquires an exclusive lock on each matching physical row found by the query.
50%UnverifiedPessimistic locking (SELECT ... FOR UPDATE) eliminates lost updates and race conditions by serializing access to contended rows, but it reduces throughput, increases latency, may exhaust the connection pool and introduces deadlock risk.
50%UnverifiedHigh-reliability ledgers use optimistic concurrency control (OCC) together with immutable append constraints, or use pessimistic row-level locking (SELECT ... FOR UPDATE) on account pointer records.
50%