# Transactions & isolation > Group changes atomically, understand MVCC and PostgreSQL's isolation levels, and prevent lost updates, write skew and deadlocks. SQL & Databases roadmap · Design & performance · 20 min read · https://levelcode.org/roadmaps/sql/transactions 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: ```sql 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; ``` ```text 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: ```sql 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 ...`. | Level in PostgreSQL | Dirty read | Non-repeatable read | Phantom read | Lost update | Write skew | | --- | --- | --- | --- | --- | --- | | Read committed (default) | no | possible | possible | possible | possible | | Repeatable read | no | no | no | no — raises an error | possible | | Serializable | no | no | no | no — raises an error | no — raises an error | 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: ```text 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): ```text 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: ```sequence title: A lost update under read committed participants: Session A, Database, Session B Session A -> Database: BEGIN; SELECT stock > Both sessions use the default isolation level, read committed. Database --> Session A: stock = 3 Session B -> Database: BEGIN; SELECT stock > A plain SELECT takes no row lock, so B reads the same committed value at the same time. Database --> Session B: stock = 3 Session A -> Database: UPDATE … SET stock = 2 > A's application computed 3 - 1. The UPDATE locks book 6's row until A's transaction ends. Database --> Session A: UPDATE 1 Session B -> Database: UPDATE … SET stock = 2 > B's application also computed 3 - 1, from a value that is about to be stale. note Database, Session B: B blocks on A's row lock > The row is locked by A's uncommitted UPDATE, so B's UPDATE waits instead of running. Session A -> Database: COMMIT > Committing releases A's row lock. The stored stock is now 2. Database --> Session B: UPDATE 1 > B's UPDATE now runs against A's committed row. Its WHERE still matches, and SET stock = 2 is a constant, so it writes 2 over 2. Session B -> Database: COMMIT note Session A, Session B: Two copies sold, stock is 2: one sale lost > No error, no warning: the database did exactly what each statement asked. The bug is the read-compute-write in the application. ``` 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: ```sql 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: ```text 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: ```sequence title: SELECT … FOR UPDATE prevents the lost update participants: Session A, Database, Session B Session A -> Database: BEGIN; SELECT … FOR UPDATE > A reads book 6 and locks its row in the same step. Database --> Session A: stock = 3 Session B -> Database: BEGIN; SELECT … FOR UPDATE > B asks for the same row lock. note Database, Session B: B blocks on A's row lock > B's SELECT waits, so B has not read anything yet. It cannot compute from a stale value. Session A -> Database: UPDATE … SET stock = 2 Database --> Session A: UPDATE 1 Session A -> Database: COMMIT > Committing releases A's lock. Database --> Session B: stock = 2 > After waiting for a row lock, read committed returns the latest committed version of the row, so B sees A's change. Session B -> Database: UPDATE … SET stock = 1 Database --> Session B: UPDATE 1 Session B -> Database: COMMIT note Session A, Session B: Two copies sold, stock is 1: correct > Under REPEATABLE READ without FOR UPDATE, B's UPDATE would instead fail with a serialization error once A commits, and B would retry. ``` 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: ```text 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: ```text 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; ``` ```text 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: ```text 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: ```text 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. > [!IMPORTANT] > Serialization failures (SQLSTATE `40001`) and deadlocks (SQLSTATE `40P01`) are normal, expected outcomes under concurrency, not bugs. Code that uses `REPEATABLE READ` or `SERIALIZABLE` must catch them and retry the **entire** transaction from `BEGIN`, a few times with a short back-off. Retrying only the failed statement is never correct. ### Choosing a level Read committed with atomic `UPDATE`s, 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: ```sql 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: ```text 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: ```text 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. > [!TIP] > Most concurrency bugs disappear if transactions are short and do only database work. Never hold a transaction open while calling a payment API, sending email or waiting for user input: do the slow work first or after, and keep the `BEGIN`…`COMMIT` window to milliseconds. ## 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? **Solution** Make the stock decrement conditional and do it **first**, so it both checks availability and takes the row lock: ```text 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; ``` When A commits, read committed re-checks B's `WHERE` clause against the newest version of the row. Stock is now 0, so `stock >= 1` is false and B's `UPDATE` affects **zero rows**. B's application must check the affected row count (or that `RETURNING` produced a row) and, when it is zero, roll back and tell the customer the book has sold out. Final stock is 0 and exactly one order exists. The data-modifying CTE inserts the order and its line in one statement, passing the generated `order_id` along. Decrementing first matters: otherwise both sessions would create orders before discovering the conflict. A `CHECK (stock >= 0)` constraint is a good safety net, turning any code path that forgets the condition into an error rather than an oversell. ## 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. ## Quiz 1. What is PostgreSQL's default transaction isolation level? A. Read uncommitted B. Read committed C. Repeatable read D. Serializable **Answer: B.** PostgreSQL defaults to read committed, where each statement sees a fresh snapshot of committed data. Requesting read uncommitted also gives read committed, because PostgreSQL never allows dirty reads. 2. Two REPEATABLE READ transactions both update the same row. The first commits. What happens to the second? A. Its update is silently applied on top of the first B. Its update is silently discarded C. It deadlocks and both transactions are aborted D. It fails with 'could not serialize access due to concurrent update' and must be retried from the start **Answer: D.** Repeatable read will not let a transaction modify a row version newer than its snapshot. Instead of losing an update, it raises a serialization error; the application should roll back and rerun the whole transaction. 3. Two transactions each read the total stock of an author's books, see 2, and each decrement a different book to enforce 'keep at least one in stock'. Which isolation level prevents both from committing? A. Serializable B. Repeatable read C. Read committed D. None — this can only be prevented with a CHECK constraint **Answer: A.** This is write skew: the transactions update different rows, so repeatable read sees no write conflict. Serializable tracks read/write dependencies and aborts one of them. A CHECK constraint only sees a single row and cannot express a rule across rows. 4. Inside BEGIN, an INSERT fails with a foreign key error. You then run another valid INSERT. What happens? A. It succeeds, and COMMIT saves everything except the failed statement B. It succeeds only if you run SAVEPOINT first C. It is rejected with 'current transaction is aborted', and COMMIT will roll back D. PostgreSQL automatically retries the failed statement **Answer: C.** Any error puts a PostgreSQL transaction into an aborted state: further statements are refused and COMMIT performs a ROLLBACK. To continue after an error you must have set a savepoint before it and roll back to that savepoint.