Locks and isolation levels — controlling concurrent execution

Two people bought the last unit of a product at the same time. Both saw "purchase succeeded", and the stock went to -1.

Incidents like this happen because someone else slips in between "read" and "write". Locks and isolation levels are the tools for preventing this. Both are "don't let it be touched simultaneously", but they work differently and have different costs.

What you'll learn in this chapter

• The difference between shared and exclusive locks and how long they're held
• The four isolation levels and the read anomalies each prevents
• What raising the isolation level alone still doesn't prevent
• Choosing between optimistic and pessimistic locking, and how to hold a version column
• Causes of deadlocks and the principle for avoiding them

Prerequisites

Builds on the basic operations and ACID from Transactions.

What this chapter does not cover

Topic	See
Retry count and interval design; when contention concentrates	Retries and idempotency
Framework-level implementation and SQLite pitfalls	Laravel API Development Guide — Transactions and concurrency
Exception design and model wiring when implementing optimistic locking in a framework	Laravel × DDD Guide — Transaction management
Delay when reading from a replica (a different layer from isolation)	Data distribution
Writing queries that join and aggregate across tables	Joins and aggregation
Counting across rows (subqueries, CTEs, window functions)	Subqueries and window functions

Two kinds of locks

	Shared lock (S)	Exclusive lock (X)
Alias	Read lock	Write lock
When taken	On read	On update / delete
Vs. other shared	Coexists	Blocks
Vs. other exclusive	Blocks	Blocks

If everyone is only reading, they don't get in each other's way. Note that this table describes the textbook locking model. In PostgreSQL and MySQL's InnoDB a plain SELECT takes no row lock, so it reads the pre-update value without waiting even while someone is writing. SELECT ... FOR SHARE is how you take a shared lock explicitly; what a writer blocks is anything trying to write and anything trying to take a lock.

Locks are held until the transaction ends. A row with an exclusive lock can't be updated by another transaction until this one COMMITs or ROLLBACKs.

BEGIN;
SELECT * FROM items WHERE id = 1 FOR UPDATE;  -- Take exclusive lock
-- From here until COMMIT / ROLLBACK, no one else can update id=1
UPDATE items SET stock = stock - 1 WHERE id = 1;
COMMIT;                                        -- Lock released here

That's why you need to keep transactions short. Keeping them open blocks others the whole time.

Three read anomalies

Problems that arise from concurrent execution fall into three representative kinds.

Anomaly	What happens
Dirty read	You read another transaction's uncommitted changes
Non-repeatable read	Reading the same row twice gives different values
Phantom read	Searching by the same condition twice gives a different number of rows

The difference between the latter two is the target. Non-repeatable read is about values on existing rows changing; phantom read is about the set of rows matching the condition growing or shrinking.

Non-repeatable read
  1st: SELECT price FROM items WHERE id = 1  →  1000
  (another transaction updates and commits)
  2nd: SELECT price FROM items WHERE id = 1  →  1200   ← value changed

Phantom read
  1st: SELECT * FROM items WHERE price < 2000  →  3 rows
  (another transaction inserts and commits)
  2nd: SELECT * FROM items WHERE price < 2000  →  4 rows   ← count changed

Four isolation levels

The SQL standard defines four levels by which anomalies are permitted.

Isolation level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Lower rows are safer but reduce concurrency through more waits and more failures. Choose by which anomalies you can tolerate. The default differs by product: PostgreSQL is READ COMMITTED, MySQL's InnoDB is REPEATABLE READ.

Implementations don't match the standard

Here's a practical note. The standard defines the lower bound of "you must not produce these anomalies"; implementations are allowed to give stronger guarantees.

PostgreSQL's documentation makes two things explicit.

• READ UNCOMMITTED behaves as READ COMMITTED. All four levels can be requested, but only three are implemented internally
• REPEATABLE READ does not permit phantom reads. A guarantee stronger than the standard

