SQL isolation levels, locks and deadlocks

#Why it matters

SQL isolation levels decide what one transaction sees while others are changing the same data, and getting them wrong produces bugs that never show up in testing: stock that goes negative, two people booked into the last seat, an on-call rota with nobody on it. These bugs need two users at the same moment, so they appear only under real traffic. In this lesson you will cause each one on purpose with two psql sessions, then fix it. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and "two users update the same row at once: what can go wrong and how do you prevent it?" is a standard backend and data engineering interview question.

This lesson builds on SQL transactions and ACID: COMMIT and ROLLBACK, which covers BEGIN, COMMIT, ROLLBACK and the I in ACID.

#The concept

#Four things that go wrong when transactions overlap

A fifth, write skew, is the subtle one: two transactions each read a set of rows, check a rule ("at least one person stays on call"), and update different rows. Each is fine alone; together they break the rule.

#Isolation levels

The SQL standard defines four levels by which anomalies they allow. What each database actually does is stricter in places:

Defaults: PostgreSQL, SQL Server and Oracle use READ COMMITTED; MySQL InnoDB uses REPEATABLE READ. Warehouses offer little choice: Snowflake supports READ COMMITTED for tables and BigQuery uses snapshot isolation. Set the level per transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;   -- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- standard, before the first statement

#MVCC: readers do not block writers

PostgreSQL, Oracle, MySQL InnoDB and SQL Server with snapshot isolation use multi-version concurrency control. An update writes a new version of the row and keeps the old one until no transaction needs it. A reader sees the version that matches its snapshot, so:

• readers never wait for writers, and writers never wait for readers;

• writers do wait for other writers on the same row (row locks);

• old versions pile up until VACUUM removes them, which is why long-open transactions cause table bloat.

SQL Server's default READ COMMITTED uses locks instead, so there readers can wait for writers unless READ_COMMITTED_SNAPSHOT is on (it is on in Azure SQL Database).

#Locks you will meet

• Row locks, taken by UPDATE, DELETE and SELECT ... FOR UPDATE, held until the transaction ends. A second writer to the same row waits.

• FOR UPDATE SKIP LOCKED skips rows another transaction has locked instead of waiting: the standard way to build a job queue in SQL.

• Table locks. Every query takes a light lock on the tables it touches. ALTER TABLE, DROP, TRUNCATE and some index builds need an exclusive lock that conflicts with everything, including SELECT.

• Deadlock: two transactions each hold a lock the other needs. The database detects the cycle (PostgreSQL checks after deadlock_timeout, 1 second by default), cancels one transaction (the victim) and lets the other continue.

#Pessimistic vs optimistic

Pessimistic locking takes the lock before reading (SELECT ... FOR UPDATE), so others wait. Good when conflicts are common and transactions are short. Optimistic locking reads without locks and checks at write time that nobody changed the row, usually with a version column; on conflict, the application reloads and retries. Good for web forms where a user may look at a record for minutes.

#Worked example: two sessions, one stock table

PostgreSQL 16 with two psql windows open on the same database: Session A and Session B. A small stock table uses two products from the UCI Online Retail data (CC BY 4.0). Every output is from a real run; a step only happens after the previous one has returned, unless the text says a session is waiting. To keep each session's view clear, the outputs are shown per session in tabs, with psql -e echoing each statement above its result.

CREATE TABLE stock (
  stock_code text PRIMARY KEY,
  description text,
  on_hand int NOT NULL CHECK (on_hand >= 0),
  version int NOT NULL DEFAULT 1
);
INSERT INTO stock (stock_code, description, on_hand) VALUES
  ('85123A', 'WHITE HANGING HEART T-LIGHT HOLDER', 100),
  ('22423',  'REGENCY CAKESTAND 3 TIER', 40);

CREATE TABLE shifts (
  shift_date date,
  staff text,
  on_call boolean NOT NULL,
  PRIMARY KEY (shift_date, staff)
);
INSERT INTO shifts VALUES
  ('2026-10-03', 'Asha', true),
  ('2026-10-03', 'Ravi', true);

