Pessimistic vs Optimistic Locking

Two strategies for protecting shared data from concurrent updates:

flowchart LR
  subgraph Pessimistic
    T1[Txn A] -->|SELECT FOR UPDATE| L[row lock]
    L -.blocks.-> T2[Txn B waits]
    T1 -->|commit, release| L
    L --> T2
  end
  subgraph Optimistic
    T3[Txn A] -->|SELECT v=1| OK
    T4[Txn B] -->|SELECT v=1| OK
    T3 -->|UPDATE WHERE v=1 SET v=2| Win[ok]
    T4 -->|UPDATE WHERE v=1| Lose[0 rows -> retry]
  end

    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    class T1,T2,T3,T4 service;
    class L,OK,Win,Lose datastore;

Pessimistic locks the row at read time; readers block. Optimistic skips the lock and detects conflict at write time via a version column or row timestamp.

Concurrency control answers "two transactions touch the same row - what happens?" Two families of answers:

• Pessimistic: assume conflict will happen; prevent it by locking.
• Optimistic: assume conflict is rare; detect it at commit time.

Pessimistic locking

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;  -- row-level lock
-- do work
UPDATE accounts SET balance = balance - 10 WHERE id = 42;
COMMIT;  -- releases lock

Variants:

Lock	Behaviour
FOR UPDATE	Exclusive; other SELECT FOR UPDATE blocks
FOR SHARE	Shared read; other FOR SHARE allowed, FOR UPDATE blocks
FOR UPDATE SKIP LOCKED	Skip rows already locked (queue workers)
FOR UPDATE NOWAIT	Fail immediately if already locked

sequenceDiagram
  participant A as Txn A
  participant DB
  participant B as Txn B
  A->>DB: SELECT FOR UPDATE id=42
  DB-->>A: locked
  B->>DB: SELECT FOR UPDATE id=42
  Note over DB: B blocks
  A->>DB: COMMIT
  DB-->>B: now you can have it

Optimistic locking

Add a version column (or use a lock_version per ORM):

-- Read
SELECT id, balance, version FROM accounts WHERE id = 42;
-- balance=100, version=5

-- Write
UPDATE accounts
   SET balance = balance - 10, version = 6
 WHERE id = 42 AND version = 5;
-- returns 1 row affected on success, 0 on conflict

If another transaction bumped version to 6 first, our UPDATE matches 0 rows. We retry from the top.

sequenceDiagram
  participant A as Txn A
  participant DB
  participant B as Txn B
  A->>DB: SELECT v=5
  B->>DB: SELECT v=5
  A->>DB: UPDATE WHERE v=5 -> v=6
  DB-->>A: 1 row
  B->>DB: UPDATE WHERE v=5
  DB-->>B: 0 rows -> conflict
  B->>B: retry from SELECT

Choosing between them

Situation	Pick
High write contention	Pessimistic; retries are expensive
Low write contention	Optimistic; no lock cost on the happy path
Long-running transaction	Optimistic; pessimistic would hold lock too long
Critical correctness, short txn	Pessimistic; deterministic
Distributed app, no DB locks	Optimistic; can run anywhere
Many readers, occasional writes	Optimistic

ORM patterns

ORM	Mechanism
Hibernate / JPA	@Version field; throws OptimisticLockException
ActiveRecord (Rails)	lock_version column; update_lock_version!
Django	select_for_update() queryset; manual version field
Entity Framework	[ConcurrencyCheck] or [Timestamp]

@Entity
class Account {
    @Id Long id;
    BigDecimal balance;
    @Version Long version;
}

Compare-and-swap (CAS) outside DBs

The optimistic pattern generalizes:

# Redis CAS via WATCH/MULTI
pipe = redis.pipeline()
while True:
    pipe.watch("k")
    val = pipe.get("k")
    pipe.multi()
    pipe.set("k", new_value(val))
    try:
        pipe.execute()
        break
    except WatchError:
        continue  # someone else modified; retry

DynamoDB conditional writes, S3 conditional puts, etcd compare-and-swap - all the same idea.

Deadlock avoidance (pessimistic)