MySQL's InnoDB also defaults to REPEATABLE READ and prevents phantom reads to a certain extent, though by a different mechanism than PostgreSQL.

"REPEATABLE READ has phantom reads" is a statement about the SQL standard, not necessarily about the database in front of you. Check the docs of your product.

Raising isolation level doesn't remove the need for locks

Raising the isolation level doesn't make locks unnecessary. Two misconceptions about isolation levels are common.

One. REPEATABLE READ is not the "always read the latest" level. The opposite: it keeps reading the snapshot from when the transaction began, so updates committed by others aren't visible to you. It deliberately shows the old value in order to guarantee repeated reads return the same value. What's "always current" is actually READ COMMITTED, which reads the latest committed data per statement.

Two. And here's why locks are needed. What isolation levels define is the anomalies visible on reads; whether update contention is detected depends on the product and the level. The stock example.

Both transactions at REPEATABLE READ, no locks

Tx1: SELECT stock FROM items WHERE id=1   → 1
Tx2: SELECT stock FROM items WHERE id=1   → 1
Tx1: UPDATE items SET stock = 0 WHERE id=1
Tx2: UPDATE items SET stock = 0 WHERE id=1

Both read "1 in stock" and both sell. This is lost update, an update contention that isolation levels alone don't prevent. The same-shape contention also happens within a single process (Processes and threads). To prevent it, one of:

• Take a lock (SELECT ... FOR UPDATE)
• Detect conflicts by version (optimistic locking)

In PostgreSQL's REPEATABLE READ, one side gets a serialization failure error in this scenario, but that presumes an implementation that catches the error and retries. Leaving it alone doesn't correct automatically.

Optimistic vs. pessimistic locking

The word "lock" appears in both, but the philosophies are opposite.

	Pessimistic locking	Optimistic locking
Assumption	Conflicts happen often	Conflicts are rare
Action	Take a lock up front, block others	Don't take a lock. Verify version at update time
On conflict	Doesn't occur (others wait)	Zero rows updated. Detect and retry or notify
Fits	High contention; blocking is acceptable	Low contention; blocking not acceptable
Cost	Wait time, risk of deadlock	Retries on conflict

Despite the name, optimistic locking does not take a lock. "Optimistic" refers to the assumption "conflicts probably won't happen"; the structure is to detect and handle conflicts only when they do.

-- Pessimistic: take it up front
BEGIN;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;   -- Block others
UPDATE items SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Optimistic: no lock; add a condition on the update
SELECT stock, version FROM items WHERE id = 1;      -- Got version = 3
UPDATE items SET stock = 0, version = 4
  WHERE id = 1 AND version = 3;                     -- If update count is 0, conflict

The key to optimistic locking is putting the version condition in the WHERE. If someone updated in between, the version moved on, the update count is 0, and the conflict is detected.

Pessimistic locking pays wait time and deadlock risk. "Just lock it, then it's safe" makes other transactions wait even where conflicts are rare. Pick by contention frequency.

Note: this is a different concept from optimistic UI updates, which "update the screen before waiting for the response". Names are similar; layers and purposes differ.

Where the version column lives

The version is one integer column. It isn't a special database feature.

CREATE TABLE items (
  id      INTEGER PRIMARY KEY,
  stock   INTEGER NOT NULL,
  version INTEGER NOT NULL DEFAULT 1   -- bumped by 1 on every update
);

Three things to decide. The initial value starts at 1 when the row is created; put it in DEFAULT and you don't have to name it in the INSERT. Where it is bumped and where it is checked are both inside the updating UPDATE statement — the SET version = 4 and WHERE version = 3 in the example above. Writing SET version = version + 1 gives the same result.

The check, the update and the version bump have to sit in one UPDATE. Split "confirm it matches", "update" and "bump the version" into separate statements and another update can slip into the gap. In a single statement the database evaluates the condition and applies the update together, so no gap opens.

