intermediate~4h

Locking — Optimistic vs Pessimistic

Locking controls concurrent access to data to prevent anomalies. The choice between optimistic and pessimistic locking dramatically affects application design and performance.

Isolation levels control what a transaction can see; locking is the actual mechanism the database uses underneath to enforce that — and once you're building anything with real concurrent writes (checkout flows, seat booking, inventory), you have to make an explicit choice about which locking strategy fits your access pattern.

Two transactions trying to update the same row at the same time is unavoidable in any system with real concurrent users — without an explicit strategy for handling that collision, you either silently lose one transaction's update (last writer wins, often wrong) or the database has to make a choice on your behalf that may not fit your actual requirements.

Pessimistic locking — acquiring a lock upfront (SELECT ... FOR UPDATE) so no other transaction can touch the row until you're done; assumes conflict is likely. Optimistic locking — proceeding without a lock, then checking a version/timestamp column at write time to detect whether anyone else changed the row first; assumes conflict is rare.

Locking controls concurrent access to data to prevent anomalies. The choice between optimistic and pessimistic locking dramatically affects application design and performance.

Pessimistic Locking

Assumes conflicts are common.
Locks the resource BEFORE accessing it.
Other transactions BLOCKED until lock is released.
SQL: SELECT ... FOR UPDATE, SELECT ... FOR SHARE.
Good for: high-conflict scenarios, short transactions, guaranteed serialization.

Optimistic Locking

Assumes conflicts are RARE.
No locks taken on read — proceed and check on write.
On write: verify that data hasn't changed since last read (using version number or timestamp).
If changed: CONFLICT — application must retry.
Good for: low-conflict scenarios, read-heavy workloads, long checkout processes.

PostgreSQL lock levels (granularity)

Row-level locks: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE.
Table-level locks: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.
Advisory locks: application-level locks using pg_advisory_lock().

Pessimistic Locking (FOR UPDATE)

