Transactions & isolation

A bookstore checkout is several statements: create the order, add its lines, decrement stock. If the server crashes between the second and third, or two customers buy the last copy at the same moment, what happens? Transactions are the database's answer: they make a group of statements all-or-nothing, and control what concurrent sessions see of each other's work. This lesson covers ACID, MVCC, PostgreSQL's isolation levels and their anomalies, row locks, deadlocks and retries.

To follow along, reload the bookstore script and open two psql sessions, A and B. Example lines are prefixed with the session that runs them, in order.

#Transactions and ACID

A transaction starts with BEGIN and ends with COMMIT (make the changes permanent) or ROLLBACK (discard them). Outside an explicit transaction, PostgreSQL runs each statement in its own transaction that commits automatically — autocommit.

Transactions provide four guarantees, known as ACID:

• Atomicity. Every change in the transaction happens, or none does — never an order without its lines.
• Consistency. Every constraint holds when the transaction commits.
• Isolation. Concurrent transactions interfere only in ways the chosen isolation level allows. Most of this lesson is about this one.
• Durability. Once COMMIT returns, the change survives a crash: PostgreSQL flushes it to its write-ahead log (WAL) on disk before confirming.

#Errors abort the whole transaction

In PostgreSQL, any error inside a transaction block puts the transaction into an aborted state. Here is a checkout that references a book that does not exist:

BEGIN;
INSERT INTO orders (customer_id, ordered_on, status)
VALUES (6, '2025-04-01', 'paid') RETURNING order_id;               -- 11
INSERT INTO order_items (order_id, book_id, quantity, unit_price)
VALUES (11, 99, 1, 9.99);
INSERT INTO order_items (order_id, book_id, quantity, unit_price)
VALUES (11, 1, 1, 12.99);
COMMIT;

ERROR:  insert or update on table "order_items" violates foreign key constraint "order_items_book_id_fkey"
DETAIL:  Key (book_id)=(99) is not present in table "books".
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

After the first error every statement is refused, and COMMIT performs a ROLLBACK — note the reply. The order row is gone too: Farid still has no orders. Treat any error as "the whole transaction failed".

#Savepoints

Sometimes you want to recover from an error without losing everything. A savepoint marks a point you can roll back to:

BEGIN;
INSERT INTO orders (customer_id, ordered_on, status)
VALUES (6, '2025-04-01', 'paid') RETURNING order_id;               -- 12
SAVEPOINT before_items;
INSERT INTO order_items (order_id, book_id, quantity, unit_price)
VALUES (12, 99, 1, 9.99);                                           -- fails
ROLLBACK TO SAVEPOINT before_items;
INSERT INTO order_items (order_id, book_id, quantity, unit_price)
VALUES (12, 1, 1, 12.99);                                           -- succeeds
COMMIT;

Order 12 is committed with one valid line. (It is 12 because the rolled-back transaction consumed 11; sequences never go backwards.)

#MVCC: how PostgreSQL isolates transactions

PostgreSQL uses multiversion concurrency control. An UPDATE does not overwrite a row in place; it writes a new version and marks the old one as expired, recording which transaction did each. Every statement (or, at higher isolation levels, every transaction) works from a snapshot of which transactions had committed when it was taken, and sees only row versions created by those. Three consequences:

1. Readers never block writers, and writers never block readers. A long report keeps reading old versions while updates continue.
2. Writers block writers on the same row. If A has updated a row and not committed, B's update of that row waits.
3. Old versions must be cleaned up by VACUUM (autovacuum). A transaction left open for hours keeps its snapshot alive and stops cleanup, so tables bloat. Never leave a session idle in transaction.

#Isolation levels and anomalies

The SQL standard defines isolation levels by the anomalies — interference effects — they permit. Set the level per transaction with BEGIN ISOLATION LEVEL ....

PostgreSQL never allows dirty reads (seeing uncommitted changes); asking for READ UNCOMMITTED gives you read committed. Its repeatable read is stricter than the standard requires and also prevents phantoms. Let's see each anomaly happen. Each experiment starts with Going Postal (book 6) at 3 copies, so reset it before each one with UPDATE books SET stock = 3 WHERE book_id = 6;.

#Read committed: each statement sees a fresh snapshot

In read committed, every statement takes a new snapshot, so two identical reads in one transaction can disagree. Going Postal (book 6) has 3 copies:

A: BEGIN;
A: SELECT stock FROM books WHERE book_id = 6;                   -- 3
B: UPDATE books SET stock = stock - 1 WHERE book_id = 6;        -- autocommits
A: SELECT stock FROM books WHERE book_id = 6;                   -- 2  (changed!)
A: COMMIT;

That is a non-repeatable read. A phantom read is the same for sets of rows: rerunning SELECT count(*) FROM orders WHERE status = 'paid' returns a different count because someone inserted a matching row. For most web requests this is fine. It matters when several reads in one transaction must agree, such as a report whose totals and details should match.

