Optimistic vs Pessimistic Locking in Databases

Optimistic locking and pessimistic locking are two strategies for handling concurrent writes to the same data without corrupting it. Pessimistic locking assumes conflicts are likely and blocks other transactions from touching a row until the current one finishes. Optimistic locking assumes conflicts are rare, lets transactions proceed freely, and only checks for a conflict at the moment of commit. Both prevent the same problem — two transactions overwriting each other’s changes — but they trade throughput against how gracefully they handle a genuine collision.

The problem they both solve

Imagine two users load the same “edit profile” form at the same time, both see the current bio text, and both submit an update a few seconds apart. Without any concurrency control, the second write silently overwrites the first — a classic race condition, just expressed at the database layer instead of in application code. Both locking strategies exist to catch or prevent that overwrite; they just intervene at different points in the sequence.

Pessimistic locking: block first, ask questions never

Pessimistic locking acquires a lock on a row (or range of rows) as soon as a transaction intends to modify it, and holds that lock until the transaction commits or rolls back. Any other transaction trying to acquire a conflicting lock on the same row simply waits.

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
-- other transactions attempting to lock row 42 now block here
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

SELECT ... FOR UPDATE is the standard SQL mechanism for this — it locks the selected rows for the duration of the transaction. This guarantees no other transaction can modify the same row in the meantime, which makes pessimistic locking straightforward to reason about: whatever you read under the lock is guaranteed still accurate when you write.

The cost is reduced concurrency. Locks held for the length of a transaction mean other transactions queue up behind them, and a slow or stalled transaction can hold a lock far longer than intended, creating contention or even deadlocks if two transactions try to lock the same rows in a different order.

Optimistic locking: proceed, then verify

Optimistic locking doesn’t take any lock up front. Instead, it reads the current state, lets the caller make changes locally, and — at write time — checks whether the row has changed since it was read. The most common implementation uses a version column:

-- Read
SELECT balance, version FROM accounts WHERE id = 42;
-- Returns: balance = 500, version = 7

-- Write, checking the version hasn't moved
UPDATE accounts
SET balance = 400, version = 8
WHERE id = 42 AND version = 7;

If another transaction updated row 42 in between — bumping its version to 8 — this UPDATE matches zero rows, and the application detects the conflict from the affected-row count and decides what to do: retry with fresh data, merge the changes, or surface an error to the user. No lock is ever held while the transaction “thinks,” which is precisely why optimistic locking scales better under high concurrency — most transactions never actually collide, and the ones that do fail fast and cheap rather than making every other transaction wait.

Comparison

	Pessimistic locking	Optimistic locking
When conflict is handled	Prevented up front	Detected at commit
Mechanism	Row/range locks (SELECT ... FOR UPDATE)	Version or timestamp column check
Best for	High contention, long transactions	Low contention, short transactions
Failure mode	Waiting, possible deadlocks	Rejected write, requires retry logic
Throughput under low contention	Unnecessary overhead	Minimal overhead
Throughput under high contention	Predictable, but serialized	Many retries, can thrash

Choosing between them

Pessimistic locking fits situations where conflicts are common and correctness matters more than raw throughput — financial ledgers, inventory counts being decremented by many concurrent orders, or any workflow with long-running transactions where a failed write late in the process would be expensive to redo. It’s also the simpler mental model: you never have to write retry logic, because the database just makes competing writers wait their turn.

Optimistic locking fits situations where conflicts are rare and most transactions are short — a user editing their own profile, a document with a single typical editor, an API updating one row at a time. Since most writes never actually collide, the version check almost always passes on the first try, and you avoid holding locks that would otherwise sit idle blocking nobody in particular.

The choice also interacts with your transaction isolation level: a database running at SERIALIZABLE isolation already does a form of optimistic conflict detection internally, aborting transactions that would violate serializability, which can reduce how much manual version-checking logic your application needs to write. Lower isolation levels leave more of that responsibility to the application.

Neither strategy is exclusive to a particular database engine — both are patterns you implement using ACID transaction primitives that most relational databases already provide. The decision is about workload shape, not tooling. If you’re also managing a pool of database connections across many concurrent transactions, see our guide on connection pooling — lock contention and pool exhaustion often compound each other under load.

The takeaway

Pessimistic locking blocks conflicting writers up front with row locks, trading concurrency for a simpler, wait-based guarantee that data won’t change underneath you. Optimistic locking skips the lock, lets transactions run freely, and checks a version column at write time, retrying only the rare transaction that actually collided. Pick pessimistic locking for high-contention or long-running writes where correctness under load matters most; pick optimistic locking for low-contention, short transactions where most writes will never conflict in the first place.