Concurrency, Isolation Levels and Locking

A single user never sees concurrency bugs. They appear with the second user, usually in production, usually under load, and almost never twice the same way.

The reason is that most concurrency bugs are timing bugs. Two transactions each did something reasonable, and the combination was wrong. Learning the named anomalies lets you recognize them, and the tools below let you rule them out.

The anomalies#

Two sessions, A and B, working on overlapping data.

Dirty read. A reads a change that B has not committed. B then rolls back, and A acted on data that never existed. PostgreSQL never allows this.

Non-repeatable read. A reads a row, B commits a change to it, A reads it again and gets a different value within the same transaction.

Phantom read. A runs a query that returns a set of rows, B inserts a new matching row and commits, A runs the same query and a new row appears.

Lost update. A and B both read a value, both compute a new one, both write. One write silently overwrites the other.

Write skew. A and B each read overlapping data, each make a decision that is valid on its own, and write to different rows. Together they violate a rule neither one checked.

Isolation levels#

The SQL standard defines four levels, each allowing fewer anomalies. PostgreSQL implements three real behaviors.

Choose a level at the start of a transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

Read Committed in action#

 time   Session A                              Session B
  1     BEGIN;
  2     SELECT stock_qty FROM products
         WHERE product_id = 9;     -- 8
  3                                            UPDATE products SET stock_qty = 5
                                                WHERE product_id = 9;
  4                                            COMMIT;
  5     SELECT stock_qty FROM products
         WHERE product_id = 9;     -- 5   (non-repeatable read)
  6     COMMIT;

At Read Committed, the second SELECT sees B's committed change. Inside one transaction the same query returned two answers. For most OLTP work that is acceptable, since each statement is consistent on its own.

Repeatable Read in action#

Repeat the timeline at REPEATABLE READ and step 5 still returns 8. A is working from a frozen snapshot. Because PostgreSQL snapshots the entire database state, phantoms cannot appear either, which is stronger than the standard requires.

The price: if A then tries to update a row that B changed after A's snapshot, PostgreSQL refuses.

ERROR:  could not serialize access due to concurrent update

Your application must catch this and retry the transaction from the start.

Lost updates and how to avoid them#

This is the bug you are most likely to write. The pattern is read, compute in the application, write back.

 Session A                          Session B
 SELECT stock_qty  -> 8
                                    SELECT stock_qty  -> 8
 (sell 1, computes 7)
                                    (sell 1, computes 7)
 UPDATE SET stock_qty = 7
                                    UPDATE SET stock_qty = 7     -- should be 6

Two sales, stock dropped by one. Three fixes, from simplest.

1. Make the database do the arithmetic#

UPDATE products
SET stock_qty = stock_qty - 1
WHERE product_id = 9 AND stock_qty > 0;

A single statement is atomic. It locks the row, evaluates stock_qty - 1 against the current value, and writes. If it affects zero rows, stock has run out, so check the reported count. This also fixes overselling, because the stock_qty > 0 check and the decrement are one step.

2. Lock the row while you work#

BEGIN;
SELECT stock_qty FROM products WHERE product_id = 9 FOR UPDATE;
-- application logic here
UPDATE products SET stock_qty = 7 WHERE product_id = 9;
COMMIT;

FOR UPDATE takes a row lock. Anyone else trying to update or FOR UPDATE that row waits until you commit. Use it when the logic between read and write cannot be expressed in SQL.

3. Optimistic locking with a version column#

Add a version column. Read it with the row, then update only if nobody changed it:

UPDATE products
SET stock_qty = 7, version = version + 1
WHERE product_id = 9 AND version = 3;

If it reports UPDATE 0, somebody else got there first, so reload and retry. Nothing is locked while the user thinks, which suits web forms where "thinking" takes minutes.

Row-level locks#

SKIP LOCKED is the basis for job queues in SQL. Several workers can pick work without blocking each other:

WITH next_job AS (
    SELECT job_id FROM jobs
    WHERE status = 'queued'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running'
FROM next_job
WHERE jobs.job_id = next_job.job_id
RETURNING jobs.*;

Each worker claims a different row and none of them wait.

Plain SELECT never takes row locks in PostgreSQL. MVCC means readers read old versions and never block writers or each other.

Deadlocks#

A deadlock is two transactions each holding a lock the other needs.

 Session A                               Session B
 UPDATE accounts ... WHERE id = 1;       UPDATE accounts ... WHERE id = 2;
 UPDATE accounts ... WHERE id = 2;       UPDATE accounts ... WHERE id = 1;
        (waits for B)                           (waits for A)

Neither can proceed. PostgreSQL notices after deadlock_timeout (one second by default), aborts one transaction and reports:

ERROR:  deadlock detected
DETAIL:  Process 4121 waits for ShareLock on transaction 893; blocked by process 4098.

The survivor continues. You cannot eliminate the possibility entirely, so retry on this error. You can make it rare by always taking locks in the same order. For a transfer between two accounts, always update the lower account_id first, regardless of direction.

Write skew and SERIALIZABLE#

A clinic requires at least one doctor on call. Two doctors, Dr. Rao and Dr. Lee, both on call. Each checks "are at least two on call?", sees yes, and goes off call.

 A: SELECT count(*) FROM on_call;   -- 2, so OK to leave
 B: SELECT count(*) FROM on_call;   -- 2, so OK to leave
 A: UPDATE on_call SET active = FALSE WHERE doctor = 'Rao';
 B: UPDATE on_call SET active = FALSE WHERE doctor = 'Lee';
 both COMMIT   -> nobody on call

They updated different rows, so row locks did not conflict, and each saw a consistent snapshot. Only SERIALIZABLE catches it. PostgreSQL tracks read-write dependencies between transactions, and when it detects a cycle that could not have happened serially, it aborts one:

ERROR:  could not serialize access due to read/write dependencies among transactions

Serializable is the simplest correct model, since you reason as if transactions ran one at a time, and the cost is that you must retry on error code 40001. A retry loop around every transaction is the standard pattern:

for attempt in range(5):
    try:
        run_transaction()
        break
    except psycopg.errors.SerializationFailure:
        continue

What MVCC costs#

Every update leaves a dead row version behind. Autovacuum reclaims them, but only versions no running transaction could still need. One transaction left open for hours pins the cleanup horizon, and the table grows ("bloat") even though the live data is small.

Useful views when something is stuck:

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Common mistakes#

Read-modify-write in application code with no lock. Push the arithmetic into the UPDATE, or lock the row.

Not retrying serialization failures and deadlocks. At higher isolation levels they are expected behavior, not bugs.

Taking locks in inconsistent order. Deadlocks follow.

Choosing SERIALIZABLE and ignoring the retry requirement. The errors surface as random failures for users.

Leaving transactions open. They block vacuum and hold locks.

Using SELECT ... FOR UPDATE on a whole table's worth of rows. You have serialized every writer.

Practice#

1. Open two psql sessions. Reproduce the non-repeatable read from the timeline at Read Committed, then repeat at REPEATABLE READ and compare.
2. Reproduce the lost update with a read-then-write pattern on products. Then fix it with an atomic UPDATE ... SET stock_qty = stock_qty - 1 WHERE ... AND stock_qty > 0.
3. Create a deadlock between two sessions updating accounts rows 1 and 2 in opposite order. Read the error. Then fix it by ordering the updates.
4. Use FOR UPDATE SKIP LOCKED in two sessions to see that each claims a different row.
5. Build the on-call table, reproduce the write skew at REPEATABLE READ, then show SERIALIZABLE stopping it.