Part 3 — Changing data › Chapter 21

Transactions and ACID

BEGIN, COMMIT, ROLLBACK, savepoints — and what each of the four letters actually buys you.

From the advert

“Esperienza su ambienti di produzione” — production is where you find out what a transaction actually guarantees, usually at the worst possible moment.

ACID, one letter at a time

🧒 Simple

Moving money between two piggy banks is two actions: take out of one, put into the other. If the lights go out in the middle, you must not end up with the money in neither. A transaction is a promise that the pair either both happen or neither does — there is no in-between anyone can see.

	What it promises	What buys it	What it does NOT promise
Atomicity	All statements or none	Undo log / rollback segments	That nobody sees the intermediate state (that is isolation)
Consistency	Constraints hold at commit	Your constraints — the engine only enforces what you declared	That your data is meaningful. It is the weakest letter
Isolation	Concurrent transactions do not corrupt each other	Locks or MVCC	Full serialisability, unless you asked for it. See ch. 22
Durability	Committed means survives a crash	Write-ahead log flushed at commit	Survival of the disk itself. That is backups. See ch. 41

🎓 The C is the one worth being sceptical about

Consistency in ACID means only “the database moves from one state satisfying its declared constraints to another”. If you declared no constraints, it promises nothing at all. It is not the same word as the C in CAP, and it is not a guarantee that your business rules hold — it is a guarantee that the rules you wrote down hold. Saying that out loud in an interview is a very cheap way to sound like you have thought about it.

Beginning, committing, and the thing about autocommit

The transfer that must not half-happen: two statements, one unit of work.

BEGIN;                          -- BEGIN TRANSACTION in T-SQL; implicit in Oracle
  UPDATE accounts SET balance_cents = balance_cents - 5000  -- take it from one account…
  WHERE id = 1;                                             -- …identified by key
  UPDATE accounts SET balance_cents = balance_cents + 5000  -- …and give it to the other
  WHERE id = 2;                                             -- both, or neither
COMMIT;                         -- or ROLLBACK, and it is as if nothing happened

What each line guarantees

balance_cents - 5000 is read-modify-write inside the engine, on the current value of the row, under a lock. Reading the balance into the application and writing back a computed number is a different, broken program.
Between BEGIN and COMMIT, no other session sees a world in which the money has left one account and not arrived at the other. That is atomicity, and it is the only reason this is safe.
COMMIT returns only once the log record is durable on disk. That is why commit latency is disk latency, and why committing once per row in a loop is slow.
Most clients run in autocommit: without the explicit BEGIN, each UPDATE is its own transaction and the failure mode above is live in production. Know which mode your driver uses.

⚠ Trap — you are probably in autocommit right now

Every mainstream client defaults to autocommit: each statement is its own transaction and commits as it completes. So the two updates above, run without BEGIN, are two transactions, and a crash between them leaves money that has left one account and arrived nowhere. Oracle is the exception — it starts a transaction implicitly and requires an explicit COMMIT, which surprises people coming the other way.

Savepoints: a partial rollback

Savepoints: undo part of a transaction without losing the rest of it.

BEGIN;                                          -- the outer unit of work
  INSERT INTO orders (customer_id) VALUES (?);  -- the order header: we want to keep this

  SAVEPOINT before_lines;                       -- a named point we can return to
  INSERT INTO order_lines (...) VALUES (...);   -- …this one violates a constraint
  ROLLBACK TO SAVEPOINT before_lines;           -- undo the line, KEEP the order

  INSERT INTO order_lines (...) VALUES (...);   -- the corrected line
COMMIT;                                         -- header and good line, together

When this is worth reaching for, and when it is not

ROLLBACK TO SAVEPOINT undoes work back to the marker and leaves the transaction open. A plain ROLLBACK ends everything.
In PostgreSQL a failed statement poisons the whole transaction — every subsequent statement errors with “current transaction is aborted” — so a savepoint is the only way to continue after a caught error. Many drivers issue them automatically for exactly this.
Savepoints are not free: each one holds resources until commit, and a loop that creates one per row is a performance bug. Use them for genuine “try this optional step” moments.
Nested BEGIN in T-SQL does not nest: only the outermost COMMIT commits, and any ROLLBACK undoes everything. SAVE TRANSACTION is T-SQL's savepoint.

