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 start to matter when application code bypasses them with a common pattern: read a row, decide something in code, write the result back. The explicit locking clauses (FOR UPDATE, NOWAIT, SKIP LOCKED) are the standard tools for fixing that pattern, and knowing when each one fits is what separates a working checkout from one that oversells.
The behavior described is PostgreSQL 17’s (earlier supported versions behave the same). To reproduce the two-session scenarios, open two psql sessions and start the same script in both within a second of each other; the pg_sleep(1) in each script makes the overlap reliable.
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.) With both sessions running it, the stock afterwards is:
after_two_sales
-----------------
7Six 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
------------------------
4FOR 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)NOWAITfails immediately (SQLSTATE55P03). It’s useful for interactive actions where “someone else is editing this, try again” is a better answer than a spinner.lock_timeoutwaits up to a limit, for any lock the statement needs. Setting it for application roles (for exampleALTER 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.
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. You can see the difference with two sessions:
-- session 1: the stock check, holding its lock
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE; -- then repeat with FOR NO KEY UPDATE
-- session 2: an order line for the same product
-- (order_lines.product_id REFERENCES products)
INSERT INTO order_lines (product_id, qty) VALUES (1, 3);With FOR UPDATE in session 1, the insert blocks until session 1 commits or rolls back. With FOR NO KEY UPDATE, it goes straight through. To check without waiting, run SELECT * FROM products WHERE id = 1 FOR KEY SHARE NOWAIT in session 2 instead: it fails with could not obtain lock on row (55P03) while session 1 holds FOR UPDATE, and returns the row while session 1 holds FOR NO KEY UPDATE. 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. With one session stuck behind another, the output looks like this:
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=1Row-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.