PostgreSQL Row Locks and Concurrent Updates

Row locks are how PostgreSQL stops two transactions from changing the same row at the same time. Most of the time you don’t think about them: every UPDATE and DELETE takes one automatically. They become an interview topic because of the pattern that bypasses them, reading a row, deciding something in application code, and writing back, and because the explicit locking clauses (FOR UPDATE, NOWAIT, SKIP LOCKED) are the standard tools for fixing it.

Everything here was run on PostgreSQL 16.15 with two real psql sessions started a fraction of a second apart.

Two sales, one decrement

A product has 10 lamps in stock. Two customers each buy 3. The checkout code reads the stock, checks there’s enough, and writes the new value. Here it is as SQL, with pg_sleep(1) standing in for the application work between the read and the write:

BEGIN;
SELECT stock AS seen FROM products WHERE id = 1 \gset
SELECT pg_sleep(1);
UPDATE products SET stock = :seen - 3 WHERE id = 1;
COMMIT;

(\gset is psql’s way of storing a query result in a variable, the same thing your app does with the row it fetched.) Both sessions ran it:

 after_two_sales
-----------------
               7

Six lamps sold, stock dropped by three. Both sessions read 10, both computed 7, both wrote 7. That’s a lost update. The second UPDATE did wait for the first session’s row lock (PostgreSQL never lets two writers modify a row concurrently), but by then it was writing a value computed from a stale read. The lock protected the write, not the decision.

The transaction didn’t help. At PostgreSQL’s default Read Committed isolation, a plain SELECT takes no row lock and sees the last committed value at the moment it runs. See transaction isolation for how Repeatable Read changes this outcome into an error instead.

Fix 1: let the UPDATE do the arithmetic

If the logic fits in SQL, do it in one statement:

UPDATE products
SET stock = stock - 3
WHERE id = 1 AND stock >= 3
RETURNING stock;

This doesn’t lose updates. The second session’s UPDATE waits for the first to commit, then PostgreSQL re-reads the newly committed row version and re-checks the WHERE clause against it before applying stock - 3. Two sales take 10 to 4. If stock were 4 and three customers each tried to buy 3, the third UPDATE would find stock >= 3 false on the re-read and update zero rows, which your code treats as “sold out”. RETURNING gives you the new value without a second query.

A CHECK (stock >= 0) constraint on the column is a good safety net behind this: even code that forgets the WHERE condition can’t drive stock negative. The constraints guide covers that side.

This is the best fix whenever it applies, because the lock is held only for the duration of one statement.

Fix 2: lock the row when you read it

Sometimes the decision can’t be expressed in SQL: you need to call a pricing service, apply rules in code or check several rows. Then lock the row as you read it:

BEGIN;
SELECT stock AS seen FROM products WHERE id = 1 FOR UPDATE \gset
SELECT pg_sleep(1);
UPDATE products SET stock = :seen - 3 WHERE id = 1;
COMMIT;

 after_two_sales_locked
------------------------
                      4

FOR UPDATE takes the same row lock an UPDATE would. The second session’s SELECT ... FOR UPDATE waits until the first commits, then returns the committed value, 7, and computes 4. Correct.

The cost is that the lock is held from the SELECT until COMMIT, including all the application work in between. Keep that window short. In particular, never hold a row lock across an HTTP call to a payment provider or anything else that can hang; one slow request then blocks every checkout for that product. Lock as late as you can, do the minimum, commit.

Not waiting: NOWAIT, lock_timeout and SKIP LOCKED

By default a blocked transaction waits indefinitely. While one session held a FOR UPDATE lock on the lamp, three other approaches behaved like this:

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

SET lock_timeout = '500ms';
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- ERROR:  canceling statement due to lock timeout
-- CONTEXT:  while updating tuple (0,7) in relation "products"

SELECT * FROM products WHERE id = 1 FOR UPDATE SKIP LOCKED;
-- (0 rows)

