A PostgreSQL deadlock hiding in a foreign key check

Sixteen threads, one bank account, and a deadlock that should have been impossible. This is how a concurrency test found a bug that had been sitting in my code since the day I wrote it, why ordering locks could not have prevented it, and a fix that adds two words.

TL;DR: inserting a row with a foreign key takes a FOR KEY SHARE lock on the row it points to. If two transactions do that and then both SELECT ... FOR UPDATE the same row, they deadlock. Use FOR NO KEY UPDATE when you are not changing the row's key.

The setup

I was building a small multi-currency ledger: accounts, deposits, and transfers between accounts, on PostgreSQL. The one thing a ledger must never do is lose money, and the classic way to lose it is a lost update: two requests read the same balance of 100, each adds its deposit, and each writes back its own result, so one deposit vanishes.

The usual cure is to lock the account row before reading it:

SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;

A second transaction asking for the same lock waits until the first commits, then reads the new balance. When a transfer touches several accounts, it locks them in ascending id order, so two transfers going in opposite directions queue up instead of deadlocking. That is the textbook rule, and I followed it.

A deposit records the deposit, locks the account, and updates the balance, all in one transaction. The deposit row has a foreign key to the account. A transfer has the same shape: it records the transfer, whose row points at the accounts, then locks them.

To check all this, I wrote concurrency tests that release sixteen threads at the same instant against the same accounts.

Reproducing it in a few lines of SQL

The first time I saw deadlock detected in the test output, I read it twice. A deadlock needs two transactions, each holding something the other wants. My test had sixteen threads moving money out of a single account. One row. What could they possibly be holding against each other?

So I stripped the problem down until there was nothing left for it to hide behind. Two tables are enough: an account, and deposits that point at it.

CREATE TABLE accounts (
    id      int PRIMARY KEY,
    balance bigint NOT NULL
);

CREATE TABLE deposits (
    id         serial PRIMARY KEY,
    account_id int    NOT NULL REFERENCES accounts (id),
    amount     bigint NOT NULL
);

INSERT INTO accounts VALUES (1, 0);

A deposit does what the application did: record the deposit, lock the account so no one else can change its balance in the meantime, then update the balance.

BEGIN;
INSERT INTO deposits (account_id, amount) VALUES (1, 100);
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Run it in two psql sessions, interleaved like this:

Step	Session A	Session B
1	BEGIN; INSERT INTO deposits ...
2		BEGIN; INSERT INTO deposits ...
3	SELECT ... FOR UPDATE (waits)
4		SELECT ... FOR UPDATE

A waiting at step 3 is fine. That's the lock doing its job. At step 4, I expected B to queue up behind A. Instead, about a second later, one of them is cancelled:

ERROR:  deadlock detected
DETAIL:  Process 103 waits for ShareLock on transaction 757; blocked by process 110.
Process 110 waits for ShareLock on transaction 756; blocked by process 103.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,1) in relation "accounts"

Every time. One row, two transactions, and each is waiting for the other. I had written exactly one lock. So where was the second one coming from?

I needed to see the locks on that row. The usual place to look, pg_locks, doesn't show them (more on why below), but the pgrowlocks extension does. Run from a third session between steps 2 and 3:

CREATE EXTENSION pgrowlocks;
SELECT locked_row, multi, modes, pids FROM pgrowlocks('accounts');

 locked_row | multi |               modes               |   pids
------------+-------+-----------------------------------+-----------
 (0,1)      | t     | {"For Key Share","For Key Share"} | {103,110}

There it was. Both sessions already held a lock on account 1, before either had run FOR UPDATE. multi = t means the lock is shared by more than one transaction. And the only thing either session had done to that row was insert a deposit that refers to it.

Where the lock comes from

So the insert was taking a lock I never asked for. It turns out that's simply how foreign keys work. PostgreSQL implements them with internal triggers (ri_triggers.c in its source). When you insert a row into deposits, a trigger checks that the account it refers to exists, and makes sure it keeps existing until your transaction ends. It does that with an ordinary query, which you can catch in the logs if you know to look. This one is copied from the project where I hit the problem, whose key has two columns:

SELECT 1 FROM ONLY "public"."accounts" x
WHERE "id" OPERATOR(pg_catalog.=) $1 AND "currency" OPERATOR(pg_catalog.=) $2
FOR KEY SHARE OF x

FOR KEY SHARE is the weakest of PostgreSQL's four row-level locks. It promises that the row won't be deleted and its key won't change, and nothing more. That is exactly what a foreign key needs, and because it asks for so little, any number of transactions can hold it on the same row at once. That's how both sessions got one.

The trouble is what FOR UPDATE conflicts with. Of the three lock modes involved here:

Held \ Requested	FOR KEY SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE			conflicts
FOR NO KEY UPDATE		conflicts	conflicts
FOR UPDATE	conflicts	conflicts	conflicts

FOR UPDATE is the strongest. It means "I might delete this row or change its key", so it conflicts even with FOR KEY SHARE. With that in mind, replay the timeline:

