When Two Writers Want the Same Row: Locking and Deadlocks

The last chapter showed how Postgres lets many transactions read the same data at once without blocking. MVCC hands every reader its own snapshot, so readers never wait. Writes are different. Two transactions can read the same row simultaneously, but they cannot both change it and each pretend the other didn’t. Something has to give. That something is a lock, and this chapter is about what locks do, the trouble two of them can get into together, and how to stay out of it.

An UPDATE locks the row

When a transaction updates a row, Postgres takes a row-level lock on it, held until the transaction ends. Any other transaction that tries to update the same row has to wait for the lock to be released, which happens at COMMIT or ROLLBACK. Readers are unaffected; they see the old version until the writer commits. Only a competing writer waits.

You can watch it happen with two sessions. I made a small scratch table to keep the demo clean:

CREATE TABLE acct(id int primary key, owner text, bal numeric);
INSERT INTO acct VALUES (1,'Ada',100),(2,'Grace',100);

Session A opens a transaction, updates row 1, then sits for three seconds before committing:

BEGIN;
UPDATE acct SET bal = bal - 10 WHERE id = 1;
SELECT pg_sleep(3);
COMMIT;

One second later, session B tries to update the same row, with timing on:

UPDATE acct SET bal = bal - 20 WHERE id = 1;
Time: 2091.117 ms (00:02.091)

Two seconds. Session B didn’t fail and it didn’t overwrite anything blindly. It waited for A to commit, then applied its own change on top of the committed result. The final balance is 70: a hundred, minus A’s ten, minus B’s twenty. Neither update was lost. That waiting is the whole point of a write lock. Without it, both sessions would read 100, both would compute their own answer, and whichever wrote last would erase the other, a classic lost update.

The lock is on the row, not the table. While A held row 1, any session was free to update row 2 without waiting. Postgres locks as narrowly as it can, so unrelated writes proceed in parallel.

Locking a row you’re only reading

Sometimes you want that lock before you write, or even without writing at all. The pattern is read-decide-write: read a row, make a decision based on its value, then update it. Between the read and the write, someone else could change the row out from under you. SELECT ... FOR UPDATE closes that gap by locking the rows the moment you read them:

BEGIN;
SELECT bal FROM acct WHERE id = 1 FOR UPDATE;   -- locked now, not at UPDATE time
-- ... application logic decides the new balance ...
UPDATE acct SET bal = 90 WHERE id = 1;
COMMIT;

Any other transaction that tries to FOR UPDATE or UPDATE that row now waits for you, exactly as if you’d already written it. There is a gentler sibling, FOR SHARE. It lets other readers take a share lock too, but blocks anyone trying to update. Reach for it when you need a row to stay put while you do related work but aren’t the one changing it. Use FOR UPDATE when you intend to write, FOR SHARE when you only need the row frozen.

The deadlock

Locks make single-row contention safe. Two locks, acquired in the wrong order, create a new problem. A deadlock is a cycle: A holds a lock B wants, and B holds a lock A wants. Neither can proceed, and neither will give up, because giving up means rolling back.

It’s easy to cause by accident. Here are two sessions that each move money between the same two accounts, but in opposite order. Session A touches row 1 then row 2; session B touches row 2 then row 1:

-- Session A
BEGIN;
UPDATE acct SET bal = bal - 10 WHERE id = 1;   -- locks row 1
SELECT pg_sleep(2);
UPDATE acct SET bal = bal + 10 WHERE id = 2;   -- wants row 2

-- Session B (started right after A)
BEGIN;
UPDATE acct SET bal = bal - 20 WHERE id = 2;   -- locks row 2
SELECT pg_sleep(2);
UPDATE acct SET bal = bal + 20 WHERE id = 1;   -- wants row 1

A locks row 1, B locks row 2, both sleep, then each reaches for the row the other is holding. The cycle is closed. Postgres runs a deadlock detector, notices the loop, and breaks it by killing one of the two transactions. Session B got the axe:

ERROR:  deadlock detected
DETAIL:  Process 3308 waits for ShareLock on transaction 990; blocked by process 3309.
Process 3309 waits for ShareLock on transaction 989; blocked by process 3308.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,5) in relation "acct"
ROLLBACK

B was rolled back whole; none of its changes survived. A, unblocked, finished cleanly. The final table showed row 1 at 90 and row 2 at 110, exactly A’s two changes and nothing of B’s. The database didn’t corrupt anything and it didn’t hang forever. It picked a victim, aborted it, and moved on. But that victim is your transaction, and now your application has to notice the error and retry.

The fix is boring, and that’s good

The cause of that deadlock wasn’t the accounts or the amounts. It was the order. A grabbed 1 then 2; B grabbed 2 then 1. If both sessions had locked the lower id first, there would have been no cycle: whoever got row 1 would also get row 2, and the other would simply wait, then proceed. The blocking demo from the top of this chapter is what a well-ordered contention looks like: a short wait, then success.

So the rule that prevents almost every application deadlock is consistent lock ordering. Whenever a transaction touches multiple rows or tables, acquire them in the same agreed order everywhere in your codebase. Order by primary key, by table name, by whatever total order is convenient, as long as it’s the same one every time. Deadlocks come from disagreement about order. Remove the disagreement and you remove the cycle.

Two more knobs are worth knowing. By default a blocked statement waits indefinitely. SET lock_timeout = '500ms' makes it give up instead:

SET lock_timeout = '500ms';
UPDATE acct SET bal = bal - 5 WHERE id = 1;   -- while another session holds row 1
ERROR:  canceling statement due to lock timeout
Time: 502.955 ms

It failed at half a second rather than waiting out the holder. And if you’d rather not wait at all, FOR UPDATE NOWAIT errors the instant the row is already locked:

SELECT * FROM acct WHERE id = 1 FOR UPDATE NOWAIT;   -- row held elsewhere
ERROR:  could not obtain lock on row in relation "acct"

Both turn an unbounded wait into a fast, catchable error, which a responsive service usually prefers over a request that hangs.

MySQL’s InnoDB behaves much the same: row locks, SELECT ... FOR UPDATE, an automatic deadlock detector that rolls back the cheaper transaction, and an innodb_lock_wait_timeout. SQLite is the outlier: it locks at the level of the whole database file. Writers are serialized, so there’s no row-level deadlock to speak of, at the cost of concurrency.

Final thoughts

Locks are how a database keeps concurrent writers from clobbering each other: an UPDATE holds its rows until it commits, and a competing writer waits its turn. That waiting is safety, not a bug. The one genuinely dangerous case is the deadlock, and it is entirely of your own making, born from two transactions disagreeing about the order to grab their locks. Lock in a consistent order and the cycle can’t form. When contention is heavy, lock_timeout and NOWAIT let you fail fast instead of hanging. Next we turn from the rows a transaction locks to the rows it leaves behind: the dead versions MVCC accumulates, and the maintenance that cleans them up.

Next: Cleaning up dead rows: VACUUM and bloat — where dead tuples come from, how to see them, and what VACUUM reclaims.