Reproducing Lost Updates, Write Skew & Deadlocks

Q: How does PostgreSQL handle deadlocks?

It detects a cycle of waiting transactions after deadlock_timeout (one second by default), aborts one transaction with error 40P01, and lets the others continue. The application should retry the aborted transaction.

Reproducing Transaction Anomalies and Deadlocks

Concurrency bugs in database code rarely show up in tests, because tests run one transaction at a time. In production, two requests interleave in exactly the wrong way: two withdrawals each read a balance of 100 and the account ends at 50 instead of 20; two doctors each check that someone else is on call and both go off call; two transfers lock accounts in opposite order and one fails with a deadlock. The reliable way to understand — and prove fixes for — these problems is to reproduce them deliberately with two database sessions, stepping through statements in a controlled order.

All examples below use PostgreSQL (behavior verified against its documented isolation semantics). MySQL InnoDB differences are noted where they matter.

TL;DR

Open two sessions (two psql windows) and interleave statements step by step to reproduce anomalies.
Lost update: two read-modify-write cycles overwrite each other; prevented by atomic updates, FOR UPDATE, version checks, or Repeatable Read.
Write skew: two transactions read overlapping data and write different rows, together violating a rule; only Serializable (or explicit locking) prevents it.
Deadlock: transactions lock rows in opposite orders; PostgreSQL aborts one with error 40P01.
Fix deadlocks with consistent lock ordering, short transactions, and retries.
Serializable transactions can fail with 40001; applications must retry them.

Quick Example

Setup:

A lost update under PostgreSQL's default Read Committed level, when the application computes the new balance itself:

The fix — let the database do the arithmetic atomically and enforce the rule:

Rerun the two-session script with this statement in both sessions: the second update waits for the first to commit, re-reads the row, and the final balance is 20.

Core Concepts

Anomalies and Isolation Levels in PostgreSQL

PostgreSQL's Repeatable Read is snapshot isolation: each transaction sees a consistent snapshot, and an update to a row modified by a concurrent committed transaction fails with could not serialize access due to concurrent update. Serializable adds serializable snapshot isolation, which tracks read-write dependencies and aborts transactions that could produce a non-serializable result.

Write Skew

Rule: at least one doctor must stay on call.

With ISOLATION LEVEL SERIALIZABLE in both sessions, the second commit fails with ERROR: could not serialize access due to read/write dependencies among transactions, and the application retries — this time seeing only one doctor on call.

Deadlocks

PostgreSQL detects the cycle and aborts one transaction; the other proceeds. Fix by locking rows in a consistent order:

Mitigations and How to Verify Them

Turn the scripts into automated tests: two database connections, a barrier or explicit step ordering between them, and assertions on final state and error codes. Run them against the same database engine and version as production.

MySQL InnoDB Differences

InnoDB defaults to Repeatable Read, but plain UPDATE statements use the latest committed row (a "current read"), so read-modify-write lost updates are not detected the way PostgreSQL's Repeatable Read detects them; use atomic updates or SELECT ... FOR UPDATE. InnoDB also takes gap and next-key locks for ranges, which prevents some phantoms but causes deadlocks in insert-heavy workloads that PostgreSQL would not see. Deadlocks report error 1213, and SHOW ENGINE INNODB STATUS shows the latest one.

Best Practices

Push Rules Into the Database

Atomic updates, CHECK constraints, unique constraints, and exclusion constraints enforce invariants regardless of interleaving.

Use Serializable Where Rules Span Rows

When correctness depends on reading several rows and writing others, Serializable with automatic retries is the simplest correct option.

Always Retry Serialization Failures and Deadlocks

Both 40001 and 40P01 mean "try again". Wrap transactions in a retry loop with backoff and keep side effects outside them.

Log Lock Problems

Enable log_lock_waits and monitor deadlocks and lock wait times in pg_stat_database and pg_locks to find contention before users do.

Common Mistakes

Read-Modify-Write in Application Code

Assuming Repeatable Read Prevents Everything

Treating Deadlocks as Bugs to Hide

FAQ

How do I reproduce a race condition in a database?

Open two sessions, begin a transaction in each, and execute statements alternately in the order that causes the problem. Then write an automated test that drives two connections through the same steps.

What is a lost update?

Two transactions read the same value, each computes a new value from it, and the second write overwrites the first, so one change disappears. It happens with read-modify-write logic outside the database.

What is write skew?

Two concurrent transactions read overlapping data, make decisions based on it, and update different rows, so each looks valid alone but together they violate a rule. Snapshot isolation does not prevent it; Serializable does.

How does PostgreSQL handle deadlocks?

It detects a cycle of waiting transactions after deadlock_timeout (one second by default), aborts one transaction with error 40P01, and lets the others continue. The application should retry the aborted transaction.

Should I use SERIALIZABLE everywhere?

It provides the strongest guarantees, but transactions may fail with serialization errors under contention and must be retried. Many applications use Read Committed with atomic statements and targeted locks, and Serializable for transactions enforcing multi-row rules.