Optimistic vs Pessimistic Locking, and Deadlocks — DB Concurrency Control (DB Series 1/4)

Deadlock: each waits for the other and both stop

Two transactions each wait for a lock the other holds, so neither can move. Textbooks say it happens when four conditions all hold (mutual exclusion, hold and wait, no preemption, circular wait), but in a database it almost always looks the same: two transactions lock the same resources in a different order.

The database doesn’t leave it there. MySQL (InnoDB) looks for cycles in its wait-for graph, and when it finds one, it picks one transaction as the victim, rolls it back, and raises an error.

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

So the application has two jobs.

• Prevent it. When locking several resources, always lock them in the same order (for example, ascending id). With a consistent order, a circular wait can’t form.
• Recover from it. If it still happens, the victim retries its transaction. Error 1213 doesn’t mean your code is wrong. It means try again.

As a backup, there are lock wait timeouts such as innodb_lock_wait_timeout.

Livelock: not stuck, just spinning

If a deadlock is two people meeting in a hallway and both freezing, a livelock is both stepping aside again and again, always to the same side, so neither ever gets past. The state keeps changing (live), but nothing progresses. It happens when two pieces of logic that retry immediately on conflict get locked in step. The fix is random delay (jitter) and backoff on retries.

Starvation, where only you keep getting pushed back, is a different problem, solved with fairness in the wait queue (FIFO).

Pessimistic and optimistic locking

When several transactions can modify the same row, there are two approaches. The pessimistic one assumes a conflict will happen and locks up front. The optimistic one assumes conflicts are rare, goes ahead, and checks at write time.

Pessimistic locking: lock when you read

START TRANSACTION;

SELECT stock FROM item WHERE id = 7 FOR UPDATE;  -- the lock is taken here
-- other transactions' FOR UPDATE and UPDATE all wait here

UPDATE item SET stock = stock - 1 WHERE id = 7;
COMMIT;                                          -- the lock is released

From the moment you read with FOR UPDATE until commit, that row is yours. It’s certain, but the longer you hold the lock, the more transactions queue up behind it, and once you lock several rows, you have the ingredients for the deadlock above.

Optimistic locking: don’t lock, verify on write

Instead of a lock, keep a version column. Remember the version when you read, and update only if it’s still that version when you write.

-- read (no lock)
SELECT stock, version FROM item WHERE id = 7;   -- version = 3

-- write: succeeds only at the version I read
UPDATE item
SET stock = stock - 1, version = version + 1
WHERE id = 7 AND version = 3;

-- if RowsAffected is 0, someone wrote first → read again and retry

It’s the same idea as the CAS pattern across multiple pods. No database lock means no waiting and no deadlocks. In exchange, retrying after a conflict is the application’s job, and on data with frequent conflicts, repeated retries can make it slower than pessimistic locking.

There’s one criterion: the likelihood of conflict. If conflicts are frequent, you’ll wait anyway, so lock pessimistically and be sure. If they’re rare, go optimistic and drop the cost of locking altogether. Always give optimistic locking a retry limit. Without one, it becomes the raw material for the livelock above.

Logging out every user with one UPDATE

A query meant to clear one user’s session wiped every user’s session. The cause is anticlimactic: the WHERE condition went out differently than intended, or not at all.

-- log out user 123 only
UPDATE user SET token = NULL
WHERE id = 123;

-- WHERE missing: everyone logged out
UPDATE user SET token = NULL;

There are plenty of variants. Getting the precedence of AND and OR wrong so the condition is effectively always true, or a join UPDATE missing its join condition and fanning out into a Cartesian product. What they share is that they look fine until they run.

Before sending a write query to production

• Run a SELECT with the same WHERE first. Check by eye that SELECT COUNT(*) returns the count you expect (usually 1), then copy that exact WHERE into the UPDATE.
• Wrap it in a transaction. START TRANSACTION → UPDATE → check RowsAffected → ROLLBACK if anything looks off. Typing into a console by hand with autocommit on is the most dangerous way.
• Turn on safe update mode. In MySQL, sql_safe_updates=1 rejects any UPDATE or DELETE without a key condition.
• Keep it out of human hands. Turning recurring operational queries into tested scripts or admin features is the real fix.

There’s a reason this incident is in the concurrency post. However carefully you design locks and isolation levels, it’s all for nothing if the WHERE in a single query falls apart. Concurrency control starts with knowing exactly which rows your query touches.

The next post covers transactions: ACID, isolation levels, and the Redo/Undo recovery that keeps data alive through a crash.