#Lost updates

The dangerous read-committed anomaly is the lost update, and it usually comes from application code that reads a value, computes in the app, and writes the result back. Both sessions sell one copy of Going Postal (stock 3):

A: BEGIN;
B: BEGIN;
A: SELECT stock FROM books WHERE book_id = 6;                   -- 3
B: SELECT stock FROM books WHERE book_id = 6;                   -- 3
A: UPDATE books SET stock = 2 WHERE book_id = 6;                -- app computed 3 - 1
B: UPDATE books SET stock = 2 WHERE book_id = 6;                -- waits for A's row lock...
A: COMMIT;
                                                                -- ...B's update now runs
B: COMMIT;

Step through the same interleaving to see exactly where the second sale disappears:

Two copies sold, stock went from 3 to 2. One sale has been lost, and no error was raised. There are three standard fixes.

1. Let the database do the arithmetic. Write the change relative to the current value:

UPDATE books SET stock = stock - 1 WHERE book_id = 6 RETURNING stock;

When B's update waits behind A's and A commits, read committed re-reads the latest version of the row before applying B's change, so B computes from 2 and returns 1. This is the best fix whenever the change fits in one statement; add AND stock >= 1 or a CHECK (stock >= 0) constraint to prevent overselling too.

2. Lock the row when you read it. SELECT ... FOR UPDATE takes a row lock as it reads, so other transactions wanting to update or lock that row must wait:

A: BEGIN;
B: BEGIN;
A: SELECT stock FROM books WHERE book_id = 6 FOR UPDATE;        -- 3
B: SELECT stock FROM books WHERE book_id = 6 FOR UPDATE;        -- waits...
A: UPDATE books SET stock = 2 WHERE book_id = 6;
A: COMMIT;
                                                                -- ...B now reads 2
B: UPDATE books SET stock = 1 WHERE book_id = 6;
B: COMMIT;

Here is the same sale with the lock in place. B's read now waits instead of returning a value that is about to go stale:

Use this when application logic must run between the read and the write, and keep the transaction short — every other buyer of that book waits behind you.

3. Use repeatable read and retry on conflict — covered next.

#Repeatable read: one snapshot per transaction

In repeatable read, the snapshot is taken at the first statement of the transaction and used for every statement after it. The non-repeatable read from earlier disappears:

A: BEGIN ISOLATION LEVEL REPEATABLE READ;
A: SELECT stock FROM books WHERE book_id = 6;                   -- 3
B: UPDATE books SET stock = stock - 1 WHERE book_id = 6;        -- autocommits
A: SELECT stock FROM books WHERE book_id = 6;                   -- still 3
A: COMMIT;

That consistent view is ideal for reports, exports and backups (pg_dump uses it). And if A tries to modify a row that someone else changed after A's snapshot was taken, PostgreSQL refuses rather than silently lose an update:

A: BEGIN ISOLATION LEVEL REPEATABLE READ;
B: BEGIN ISOLATION LEVEL REPEATABLE READ;
A: SELECT stock FROM books WHERE book_id = 6;                   -- 3
B: SELECT stock FROM books WHERE book_id = 6;                   -- 3
A: UPDATE books SET stock = stock - 1 WHERE book_id = 6;
B: UPDATE books SET stock = stock - 1 WHERE book_id = 6;        -- waits for A...
A: COMMIT;

ERROR:  could not serialize access due to concurrent update

B's transaction is now aborted. The correct response is to roll back and run the whole transaction again, which will see A's committed change. Applications using repeatable read or serializable must be written to retry.

#Write skew and serializable

Repeatable read still allows one subtle anomaly. Suppose the shop's rule is "at least one Haruki Murakami book must stay in stock for the display table". Set both his books to one copy (UPDATE books SET stock = 1 WHERE author_id = 4;), then two clerks each sell a different one:

A: BEGIN ISOLATION LEVEL REPEATABLE READ;
B: BEGIN ISOLATION LEVEL REPEATABLE READ;
A: SELECT sum(stock) FROM books WHERE author_id = 4;            -- 2, so selling one is fine
B: SELECT sum(stock) FROM books WHERE author_id = 4;            -- 2, so selling one is fine
A: UPDATE books SET stock = stock - 1 WHERE book_id = 9;        -- Norwegian Wood
B: UPDATE books SET stock = stock - 1 WHERE book_id = 10;       -- Kafka on the Shore
A: COMMIT;
B: COMMIT;                                                      -- both succeed: 0 copies left

Each transaction checked the rule against its snapshot, and each would have been correct alone, but they updated different rows, so no write conflict was detected. This is write skew. No row constraint can catch it, because the rule spans rows.

