Optimistic vs. Pessimistic Locking: Two Ways to Stop Database Updates From Colliding

When two people or processes update the same piece of data at nearly the same time, one can end up overwriting the other’s change without ever knowing it happened. This is the classic “lost update” problem, and it shows up anywhere a database, or any multi-process application, allows concurrent writes. There are two broad strategies for handling it: pessimistic locking and optimistic locking. The names are opposites, and so is the approach each one takes.

The “silent overwrite” problem

Say a table tracks inventory, and two processes, A and B, both read the same row at nearly the same moment. Both see “10 units in stock” and both compute “subtract one, so it should become 9.” With no safeguard, whichever update runs last simply writes 9 — even though two separate decrements should have produced 8. One update vanishes without a trace.

There are two ways to prevent that: don’t let anyone else touch the data while you’re working on it (pessimistic locking), or let everyone touch it, but check for contradictions before committing (optimistic locking).

Pessimistic locking: lock first, then touch the data

Pessimistic locking grabs an exclusive lock the moment it reads a row, and makes every other process wait until it’s done. The assumption baked into the name — “someone else is probably going to try to touch this too” — is why you lock it down before doing anything else.

The FileLock class in this project’s own core/file_lock.py is a real example. The GUI (site_manager_web.py) and a background maintenance run (maintenance_agent.py) can both try to write the same config file (sites_*.json and similar) at once, so the code takes an exclusive lock before writing and makes the other process wait until it’s released.

from core.file_lock import FileLock

with FileLock('sites_default.json', timeout=10):
    # anything else trying to touch this file waits here
    _atomic_write_json('sites_default.json', data)

An earlier article covered how that lock is implemented at the OS level — fcntl on Unix versus msvcrt on Windows. This one is a level up: the design decision to take a lock before writing at all, not the mechanics of how the lock itself works.

On the database side, most RDBMSes offer pessimistic locking through a SELECT ... FOR UPDATE clause.

BEGIN;
SELECT quantity FROM stock WHERE id = 42 FOR UPDATE;
-- any other transaction trying to update this row now blocks here
UPDATE stock SET quantity = quantity - 1 WHERE id = 42;
COMMIT;

Note: a deadlock happens when two transactions each hold a lock the other is waiting for, so neither can proceed. Pessimistic locking reliably prevents lost updates, but getting the lock order wrong can produce exactly this situation. Most RDBMSes detect deadlocks and force one transaction to fail, leaving retry logic to the application.

The downside is that everyone else waits while a lock is held. With long-running operations or heavy concurrent access, that waiting adds up.

Optimistic locking: assume conflicts are rare, check afterward

Optimistic locking takes the opposite view: “two processes updating the same row at once probably won’t happen often,” so it never locks on read. Instead, it checks at write time whether the row has changed since it was read.

The usual implementation adds a version or updated_at column and includes that value in the update’s WHERE clause.

UPDATE stock
SET quantity = 9, version = 6
WHERE id = 42 AND version = 5;

If this affects exactly one row, nobody touched it since it was read, and the update succeeds. If it affects zero rows, the version no longer matches — someone else got there first. The application treats that as a detected conflict: re-read the current value and retry, or tell the user their change collided with someone else’s.

Note: “conflict” here means two attempted changes to the same data that can’t both be applied. Optimistic locking doesn’t prevent conflicts — it detects, at write time, whether one actually occurred.

The same idea shows up on the web. An earlier article on HTTP cache headers covered ETag, which normally validates a cache. Paired with an If-Match header, though, it becomes a form of optimistic locking: “only accept this update if the version I last fetched still matches what’s on the server now.” A client sends If-Match: "<its last-fetched ETag>" on an update request, and the server rejects the write if the current ETag doesn’t match.

WordPress’s “post lock” isn’t strictly pessimistic locking

WordPress’s admin screen has a familiar warning: open a post someone else is already editing, and you’ll see “So-and-so is currently editing this post.” It’s built on the Heartbeat API, which periodically writes and refreshes a lock transient marking who’s editing.

But this isn’t a hard, database-enforced lock. It’s advisory — a notice, not a barrier. If both users ignore it and hit save, wp_update_post() has no built-in conflict detection, so whichever save happens last simply wins. The UI flags that someone else is in there, but nothing at the database level actually prevents a lost update — so saving anyway, warning ignored, is expected behavior rather than a bug.

Comparing the two

Which one to use

When conflicts happen often, or redoing a failed update is expensive (inventory allocation, say, where retrying gets complicated), locking up front is usually easier to reason about. When reads dominate and write collisions are rare, pessimistic locking’s constant waiting becomes a drag, and optimistic locking fits better.

This project uses FileLock — pessimistic — for config writes because the GUI and background process rarely collide on the same file, so the occasional wait doesn’t matter, while a corrupted partial write (an incomplete JSON file) is worth avoiding outright. The lock is also held only briefly, just long enough to finish one file. Pessimistic locking isn’t the right answer everywhere; it comes down to how often conflicts happen, how costly a failed retry is, and whether waiting is acceptable.

Summary

Pessimistic locking locks on read and makes everyone else wait, trading that wait — and some deadlock risk — for guaranteed conflict prevention. Optimistic locking skips the lock and checks at write time whether the version changed, avoiding waiting under normal conditions but requiring retry handling once a conflict is detected. This project’s FileLock is a real instance of the former; HTTP’s ETag paired with If-Match is a web-native version of the latter. Both answer the same question: what happens when more than one process reaches for the same data at once.