Two account transfers have been waiting in the database for several seconds. CPU is low. The network is fine. Each transfer already updated one account and is waiting to update another. An engineer increases the HTTP timeout because the service looks slow. That does not help.
The interview question. Explain the deadlock using a concrete two-account execution, distinguish it from ordinary lock contention, and redesign the transaction so the database can make progress predictably under concurrent transfers. Assume PostgreSQL and that both account balances live in the same database.
Think first
A transaction moves money from account 41 to 72 by updating the source row first, then the destination. A second transaction simultaneously moves money from 72 to 41 using the same source-first code. Some requests fail with 40P01. Draw the lock graph, propose a correction to the acquisition order, and explain how you would retry while retaining atomicity.
Take a few minutes to form an approach, then compare it with the worked explanation.
Reveal the worked explanation
Waiting is sometimes necessary. A cycle is different
A transfer from account 10 to account 20 updates two records. Another transfer from 20 to 10 happens at the same moment. The initial implementation locks whichever account the request calls the source first.
Transaction A Transaction B
Lock account 10 Lock account 20
Update account 10 Update account 20
Try lock account 20 Try lock account 10
Wait for B Wait for A
Two transactions hold opposite account-row locks and wait on each other, creating a cycle in the wait-for graph
Transaction A cannot continue until B releases account 20. Transaction B cannot continue until A releases account 10. Neither can reach commit, which is the normal point at which these transaction-level row locks would be released.
That is a deadlock. If A were merely waiting for B to finish a single long update and B was still making progress, it would be contention, not necessarily a cycle. The difference changes how you debug and fix it.
PostgreSQL detects deadlocks and aborts one participant so the other can proceed. You should not rely on which transaction is chosen as the victim. The failed transaction has to be handled by the application. PostgreSQL explicit locking
A simple lock-order rule removes this particular cycle
The transfer's business direction does not have to determine the order of database locks. Normalize the account IDs and acquire the needed row locks in ascending order, regardless of which account is debited.
For both transfers in this scenario:
Lock account 10 first
Then lock account 20
Validate balances and transfer conditions
Perform debit and credit
Commit
Now A may take account 10 while B waits for it. A can then take account 20 because B has not acquired it first. A finishes and B can continue using the current committed state according to the selected isolation and locking rules. The circular wait created by opposite lock orders is removed.
The application still needs correct funds checks and atomic debit and credit. Ordering locks stops this deadlock pattern. It does not, by itself, guarantee sufficient funds or prevent the same transfer intent from being processed twice.
Be careful when implementing the ordering. Do not assume a SELECT ... WHERE id IN (...) FOR UPDATE acquires locks in the order in which identifiers appear in a request. Use a database-specific, verified locking strategy that produces the intended order, and test it with competing transactions. A convention in application code is only valuable when its actual SQL honors it.
If a transfer sometimes also locks an account ledger row or a currency-limit record, extend the protocol. A consistent account-ID order will not protect you when another code path first locks the ledger and then the account, while this path does the reverse. Document the order across the complete set of resources and make it part of integration tests.
Why does the database kill work instead of waiting longer?
A timeout cannot break the logical dependency. Raising it lets the application wait longer for a state that no participant can resolve by itself.
PostgreSQL uses a deadlock detection mechanism rather than waiting forever for a known cycle. For an ordinary deadlock the application can see SQLSTATE 40P01. That is distinct from the serialization failure 40001 discussed in the next lesson. Both can require retrying a transaction, but the causes and optimization work differ.
When the database aborts one transaction, its uncommitted changes are rolled back. The application should retry the whole transfer decision within a bounded policy. Repeating only the failed second UPDATE in the now-aborted transaction will not restore the earlier work. A retry also must re-evaluate the balance and any constraints against current data.
For latency-sensitive operations, I would rather set bounded database lock waits, handle deadlock errors explicitly, and surface a controlled retryable outcome when the attempt budget is exhausted. The service must not tell the user a transfer definitely failed if its separate external execution boundary is uncertain. A timeout at an external boundary does not prove that its side effect failed; establish the original outcome before repeating an uncertain effect.
How would you prove this in production?
First look for database evidence. PostgreSQL can log detected deadlocks and expose active sessions and lock waits in its monitoring views. pg_stat_activity, pg_locks, and pg_blocking_pids() can help identify which queries are blocked and by whom. A deadlock report can show the statements and resources forming the cycle. PostgreSQL monitoring statistics
Then compare transaction shapes rather than tuning random timeouts. Which tables and rows does each path touch? Are there hidden triggers that lock additional records? Does one path acquire locks during an inventory check and another during order creation? Are transactions holding locks while waiting on network calls or doing unnecessary calculations?
Shorter transactions reduce the window in which contention and deadlocks can arise. Fewer resources and consistent ordering reduce cycle opportunities. But splitting a transfer into two unrelated commits to avoid waiting would sacrifice its atomicity. That is not a free optimization.
Sometimes NOWAIT or SKIP LOCKED is useful, such as distributing independent background jobs among workers. It is not automatically appropriate for account transfers. Skipping a locked account could silently skip a business obligation. The workload must justify that behavior.
The interview answer should draw the wait-for cycle, show a deterministic acquisition protocol, and describe database detection plus safe whole-transaction recovery. In the next problem, transactions are not blocked in a cycle at all. The database rejects them because letting all of them commit would produce an impossible history.
Go deeper
Locks you did not explicitly request
PostgreSQL takes row locks when updating rows. This means deadlocks are possible even when code contains no explicit SELECT FOR UPDATE. Foreign-key checks, unique indexes, triggers, and other operations can add lock relationships that are not obvious from one service method.
deadlock_timeout influences when PostgreSQL checks for a deadlock after a lock wait. Making it extremely short can add overhead, and making it very long delays detection. It is a diagnostic and operational knob, not the primary solution to a reproducible cycle.
For batch work, deterministic ordering may be implemented by explicitly locking rows in sorted order and then performing writes. Large batches still need bounded sizes to keep the lock hold period reasonable. A single business transaction must preserve its accounting invariant, even if an outer workflow splits a large batch into independent units.
The database might also abort for statement or lock timeouts. Those are not the same error as deadlock. Handle errors by their actual semantics instead of turning every database exception into an unlimited retry.
Check your reasoning
Show the cycle of A holding 41 and waiting for 72 while B holds 72 and waits for 41.
Distinguish deadlock from a long noncyclic lock wait.
Use consistent sorted account locking independent of business transfer direction, with real SQL behavior verified.
Explain rollback of one victim and a bounded retry of the full transfer with current balance validation.
Show how to inspect PostgreSQL deadlock logs and lock views rather than changing HTTP timeouts.
Common mistake: Using SKIP LOCKED on financial transfers or claiming a larger timeout resolves a wait cycle.
Read the answer explanation
A cycle of waits that cannot resolve without aborting or releasing a lock. — Each transaction needs the other to complete first. Deterministic lock ordering prevents this particular cycle.
Apply it elsewhere
An inventory checkout updates a product row and then a coupon row. Coupon redemption updates the coupon row and then the product row. How should you evaluate deadlock risk?
Compare your approach
Map all lock acquisitions, including triggers and implicit locks. Establish shared ordering, short critical sections and whole-transaction retry on actual deadlock errors.
One more constraint
Every transfer uses sorted account locks, but deadlocks persist. Which hidden resources or code paths would you investigate?
Quick revision
A deadlock is a cycle of waits, not merely a slow lock. Transfer A locks account 10 and waits for 20, while transfer B locks 20 and waits for 10. PostgreSQL detects the cycle and aborts one transaction. Prevent this pattern by locking the two accounts in a deterministic order independent of transfer direction, keeping transactions short, and applying the same order across all participating code paths. Retry the whole aborted transaction after 40P01, with a bounded policy and fresh validations. Do not fix it by increasing HTTP timeouts or splitting one required atomic transfer into two commits.