flowchart TB
  TA[Txn A locks row 1] --> TA2[wants row 2]
  TB[Txn B locks row 2] --> TB2[wants row 1]
  TA2 -. deadlock .-> TB2

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;

• Always acquire locks in the same canonical order (e.g. by id ascending).
• Set LOCK_TIMEOUT so deadlocks abort instead of hang.
• Most DBs detect deadlock and abort one txn; handle the abort and retry.

Retry budgets (optimistic)

for attempt in range(3):
    try:
        update_with_version_check()
        break
    except OptimisticLockException:
        sleep(0.05 * 2**attempt + random())  # jitter
else:
    raise TooManyRetries

Without a budget, hot rows livelock under contention.

Where locking choices fit

flowchart TB
  PO((Pessimistic /<br/>optimistic))
  MVCC[MVCC isolation levels<br/>DB primitive both build on]
  TPL[Two-phase locking<br/>specific pessimistic algo]
  TD[Threading / deadlocks<br/>OS analog]
  DL[Distributed lock<br/>cross-process generalisation]
  MVCC --> PO
  PO --> TPL
  TD -. analog .- PO
  DL --> PO

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class PO service;
    class MVCC,TPL,TD,DL datastore;

Glossary & fundamentals

Tag	Concept	What it is	Page
HLD	MVCC isolation levels	the DB primitive both build on	mvcc-isolation-levels
LLD	Threading and deadlocks	OS-level analog of pessimistic locking	threading-and-deadlocks
HLD	Distributed lock	cross-process pessimistic lock	distributed-lock
HLD	Idempotency retries	optimistic retry needs idempotency	idempotency-retries

Quick reference

Pessimistic

SELECT ... FOR UPDATE         -- exclusive row lock
SELECT ... FOR UPDATE SKIP LOCKED  -- queue workers
SELECT ... FOR SHARE          -- shared read lock

Optimistic

UPDATE t SET col=..., version=version+1 WHERE id=? AND version=?
-- 0 rows = conflict → retry

Choose pessimistic when

• High write contention
• Short transactions
• Determinism matters

Choose optimistic when

• Low contention
• Long transactions
• Distributed; no shared DB
• Many readers, few writers

ORM notes

• JPA/Hibernate: @Version → OptimisticLockException
• Rails ActiveRecord: lock_version column
• Django: select_for_update()
• EF Core: [Timestamp] or [ConcurrencyCheck]

CAS pattern (Redis, DynamoDB, etcd)

WATCH + MULTI/EXEC; conditional put; compare-and-swap.

Deadlock checklist (pessimistic)

• Always lock in canonical order (by id ascending)
• Set LOCK_TIMEOUT
• Handle DB's deadlock-aborted error and retry

Retry checklist (optimistic)

• Bounded attempts (3-5)
• Exponential backoff with jitter
• Surface error after budget exhausted

Refs

• PostgreSQL Explicit Locking docs
• Vlad Mihalcea blog
• Hibernate locking docs

FAQ

What is the difference between pessimistic and optimistic locking?

Pessimistic locking grabs the row at read time so other writers wait. Optimistic locking skips the lock and detects conflicts at commit using a version column.

When should I use optimistic locking?

Use it when conflicts are rare and you want maximum read throughput. Hot rows with frequent contention will retry too often and benefit from pessimistic locks instead.

How does optimistic locking detect conflicts?

Each row carries a version column. The UPDATE statement matches on the old version; if no row matches, another writer beat you and you must retry.

Does SELECT FOR UPDATE cause deadlocks?

It can. Two transactions that lock rows in different orders can deadlock. Always lock rows in a consistent order or use a single statement when possible.

Can I combine optimistic and pessimistic locking?

Yes. Use pessimistic locks on hot inventory rows and optimistic locking elsewhere. Many ORMs offer both via an annotation or query hint.

Related Topics

• MVCC Isolation Levels: the DB engine machinery both locking styles sit on
• Threading and Deadlocks: OS-level analog of pessimistic locking
• Distributed Lock: cross-process generalization when one DB isn't enough

Further reading

• Doc - PostgreSQL - Explicit Locking
• Doc - MySQL - Locking reads
• Blog - Vlad Mihalcea - Optimistic vs Pessimistic locking
• Doc - Hibernate - Locking