Optimistic vs Pessimistic Locking
Two concurrency control strategies: optimistic locking detects conflicts at write time using version numbers, while pessimistic locking prevents conflicts by acquiring locks at read time.
Description
Pessimistic locking assumes conflicts are likely and acquires an exclusive lock on data when it is first read. In SQL, this is done with SELECT ... FOR UPDATE, which blocks other transactions from reading (FOR UPDATE) or modifying the same rows until the lock is released at COMMIT. Pessimistic locking guarantees conflict-free writes but reduces concurrency because other transactions must wait. It is appropriate for high-contention scenarios like seat reservation or inventory decrement where conflicts are frequent.
Optimistic locking assumes conflicts are rare and does not acquire locks during reads. Instead, it adds a version column (or updated_at timestamp) to each row. When updating, the application includes the original version in the WHERE clause: UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = $1 AND version = $2. If another transaction modified the row first, the WHERE clause matches zero rows, and the application detects the conflict and retries or reports an error. Optimistic locking is ideal for low-contention scenarios with long think times, like editing a form.
The choice depends on contention frequency and acceptable complexity. Pessimistic locking is simpler to reason about but risks deadlocks when multiple transactions lock rows in different orders. Optimistic locking scales better and never blocks but requires retry logic and can suffer from starvation under high contention (one transaction keeps losing the race). Many ORMs support optimistic locking natively -- Prisma uses @updatedAt, JPA uses @Version, and Django has django-concurrency.
Prompt Snippet
Implement optimistic locking by adding a version INTEGER DEFAULT 0 column and including AND version = $expected_version in every UPDATE WHERE clause. Return the updated row count and throw a ConcurrentModificationError if rows affected is zero. For high-contention paths like inventory decrement, use pessimistic locking with SELECT ... FOR UPDATE SKIP LOCKED to process only non-locked rows, avoiding queue starvation. Set a lock_timeout = 3s per transaction to prevent indefinite lock waits and add monitoring for lock wait events via pg_stat_activity.
Tags
Related Terms
ACID Transactions
The four guarantees (Atomicity, Consistency, Isolation, Durability) that ensure database transactions are processed reliably even under concurrent access and system failures.
Transaction Isolation Levels
Database settings that control the visibility of concurrent transaction changes, trading off between data consistency and throughput.
Deadlock Detection & Prevention
Strategies for detecting when two or more transactions are permanently blocked waiting for each other's locks, and design patterns to prevent this from occurring.