Transactions

ACID, savepoints, and the lost update that transactions alone do not prevent.

Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.

On this page
  1. ACID in one paragraph each
  2. Writing one
  3. Savepoints: undo part of a transaction
  4. When two transactions touch the same row
  5. Keep transactions short
  6. In MySQL
  7. Sources

A transaction groups statements so that they succeed or fail together. It is the promise that makes a bank transfer, an order with its line items, or a sign-up with its profile safe, even when two people do the same thing at the same moment or the server crashes halfway.

ACID in one paragraph each#

  • Atomicity. Either every statement in the transaction takes effect, or none does. A failure halfway, an error, or a ROLLBACK undoes the lot.
  • Consistency. The database moves from one valid state to another: constraints such as primary keys, foreign keys and CHECKs hold at the end of every transaction.
  • Isolation. Transactions running at the same time do not see each other's unfinished work, and, depending on the isolation level, are protected from some or all of the anomalies that concurrency causes.
  • Durability. Once COMMIT returns, the change survives a crash, because its write-ahead log records reached disk first.

Writing one#

text
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Without BEGIN, every statement is its own transaction (autocommit). In PostgreSQL, once a statement inside a transaction fails, the transaction is aborted: every later statement is refused until you ROLLBACK. That is deliberate, so that half of a unit of work can never be committed by accident.

Savepoints: undo part of a transaction#

A savepoint marks a point you can roll back to without abandoning the whole transaction. This runs on the sample database:

sql
savepoint before_cancel;
update orders set status = 'cancelled' where id = 10;
sql
select id, status from orders where id = 10;
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
idstatus
10cancelled
sql
rollback to savepoint before_cancel;
sql
select id, status from orders where id = 10;
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
idstatus
10delivered

The order is back to its original status, and the transaction around it is still open. Drivers and ORMs use savepoints to implement nested transactions.

When two transactions touch the same row#

Transactions protect each statement's integrity, but not the logic your application runs between statements. The classic failure is the lost update: two sessions read the same balance, each adds to it in application code, and each writes back its own total. Both commit, no error is raised, and one deposit disappears. Recorded on a real server, with two connections:

Recorded on PostgreSQL 18.3, 2026-10-16

A lost update, and the fix

  1. A: begin → ok
  2. A: select balance from accounts where id = 1 → 100
    A reads 100 and plans to add 50.
  3. B: begin → ok
  4. B: select balance from accounts where id = 1 → 100
    B also reads 100 and plans to add 30.
  5. A: update accounts set balance = 150 where id = 1 → ok
    A writes the total it computed in the application.
  6. A: commit → ok
  7. B: update accounts set balance = 130 where id = 1 → ok
    B writes the total it computed from its stale read.
  8. B: commit → ok
  9. A: select balance from accounts where id = 1 → 130
    130. A's deposit of 50 is gone, and no error was raised. The fix: update balance = balance + 30 in the database, or read with SELECT … FOR UPDATE so the second reader waits.

There are three standard fixes:

  1. Let the database do the arithmetic: update accounts set balance = balance + 30 where id = 1. The second update waits for the first and adds to its result.
  2. Lock the row when you read it with SELECT … FOR UPDATE, so the second reader waits until the first has finished:

Recorded on PostgreSQL 18.3, 2026-10-16

SELECT … FOR UPDATE makes the second writer wait

  1. A: begin → ok
  2. A: select balance from accounts where id = 1 for update → 100
    A reads 100 and locks the row for the rest of its transaction.
  3. B: begin → ok
  4. B: select balance from accounts where id = 1 for update → waits
    B asks for the same lock and has to wait for A.
  5. A: update accounts set balance = 150 where id = 1 → ok
  6. A: commit → ok
    A commits and releases the lock. B's read completes, and it sees 150, the new value.
  7. B (finishes): select balance from accounts where id = 1 for update → 150
  8. B: update accounts set balance = 180 where id = 1 → ok
  9. B: commit → ok
    Both deposits are kept: 100 + 50 + 30 = 180.
  1. Detect the conflict with a higher isolation level, or with a version column (update … set version = version + 1 where id = 1 and version = 7, and treat zero updated rows as a conflict), and retry.

Keep transactions short#

An open transaction holds its locks and keeps old row versions alive for VACUUM to wait on. Never wait for a user, a network call or a file upload inside one. A connection left idle in transaction is a common cause of blocked queries and table bloat; idle_in_transaction_session_timeout makes PostgreSQL end such sessions automatically.

In MySQL#

Not run here; from the MySQL reference manual. InnoDB transactions are ACID as well, with START TRANSACTION, COMMIT, ROLLBACK and savepoints. One difference matters in practice: in MySQL a failed statement does not abort the surrounding transaction, so the application must decide whether to roll back. Statements such as ALTER TABLE cause an implicit commit in MySQL; in PostgreSQL most schema changes are transactional and can be rolled back.

Sources#

Esc
↑ ↓ to move↵ to open