1. A inserts a deposit and holds FOR KEY SHARE on the account.
2. B does the same. The two shared locks don't conflict, so B gets one too.
3. A asks for FOR UPDATE. B's shared lock is in the way, so A waits for B.
4. B asks for FOR UPDATE. A's shared lock is in the way, so B waits for A.

Written out like that, it's almost obvious. Each holds a shared lock and wants to upgrade it to an exclusive one, which the other's shared lock prevents. This is a lock upgrade deadlock, and it's why ordering locks by id could never have helped: there was only one row to order. After deadlock_timeout (one second by default), PostgreSQL notices the cycle and cancels one transaction so the other can finish.

The error message hadn't helped me find any of this, and now I could see why. It says each process "waits for ShareLock on transaction 757", not for a lock on a row. PostgreSQL doesn't keep row locks in its shared lock table, which is also why pg_locks couldn't show them; it records them in the row itself. To wait for a row lock, a transaction waits for the transaction holding it to end, by requesting a share lock on that transaction's ID. So "ShareLock on transaction" is how every row lock wait shows up in that message, and CONTEXT: while locking tuple (0,1) is what tells you which row.

None of this is new, as I found out once I knew what to search for. FOR KEY SHARE and FOR NO KEY UPDATE were added in PostgreSQL 9.3, precisely so that foreign key checks would stop blocking ordinary updates; before that, the checks used FOR SHARE, which conflicts with any update of the row. Laurenz Albe walked through almost exactly this deadlock in Debugging deadlocks in PostgreSQL (2022), and Leo Sjöberg hit a variant of it in Lock propagation in Postgres (2024); both arrive at FOR NO KEY UPDATE too. What follows is how a test caught it in a real code base, the fix, and a second bug with the same cause.

The fix: ask for the lock you need

Once I could see the cause, the fix was almost embarrassingly small. FOR UPDATE says "I might delete this row or change its key." I was doing neither. I was changing a balance, and PostgreSQL has a lock for exactly that:

SELECT balance FROM accounts WHERE id = 1 FOR NO KEY UPDATE;

Two words. Look at the table again: FOR NO KEY UPDATE does not conflict with FOR KEY SHARE, so the foreign key check no longer stands in its way. But it does conflict with itself, so two deposits into the same account still take turns, which was the whole point of locking in the first place.

I didn't quite believe it until I had replayed the two sessions:

Step	Session A	Session B
1	BEGIN; INSERT INTO deposits ...
2		BEGIN; INSERT INTO deposits ...
3	SELECT ... FOR NO KEY UPDATE → 0
4		SELECT ... FOR NO KEY UPDATE (waits)
5	UPDATE ...; COMMIT;
6		→ 100
7		UPDATE ...; COMMIT;

A gets its lock straight away, even though B holds a shared lock on the same row. B waits, and when A commits, B reads 100, not 0. The final balance is 200: no deadlock, and no lost update.

pgrowlocks shows why A didn't have to wait. Run from a third session between steps 3 and 5, it finds three locks on the one row at the same time:

SELECT modes, pids FROM pgrowlocks('accounts');

                         modes                         |     pids
-------------------------------------------------------+---------------
 {"For Key Share","For No Key Update","For Key Share"} | {103,103,110}

The two shared locks from the foreign key checks, and A's FOR NO KEY UPDATE, sitting side by side. A (process 103) appears twice: it still holds the shared lock from its own insert, and now the stronger one as well. With FOR UPDATE, that middle lock could never have joined the other two.

FOR NO KEY UPDATE is also, it turns out, the lock PostgreSQL takes on its own for an UPDATE that doesn't touch a key column. My FOR UPDATE had been asking for more than a plain UPDATE of the same row would.

Why not just change the order?

The obvious alternative is to lock the account before inserting the deposit. That avoids the deadlock too: B would queue at its FOR UPDATE before it ever held a shared lock. But it only works as long as every code path, now and in the future, gets the order right. The first time someone writes a new path that inserts a row pointing at the account and then locks it, the deadlock is back, and nothing will warn them. FOR NO KEY UPDATE removes the conflict itself, so no ordering can bring it back.

It wasn't a one-off, either. Transfers have a foreign key to the quote they carry out, and they locked that quote the same way. Once I knew what to look for, that lock changed too.

What about SERIALIZABLE?

If you know PostgreSQL well, you may be wondering about this. Raising the isolation level on its own changes nothing: FOR UPDATE takes the same row lock at every isolation level. The real alternative is SERIALIZABLE without explicit row locks: let PostgreSQL detect the conflict, fail one transaction with a serialization error, and retry it. That is a sound design, but a different one, in which every write path has to be ready to retry.

Why lock at all? Why not UPDATE ... SET balance = balance + 100?

It's the question I would ask too, and the honest answer is that it would work. A single UPDATE ... RETURNING balance has no gap between reading and writing, so there is no lost update, and it takes a FOR NO KEY UPDATE lock, so it would not have deadlocked either. A CHECK constraint can refuse an overdraft.

