Locks, SELECT FOR UPDATE and Deadlocks

A lock is how PostgreSQL makes two sessions take turns on the same data. Most of the time you never notice, because a normal UPDATE locks only the rows it changes, and only until the transaction ends. The problems start when your own code reads a value, thinks about it, and writes it back; when two transactions lock the same rows in a different order; or when one transaction holds a lock and then stops to wait for something else. This lesson shows each case with two psql sessions, A and B, in the order the commands ran in time. Every output is from a real run on two connections.

The lost update

A stock table has 10 pens. Two checkout sessions each sell one. Each reads the quantity, subtracts one in application code, and writes the result back.

Session A

CREATE TABLE stock (sku text PRIMARY KEY, qty integer NOT NULL);
INSERT INTO stock VALUES ('PEN-01', 10), ('MUG-01', 4);
BEGIN;
SELECT qty FROM stock WHERE sku = 'PEN-01';

CREATE TABLE
INSERT 0 2
BEGIN
 qty
-----
  10
(1 row)

Session B reads the same row before A has written anything.

BEGIN;
SELECT qty FROM stock WHERE sku = 'PEN-01';

BEGIN
 qty
-----
  10
(1 row)

Both have seen 10. Each now writes 9, and commits.

UPDATE stock SET qty = 9 WHERE sku = 'PEN-01';
COMMIT;

UPDATE 1
COMMIT

UPDATE stock SET qty = 9 WHERE sku = 'PEN-01';
COMMIT;
SELECT qty FROM stock WHERE sku = 'PEN-01';

UPDATE 1
COMMIT
 qty
-----
   9
(1 row)

Two pens were sold and the stock says 9. PostgreSQL did nothing wrong: B's UPDATE was a plain write of the value 9, and B was allowed to do it once A had committed. The mistake is the gap between the read and the write. There are two fixes. The first is to let the database do the arithmetic in one statement, with SET qty = qty - 1. The database locks the row, reads the current value, and writes it, with nobody in between. The second is needed when the logic is more than one subtraction: lock the row at the read.

SELECT ... FOR UPDATE

FOR UPDATE reads the row and locks it until the transaction ends, so another session that wants to change it, or lock it, has to wait. Plain reads are not blocked.

BEGIN;
SELECT qty FROM stock WHERE sku = 'PEN-01' FOR UPDATE;

BEGIN
 qty
-----
   9
(1 row)

Session B can still read the row without waiting. It cannot lock it. NOWAIT makes B fail at once instead of waiting, and SKIP LOCKED makes it pass over locked rows (the next tutorial in this track uses that for a job queue). The \set VERBOSITY terse line keeps only the first line of each error message.

\set VERBOSITY terse
SELECT qty FROM stock WHERE sku = 'PEN-01';
BEGIN;
SELECT qty FROM stock WHERE sku = 'PEN-01' FOR UPDATE NOWAIT;
ROLLBACK;

 qty
-----
   9
(1 row)

BEGIN
ERROR:  could not obtain lock on row in relation "stock"
ROLLBACK

If B had asked without NOWAIT, it would have waited until A finished, which can be forever when A is stuck. A cap on the waiting is lock_timeout.

SET lock_timeout = '300ms';
UPDATE stock SET qty = qty - 1 WHERE sku = 'PEN-01';

SET
ERROR:  canceling statement due to lock timeout

Seeing who is waiting

Now B waits with no limit, and A looks at the server. Session B's statement stays blocked, and the output below appears only after A commits.

RESET lock_timeout;
UPDATE stock SET qty = qty - 1 WHERE sku = 'PEN-01';

RESET
UPDATE 1

SELECT state, wait_event_type, wait_event, left(query, 38) AS query,
       cardinality(pg_blocking_pids(pid)) AS blocked_by
FROM pg_stat_activity
WHERE datname = current_database() AND wait_event_type = 'Lock';
COMMIT;

 state  | wait_event_type |  wait_event   |                 query                  | blocked_by
--------+-----------------+---------------+----------------------------------------+------------
 active | Lock            | transactionid | UPDATE stock SET qty = qty - 1 WHERE s |          1
(1 row)

COMMIT

pg_stat_activity shows the session that waits for a lock, and pg_blocking_pids(pid) lists the sessions in its way. To find the blocker in a real incident, take that list and look up its query and state in the same view. Most of the time the blocker is a transaction that was left open.

A deadlock

A deadlock is two transactions that each hold a lock the other needs. Waiting longer cannot fix it, so PostgreSQL watches for it. After deadlock_timeout (one second by default) it checks for a cycle and cancels one of the transactions.

BEGIN;
UPDATE stock SET qty = 100 WHERE sku = 'PEN-01';

BEGIN
UPDATE 1

BEGIN;
UPDATE stock SET qty = 200 WHERE sku = 'MUG-01';

BEGIN
UPDATE 1

A now wants the mug, which B holds, and has to wait. Its output appears only after the deadlock is resolved.

UPDATE stock SET qty = 101 WHERE sku = 'MUG-01';

UPDATE 1

B wants the pen, which A holds. Now each waits for the other.

UPDATE stock SET qty = 201 WHERE sku = 'PEN-01';

ERROR:  deadlock detected

PostgreSQL cancelled B's statement and left A's to finish. B's transaction is aborted and has to be rolled back. In the real log, the message also has lines with the process numbers and the two queries, which is where you look first.

ROLLBACK;

ROLLBACK

COMMIT;
SELECT sku, qty FROM stock ORDER BY sku;

COMMIT
  sku   | qty
--------+-----
 MUG-01 | 101
 PEN-01 | 100
(2 rows)

Preventing deadlocks

The cycle exists because A took the pen and then the mug, while B took the mug and then the pen. If every transaction takes its rows in the same order, one of them simply waits for the other and no cycle can form. When a transaction needs several rows, lock them together, sorted:

BEGIN;
SELECT sku FROM stock WHERE sku IN ('MUG-01', 'PEN-01') ORDER BY sku FOR UPDATE;
COMMIT;

BEGIN
  sku
--------
 MUG-01
 PEN-01
(2 rows)

COMMIT

Keep transactions short, and do slow work such as network calls outside them. A deadlock is also a normal event in busy systems. Your application should be ready to run the whole transaction again when it gets the error 40P01.

Common mistakes

• Reading a value, calculating in application code, and writing it back without a lock.
• Taking rows in a different order in different parts of the code.
• Leaving a transaction open while the application waits for something else.
• Retrying a single statement after a deadlock. The whole transaction was rolled back, so retry all of it.
• Using LOCK TABLE to avoid thinking about rows. It blocks every other writer.

If you skip this, your stock will be wrong by small amounts that nobody can explain, and the first busy day will bring an error that you cannot reproduce on your laptop. Where in your code does a value go out of the database and come back in a second statement?