• NOWAIT fails immediately (SQLSTATE 55P03). It’s useful for interactive actions where “someone else is editing this, try again” is a better answer than a spinner.
• lock_timeout waits up to a limit, for any lock the statement needs. Setting it for application roles (for example ALTER ROLE app SET lock_timeout = '5s') turns a pile-up of waiting connections into fast errors you can see and retry. It’s also standard practice before running migrations, which can otherwise queue behind a long transaction and block everyone behind them.
• SKIP LOCKED pretends locked rows don’t exist. That’s wrong for inventory (the lamp is still there) and perfect for work queues, where any free job will do. See concurrent job claiming for that pattern.

Lock strength and foreign keys

PostgreSQL has four row-lock modes, from strongest to weakest: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. The difference that matters in practice is between the first two.

When you insert an order line referencing product 1, the foreign key check takes a FOR KEY SHARE lock on the product row, to make sure nobody deletes it or changes its key mid-insert. FOR KEY SHARE conflicts with FOR UPDATE but not with FOR NO KEY UPDATE. Timing an order-line insert while another session held each lock on the product:

FOR UPDATE         fk insert waited 2.6s
FOR NO KEY UPDATE  fk insert waited 0.1s

With FOR UPDATE, inserting an order line for the lamp blocked until the stock-checking transaction committed. With FOR NO KEY UPDATE, it went straight through. A plain UPDATE that doesn’t modify key columns takes the weaker FOR NO KEY UPDATE lock automatically, so it doesn’t block those inserts either. If you lock a row only to change non-key columns, SELECT ... FOR NO KEY UPDATE gives you the same protection against other writers with less contention. Use FOR UPDATE when you might delete the row or change its primary key.

FOR SHARE lets several transactions lock the same row for reading while blocking writers. It’s useful for “make sure this row doesn’t change until I commit” when you’re not going to update it yourself.

Seeing who’s blocking whom

When requests hang, pg_stat_activity and pg_blocking_pids show the chain:

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, left(query, 50)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

 pid | wait_event_type |  wait_event   | blocked_by |                      query
-----+-----------------+---------------+------------+--------------------------------------------------
 554 | Lock            | transactionid | {526}      | UPDATE products SET stock = stock - 1 WHERE id=1

Row-lock waits often show as transactionid, because a waiter waits for the holding transaction to finish rather than for a lock on the row itself. Look up pid 526 to find the holder, which is very often idle in transaction: an application that opened a transaction and never committed.

Two transactions that lock the same rows in opposite orders deadlock. PostgreSQL detects that after deadlock_timeout (1 second by default) and aborts one with SQLSTATE 40P01. The fix is a consistent lock order, such as always locking rows sorted by id, plus a retry; see deadlock retry.

Interview practice: transferring money between wallets

A wallet service moves money from one user to another. The current code does SELECT balance FROM wallets WHERE id = $from, checks the balance in application code, then runs two UPDATEs in a transaction. Users report that balances occasionally go negative under load, and once the service logged a deadlock. Explain both problems and how you’d fix them.

Answer

Negative balances are lost decisions. Two transfers from the same wallet both read the balance before either writes, both pass the check, and both debit. The plain SELECT takes no lock, and the transaction doesn’t prevent this at Read Committed.

The simplest fix is to put the check in the debit itself: UPDATE wallets SET balance = balance - $amt WHERE id = $from AND balance >= $amt, and abort the transfer if it updated zero rows. The re-read after waiting makes the check use the committed balance. A CHECK (balance >= 0) constraint backs it up. If the business rules need code (fraud limits, currency conversion), lock both wallets with SELECT ... FOR NO KEY UPDATE first, then check and update.

The deadlock comes from lock order. A transfer from A to B locks A then B; a simultaneous transfer from B to A locks B then A, and each waits for the other. Lock both rows in a consistent order, for example SELECT ... FROM wallets WHERE id IN ($from, $to) ORDER BY id FOR NO KEY UPDATE, before modifying either. I’d also add a retry on 40P01 and 40001, set lock_timeout for the service role, and keep the transaction free of network calls so locks are held for milliseconds.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• SQL transaction isolation and observable anomalies
• Concurrent job claiming with SKIP LOCKED
• Official docs: Explicit locking: row-level locks