CREATE TABLE jobs (
  job_id int PRIMARY KEY,
  invoice_no text,
  status text NOT NULL DEFAULT 'queued',
  worker text
);
INSERT INTO jobs (job_id, invoice_no) VALUES
  (1, '536365'), (2, '536366'), (3, '536367'),
  (4, '536368'), (5, '536369'), (6, '536370');

CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 6

UPDATE stock SET on_hand = 100, version = 1 WHERE stock_code = '85123A';
UPDATE stock SET on_hand = 40, version = 1 WHERE stock_code = '22423';
UPDATE shifts SET on_call = true;
UPDATE jobs SET status = 'queued', worker = NULL;

#Step 1: a lost update

Two orders for product 85123A arrive at the same moment: one for 5, one for 3. The application reads the stock, subtracts in code, and writes the new number back. \gset stores the value read into a psql variable :on_hand, playing the part of the application.

-- both sessions
BEGIN;
SELECT on_hand FROM stock WHERE stock_code = '85123A';
SELECT on_hand FROM stock WHERE stock_code = '85123A' \gset

-- Session A
UPDATE stock SET on_hand = :on_hand - 5 WHERE stock_code = '85123A';
COMMIT;

-- Session B
UPDATE stock SET on_hand = :on_hand - 3 WHERE stock_code = '85123A';
COMMIT;
SELECT on_hand FROM stock WHERE stock_code = '85123A';

BEGIN;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
     100
(1 row)
 
SELECT on_hand FROM stock WHERE stock_code = '85123A' 
UPDATE stock SET on_hand = 100 - 5 WHERE stock_code = '85123A';
UPDATE 1
COMMIT;
COMMIT

BEGIN;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
     100
(1 row)
 
SELECT on_hand FROM stock WHERE stock_code = '85123A' 
UPDATE stock SET on_hand = 100 - 3 WHERE stock_code = '85123A';
UPDATE 1
COMMIT;
COMMIT
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
      97
(1 row)

8 units left the warehouse, but the table says 97 instead of 92. Session B's write was computed from a value that was already out of date, and it overwrote Session A's change. No error, no warning: the default READ COMMITTED level allows this, because each statement on its own read committed data. It is one of the most common concurrency bugs in real applications.

#Step 2: fix it with an atomic update or FOR UPDATE

The simplest fix is to let the database do the arithmetic, so the update reads the current value itself:

-- Session A
BEGIN;
UPDATE stock SET on_hand = on_hand - 5 WHERE stock_code = '85123A';

-- Session B, while A is still open
BEGIN;
SELECT on_hand FROM stock WHERE stock_code = '85123A';
UPDATE stock SET on_hand = on_hand - 3 WHERE stock_code = '85123A';  -- waits for A

-- Session A
COMMIT;

-- Session B
COMMIT;
SELECT on_hand FROM stock WHERE stock_code = '85123A';

BEGIN;
BEGIN
UPDATE stock SET on_hand = on_hand - 5 WHERE stock_code = '85123A';
UPDATE 1
COMMIT;
COMMIT

BEGIN;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
     100
(1 row)
 
UPDATE stock SET on_hand = on_hand - 3 WHERE stock_code = '85123A';
UPDATE 1
COMMIT;
COMMIT
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
      92
(1 row)

92, correct. Two details in Session B show MVCC at work. Its SELECT returned 100 instantly while A had an uncommitted update on that row: no dirty read, and the reader did not wait for the writer. Its UPDATE, a second writer on the same row, did wait until A committed, then re-read the row and applied - 3 to 95. The CHECK (on_hand >= 0) constraint adds a safety net: an update that would oversell fails instead of going negative.

When the application must look at the value before deciding (is there enough stock? what discount applies?), lock the row while reading with SELECT ... FOR UPDATE:

-- Session A
BEGIN;
SELECT on_hand FROM stock WHERE stock_code = '85123A' FOR UPDATE;
SELECT on_hand FROM stock WHERE stock_code = '85123A' \gset

-- Session B, with \timing on
BEGIN;
SELECT on_hand FROM stock WHERE stock_code = '85123A' FOR UPDATE;  -- waits for A

-- Session A, about two seconds later
UPDATE stock SET on_hand = :on_hand - 5 WHERE stock_code = '85123A';
COMMIT;