Savepoints are what an ORM uses to implement nested transactions — there is no such thing as a genuinely nested transaction in SQL, so the inner “transaction” becomes a savepoint. Worth knowing because it explains why an inner rollback in your ORM does not abort the outer one.

⚠ Trap — SQL Server's nested transaction count is a lie

In T-SQL, a nested BEGIN TRANSACTION only increments @@TRANCOUNT. A nested COMMIT decrements it and commits nothing; only the outermost commit is real. But a ROLLBACK TRANSACTION — at any depth — rolls back everything and sets the count to zero. So nesting looks symmetric and is not. Use savepoints if you need partial rollback, and check @@TRANCOUNT in error handlers.

Long transactions are the real production problem

🎓 What a long transaction costs, by engine

PostgreSQL: it holds back the vacuum horizon, so dead row versions accumulate across the whole database. One forgotten open transaction is the single most common cause of table bloat.
SQL Server: the transaction log cannot truncate past the oldest active transaction, so the log grows until the volume fills.
All of them: locks are held to the end of the transaction, so everyone else waits longer.

⚠ Never hold a transaction open across a network call

BEGIN → call a payment API → COMMIT means your database transaction now lasts as long as somebody else's HTTP timeout, holding locks the whole time. The same applies to waiting for user input. Do the external work outside, and reconcile with an idempotency key (chapter 20) or a transactional outbox.

Is DDL transactional?

Engine	DDL in a transaction?
PostgreSQL	Yes — `CREATE TABLE`, `ALTER`, even `DROP`, all rollback-able. Migrations can be atomic
SQL Server	Mostly yes
MySQL / MariaDB	No — DDL causes an implicit commit. A failed migration leaves you half-migrated
Oracle	No — implicit commit before and after

This single difference shapes how you write migrations: on PostgreSQL a migration can be one atomic unit, and on MySQL every step must be independently safe to have half-applied. See chapter 36.

Atomicity, demonstrated in six lines you can run right now.

SELECT COUNT(*) FROM orders;                  -- 1800 — the starting point
BEGIN;                                        -- open the transaction
  DELETE FROM order_events;                   -- delete a whole table's rows…
  DELETE FROM payments;                       -- …and another
  SELECT COUNT(*) FROM payments;              -- 0 — INSIDE the transaction, it happened
ROLLBACK;                                     -- change our mind
SELECT COUNT(*) FROM payments;                -- 1440 — outside, nothing ever happened

What the two counts prove

Inside the transaction your own changes are visible to you and to nobody else. That is isolation, and chapter 22 is about exactly how much of other people's work you can see.
ROLLBACK restores the rows because the engine wrote the old values to its log before changing the pages. Undo is not magic; it is the write-ahead log.
Try it with the order reversed — delete payments before order_events — and note whether a foreign key refuses. Constraint checking happens inside the transaction too.

Atomicity is all-or-nothing; isolation is what other sessions can see meanwhile. They are different letters for a reason.
Consistency in ACID only means “the constraints you declared still hold”. Declare none and it promises nothing.
Durability means it survives a crash, not a dead disk. That is backups.
You are in autocommit unless you said otherwise — except in Oracle, where you are not.
Savepoints give partial rollback and are how ORMs fake nested transactions.
In T-SQL, a nested COMMIT commits nothing but a ROLLBACK at any depth rolls back everything.
Long transactions bloat PostgreSQL, grow the SQL Server log, and make everyone else wait.
Never hold a transaction open across a network call or user input.
DDL is transactional in PostgreSQL and SQL Server, and is not in MySQL or Oracle. That decides how migrations must be written.

Interview questions this chapter answers

Explain ACID.

Atomicity: all statements or none, via the undo log. Consistency: declared constraints hold at commit — which is weaker than people assume. Isolation: concurrent transactions do not corrupt each other, to the degree the isolation level specifies. Durability: once committed it survives a crash, because the log was flushed before the commit returned.

Why are long-running transactions a problem?

They hold locks to the end, so contention rises. In PostgreSQL they hold back the vacuum horizon and cause bloat across the whole database; in SQL Server they prevent the log truncating. And a transaction held open across an external HTTP call lasts as long as somebody else's timeout.

Can you roll back a CREATE TABLE?

In PostgreSQL and SQL Server, yes — DDL is transactional, which is why a migration can be atomic there. In MySQL and Oracle, no: DDL causes an implicit commit, so a failed multi-step migration leaves the schema half-changed and every step must be independently safe.