Optimistic vs pessimistic locking

Both prevent lost updates when two clients modify the same row. Pessimistic locking locks the row (or document) for the duration of the transaction so others must wait. Optimistic locking doesn’t lock; it checks at update time that the row hasn’t changed (e.g. via a version column) and fails the update if it has, so the client can retry.

Pessimistic: lock then read/update

sequenceDiagram participant T1 as Transaction 1 participant T2 as Transaction 2 participant DB as Database T1->>DB: SELECT ... FOR UPDATE (lock row) DB-->>T1: Row locked T2->>DB: SELECT ... FOR UPDATE (same row) DB-->>T2: Wait... T1->>DB: UPDATE; COMMIT (release lock) DB-->>T2: Now lock granted

Optimistic: check version on update

sequenceDiagram participant T1 as Client A participant T2 as Client B participant DB as Database T1->>DB: SELECT (version=1) T2->>DB: SELECT (version=1) T1->>DB: UPDATE SET ... WHERE id=x AND version=1 DB-->>T1: OK, version=2 T2->>DB: UPDATE SET ... WHERE id=x AND version=1 DB-->>T2: 0 rows (version now 2) - conflict Note over T2: Retry: re-read, then update

Aspect	Pessimistic	Optimistic
Lock	Hold lock during txn	No lock; check version/timestamp on write
Conflict	Second writer waits	Second writer gets 0 rows updated; retry
Best for	High contention, critical sections	Low contention, high throughput

Use pessimistic when conflicts are frequent and blocking is acceptable. Use optimistic when conflicts are rare and you want to avoid holding locks (e.g. version column + WHERE version=old_version).