Transaction A: SELECT * FROM accounts WHERE id=1 FOR UPDATE.
PostgreSQL adds a row-level lock on account_id=1.
Transaction B tries: SELECT * FROM accounts WHERE id=1 FOR UPDATE.
Transaction B BLOCKS (waits for Transaction A's lock).
Transaction A: UPDATE accounts SET balance=balance-1000 WHERE id=1.
Transaction A: COMMIT. Lock released.
Transaction B resumes with fresh data.

Optimistic Locking (version-based)

Transaction A: SELECT id, balance, version FROM accounts WHERE id=1.

(Returns: id=1, balance=10000, version=5)

Transaction A processes the transfer locally.
Transaction B also reads the same row (version=5).
Transaction B updates: UPDATE accounts SET balance=9000, version=6 WHERE id=1 AND version=5. (1 row updated — success)
Transaction A tries to update: UPDATE accounts SET balance=9500, version=6 WHERE id=1 AND version=5. (0 rows updated! version is now 6)
Transaction A: sees 0 rows updated → CONFLICT → retry from step 1.

Step 1: Assess your conflict rate — high conflict → pessimistic; low conflict → optimistic.

Step 2: For pessimistic: use SELECT ... FOR UPDATE to lock rows before reading and modifying.

Step 3: For optimistic: add a version or updated_at column to the entity.

Step 4: On update: include AND version = :expected_version in WHERE clause.

Step 5: Check rows affected — 0 rows means conflict; retry or notify user.

Step 6: Set lock_timeout for pessimistic to prevent indefinite blocking.

Step 7: Test under concurrent load to verify your locking strategy.

Choose locking strategy based on conflict rate: optimistic (rare conflicts), pessimistic (frequent conflicts).
Implement version/timestamp column for optimistic locking at the table level (not application level).
Always lock resources in a consistent order to prevent deadlocks.
Set lock_timeout for pessimistic: never wait indefinitely (SET lock_timeout = '5s').
Use SKIP LOCKED for queue processing — multiple workers claim rows without contention.
NOWAIT for user-facing operations — fail fast rather than making users wait.
Handle deadlocks with retry logic — PostgreSQL automatically detects and resolves deadlocks.
Short transactions with pessimistic locking — locks are held for the transaction duration.
Use optimistic locking in Spring Boot JPA: @Version annotation on entity.
Monitor lock contention: pg_locks and pg_stat_activity for blocking queries.

Deadlock from inconsistent lock order — always lock rows in same order (order by ID).
Long pessimistic lock duration — locking a row for a 30-second HTTP request blocks all others.
Not checking rows_affected for optimistic — silently accepting 0 rows updated as success.
Optimistic conflict not retried — treating a conflict as an error rather than a retryable condition.
FOR UPDATE on read replicas — read replicas don't support FOR UPDATE; must use primary.
Nested transactions with locks — releasing outer lock releases inner locks in PostgreSQL.
Not setting lock_timeout — application waits indefinitely; cascading failures under load.
Using advisory locks without cleanup — session-level advisory locks persist until session ends.

Optimistic locking scales better under low conflict — no blocking; higher concurrency.
SKIP LOCKED enables high-performance job queues — N workers claim N rows without blocking.
Row-level locking (FOR UPDATE) > table-level locking — higher concurrency.
Index the version column or WHERE clause columns for optimistic locking — fast conflict detection.
Monitor pg_stat_activity for 'waiting' state — indicates lock contention.
pg_blocking_pids(pid) function: find what's blocking a session.
Reduce lock scope: lock only rows you'll modify, not all rows you read.

Pessimistic locks held too long on rows tied to a user-facing action (an editable form left open) can be weaponized as a denial-of-service vector — a malicious or careless client that opens the edit flow and never completes it can block legitimate users from the same row indefinitely unless a lock timeout is enforced.

Track deadlock rate and lock-wait time as first-class production metrics — a rising deadlock rate almost always signals a new code path acquiring locks in an inconsistent order, and is much easier to trace right after it starts than months later.

Implement deadlock retry in application layer: catch 40P01 error code, retry with backoff.
Alert on deadlocks: PostgreSQL logs deadlocks with log_lock_waits=on.
SET lock_timeout = '10s' at session level for all application connections.
Monitor lock wait time: pg_stat_activity.wait_event_type = 'Lock'.
For Spring Boot JPA: use @Version on entities; set Hibernate optimistic-lock strategy.
Queue pattern with SKIP LOCKED: benchmark with N workers to find optimal parallelism.

Implement pessimistic locking for a bank transfer. Demonstrate that Session A holds the lock while Session B waits. Measure the wait time.
Implement optimistic locking using a version column. Simulate two sessions both reading version=5 and both trying to update. Verify only one succeeds and the other gets 0 rows updated.
Create a DEADLOCK intentionally with two sessions locking rows in opposite orders. Observe PostgreSQL's detection. Then fix by enforcing consistent lock order.
Implement a job queue with SKIP LOCKED. Start 3 psql sessions simultaneously, all claiming from a queue. Verify each gets a different job.

Pessimistic locking: lock before access (SELECT FOR UPDATE); blocks concurrent access; good for high-conflict.
Optimistic locking: no lock; version check on write; retry on conflict; good for low-conflict, high-throughput.
FOR UPDATE NOWAIT: fail immediately if locked (don't wait).
FOR UPDATE SKIP LOCKED: skip locked rows (perfect for queue processing with multiple workers).
Deadlock: circular lock dependency; PostgreSQL detects automatically, aborts one transaction.
Deadlock prevention: always acquire locks in consistent order.
lock_timeout: essential production setting to prevent indefinite blocking.
Spring Boot: @Version for optimistic locking in JPA entities.

Want a visual for this concept?

Generate a diagram tailored to “Locking — Optimistic vs Pessimistic” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Window Functions — ROW_NUMBER, RANK, LEAD, LAG →← Back to all SQL chapters