# Transaction ## What it is A transaction is a group of changes that a database applies as one unit: either all of them take effect, or none of them does. People write database transaction when they need to keep it apart from a payment, which shares the word. The PostgreSQL tutorial describes a transaction as something that "bundles multiple steps into a single, all-or-nothing operation", and nothing in between is visible to anyone else. In PostgreSQL a transaction opens with `BEGIN` and ends with `COMMIT`, which makes its changes permanent and visible to other sessions, or with `ROLLBACK`, which throws them away. A statement sent on its own still runs inside one: PostgreSQL wraps it in an implicit `BEGIN` and, if it succeeds, an implicit `COMMIT`. A long transaction can set a `SAVEPOINT` and later return to it with `ROLLBACK TO`, discarding the work done since while keeping everything before it. The guarantees a transaction gives are summed up as ACID. Atomicity means a transaction completes as one unit or not at all, and no half-finished result survives a crash. Consistency means the constraints declared on the data hold once it commits, and a transaction that would leave one broken is rolled back instead. Isolation means its changes stay invisible to concurrent transactions until it commits. Durability means a committed change survives a crash of the server. Isolation comes in levels, and the SQL standard defines four: Read Uncommitted, Read Committed, Repeatable Read and Serializable. PostgreSQL starts every transaction at Read Committed unless told otherwise, and its Read Uncommitted behaves exactly like Read Committed. At Read Committed each statement sees the data committed before that statement began, so two queries in the same transaction may see different numbers. At Repeatable Read the whole transaction works from one snapshot, taken when its first statement after `BEGIN` runs. Serializable goes further and makes concurrent transactions produce the same result as if they had run one after another. ## Why it matters Almost every action worth recording touches more than one row. An order is a row in one table, a decrement in another and an entry in a ledger. Without a transaction, a crash, a timeout or a failed constraint between the second and the third statement leaves an order whose stock was never taken, or stock taken for an order that does not exist. Someone then finds it in a report and repairs it by hand. With a transaction that half state never exists for anyone to see, and the database, not the application, is what guarantees it. The second reason is concurrency. Two requests that arrive together run side by side, and the isolation level decides what each one sees while locks decide which one waits. This is where teams are most often surprised: wrapping code in `BEGIN` and `COMMIT` does not on its own stop two requests from reading the same balance and both spending it. At Read Committed each statement reads what was committed when it started, so a check made in one statement and a write made in the next can be separated by another session's commit. The check has to lock the row first or become part of the write. Transactions also have a cost, and it is paid in time. Row locks taken by `UPDATE`, `DELETE` and `SELECT ... FOR UPDATE` are held until the transaction ends, and a session that wants to change or lock the same row waits for as long as that takes. An open transaction also stops `VACUUM` from removing old row versions it might still need to see, so a transaction left open for hours lets tables bloat. The design question is never only whether to use a transaction. It is where its edges go and how little happens between them. Finally, a transaction covers one database and nothing else. A call to a payment provider, an email or a message on a queue happens outside it, and a rollback cannot take any of them back. Deciding what sits inside the boundary, and how the outside world hears about a commit, is most of the work of designing one. ## What it looks like in practice A member of a loyalty scheme spends 250 points on a cinema voucher. Three things have to happen together: the wallet loses the points, the ledger records why, and the redemption is created. Written as one transaction it looks like this: ```sql BEGIN; UPDATE wallet SET points = points - 250 WHERE member_id = 42 AND points >= 250 RETURNING points; -- no row back: not enough points, so ROLLBACK and stop here INSERT INTO points_entry (member_id, delta, reason) VALUES (42, -250, 'redeemed: cinema voucher'); INSERT INTO redemption (member_id, reward_id) VALUES (42, 7); COMMIT; ``` The `UPDATE` takes a row lock on member 42's wallet. Until `COMMIT`, any other session that reads the wallet still sees the old balance, because row locks do not block readers and uncommitted changes are invisible. A second redemption for the same member, sent from another tab a moment later, reaches its own `UPDATE` and waits. When the first transaction commits, PostgreSQL at Read Committed evaluates the second one's `WHERE` clause again against the row as it now stands. If only 100 points are left, the condition fails, no row comes back, and the second request rolls back without having written anything. If instead the last `INSERT` fails, say because reward 7 has been withdrawn and a foreign key refuses it, the client rolls back and the points are exactly where they were. This is the shape of the [loyalty wallet with leagues and badges](/portfolio/built-a-loyalty-wallet-leagues-and-badges/). Every change to a wallet there takes a row lock on it before reading it, so a second request waits for the first to finish instead of racing it, and because the wallet lives in the same database as everything else, a balance and what it paid for are committed together or not at all. The page is open about the price: row locking is the slower answer, and members wait their turn rather than spend the same points twice. The note on [selling the last ticket exactly once](/notes/selling-limited-stock-without-overselling/) puts the same tool under a flash sale. Its fix makes the stock check and the decrement one conditional `UPDATE`, because a lock held while the application thinks between a `SELECT` and an `UPDATE` makes the next buyer wait for every millisecond of it. First on its list for the rest of the work is keeping everything external out of the transaction: a payment call, an HTTP request or a published message belongs before it or after it. ## Where it goes wrong **A session sits idle in a transaction and everything behind it waits.** An application opens a transaction, updates a row, and then waits for a slow API, for a user's next click, or for a worker that crashed without ever sending `COMMIT`. Its locks stay, and PostgreSQL makes a session that wants a conflicting lock wait indefinitely unless it detects a deadlock. The documentation calls holding a transaction open while waiting for user input a bad idea. `pg_stat_activity` shows these sessions with the state `idle in transaction`, and `xact_start` says when the transaction began: ```sql SELECT pid, now() - xact_start AS open_for, state, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' ORDER BY xact_start; ``` Move the slow work out of the transaction, and give the server a limit so a forgotten one cannot hold locks for hours. `idle_in_transaction_session_timeout` ends any session left idle inside an open transaction for longer than its value. Its default is 0, which disables it, so it has to be set, for example for the application's role with `ALTER ROLE app SET idle_in_transaction_session_timeout = '30s';`. **Two requests both spend the same balance.** The code reads a value in one statement, decides in the application, and writes the result in another. Two sessions read the same number, both pass the check, and both write, so one change silently overwrites the other. A surrounding `BEGIN` does not prevent it at Read Committed. Make the check part of the write with a condition in the `UPDATE`, as in the example above, or lock the row before reading it with `SELECT ... FOR UPDATE`, which makes the second session wait for the first. **Errors appear under load at Repeatable Read or Serializable.** At these levels PostgreSQL refuses a transaction that would break the level's promise and reports a serialization failure, SQLSTATE `40001`, with messages such as `could not serialize access due to concurrent update`. This is the design working, not a bug, and the documentation states that applications using these levels must be prepared to retry. Catch `40001` and run the whole transaction again from `BEGIN`, including its reads, since the decision it made was based on data that has since changed. Retrying only the statement that failed repeats the mistake. **Two transactions deadlock.** One locks order 1 and then order 2, another locks order 2 and then order 1, and each waits for the other. PostgreSQL detects the cycle and aborts one of the transactions so the other can finish, and which one it picks is hard to predict. The documented defence is to have every code path acquire locks in the same order, for example by updating rows in ascending order of their primary key, and to retry a transaction that was aborted for a deadlock. **An email goes out for an order that was rolled back.** The application sends a confirmation or publishes an event from inside the transaction, and then a later statement fails. The rollback removes the order, but nothing can unsend the email. Record the message in an outbox table in the same transaction as the change it describes, and let a separate process send it once the commit has happened. The message then exists exactly when the order does. ## Further reading - [Transactions](https://www.postgresql.org/docs/current/tutorial-transactions.html) — the PostgreSQL tutorial chapter: all-or-nothing behaviour, implicit transactions and savepoints, shown on a bank transfer. - [Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html) — the four standard levels, what each one sees in PostgreSQL, and the serialization failures that call for a retry. - [Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html) — row and table locks, how long they are held, and how deadlocks are detected and avoided. - [Client Connection Defaults](https://www.postgresql.org/docs/current/runtime-config-client.html) — `default_transaction_isolation`, `idle_in_transaction_session_timeout`, `lock_timeout` and the other limits on a session. - [Routine Vacuuming](https://www.postgresql.org/docs/current/routine-vacuuming.html) — why old row versions stay while any transaction might still see them, and what long open transactions do to cleanup.