Locks and Deadlocks

The Lock Chamber has one door and one guard, Mutex, who lets exactly one worker through at a time. Everyone else waits outside until the worker inside leaves. A lock is that guard in code: it turns "many things at once" into "one at a time" for a small piece of work. Used well, it fixes races that an atomic update can't express; used carelessly, it freezes your app.

What a lock guarantees

A lock (often called a mutex, for "mutual exclusion") guarantees that only one holder at a time runs the protected section. Others who ask for the same lock wait until it is released. It does not make code faster or safer in any other way, and it only protects code that actually asks for it. If one route takes the lock and another route writes the same data without it, the lock protects nothing.

Where the lock lives matters. A lock in JavaScript memory only covers one process. With several servers, the lock has to live somewhere they all share, which for most apps is the database.

Row locks: SELECT ... FOR UPDATE

Sometimes the logic is too complex for one UPDATE: you read a balance, check limits, look up fees, then write. Postgres lets you lock the row while you do it:

BEGIN;
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
-- your code checks the balance and computes the new value
UPDATE accounts SET balance = $2 WHERE id = $1;
COMMIT;

FOR UPDATE means "I am about to change this row; nobody else may lock or change it until I finish." A second transaction running the same SELECT ... FOR UPDATE on that row waits until the first commits or rolls back, then reads the new value. Plain SELECTs without FOR UPDATE are not blocked. The lock lasts until the transaction ends, so it only works inside BEGIN … COMMIT; on its own, the statement's transaction ends right away and so does the lock.

Hold locks briefly

While you hold a lock, everyone who needs it is stuck. A common bug is doing slow work inside the locked section:

await client.query('BEGIN');
await client.query('SELECT ... FOR UPDATE', [productId]);
await paymentApi.charge(card, total); // 3 seconds, lock still held
await client.query('UPDATE ...');
await client.query('COMMIT');

Every other checkout for that product now queues behind a network call, and each waiting request also holds a database connection. Do slow work (API calls, emails, heavy computation) before taking the lock or after releasing it. Inside, do only the read, the check and the write.

Deadlocks

Two transfers run at once. Transfer 1 moves money from account A to B, so it locks A and then B. Transfer 2 moves money from B to A, so it locks B and then A. Each gets its first lock, then waits forever for the lock the other one holds. That is a deadlock: two holders, each waiting on the other.

Postgres notices this after about a second, picks one transaction, and aborts it with a deadlock detected error (code 40P01). The other one continues. Your app sees a failed request that seems random, because it only happens when two opposite transfers overlap.

Fixing deadlocks

The main fix is a consistent order: every piece of code takes locks in the same order, for example lowest id first.

const [first, second] = [fromId, toId].sort((a, b) => a - b);
await client.query('SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE', [first]);
await client.query('SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE', [second]);

Now both transfers try to lock the same account first, so one simply waits for the other. Two more habits help:

• Timeouts. SET lock_timeout = '2s' makes a transaction give up with an error instead of waiting forever, so one stuck request can't pile up every other one.
• Retry once. A transaction aborted by a deadlock or lock timeout did nothing, since it was rolled back, so it is safe to retry it a small number of times.

Choosing the right tool

Reach for the simplest option that works. An atomic UPDATE ... WHERE covers counters, stock and balances. A unique constraint covers "only one." Use SELECT ... FOR UPDATE when the decision needs several reads and some code, and keep that transaction short. If you find yourself locking many rows in different orders across the codebase, write one helper that sorts and locks, and use it everywhere.