How a Foreign Key Silently Causes PostgreSQL Deadlocks in Concurrent Transactions

A developer building a multi-currency ledger on PostgreSQL discovered a recurring deadlock despite following standard lock-ordering practices. The root cause was an implicit FOR KEY SHARE lock that PostgreSQL automatically acquires on a referenced row whenever a foreign key constraint is checked during an INSERT. When two concurrent transactions each inserted a deposit row pointing to the same account, both held a FOR KEY SHARE lock on that account row. Attempting a SELECT FOR UPDATE afterward caused each transaction to wait for the other to release its lock, creating a deadlock. The fix requires replacing FOR UPDATE with FOR NO KEY UPDATE, which is compatible with FOR KEY SHARE and prevents the conflict entirely.