-- Session B
SELECT on_hand FROM stock WHERE stock_code = '85123A' \gset
UPDATE stock SET on_hand = :on_hand - 3 WHERE stock_code = '85123A';
COMMIT;
SELECT on_hand FROM stock WHERE stock_code = '85123A';

BEGIN;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '85123A' FOR UPDATE;
 on_hand 
---------
     100
(1 row)
 
SELECT on_hand FROM stock WHERE stock_code = '85123A' 
UPDATE stock SET on_hand = 100 - 5 WHERE stock_code = '85123A';
UPDATE 1
COMMIT;
COMMIT

Timing is on.
BEGIN;
BEGIN
Time: 0.446 ms
SELECT on_hand FROM stock WHERE stock_code = '85123A' FOR UPDATE;
 on_hand 
---------
      95
(1 row)
 
Time: 2302.622 ms (00:02.303)
SELECT on_hand FROM stock WHERE stock_code = '85123A' 
Time: 0.952 ms
UPDATE stock SET on_hand = 95 - 3 WHERE stock_code = '85123A';
UPDATE 1
Time: 2.708 ms
COMMIT;
COMMIT
Time: 4.629 ms
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
      92
(1 row)
 
Time: 0.523 ms

Session B's FOR UPDATE waited 2.3 seconds, as long as A held the lock, and then returned 95, the committed value, not the 100 it would have read without the lock. Keep transactions that hold row locks short: never wait for user input or an API call while holding one.

#Step 3: READ COMMITTED vs REPEATABLE READ

Session A reads the cake stand stock twice in one transaction while Session B updates it in between, first at the default level and then at REPEATABLE READ:

-- Session A
BEGIN;  -- READ COMMITTED, the default
SELECT on_hand FROM stock WHERE stock_code = '22423';
-- Session B
UPDATE stock SET on_hand = 30 WHERE stock_code = '22423';
-- Session A
SELECT on_hand FROM stock WHERE stock_code = '22423';
COMMIT;

-- reset to 40, then
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT on_hand FROM stock WHERE stock_code = '22423';
-- Session B
UPDATE stock SET on_hand = 30 WHERE stock_code = '22423';
-- Session A
SELECT on_hand FROM stock WHERE stock_code = '22423';
COMMIT;
SELECT on_hand FROM stock WHERE stock_code = '22423';

BEGIN;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '22423';
 on_hand 
---------
      40
(1 row)
 
SELECT on_hand FROM stock WHERE stock_code = '22423';
 on_hand 
---------
      30
(1 row)
 
COMMIT;
COMMIT
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '22423';
 on_hand 
---------
      40
(1 row)
 
SELECT on_hand FROM stock WHERE stock_code = '22423';
 on_hand 
---------
      40
(1 row)
 
COMMIT;
COMMIT
SELECT on_hand FROM stock WHERE stock_code = '22423';
 on_hand 
---------
      30
(1 row)

UPDATE stock SET on_hand = 30 WHERE stock_code = '22423';
UPDATE 1
UPDATE stock SET on_hand = 30 WHERE stock_code = '22423';
UPDATE 1

Under READ COMMITTED the second read saw B's change (40, then 30): a non-repeatable read. Under REPEATABLE READ both reads returned 40, because the whole transaction reads from one snapshot taken at its first statement; the new value appeared only after COMMIT. B's updates were never blocked by A's reads. Use REPEATABLE READ for reports and exports that run several queries and must agree with each other, for example a revenue total and the breakdown that should sum to it.

#Step 4: REPEATABLE READ refuses a lost update

Repeat step 2's atomic update, but with both sessions at REPEATABLE READ:

-- both sessions
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT on_hand FROM stock WHERE stock_code = '85123A';
-- Session A
UPDATE stock SET on_hand = on_hand - 5 WHERE stock_code = '85123A';
-- Session B
UPDATE stock SET on_hand = on_hand - 3 WHERE stock_code = '85123A';  -- waits for A
-- Session A
COMMIT;
-- Session B
ROLLBACK;
SELECT on_hand FROM stock WHERE stock_code = '85123A';

BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
     100
(1 row)
 
UPDATE stock SET on_hand = on_hand - 5 WHERE stock_code = '85123A';
UPDATE 1
COMMIT;
COMMIT

BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
     100
(1 row)
 