I lock explicitly because a transfer touches several rows, a quote and up to five accounts (sender, recipient, fee revenue and two FX positions), and I wanted the lock order visible in one place, with the business checks (currency matches, balance covers the amount) written as code that returns a clear error, not inferred from a constraint violation. That is a trade-off, not a necessity, and I can see someone reasonably choosing the other way.

If you use JPA

If you're on Spring with JPA, you may have this bug without ever having typed FOR UPDATE. @Lock(LockModeType.PESSIMISTIC_WRITE) is how Hibernate asks for this lock, and the SQL it becomes depends on the version:

• Hibernate 6.x and earlier (Spring Boot 3 and before): the PostgreSQL dialect inherits the default, " for update". An entity whose table has foreign keys pointing at it can deadlock exactly as above.
• Hibernate 7.0 and later (Spring Boot 4): " for no key update". But the NOWAIT and SKIP LOCKED variants still emit for update.

A second bug with the same cause

The foreign key check had one more surprise for me.

Waiting for a lock shouldn't take forever, so I capped it with lock_timeout: after five seconds, a request fails with a retryable error instead of hanging. I set it with SET LOCAL at the start of the method that locks accounts. That seemed like the obvious place, since that was where the locks were.

To test it, I held a lock on an account from one transaction and sent a deposit from another, with the timeout set to 300 ms. I expected an error almost immediately. Instead, the deposit sat there for ten seconds, until the test released its lock, and then succeeded as if nothing had happened. The limit had never applied at all.

By then I recognised the pattern. The deposit's INSERT ran the foreign key check, and the check waited for the test's lock, before my code ever reached the line that set the timeout. I had put the limit next to the locks I had written, but the lock that mattered was one I hadn't written.

The fix was to set it on every connection, when the connection pool opens it:

spring.datasource.hikari.connection-init-sql=SET lock_timeout = '5s'

Running SET LOCAL lock_timeout as the first statement of every transaction would also work, but only as long as every transaction remembers to. Setting it on the connection doesn't depend on anyone remembering, which is the same reason I preferred FOR NO KEY UPDATE to getting the order right.

When the timeout fires, PostgreSQL reports SQLState 55P03. The service checks for that SQLState and turns it into a 503, so the client knows it can retry with the same idempotency key.

How the test caught it

The test that failed sent sixteen transfers out of one account at once. Transfers and deposits have the same shape, so I wrote a test for sixteen different deposits into one account. It deadlocked too, and that was the uncomfortable part: the bug had been there since deposits were added. The earlier tests had simply never sent two different deposits into the same account at the same moment.

Looking back, two things made the difference between a test that caught this and one that would have passed forever.

The first was making the threads actually collide. Starting sixteen threads is not the same as sixteen requests at once: the first few can finish before the last few have even started, and then you are testing sixteen deposits in a row. So every test waits at a start gate until all its threads are ready, then releases them together:

CountDownLatch ready = new CountDownLatch(threads);
CountDownLatch go = new CountDownLatch(1);
// each thread: ready.countDown(); go.await(); deposit(...);
ready.await();   // every thread is waiting at the gate
go.countDown();  // release them all at once

The second was running against the real thing. The tests use an actual PostgreSQL, started in Docker by Testcontainers. An in-memory database has its own locking rules, and a mock has none at all. Either could have passed while PostgreSQL deadlocked. The lock that caused all this was taken inside PostgreSQL's own foreign key trigger, and only PostgreSQL was going to take it.

I also wanted to know how often this actually happens, because "the test failed once" can mean a lot of things with concurrency. So I ran the deposit test 30 times with each lock:

Row lock	Runs	Deadlocks
FOR UPDATE	30	30
FOR NO KEY UPDATE	30	0

Thirty out of thirty. This was never a rare race waiting for bad luck. Whenever two deposits into the same account overlapped, both inserting before either locked, they deadlocked.

What I took away

I went into this thinking I understood row locks: lock before you read, always in the same order, done. What I had missed is that I wasn't the only one taking them.

• A foreign key locks the row it points to. Every insert into deposits quietly took a lock on an account. Nothing in my code said so, and nothing in the error message pointed at it. Any lock I took on that row afterwards had to get along with it.
• Ask for the lock you need, not the strongest one. I reached for FOR UPDATE because it's the one everyone knows. But I was changing a balance, not deleting the row or its key, and FOR NO KEY UPDATE says exactly that. It still stops lost updates, and it doesn't fight foreign keys.
• Make concurrent tests really concurrent, against the real database. Every test that sent one request at a time passed. The bug only existed when two requests overlapped, in PostgreSQL, and that is the only place I could have found it.

If you want to see it for yourself, everything is in fx-ledger. The lock is in AccountRepository.lockById, and the tests that caught it are LedgerConcurrencyTest and TransferConcurrencyTest. Change FOR NO KEY UPDATE back to FOR UPDATE and run them.