Some designs use an update timestamp (updated_at) instead of a version. Two updates within the same instant can't be told apart that way; an integer version always changes on every update.

Deadlock

Two transactions each waiting on locks the other holds — neither can proceed.

Most databases detect deadlocks and forcibly roll back one side. The application catches the error and retries.

The avoidance principle is "fix the order"

To prevent deadlocks, order lock acquisition consistently across all transactions.

The combination that tends to bite is two rows touched in opposite directions. Call the accounts row 1 and row 2: a transfer from row 1 to row 2 running at the same time as a transfer from row 2 to row 1 means one takes locks in the order 1 → 2 and the other 2 → 1. That is the shape in the figure above.

// Bad: lock in argument order → order flips by call site
lockRow(fromId);
lockRow(toId);

// Good: fix on ascending ID → same order everywhere
const [first, second] = [fromId, toId].sort((a, b) => a - b);
lockRow(first);
lockRow(second);

Fixed on ascending ID, both go for row 1 first regardless of which way the money moves.

	Order not fixed	Fixed on ascending ID
Transfer 1 → 2	Takes row 1, waits for row 2	Takes row 1, then row 2 as well
Transfer 2 → 1	Takes row 2, waits for row 1	Waits for row 1 (holding nothing yet)
Result	Each waits on the other	Whichever got row 1 takes both and proceeds; the other waits until row 1 frees up

A deadlock happens when the waiting relation closes into a loop. As long as locks are taken in ascending order, a transaction only ever waits on a row numbered higher than the ones it already holds. Every wait points from a lower number to a higher one, so no combination can come back around.

The other half of the effect is that the waiting side holds nothing. In the left-hand column, the 2 → 1 transfer gets stuck because it waits for row 1 while holding row 2. With a fixed order, a transaction that fails to get the first lock is holding nothing, so it drags no one else down.

Randomizing the order is counterproductive. The instinct to jitter timing to avoid clashes actually misaligns the ordering, making circular combinations easier to form. What works is fixing the order.

Other mitigations:

Means	Effect
Fix lock ordering	Fundamental avoidance
Keep transactions short	Narrows the window for waiting
Finer-grained locks	Reduces contended range
Implement retries	Recover when it happens
Lower the isolation level	May narrow the lock range in some cases

Deadlocks can't be fully avoided, so cut the probability with ordering and catch the rest with retries is the practical stance. How to design retry count and interval is covered by Retries and idempotency.

Common misconceptions

"Row exclusive locks are released when the query finishes." — Held until the transaction COMMITs or ROLLBACKs. That's why transactions stay short.

"Raising the isolation level removes the need for locks." — What isolation regulates is anomalies visible on reads. The failure of "reading a value then writing it back" (lost update) may go undetected depending on product and level. Even when detected it comes as a serialization-failure error, so without retries the process crashes.

"REPEATABLE READ always returns the latest." — The opposite. It keeps reading the snapshot from when the transaction began; updates others committed aren't visible. It deliberately shows old values to guarantee repeated reads return the same value.

"Optimistic locking is a kind of lock." — Despite the name, it doesn't take locks. It verifies versions at update time to detect conflicts. "Optimistic" is the assumption that conflicts are rare.

"When in doubt, take an exclusive lock and you're safe." — At the cost of wait time and deadlock risk. Pick by contention frequency.

"Randomizing lock order avoids clashes." — Counterproductive. Fixing the order is the fix.

"REPEATABLE READ always produces phantom reads." — The SQL standard says only that they're permitted, not required. Implementations may give stronger guarantees, and PostgreSQL actually prevents them. Check your product's docs.

Exercises

Q1. Someone proposes "we upgraded to SERIALIZABLE, so locks aren't needed anymore". Is this valid?

Answer and explanation

Answer: You do stop writing explicit locks, but that does not mean nothing else is needed.

Two points to push back on.