UPDATE stock SET on_hand = on_hand - 3 WHERE stock_code = '85123A';
ERROR:  could not serialize access due to concurrent update
ROLLBACK;
ROLLBACK
SELECT on_hand FROM stock WHERE stock_code = '85123A';
 on_hand 
---------
      95
(1 row)

B's snapshot still says 100, and applying - 3 to a row someone else changed after that snapshot would contradict it. PostgreSQL refuses with could not serialize access due to concurrent update (SQLSTATE 40001). Nothing is lost, but the order for 3 was not applied: the application must catch 40001, roll back and retry the whole transaction, which will then see 95 and write 92. At REPEATABLE READ and SERIALIZABLE, retry logic is part of the contract, not an edge case. MySQL's REPEATABLE READ behaves differently: the UPDATE reads the latest committed row and succeeds, so test your own database rather than assuming the level name means the same thing everywhere.

#Step 5: write skew and SERIALIZABLE

The rule: at least one person must stay on call on 3 October. Asha and Ravi both are. Each opens the app, checks that someone else is on call, and goes off call:

-- both sessions
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) AS on_call FROM shifts WHERE shift_date = '2026-10-03' AND on_call;
-- Session A
UPDATE shifts SET on_call = false WHERE shift_date = '2026-10-03' AND staff = 'Asha';
-- Session B
UPDATE shifts SET on_call = false WHERE shift_date = '2026-10-03' AND staff = 'Ravi';
-- Session A, then Session B
COMMIT;
-- Session B
SELECT staff, on_call FROM shifts WHERE shift_date = '2026-10-03' ORDER BY staff;

BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
SELECT count(*) AS on_call FROM shifts WHERE shift_date = '2026-10-03' AND on_call;
 on_call 
---------
       2
(1 row)
 
UPDATE shifts SET on_call = false WHERE shift_date = '2026-10-03' AND staff = 'Asha';
UPDATE 1
COMMIT;
COMMIT

BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
SELECT count(*) AS on_call FROM shifts WHERE shift_date = '2026-10-03' AND on_call;
 on_call 
---------
       2
(1 row)
 
UPDATE shifts SET on_call = false WHERE shift_date = '2026-10-03' AND staff = 'Ravi';
UPDATE 1
COMMIT;
COMMIT
SELECT staff, on_call FROM shifts WHERE shift_date = '2026-10-03' ORDER BY staff;
 staff | on_call 
-------+---------
 Asha  | f
 Ravi  | f
(2 rows)

Nobody is on call. Each transaction saw 2, each updated a different row, so no row lock conflicted and REPEATABLE READ allowed both. That is write skew. Reset and run exactly the same steps with BEGIN ISOLATION LEVEL SERIALIZABLE;:

BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
SELECT count(*) AS on_call FROM shifts WHERE shift_date = '2026-10-03' AND on_call;
 on_call 
---------
       2
(1 row)
 
UPDATE shifts SET on_call = false WHERE shift_date = '2026-10-03' AND staff = 'Asha';
UPDATE 1
COMMIT;
COMMIT

BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
SELECT count(*) AS on_call FROM shifts WHERE shift_date = '2026-10-03' AND on_call;
 on_call 
---------
       2
(1 row)
 
UPDATE shifts SET on_call = false WHERE shift_date = '2026-10-03' AND staff = 'Ravi';
UPDATE 1
COMMIT;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
SELECT staff, on_call FROM shifts WHERE shift_date = '2026-10-03' ORDER BY staff;
 staff | on_call 
-------+---------
 Asha  | f
 Ravi  | t
(2 rows)

PostgreSQL's SERIALIZABLE (serializable snapshot isolation) tracks what each transaction read. It noticed that B read rows A changed and A read rows B changed, so no one-at-a-time order could produce both results, and it cancelled B at COMMIT. Ravi stays on call. The HINT says what to do: retry. The retry sees a count of 1 (only Ravi), so the application refuses to take Ravi off call.

Alternatives when you cannot run SERIALIZABLE: lock the rows the rule depends on (SELECT ... FROM shifts WHERE shift_date = '2026-10-03' FOR UPDATE before deciding), or turn the rule into a constraint the database enforces.

#Step 6: a deadlock

Session A updates the heart holder, then the cake stand. Session B updates them in the opposite order:

