Locking and deadlocks

Locks are how the database keeps two transactions from corrupting the same row, and deadlocks are what happens when two transactions each hold a lock the other needs. Both are normal; a system with no lock waits is a system with no concurrency. What a senior engineer knows is which lock each statement takes, how long it is held, how to order work so that it cannot deadlock, and how to read the report the engine writes when it does.

#What takes a lock, and for how long

Every UPDATE, DELETE and INSERT takes an exclusive lock on the rows it touches, and holds it until the transaction ends. Not until the statement ends: until COMMIT or ROLLBACK. That single fact explains most lock problems. A transaction that updates a row and then calls a payment API for four seconds holds that row for four seconds, and every other transaction that wants it queues behind.

Plain SELECTs take no row locks on either engine, because of MVCC; readers see a version, they do not wait for a writer. The locks that matter are between writers, and between writers and locking reads.

#Lock types

InnoDB's gap locks are the source of deadlocks people cannot explain. Two transactions each INSERT into a range the other has a gap lock on (from an earlier SELECT ... FOR UPDATE that found no row, or from a DELETE on a range) and they wait on each other. Running at read committed removes gap locking, which is why write-heavy MySQL deployments often choose it.

#FOR UPDATE and SKIP LOCKED

BEGIN;
SELECT quantity FROM stock WHERE sku = 'A1' FOR UPDATE;   -- nobody else reads-to-write this row now
-- decide in application code
UPDATE stock SET quantity = quantity - 1 WHERE sku = 'A1';
COMMIT;

FOR UPDATE turns a read into a claim. Two workers doing this serialise on the row; the second waits at the SELECT until the first commits, then sees the committed value. Add NOWAIT to fail immediately instead of waiting, which is right for an interactive request that should not queue.

SKIP LOCKED is the job queue in one clause:

UPDATE jobs SET status = 'RUNNING', claimed_by = :worker, claimed_at = now()
WHERE id = (
  SELECT id FROM jobs WHERE status = 'PENDING'
  ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

Each worker locks the first pending row that nobody else has locked, skipping the ones in flight, with no coordination and no duplicates. Both engines support it (PostgreSQL 9.5, MySQL 8.0). This is exactly how this site's blog worker claims jobs.

#Optimistic versus pessimistic

Pessimistic locking (FOR UPDATE) holds the row while you think. It is right when conflicts are common and the think time is short and inside the database: stock, balances, counters.

Optimistic locking holds nothing. It reads a version, does its work, and writes back only if the version has not changed:

UPDATE orders SET status = 'SHIPPED', version = version + 1
WHERE id = :id AND version = :versionRead;
-- 0 rows: someone else changed it; reload, re-decide, retry or tell the user

It is right when conflicts are rare and the think time is long or outside the database: a user editing a form for two minutes, a workflow step that calls three services. JPA's @Version does this and throws OptimisticLockException on the 0-row case; the exception has to be handled somewhere, and "somewhere" is usually a retry for background work and a "this changed while you were editing" for people.

#Deadlocks

T1 locks row A and wants row B; T2 locks row B and wants row A. Neither can proceed. Both engines detect this rather than letting it hang: InnoDB checks the wait-for graph immediately and rolls back the transaction with the smaller undo footprint (ERROR 1213: Deadlock found when trying to get lock); PostgreSQL checks after deadlock_timeout (one second by default) and aborts one with ERROR 40P01: deadlock detected. The aborted transaction must be retried by the application. A deadlock is not data corruption, it is a retry.

Reading the report is how you find the cause:

ERROR:  deadlock detected
DETAIL:  Process 8123 waits for ShareLock on transaction 5501; blocked by process 8140.
         Process 8140 waits for ShareLock on transaction 5498; blocked by process 8123.
         Process 8123: UPDATE accounts SET balance = balance - 30 WHERE id = 2
         Process 8140: UPDATE accounts SET balance = balance + 30 WHERE id = 1
HINT:  See server log for query details.

Two transfers, 1→2 and 2→1, each updated its source account first and then its destination. MySQL's SHOW ENGINE INNODB STATUS prints a LATEST DETECTED DEADLOCK section with the same shape: both transactions, the locks each holds and waits for, and which one it rolled back.

#Lock ordering: the fix that removes deadlocks

Deadlocks need a cycle, and a cycle needs two transactions to take the same locks in different orders. Take them in the same order everywhere and no cycle can form:

-- transfer between a and b, whichever direction
SELECT * FROM accounts WHERE id IN (:a, :b) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - :amt WHERE id = :from;
UPDATE accounts SET balance = balance + :amt WHERE id = :to;

The same rule for multi-row updates: UPDATE ... WHERE id IN (...) locks rows in whatever order the plan visits them, which can differ between two statements with different predicates. Lock the set explicitly with an ordered SELECT ... FOR UPDATE first. And keep parent-then-child order consistent across the codebase: always the order, then its lines, never the reverse in one place.

#Locks you did not ask for

• Foreign keys. Inserting a child row takes a share lock on the parent row (FOR KEY SHARE in PostgreSQL). Updating a parent's primary key, or deleting it, waits for those. A hot parent row (one "default" tenant everybody references) is a serialisation point.
• DDL. ALTER TABLE in PostgreSQL takes ACCESS EXCLUSIVE, which waits for every running transaction on the table and, while it waits, blocks every new one, including reads. A migration that waits behind one long report takes the whole application down with it. Set lock_timeout before DDL so it gives up instead of queueing everyone, and use CREATE INDEX CONCURRENTLY and ADD COLUMN without a default expression (instant since PostgreSQL 11) to avoid long holds.
• Long transactions. Every lock is held to commit. An open transaction that is idle (idle in transaction in pg_stat_activity) holds its locks and blocks vacuum. idle_in_transaction_session_timeout kills them.

#Under the hood: where a row lock lives, and how a deadlock is found

Neither engine keeps a lock table entry per locked row; there would be millions. PostgreSQL writes the locking transaction's id into the row header (xmax, the same field that marks deletion, with a flag bit saying "locked, not deleted"). A second transaction wanting the row reads that id, sees it is still in progress, and waits on a transaction-level lock the first transaction holds on its own id for its whole life: waits for ShareLock on transaction 5501 in the deadlock report is exactly that. The shared lock table (pg_locks) holds those transaction locks and the table-level locks, not the rows. When a transaction commits, its id lock is released and every waiter wakes, re-reads the row version, and finds either that it can proceed or that the row changed and it must re-check its WHERE (read committed) or abort (repeatable read).

InnoDB keeps a real lock hash in the buffer pool, keyed by (space, page) with a bitmap of locked records per page, which costs memory per locked row and is why a DELETE of ten million rows in one transaction can exhaust the lock table. Its lock modes are richer because of the clustered index: a locking read on a secondary index locks the secondary entry and the clustered record, and at repeatable read also the gap before each record it scanned, so the set of locks a single SELECT ... FOR UPDATE takes depends on which index the planner chose. Change the index, change the locks, and a deadlock appears where none was.

Deadlock detection is a wait-for graph: nodes are transactions, edges are "waits on". InnoDB checks for a cycle every time a transaction starts to wait, walking the graph immediately (with a depth limit that, when exceeded, treats the wait as a deadlock anyway). PostgreSQL waits deadlock_timeout (1 s) before checking, on the theory that most waits resolve sooner and the check is not free; then it walks the graph and, if a cycle exists, aborts the transaction that ran the check. That one-second delay is why a PostgreSQL deadlock costs a second of latency for every participant, and why lowering deadlock_timeout on a system with many short lock waits is a trade, not a free win. Lock waits that are not deadlocks are simply not detected at all; lock_timeout is the only bound on them.

#Walkthrough: the migration that stopped the site for eleven minutes

A routine deploy added a column with a default to a busy PostgreSQL table.

ALTER TABLE orders ADD COLUMN channel TEXT NOT NULL DEFAULT 'web';

1. On PostgreSQL 11+ this is instant: the default is stored in the catalog and no rows are rewritten. The DDL still needs ACCESS EXCLUSIVE on orders, for a few milliseconds.
2. A reporting query had been running against orders for nine minutes, holding ACCESS SHARE. The ALTER queued behind it. That is expected and harmless on its own.
3. PostgreSQL's lock queue is fair: every new request for a lock on orders queued behind the waiting ACCESS EXCLUSIVE, including plain SELECTs from every request thread. Within seconds the connection pool was full of sessions waiting on orders, Tomcat's workers were full of requests waiting on the pool, and the health check timed out.
4. The report finished after eleven minutes, the ALTER ran in 4 ms, and everything drained. The migration tool reported success; the incident was logged as "database slow".
5. The fix is one line before any DDL: SET lock_timeout = '3s'. The ALTER then fails after three seconds instead of queueing the world, the migration retries off-peak or after cancelling the report, and the tool (Flyway with a beforeMigrate callback, or the statement in each script) enforces it. The team also set idle_in_transaction_session_timeout so a forgotten open transaction could not hold ACCESS SHARE indefinitely, and statement_timeout on the reporting role.

The DDL was correct and fast. The lock it needed was queued behind one reader, and the queue's fairness turned one slow report into an outage.

#Try it yourself

#Misconceptions

• "The database keeps a list of locked rows." PostgreSQL marks the row header and locks transaction ids; InnoDB keeps per-page bitmaps. Both make waits a matter of "wait for that transaction", which is what the reports say.
• "Deadlocks are detected instantly." InnoDB checks on each wait; PostgreSQL checks after deadlock_timeout. A plain lock wait is never detected, only bounded by lock_timeout.
• "SELECT never locks anything." Plain reads do not. FOR UPDATE/FOR SHARE do, and at InnoDB repeatable read they lock gaps too, including on rows that do not exist.
• "A fast DDL cannot cause an outage." It needs an exclusive lock; while it waits, every new request on the table queues behind it. lock_timeout first.
• "Optimistic locking avoids all of this." It avoids holding locks during think time; the final UPDATE still takes a row lock, and the retry on conflict is still your code.

#Going deeper

• PostgreSQL manual, "Explicit Locking" (the lock mode conflict table) and the pg_locks view; deadlock_timeout, lock_timeout, idle_in_transaction_session_timeout.
• MySQL reference, "InnoDB Locking" (record, gap, next-key and insert-intention locks) and "Deadlock Detection".
• PostgreSQL wiki, "Lock Monitoring", with the query that shows who blocks whom.
• Braintree's "Safe Operations for High Volume PostgreSQL" and the strong_migrations checklist, for DDL that does not queue the world.
• Egor Rogov, PostgreSQL 14 Internals, part II on locks.