Pessimistic Locking
Subject: Computer and Technology noun /ˌpɛs.əˈmɪs.tɪk ˈlɑːk.ɪŋ/ 1 definition
- database-systems
- concurrency-control
- transaction-management
- data-integrity
-
Definition (noun)
Entry #315 · UpdatedPessimistic Locking is a concurrency control strategy that assumes simultaneous accesses to a shared resource are likely to conflict and therefore prevents interference proactively by acquiring locks before reading or modifying data. Under this model, a transaction or thread requests a shared or exclusive lock from a lock manager—or through explicit database syntax such as
SELECT ... FOR UPDATE—forcing competing operations that request an incompatible lock mode to block or fail fast until the holder commits, rolls back, or releases the lock. Relational engines commonly enforce pessimistic locking through protocols such as strict two-phase locking (Strict 2PL), holding write locks until transaction completion to eliminate lost updates and dirty reads without requiring post-hoc conflict validation. Its core purpose is to preserve strict data integrity under high contention or when rollbacks are costly, trading reduced throughput and potential deadlock exposure—unlike optimistic locking, which detects conflicts only at commit time—for deterministic serialized access.Common Forms
Shared (Read) Locks: Allow multiple concurrent transactions to hold read locks on the same resource simultaneously (such as
SELECT ... FOR SHARE) while blocking any writer from acquiring an exclusive lock until all shared holders release.Exclusive (Write) Locks: Grant a single transaction sole modification rights on a row, page, or table (such as
SELECT ... FOR UPDATE), blocking conflicting updates, deletes, and competing lock acquisitions—and blocking plain reads in non-MVCC engines—until commit or rollback.Intent and Update Locks: Coordinate multi-granularity and lock-upgrade paths inside relational lock managers—intent (
IS/IX) locks advertise finer row-level locks at the table level so coarse operations check compatibility without scanning rows, while update (U) locks serialize read-before-write upgrades to prevent conversion deadlocks.Distributed Lease Locks: Enforce mutual exclusion across decoupled microservices or cluster nodes through consensus-backed stores such as etcd or ZooKeeper, pairing time-bounded TTL leases with monotonic fencing tokens so crashed holders automatically lose ownership and cannot commit stale writes.
Example
In an airline seat-reservation service backed by PostgreSQL, a checkout transaction runs
BEGIN; SELECT status FROM seats WHERE flight_id = 'UA412' AND seat_no = '14A' FOR UPDATE;to lock the row exclusively before charging the customer's payment card. While that transaction runs, a competing worker executing the sameFOR UPDATEquery blocks at the lock manager until the holder updatesstatusfrom'available'to'booked'and commits (or fails immediately ifFOR UPDATE NOWAITis specified), allowing the second worker to observe'booked'and decline the duplicate purchase.