-- Session A
BEGIN;
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '85123A';
-- Session B
BEGIN;
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '22423';
-- Session A: waits for B's lock on 22423
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '22423';
-- Session B: waits for A's lock on 85123A, a cycle
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '85123A';
-- Session A
COMMIT;
-- Session B
ROLLBACK;

BEGIN;
BEGIN
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '85123A';
UPDATE 1
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '22423';
ERROR:  deadlock detected
DETAIL:  Process 9698 waits for ShareLock on transaction 1314; blocked by process 9700.
Process 9700 waits for ShareLock on transaction 1313; blocked by process 9698.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,34) in relation "stock"
COMMIT;
ROLLBACK

BEGIN;
BEGIN
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '22423';
UPDATE 1
UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '85123A';
UPDATE 1
ROLLBACK;
ROLLBACK

After one second of waiting (deadlock_timeout), PostgreSQL found the cycle and chose Session A as the victim: its statement failed and its whole transaction was aborted, which is why A's COMMIT answered ROLLBACK. Session B's waiting update then went through. The server log names both statements, which is what you look at in production:

2026-10-02 12:00:54.014 UTC [9698] ERROR:  deadlock detected
2026-10-02 12:00:54.014 UTC [9698] DETAIL:  Process 9698 waits for ShareLock on transaction 1314; blocked by process 9700.
	Process 9700 waits for ShareLock on transaction 1313; blocked by process 9698.
	Process 9698: UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '22423';
	Process 9700: UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '85123A';
2026-10-02 12:00:54.014 UTC [9698] HINT:  See server log for query details.
2026-10-02 12:00:54.014 UTC [9698] CONTEXT:  while updating tuple (0,34) in relation "stock"
2026-10-02 12:00:54.014 UTC [9698] STATEMENT:  UPDATE stock SET on_hand = on_hand - 1 WHERE stock_code = '22423';

The fix is a consistent lock order: every code path that touches several rows locks them in the same order, for example sorted by stock_code. An order with lines for 85123A and 22423 then always locks 22423 first, whoever runs it, and the cycle cannot form. A single UPDATE ... WHERE stock_code IN (...) does not guarantee an order by itself; when it matters, lock first with SELECT ... ORDER BY stock_code FOR UPDATE. Deadlocks that remain should be retried like serialization failures (SQLSTATE 40P01).

#Step 7: a job queue with SKIP LOCKED

Six invoices wait to be processed. Two workers each claim the next job:

-- each worker, inside a transaction
BEGIN;
UPDATE jobs SET status = 'running', worker = 'worker-a'
WHERE job_id = (
  SELECT job_id FROM jobs
  WHERE status = 'queued'
  ORDER BY job_id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING job_id, invoice_no;
-- ... process the invoice ...
COMMIT;

BEGIN;
BEGIN
UPDATE jobs SET status = 'running', worker = 'worker-a'
WHERE job_id = (
  SELECT job_id FROM jobs
  WHERE status = 'queued'
  ORDER BY job_id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING job_id, invoice_no;
 job_id | invoice_no 
--------+------------
      1 | 536365
(1 row)
 
UPDATE 1
COMMIT;
COMMIT

BEGIN;
BEGIN
UPDATE jobs SET status = 'running', worker = 'worker-b'
WHERE job_id = (
  SELECT job_id FROM jobs
  WHERE status = 'queued'
  ORDER BY job_id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING job_id, invoice_no;
 job_id | invoice_no 
--------+------------
      2 | 536366
(1 row)
 
UPDATE 1
COMMIT;
COMMIT
SELECT job_id, status, worker FROM jobs ORDER BY job_id;
 job_id | status  |  worker  
--------+---------+----------
      1 | running | worker-a
      2 | running | worker-b
      3 | queued  | 
      4 | queued  | 
      5 | queued  | 
      6 | queued  | 
(6 rows)

Worker B ran while worker A still held job 1. Without SKIP LOCKED, B would have waited for A's job 1, then found it no longer queued. With it, B skipped the locked row and took job 2 immediately. Neither job was processed twice. If a worker crashes, its transaction rolls back and the job returns to queued. This pattern (supported by PostgreSQL 9.5+, MySQL 8.0+ and Oracle; SQL Server uses WITH (READPAST)) is behind many SQL-based job queues.

#Step 8: optimistic locking with a version column

A stock editor in a web app loads the record, the user edits for a minute, then saves. Holding a row lock for that minute is not an option. Instead, each save checks the version it loaded:

-- both users load the record
SELECT on_hand, version FROM stock WHERE stock_code = '85123A';
-- user A saves 95
UPDATE stock SET on_hand = 95, version = version + 1
WHERE stock_code = '85123A' AND version = 1;
-- user B saves 97, based on the same version 1
UPDATE stock SET on_hand = 97, version = version + 1
WHERE stock_code = '85123A' AND version = 1;
SELECT on_hand, version FROM stock WHERE stock_code = '85123A';

SELECT on_hand, version FROM stock WHERE stock_code = '85123A';
 on_hand | version 
---------+---------
     100 |       1
(1 row)
 
UPDATE stock SET on_hand = 95, version = version + 1
WHERE stock_code = '85123A' AND version = 1;
UPDATE 1

SELECT on_hand, version FROM stock WHERE stock_code = '85123A';
 on_hand | version 
---------+---------
     100 |       1
(1 row)
 
UPDATE stock SET on_hand = 97, version = version + 1
WHERE stock_code = '85123A' AND version = 1;
UPDATE 0
SELECT on_hand, version FROM stock WHERE stock_code = '85123A';
 on_hand | version 
---------+---------
      95 |       2
(1 row)

UPDATE 0 is the conflict signal: version 1 no longer exists, so B's save matched nothing. The application checks the affected row count and tells user B "this record changed while you were editing; reload". Many ORMs build this in (JPA @Version, Django and Rails optimistic locking options, Entity Framework concurrency tokens). An updated_at timestamp can play the same role, though an integer version is more reliable.

#Step 9: how a migration blocks everything

Session A runs a query inside a transaction and leaves it open (a forgotten BEGIN in a SQL client, or an application that does slow work mid-transaction). Session B runs a migration. Session C is a normal application read:

-- Session A
BEGIN;
SELECT count(*) FROM stock;
-- Session B: needs an exclusive lock, waits for A
ALTER TABLE stock ADD COLUMN reorder_level int;
-- Session C, with \timing on: a simple read
SELECT on_hand FROM stock WHERE stock_code = '22423';
-- a fourth session looks at who waits for whom
SELECT a.application_name AS session, a.state, a.wait_event_type, a.wait_event,
       (SELECT string_agg(b.application_name, ', ')
        FROM pg_stat_activity AS b
        WHERE b.pid = ANY (pg_blocking_pids(a.pid))) AS blocked_by,
       left(a.query, 40) AS query
FROM pg_stat_activity AS a
WHERE a.application_name LIKE 'session%'
ORDER BY a.application_name;
-- Session A, two seconds later
COMMIT;

Each session first ran SET application_name = 'session_a'; (and _b, _c) so they are easy to tell apart.

  session  |        state        | wait_event_type | wait_event | blocked_by |                  query                   
-----------+---------------------+-----------------+------------+------------+------------------------------------------
 session_a | idle in transaction | Client          | ClientRead |            | SELECT count(*) FROM stock;
 session_b | active              | Lock            | relation   | session_a  | ALTER TABLE stock ADD COLUMN reorder_lev
 session_c | active              | Lock            | relation   | session_b  | SELECT on_hand FROM stock WHERE stock_co
(3 rows)

SET application_name = 'session_c';
SET
Timing is on.
SELECT on_hand FROM stock WHERE stock_code = '22423';
 on_hand 
---------
      40
(1 row)
 
Time: 2504.083 ms (00:02.504)

Session C's read does not conflict with A at all, but it queued behind B's waiting ALTER TABLE, which needs an exclusive lock. One idle transaction plus one migration froze every query on the table for 2.5 seconds; in production that is every request until someone notices. idle in transaction in pg_stat_activity is the red flag.

The defence is a lock timeout on migrations, so they give up instead of queueing everyone behind them, and are retried later:

-- Session A holds the same open transaction; Session B, with \timing on
SET lock_timeout = '2s';
ALTER TABLE stock DROP COLUMN reorder_level;
-- after Session A commits, try again
ALTER TABLE stock DROP COLUMN reorder_level;

Timing is on.
SET lock_timeout = '2s';
SET
Time: 0.557 ms
ALTER TABLE stock DROP COLUMN reorder_level;
ERROR:  canceling statement due to lock timeout
Time: 2001.031 ms (00:02.001)
ALTER TABLE stock DROP COLUMN reorder_level;
ALTER TABLE
Time: 2.024 ms

Also set idle_in_transaction_session_timeout so forgotten transactions are closed for you, and keep migrations that rewrite a whole table (changing a column type, for example) for quiet hours.

#Common mistakes

• Read, compute in the application, write back. The lost update from step 1. Use SET x = x - n, FOR UPDATE, or a version check.

• Assuming the default level protects you. READ COMMITTED allows lost updates, non-repeatable reads and write skew.

• Using REPEATABLE READ or SERIALIZABLE without retry logic. Error 40001 (and deadlock 40P01) is expected behaviour; catch it and rerun the whole transaction, a few times with a short back-off.

• Holding locks during slow work. Calling an API, sending email or waiting for a user while a transaction holds row locks makes everyone else wait.

• Locking rows in different orders in different code paths. The recipe for deadlocks.

• Leaving transactions open in a SQL client. idle in transaction sessions hold locks and block VACUUM from cleaning old row versions.

• Running ALTER TABLE on a busy table without lock_timeout. It queues every query behind it.

• Expecting the same behaviour from the same level name on every database. MySQL REPEATABLE READ, PostgreSQL REPEATABLE READ and SQL Server REPEATABLE READ differ; test the case you care about.

#Interview angle

#Practice task

Use the same tables and two psql sessions.

1. Overselling. The cake stand (22423) has 40 in stock. Session A wants 30 and Session B wants 15. Write a single UPDATE per order that both decreases the stock and refuses to oversell, without relying on the CHECK constraint. Run both concurrently (B while A is still open) and explain what B sees.

2. Spot the blocker. Leave a transaction open in Session A after updating 22423, start an update of the same row in Session B, and from a third session list which session is blocked and by whom.

-- Session A
BEGIN;
UPDATE stock SET on_hand = on_hand - 30
WHERE stock_code = '22423' AND on_hand >= 30
RETURNING on_hand;

-- Session B, while A is open: waits
BEGIN;
UPDATE stock SET on_hand = on_hand - 15
WHERE stock_code = '22423' AND on_hand >= 15
RETURNING on_hand;

-- Session A
COMMIT;
-- Session B
COMMIT;

BEGIN;
BEGIN
UPDATE stock SET on_hand = on_hand - 30
WHERE stock_code = '22423' AND on_hand >= 30
RETURNING on_hand;
 on_hand 
---------
      10
(1 row)
 
UPDATE 1
COMMIT;
COMMIT

BEGIN;
BEGIN
UPDATE stock SET on_hand = on_hand - 15
WHERE stock_code = '22423' AND on_hand >= 15
RETURNING on_hand;
 on_hand 
---------
(0 rows)
 
UPDATE 0
COMMIT;
COMMIT

SELECT pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 50) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY backend_start;

 pid  |        state        | wait_event_type |  wait_event   | blocked_by |                       query                        
------+---------------------+-----------------+---------------+------------+----------------------------------------------------
 9916 | idle in transaction | Client          | ClientRead    | {}         | UPDATE stock SET on_hand = on_hand - 30 WHERE stoc
 9918 | active              | Lock            | transactionid | {9916}     | UPDATE stock SET on_hand = on_hand - 15 WHERE stoc
(2 rows)

#Further reading

• PostgreSQL: transaction isolation and explicit locking.

• PostgreSQL: SELECT ... FOR UPDATE and SKIP LOCKED.

• MySQL: InnoDB transaction isolation levels.

• SQL Server: SET TRANSACTION ISOLATION LEVEL.

• Martin Kleppmann, Designing Data-Intensive Applications, chapter 7 (Transactions), for weak isolation levels and write skew in depth.

• Earlier lessons: SQL transactions and ACID: COMMIT and ROLLBACK, SQL UPSERT and MERGE: insert or update rows and SQL query optimization: rewrite slow queries.