Build1 publisher3 min readPublished
PostgreSQL foreign key inserts take a hidden row lock that deadlocks ledger deposits
Sixteen test threads on one PostgreSQL account row exposed a deadlock that ascending lock order cannot prevent, caused by a foreign key's hidden lock. Code that inserts a child row and then locks the parent FOR UPDATE can hit it under concurrency, and the author's two-word fix is FOR NO KEY UPDATE.
The Engineer · Build desk
Drafted by a language model from the sources cited here and checked against its claim ledger before publication. How we use AISend a correction
What happened
- The deadlock surfaced in a multi-currency ledger whose deposits insert a row pointing at an account, then lock that account FOR UPDATE to guard against lost updates.
- Two psql sessions running the same deposit transaction against account 1 reproduced it every time, with one session cancelled about a second later.
- According to pgrowlocks output, both sessions already held For Key Share locks on the account row before either one ran its FOR UPDATE.
- PostgreSQL enforces foreign keys with internal triggers in ri_triggers.c that keep the referenced row in place until the inserting transaction ends.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Sorting explicit locks by id cannot remove this cycle, since the first lock lands on the same row from the INSERT before any lock statement the application wrote.
- exposure Transfer code that records a row pointing at two accounts and then locks both in order stays exposed on each account, however carefully its explicit locks are sorted.
- cost Diagnosis depends on installing pgrowlocks, because pg_locks did not show these row locks; without it, operators see only a deadlock error naming transaction IDs.
- decision Every FOR UPDATE on a referenced parent row now needs a check on whether the key changes, and balance updates qualify for the weaker FOR NO KEY UPDATE under the author's rule.
Ascending id order prevents one kind of deadlock. Two transfers moving money in opposite directions take the same locks in the same sequence, so the second queues behind the first [6]. The rule assumes the application wrote every lock in the transaction. "I had written exactly one lock," the post's author wrote [21].
The error message shows the cycle. "Process 103 waits for ShareLock on transaction 757; blocked by process 110. Process 110 waits for ShareLock on transaction 756; blocked by process 103," PostgreSQL reported, while locking tuple (0,1) in accounts [11]. Both processes already held a shared lock on that tuple, marked multi = t in the pgrowlocks output [13]. Each then asked for FOR UPDATE and had to wait for the other's share lock. The foreign key keeps that lock until the transaction ends [14], so neither wait can finish [18].
"A deadlock needs two transactions, each holding something the other wants," the author wrote [15]. Here both hold the same weak lock on the same row, and each wants a stronger one. There is one row, so there is nothing to sort [17]. The foreign key trigger takes its lock with an ordinary query, and that query shows up in the logs if you look for it [14]. "It turns out that's simply how foreign keys work," the post says [16].
The first "deadlock detected" came from a test of sixteen threads moving money out of a single account, and the author read it twice [8]. The reduction that followed is careful work. Two tables and one row leave no room for another explanation. The deposit is three statements inside one transaction: an INSERT into deposits, a SELECT ... FOR UPDATE on the account, then an UPDATE of the balance [9].
Transfers in the same ledger have the same shape. A transfer records a row that points at its accounts, then locks those accounts in ascending order [7][6]. By the time the sorted locks run, the insert has already taken FOR KEY SHARE on every account it references. Sorting the explicit locks does nothing about those [19].
For the bug to carry over to other code, three conditions have to hold. A transaction inserts a row that references a parent. It then locks that parent FOR UPDATE. A second transaction does the same to the same parent before the first commits [2]. Ledgers and balance tables meet all three whenever many requests hit one account at once. The author's test did exactly that [8].
The author's fix swaps FOR UPDATE for FOR NO KEY UPDATE. The post limits it to transactions that are not changing the row's key [3]. The repro's UPDATE sets balance and leaves id alone, so a ledger deposit meets that condition [20].
What to watch
- A rerun of the sixteen-thread test with FOR NO KEY UPDATE, confirming the fix holds for transfers that reference two accounts as well as single-account deposits.
- Code paths that do change the referenced key, where the author's rule says FOR NO KEY UPDATE no longer applies and the deadlock needs another answer.
- Reports of the same ShareLock-on-transaction deadlock from other ledger codebases that insert a child row before locking its parent under load.