Transactions and ACID

The four letters

• Atomicity — the whole transaction commits or none of it does. A failure midway rolls everything back, including steps that already ran cleanly.
• Consistency — a transaction moves the database from one valid state to another; it cannot leave money existing that came from nowhere.
• Isolation — concurrent transactions do not see each other's half-finished work, to a degree controlled by the isolation level.
• Durability — once COMMIT returns, the change survives a crash the instant after.

Isolation levels, briefly

READ UNCOMMITTED lets one session see another's uncommitted changes — a "dirty read". READ COMMITTED, the default in most engines, blocks that: a session only ever sees data that has actually been committed. Stricter levels (REPEATABLE READ, SERIALIZABLE) exist to stop other classes of anomaly, at a cost in concurrency.

The bank transfer, and why it needs a transaction

Move £100 from account A to account B and two statements are involved:

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

If the server loses power between them, £100 has vanished. Not been delayed — vanished, permanently, with the database in a state that no accounting rule allows.

A transaction makes the pair inseparable:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

Now either both updates are permanent or neither happened. A crash before COMMIT leaves the database exactly as it was; a crash after it leaves both changes in place, because the engine wrote them durably before acknowledging the commit.

Everything else in this topic is about the guarantees that make that promise trustworthy, and about what happens when several transactions run at once.

The four letters, with the failure each one prevents

Atomicity — all or nothing. Prevents: half a transfer. Implemented with an undo log, so an aborted transaction can be rolled back to its starting point.

Consistency — the database moves from one valid state to another, with all constraints satisfied. Prevents: a foreign key pointing at a deleted row, a negative balance where a CHECK forbids it. This is the letter that depends partly on you: the database enforces the rules you declared, not the ones you only had in mind.

Isolation — concurrent transactions do not see each other's unfinished work. Prevents: reading a balance mid-transfer and computing a total that never actually existed. This is the letter with dials on it, and the next section is about them.

Durability — once committed, it survives a crash. Prevents: losing an acknowledged write to a power cut. Implemented by writing to a log on stable storage before reporting success, which is why commit latency is bounded by how fast your disk can flush.

Isolation levels, and the anomalies they allow

Perfect isolation is expensive, so SQL defines levels that trade correctness guarantees for concurrency.

The three anomalies, in plain terms:

• Dirty read — you read a change another transaction has not committed and may still roll back.
• Non-repeatable read — you read the same row twice in one transaction and get different values, because someone committed in between.
• Phantom read — you run the same query twice and get different rows, because someone inserted or deleted matching records.

Read Committed is the default in PostgreSQL, Oracle and SQL Server; MySQL's InnoDB defaults to Repeatable Read. The asterisk above is because PostgreSQL's Repeatable Read, implemented with snapshots, prevents phantoms too — the standard permits them, this implementation does not.

Serializable is the only level that guarantees the result is equivalent to running the transactions one after another. It costs throughput, either through locking or through aborting transactions that would have conflicted, so it is reserved for the parts of a system where correctness genuinely outranks speed — money, inventory, seat booking.

Making several statements into one

A transaction is the promise that a group of statements happens completely or not at all. The demonstration is short because the idea is short -- and the interesting part is what the promise costs and what it does not cover.

-- The classic case: move money, and never lose it in transit.
-- Two UPDATEs that must both happen.

CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance REAL);
INSERT INTO accounts VALUES (1, 'Ada', 500.0), (2, 'Bala', 120.0);

SELECT owner, balance FROM accounts ORDER BY id;

-- Committed: both halves stick.

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

SELECT owner, balance FROM accounts ORDER BY id;

-- Rolled back: neither half survives, including the one that had
-- already run.

BEGIN;
UPDATE accounts SET balance = balance - 400 WHERE id = 1;
UPDATE accounts SET balance = balance + 400 WHERE id = 2;
ROLLBACK;

SELECT owner, balance FROM accounts ORDER BY id;

-- That is ATOMICITY, the A. The other three:
--
-- CONSISTENCY  the database moves between valid states -- constraints
--              hold before and after, never halfway.
-- ISOLATION    concurrent transactions do not see each other's
--              unfinished work. This is the one with settings: read
--              committed, repeatable read, serializable, each trading
--              strictness for concurrency.
-- DURABILITY   once COMMIT returns, the change survives a crash.
--
-- Isolation is the one that leaks into application code, because the
-- weaker levels permit specific anomalies -- a row that changes between
-- two reads in the same transaction, or a sum that includes a row that
-- was not there when you counted. Which anomalies you can tolerate is a
-- decision, and the default in most engines is not the strictest one.
--
-- And the limit worth knowing: a transaction protects the DATABASE. If
-- your handler charges a card and then writes a row, the rollback undoes
-- the row and not the charge.