1. Locks and conflict detection are still working internally. You just aren't writing SELECT ... FOR UPDATE anymore; waits and failures don't disappear. Under SERIALIZABLE, transactions that fail to serialize error out, so without retries the process crashes.

2. Concurrency drops significantly. SERIALIZABLE gives the strongest guarantee at the cost of more waits and failures. Targeting just the places where contention occurs is more practical than raising the whole thing.

The order of choice:

Intent	Means
Reliably hold a specific row	SELECT ... FOR UPDATE (pessimistic)
Rare conflicts, notice only when they happen	Version check (optimistic)
Broad read consistency	Raise isolation level

Under SERIALIZABLE you stop writing explicit locks, at the cost of retrying serialization failures. If you would rather not raise the level, guard just the contended rows with a lock or a version check.

Q2. A reservation system holds seats. For popular shows, contention on the same seat concentrates. Optimistic or pessimistic locking?

Answer and explanation

Answer: Pessimistic locking.

The axis is contention frequency.

	Optimistic	Pessimistic
Low contention	Wins. No waits	Unnecessary waits
High contention	Loses. Retries pile up	Wins. Processed reliably in order

Popular shows concentrate contention on the same seat, so with optimistic most requests would conflict and retry. Retrying again conflicts again, so from the user's view "no matter how many times I try, I can't book". Server load also grows with each retry.

Pessimistic (SELECT ... FOR UPDATE) waits but processes reliably in order. Waiting is a better experience than repeated retries in this situation.

Conversely, for editing a user profile where the same record is rarely touched at the same time, optimistic is appropriate.

Q3. Deadlocks happen frequently in a transfer process. Cause and fix.

async function transfer(fromId: number, toId: number, amount: number) {
  await lockAccount(fromId);
  await lockAccount(toId);
  // Move the balance
}

Answer and explanation

Answer: Lock order flips per call. Fix by ordering by ID.

If transfer(1, 2) and transfer(2, 1) run at the same time:

Tx1 (1→2): Lock account 1 → Wait for account 2
Tx2 (2→1): Lock account 2 → Wait for account 1

They wait on each other and don't progress.

Fix by ordering not by the business direction (from → to) but by a stable criterion like ID.

async function transfer(fromId: number, toId: number, amount: number) {
  const [first, second] = [fromId, toId].sort((a, b) => a - b);
  await lockAccount(first);
  await lockAccount(second);
  // Move the balance
}

Now both transfer(1, 2) and transfer(2, 1) lock in "account 1 → account 2" order. Waits become one-directional.

Also implement retries for deadlocks that still occur. Fix ordering to lower the rate; catch the rest with retries is the practical stance.

Summary

• Shared locks coexist; exclusive locks block writes and lock requests
• Locks are held until the transaction ends. That's why they stay short
• Four isolation levels differ in which of dirty read, non-repeatable read, and phantom read are permitted
• Implementations don't match the standard. PostgreSQL's READ UNCOMMITTED equals READ COMMITTED, and REPEATABLE READ prevents phantom reads
• REPEATABLE READ is not "always current" — it keeps reading a snapshot from the start
• Isolation levels alone don't prevent lost updates. A lock or version-based conflict detection is needed
• Optimistic locking doesn't take a lock; it detects conflicts by version. Despite the name, not a kind of lock
• One integer column is enough for the version. Keep the check, the update and the bump in a single UPDATE
• The deadlock avoidance principle is fixed lock ordering. Randomization is counterproductive

Related references

• Retries and idempotency — Catching serialization failures and deadlocks with retries
• Laravel API Development Guide — Transactions and concurrency — Implementation and SQLite pitfalls
• Laravel × DDD Guide — Transaction management — Exception design for optimistic locking
• PostgreSQL — Transaction Isolation

What to read next

• Indexes — How lookups get faster and what it costs
• Optimistic updates — The similarly named UI-side technique, and how it differs