Transactions and ACID

A transaction groups statements so they take effect together or not at all. The canonical example is a transfer: subtract from one account, add to another. If the second statement fails and the first has already been applied, money has vanished.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

BEGIN opens it, COMMIT makes every change permanent at once, and ROLLBACK discards all of them. If the connection drops between the two updates, the database rolls back automatically — the first update never happened.

None of the blocks in this lesson have Run buttons. The playground executes one statement per request against a database it rebuilds each time, which is precisely what a transaction is not. Showing you a BEGIN that appeared to work would be a lie in the shape of a demo.

ACID

Four guarantees, and they are worth being able to state precisely:

Atomicity — all or nothing. A transaction's statements are one indivisible unit. This is the transfer example.

Consistency — the database moves from one valid state to another. Constraints, foreign keys and triggers hold at commit; a transaction that would violate one is rejected.

Isolation — concurrent transactions do not see each other's incomplete work. How strictly is configurable, and is the whole of the next lesson.

Durability — once COMMIT returns, the change survives a crash. Usually via a write-ahead log flushed to disk before the commit is acknowledged.

Interviewers ask for these by name. The follow-up that separates memorisation from understanding is usually isolation, because it is the one with dials.

ROLLBACK

BEGIN;

DELETE FROM orders WHERE status = 'cancelled';
-- check what happened before making it permanent
SELECT COUNT(*) FROM orders;

ROLLBACK;   -- undo it all

Wrapping a risky DELETE or UPDATE in an explicit transaction and inspecting before committing is a habit worth having. Note this only protects you inside the transaction — once committed, ROLLBACK has nothing to undo.

Autocommit

Without an explicit BEGIN, most databases run every statement in its own transaction and commit it immediately. That is autocommit, and it is why a single UPDATE is already atomic — it is a one-statement transaction.

The catch is what happens to a multi-statement script under autocommit: each line commits independently, and a failure halfway through leaves the first half applied. Scripts that modify data need an explicit transaction.

Behaviour differs: PostgreSQL, MySQL and SQL Server default to autocommit; Oracle opens a transaction implicitly and requires an explicit COMMIT. Knowing which one you are on matters the first time you close a client without committing.

Savepoints

Partial rollback within a transaction:

BEGIN;

INSERT INTO orders VALUES (2001, 1, '2024-04-01', NULL, 'pending', 50.00);

SAVEPOINT after_order;

INSERT INTO payments VALUES (99, 2001, '2024-04-01', 'card', 50.00);
ROLLBACK TO SAVEPOINT after_order;   -- undo the payment, keep the order

COMMIT;

The order survives, the payment does not. Useful in long procedures where one optional step may fail without invalidating the rest.

What a transaction should not be

The most common production incident involving transactions is one held open too long. A transaction holds locks and prevents cleanup for its whole lifetime, so:

Do not wait on anything external inside one. No HTTP calls, no user input, no waiting for a queue. The classic outage is a transaction opened, an API called, the API hanging, and every other writer blocking behind it.
Keep them short. Read what you need, write, commit.
Do not batch a million rows into one transaction. Chunk it, and commit between chunks.

Errors inside a transaction

PostgreSQL is strict: once any statement fails, the transaction is poisoned and every subsequent statement errors with "current transaction is aborted" until you roll back. Savepoints are how you recover mid-transaction.

MySQL and SQL Server are more lenient — some errors abort the statement but leave the transaction usable, others abort everything. This is a real portability difference in error-handling code and worth checking rather than assuming.

Read-only transactions

Multiple SELECTs in one transaction see a consistent snapshot — useful when a report queries several tables and must not see a write land between them:

BEGIN READ ONLY;
SELECT COUNT(*) FROM orders;
SELECT SUM(amount) FROM payments;
COMMIT;

Without this, the two queries can disagree: an order counted in the first is paid before the second runs.

Common mistakes

Assuming a multi-statement script is atomic — under autocommit each line commits on its own.
Long-running transactions — locks held, other writers blocked, cleanup deferred.
External calls inside a transaction — the whole database waits for someone else's network.
Expecting ROLLBACK to undo a committed change — it cannot.
Ignoring the error return of COMMIT — a commit can fail, and then nothing was saved.

Interview question

What does ACID stand for, and which property is about concurrency?

Atomicity, Consistency, Isolation, Durability — isolation is the concurrency one, and the only one with configurable levels. Expect the follow-up straight into isolation levels, which is the next lesson.

Check yourself

Why is a single UPDATE atomic without an explicit BEGIN?
What does a savepoint let you do that ROLLBACK alone does not?
Why is calling an external API inside a transaction dangerous?