Guided tour

1. Step through a clean transfer. BEGIN, debit Alice, credit Bob, COMMIT. Session B's balances only change at the very last step.
2. Switch to READ UNCOMMITTED and step again. Now B sees Alice's balance drop the instant A's UPDATE runs — before COMMIT. That is a dirty read, and it is why READ UNCOMMITTED is rarely the default.Run this
3. Tick "Fail after the second UPDATE" and step through again. Both writes ran — Alice was debited, Bob was credited — but the transaction rolls back instead of committing, and Session A's own view snaps back to the original balances. Atomicity in one demonstration: a completed write is not a committed write.

In one line

A transaction makes a group of writes behave as one unit: Atomicity guarantees all-or-nothing, Consistency guarantees the result is valid, Isolation controls what concurrent sessions can see of work in progress, and Durability guarantees a commit survives a crash. Isolation is a dial, not a switch — READ UNCOMMITTED trades correctness for speed by permitting dirty reads, which is why READ COMMITTED is the default almost everywhere.

Locks, deadlocks, and how to avoid them

When two transactions want the same row, one waits. When two transactions each hold something the other wants, both wait forever — a deadlock. Databases detect this and kill one of the transactions with an error rather than hanging.

The classic recipe:

Transaction 1: UPDATE accounts SET ... WHERE id = 'A';   -- holds A
Transaction 2: UPDATE accounts SET ... WHERE id = 'B';   -- holds B
Transaction 1: UPDATE accounts SET ... WHERE id = 'B';   -- waits for 2
Transaction 2: UPDATE accounts SET ... WHERE id = 'A';   -- waits for 1

Three practices remove most deadlocks in practice:

1. Always touch rows in a consistent order — sort account IDs before updating them, and the cycle above cannot form.
2. Keep transactions short. Never hold a transaction open across a network call, a user's think time, or an external API. The most common cause of lock contention is a transaction that started before it needed to.
3. Retry on deadlock. Deadlock errors are expected under load, not exceptional. Application code that touches contended rows should catch the error and retry with a short random delay.

A fourth, for the specific case of updating a row you have just read: use SELECT ... FOR UPDATE to lock it at read time, or you have a lost-update race where two transactions both read 100, both write 90, and one update disappears.

Transactions in application code

Two patterns cause most production incidents in this area.

The long transaction. Opening a transaction, calling a payment provider, and committing on the response holds locks for the duration of an unpredictable network call. Do the external work first, or split the work so the transaction covers only the database changes.

The forgotten rollback. An exception path that leaves a transaction open ties up a connection and holds its locks until a timeout. Use the language's context manager or transaction decorator rather than manual BEGIN/COMMIT pairs:

with connection.transaction():          # commits on success, rolls back on error
    debit(account_a, 100)
    credit(account_b, 100)

Savepoints are worth knowing for long multi-step work: they mark a point you can roll back to without abandoning the whole transaction, which lets one step fail and be retried while the earlier steps stand.

Questions people ask

Do I need a transaction for a single statement? No — every individual statement runs in its own implicit transaction. You need one when two or more statements must succeed or fail together.

Does a rollback undo everything? Everything transactional. Sequence and auto-increment counters typically do not roll back, which is why IDs can have gaps, and anything you did outside the database certainly does not.

What does NoSQL give up here? Varies by system. Many offer atomicity within a single document or partition but not across them, and eventual rather than immediate consistency across replicas. Several document databases have added multi-document transactions since.

Is Serializable always the safest choice? Safest, and slowest, and it makes serialisation-failure retries a normal part of application logic. Use it where the correctness matters most rather than everywhere by default.

What is two-phase commit? A protocol for making a transaction atomic across several databases: all participants promise they can commit, then all commit. It works, and it is slow and fragile enough that most modern designs prefer a saga — a sequence of local transactions with compensating actions — instead.

Why did my transaction fail with a serialisation error? Because the engine detected that committing it would break the illusion of one-at-a-time execution. It is not a bug; retry the transaction.

Recap in one screen

• A transaction makes several statements succeed or fail as one unit.
• Atomicity: all or nothing. Consistency: constraints hold. Isolation: concurrent work stays invisible. Durability: committed means survived.
• Isolation levels trade guarantees for concurrency; Read Committed is the common default, Serializable the strict one.
• Deadlocks are normal under load — order your writes consistently, keep transactions short, and retry.
• Never hold a transaction open across a network call.