The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use optimistic locking when two transactions rarely write the same row, and use pessimistic locking when they collide often enough that it is cheaper to make one wait than to let both run and undo one. Neither approach is faster in general. The right choice depends on how often conflicts happen, what a conflict costs your application, and how your specific database and ORM enforce locks.
What each approach does
Both approaches solve the same problem: two transactions read the same data, both compute a new value, and the second write silently erases the first. This is the classic lost update. The difference is when the database or application discovers the collision and what it does about it.
Optimistic concurrency control: detect the conflict at write time
An optimistic transaction reads data without reserving it. When it later writes, the system checks whether the data still matches what the transaction saw. If it does, the write goes through. If someone else changed it in the meantime, the write is rejected and the application must respond.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Microsoft Learn’s Transaction Locking and Row Versioning Guide for SQL Server (checked October 2026) puts the model simply: “In optimistic concurrency control, transactions don’t lock data when they read it.” Microsoft positions this approach for low-contention cases, where an occasional rollback costs less than locking every read.
#1 Best Overall
The key point is that optimistic locking detects conflicts; it does not prevent them. A rejected write is not a resolved conflict. Your code has to retry the operation against fresh data, or show the user what changed and let them reconcile it.
Pessimistic locking: reserve the data before changing it
A pessimistic transaction takes a lock on the data it intends to use. Other transactions that need a conflicting lock wait until the holder finishes. In PostgreSQL, the usual tool is a locking read such as SELECT ... FOR UPDATE. The PostgreSQL 17 documentation, “Explicit Locking,” states that conflicting updates and locking reads wait for the transaction holding the row lock to end.
This suits data where conflicts are frequent and predictable, and where waiting is cheaper than repeatedly discovering a conflict and rolling back. The cost is that waiting consumes time and throughput, and every lock you hold lengthens the queue behind it.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Side-by-side comparison
| Decision axis | Optimistic | Pessimistic |
|---|---|---|
| Expected conflicts | Fits when conflicts are uncommon | Worth considering when conflicts are frequent and concentrated on known rows |
| Cost when a conflict happens | The write fails; the application pays for a retry, a rollback, or a reconciliation step | The second transaction waits; lock management and queueing consume time |
| Cost when there is no conflict | Usually minimal: no locks taken on read | Lock acquisition and hold time apply even when nothing collides |
| Application obligations | Check the version on every relevant write; define a retry or reconciliation path | Keep transactions short; choose lock scope carefully; handle lock-wait timeouts and deadlock aborts |
| Typical mechanism | A version number or timestamp compared during the update | An explicit lock, such as a row-locking read |
| Main implementation question | Is every write to this data checked against the version the reader saw? | Does the lock mode requested in this engine protect exactly the rows and operations intended? |
These are workload heuristics, not guarantees. No published benchmark establishes a universal conflict threshold or a general performance multiplier, so treat the table as a way to frame your decision, not as a measured result for your system.
Implementing the optimistic pattern
The most common form is a version column. The read returns the row and its version, and the update succeeds only if the version has not moved.
- Add a version column to the table, for example
version integer NOT NULL DEFAULT 0. - Read the row and keep its version value with the data your code is working on.
- Compute the new state in application code, without holding a database lock.
- Issue an update conditioned on the version you read, and increment it:
UPDATE products
SET price = 19.99,
version = version + 1
WHERE id = 42
AND version = 7;- Check the number of affected rows. If it is zero, another transaction changed the row (or the row no longer exists). Treat that as a conflict. Do not retry the same statement blindly; reload the row, re-apply the user’s intent, and try again, or surface the difference to the user.
A timestamp such as updated_at can serve the same purpose, but it has to be exact. Two writes within the same clock tick can share a value, and a timestamp that a process updates inconsistently offers no protection. A numeric version is usually easier to reason about.
Two discipline problems undermine this pattern. The first is writes that bypass the version check, such as a batch script or a direct SQL fix that does not increment the version. The second is a code path that updates the row without reading the version first. One unguarded writer is enough to reintroduce lost updates for everyone else.
If you use an ORM, its optimistic version support does this for the entities it manages. Hibernate’s documentation on locking (main-branch user guide, checked October 2026) describes version-based checks and notes that Hibernate relies on database mechanisms. Confirm how your ORM version handles bulk updates and native queries, because those often run outside the entity lifecycle that the check depends on.
Implementing the pessimistic pattern
The pessimistic version reads the row with a locking clause inside a transaction, performs the change, and commits:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;Once the first transaction runs FOR UPDATE on row 1, any other transaction that tries to lock or update that row waits until the first one commits or rolls back. The lock is released at transaction end, not when the statement finishes, which is why transaction length matters so much.
Follow these rules to keep pessimistic locking from becoming a bottleneck:
- Keep the transaction short. Do the reads, compute the result, write, and commit. Do not hold a lock while waiting for user input, an email, a payment API, or any other slow external call unless you have deliberately accepted that cost.
- Lock the smallest set of rows that protects the invariant. Locking a whole table because one row matters serializes unrelated work.
- Acquire multiple locks in a consistent order, such as ascending primary key. Inconsistent ordering is the usual route to deadlock.
- Set a lock-wait timeout appropriate for your application so that a stuck transaction cannot hold callers indefinitely.
- Expect lock cost even without contention. PostgreSQL’s documentation notes that row locks can cause disk writes, so do not assume a locking read is free.
Where the database and ORM change the answer
Locks are not the whole isolation story
Row-level locks and isolation levels are separate mechanisms. Isolation levels define which anomalies a transaction may observe; locks control whether specific rows can be changed concurrently. In PostgreSQL, ordinary UPDATE statements already take row-level locks on the rows they modify, and its consistency guidance distinguishes ordinary MVCC behavior from cases where an application invariant needs an explicit lock. If a rule spans several rows or depends on a read that was not locked, an ordinary update may not protect it.
Engine-specific behavior
Microsoft documents both locking and row-versioning mechanisms for SQL Server, and its behavior is specific to that engine. PostgreSQL’s rules differ in detail. Do not assume that a lock mode or isolation behavior you verified in one database holds in another, and recheck the behavior when you upgrade the database or the ORM.
Failure modes and recovery
- Silent overwrites despite a version column. A writer skipped the version condition. Audit every update path, including migrations and scripts, and route all writes through the same guarded function or repository method.
- Retry storms under optimistic locking. Under sustained contention, many transactions keep failing and retrying. Add a bounded retry count with a short backoff, and if the same rows fail repeatedly, switch those rows to pessimistic locking.
- Lock waits that stall the application. A long transaction holds a row lock. Shorten the transaction, move external calls outside it, and apply a lock-wait timeout.
- Deadlocks. PostgreSQL detects deadlocks automatically and aborts one participating transaction. Make the application treat that abort as a retryable failure only when the transaction is safe to repeat, and fix the lock ordering that caused it.
How to choose
- Measure or estimate how often two writers touch the same row within one transaction’s lifetime. Low overlap favors optimistic locking.
- Estimate what a rejected write costs your users: a silent retry, a visible error, or a manual merge. If a manual merge is expensive, waiting may be the better trade.
- Check whether the work spans external calls or user think time. If it does, pessimistic locking over that span is usually a poor choice; redesign the flow around a version check instead.
- Confirm that every writer, including scripts and the ORM’s bulk paths, participates in the protocol you pick.
- Test the chosen behavior against the actual database version, isolation level, and ORM version you run in production, under realistic concurrency.
In practice, many systems use both: optimistic version checks for most entities, with pessimistic locks reserved for a few hot rows or multi-row invariants where collisions are routine.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