SERIALIZABLE guarantees that the result of concurrent transactions is the same as if they had run one at a time in some order. PostgreSQL implements it with serializable snapshot isolation: it tracks what each transaction read and wrote, and aborts one when it detects a dangerous pattern. Reset the stock to 1 for both books and repeat with BEGIN ISOLATION LEVEL SERIALIZABLE; in both sessions. This time B's COMMIT fails:

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.

If B retries, its new snapshot sees only one copy left, and the rule check stops the sale.

#Choosing a level

Read committed with atomic UPDATEs, constraints and occasional FOR UPDATE is the right default for most application code. Use repeatable read for multi-statement reads that must be consistent, and serializable when correctness depends on rules spanning many rows that you cannot easily lock — accepting the retries.

#Locks and deadlocks

#Row and table locks

Besides FOR UPDATE, there are weaker row locks such as FOR SHARE and FOR KEY SHARE; the latter is taken when foreign keys are checked, so inserting an order locks its customer row against deletion until the transaction ends. Two modifiers change what happens on contention: NOWAIT raises an error instead of waiting, and SKIP LOCKED skips rows other transactions hold, which turns a table into a work queue for parallel workers:

BEGIN;
SELECT order_id
FROM orders
WHERE status = 'paid'
ORDER BY ordered_on
LIMIT 1
FOR UPDATE SKIP LOCKED;          -- worker A gets order 7, worker B gets order 8
-- ... pack the order ...
UPDATE orders SET status = 'shipped' WHERE order_id = 7;
COMMIT;

Statements also take table-level locks. Ordinary reads and writes take weak ones that do not conflict, but many ALTER TABLE forms need an ACCESS EXCLUSIVE lock, and while waiting for it they block everyone queued behind them. Run SET lock_timeout = '5s'; before migrations so a blocked ALTER gives up instead of stalling your application.

#Deadlocks

A deadlock happens when two transactions each hold a lock the other needs. A sells A Wizard of Earthsea then The Left Hand of Darkness; B sells them in the opposite order:

A: BEGIN;
B: BEGIN;
A: UPDATE books SET stock = stock - 1 WHERE book_id = 1;
B: UPDATE books SET stock = stock - 1 WHERE book_id = 2;
A: UPDATE books SET stock = stock - 1 WHERE book_id = 2;        -- waits for B
B: UPDATE books SET stock = stock - 1 WHERE book_id = 1;        -- waits for A: deadlock

Neither can ever proceed. After deadlock_timeout (one second by default), PostgreSQL detects the cycle and aborts one of them:

ERROR:  deadlock detected
DETAIL:  Process 14326 waits for ShareLock on transaction 1358; blocked by process 14327.
Process 14327 waits for ShareLock on transaction 1357; blocked by process 14326.
HINT:  See server log for query details.

The other transaction then continues. Prevent deadlocks by acquiring locks in a consistent order — for example, always updating an order's books in ascending book_id order — and retry the rare remaining ones like serialization failures.

#Practice

Two customers try to buy the last copy of Going Postal at the same moment. Set its stock to 1 (UPDATE books SET stock = 1 WHERE book_id = 6;). Write a checkout transaction for customer 5 that decrements stock, inserts an order and inserts its line, and that can never oversell, even under the default read committed level and without FOR UPDATE. Run it in session A and the same decrement in session B, interleaved so that B's decrement is issued before A commits. What does B see, and how should its application code react?

A: BEGIN;
B: BEGIN;
A: UPDATE books SET stock = stock - 1
   WHERE book_id = 6 AND stock >= 1
   RETURNING stock;                                             -- 0 (one row updated)
B: UPDATE books SET stock = stock - 1
   WHERE book_id = 6 AND stock >= 1
   RETURNING stock;                                             -- waits for A...
A: WITH new_order AS (
     INSERT INTO orders (customer_id, ordered_on, status)
     VALUES (5, '2025-04-01', 'paid')
     RETURNING order_id
   )
   INSERT INTO order_items (order_id, book_id, quantity, unit_price)
   SELECT new_order.order_id, b.book_id, 1, b.price
   FROM new_order, books AS b
   WHERE b.book_id = 6;
A: COMMIT;
                                                                -- ...B: UPDATE 0, no rows returned
B: ROLLBACK;

#Key takeaways

• A transaction is all-or-nothing: BEGIN … COMMIT/ROLLBACK. In PostgreSQL any error aborts the whole transaction; savepoints allow partial recovery.
• MVCC keeps multiple row versions so readers and writers do not block each other; writers of the same row do, and long transactions stop cleanup.
• Read committed (the default) takes a snapshot per statement; repeatable read takes one per transaction and raises an error on conflicting updates; serializable also prevents write skew.
• Prevent lost updates with atomic UPDATE ... SET x = x - 1, SELECT ... FOR UPDATE, or a higher isolation level plus retries.
• Serialization failures and deadlocks are expected: retry the whole transaction, take locks in a consistent order, and keep transactions short.