Transactions and ACID

A transaction is the database's way of saying "these changes belong together". Almost every correctness argument in a design rests on it, and "we use a transaction" is often said without knowing what it does and does not protect.

Crux: a transaction makes a group of changes all-or-nothing; ACID names four separate promises, and each costs something and stops at a boundary (one database), so say which promise your design needs and where it ends.

The problem a transaction solves

Move 100 from account A to account B. That is two writes.

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

Without a transaction, three things can go wrong:

• A crash between the two writes. A has lost 100 and B has not received it. Money disappeared.
• A reader in between. Another query sums both accounts and sees a total that is 100 short.
• A failure you cannot undo by hand. If the second write is rejected, you must remember to put the first one back, in code that may itself crash.

A transaction fixes all three: either both writes happen, or neither does, and nobody sees the half-done state.

ACID, one letter at a time

Atomicity: all or nothing

If anything fails before COMMIT (an error, a crash, a lost connection), the database undoes the changes. It can do this because it records enough to reverse or discard them: Postgres writes new row versions that simply stay invisible if the transaction never commits, and other engines keep an undo log.

• You can also ask for it: ROLLBACK throws the changes away.
• In Postgres, once a statement fails inside a transaction, the transaction is aborted: every later statement is rejected until you ROLLBACK. Do not assume you can catch the error and carry on.
• Atomicity covers one database. It does not cover "write the row, then call the payment API". That needs the patterns in the multi-step processes lesson.

Consistency: your rules still hold

This is the odd letter, because the database does not invent the rules. You declare them, and the database refuses any transaction that would break them.

CREATE TABLE accounts (
  id      TEXT PRIMARY KEY,
  balance INT NOT NULL CHECK (balance >= 0)
);

With the CHECK, a transfer that would overdraw A fails and rolls back. Constraints are primary keys, unique keys, foreign keys, NOT NULL and CHECK.

• A rule you did not declare is not protected. "A user has at most one active subscription" must be a unique index, not a comment in the service.
• Do not confuse this with the C in CAP. ACID consistency means "my rules hold". CAP consistency means "every node returns the latest write". Different ideas, same letter.

Isolation: concurrent transactions do not trip over each other

While your transaction runs, others run too. Isolation says how much of each other's unfinished work they can see.

• Full isolation (serializable) behaves as if transactions ran one at a time. It is the safest and the slowest, and some transactions must be retried.
• Weaker levels allow more concurrency and let certain surprises through. The surprises have names (dirty read, lost update, write skew), and the next lesson covers them.
• Defaults differ. Postgres starts at read committed. MySQL's InnoDB engine defaults to repeatable read. Check what your database does, because "we use transactions" does not mean "we are serializable".

Durability: committed means it stays committed

When COMMIT returns, the data survives a crash and a power cut. The mechanism is the write-ahead log (WAL): the database writes the change to a sequential log and forces it to disk (fsync) before telling you it is done. After a crash it replays the log.

• The cost is that a commit waits for a disk flush. That is why commit latency is often milliseconds, not microseconds.
• Databases batch several waiting commits into one flush (group commit) to recover throughput.
• Postgres lets you turn the wait off (synchronous_commit = off). Commits become faster, and a crash can lose the last few moments of "committed" data (not corrupt it). Know that trade before you use it.
• For survival of a whole machine, durability means the log is also on another machine (synchronous replication). That is a latency cost again, covered in the replication lesson.

What it costs, and how to keep it cheap

• Locks or versions are held for the life of the transaction. A long transaction blocks others or keeps old row versions alive. Keep transactions short: do the reads, the writes, commit.
• Never wait on something slow inside one. No HTTP call, no message publish that waits, no user input between BEGIN and COMMIT.
• Retries are part of the deal. At stricter isolation levels, and when two transactions deadlock, the database aborts one with an error. Your code must retry it. A transaction without a retry loop works in testing and fails under load.
• One transaction means one database. The guarantee does not extend across two shards or two services. You can pay for it (two-phase commit, slow and fragile) or design around it (keep the data that changes together on one shard, and use sagas for the rest).

Not every store offers all four

Many non-relational stores give atomicity only for a single item or a single partition by default, and offer richer transactions as a separate, more limited feature. A design that assumes "multi-row transaction" on such a store needs to check what it actually provides. This is one reason the choice of database starts from the guarantee you need.

How to use this in a design

• Name the invariant first. "A seat can be sold once." "Balance never goes below zero." The invariant tells you what must be atomic.
• Put the invariant in the database. A unique index on (event_id, seat_id) or a CHECK rather than a check in code that two requests can race through.
• Keep the transaction boundary inside one database. Choose the shard key so the rows that change together live together.
• Prefer one statement over read, think, write. SET balance = balance - 100 is applied inside the database in one step. SELECT balance, compute in the service, then UPDATE can lose a concurrent update at the default isolation level.
• Say what happens when the commit answer is lost. If the connection drops during COMMIT, the client does not know whether it committed. The retry must be safe: an idempotency key or a unique constraint that turns the duplicate into a no-op.

Worked example, a ticket booking: insert the booking row and mark the seat as sold in one transaction, with a unique constraint on the seat so a second buyer fails cleanly. Charging the card is outside the transaction, and its outcome is handled by a separate step (see the multi-step processes lesson).

Common mistakes

• Holding a transaction open across a network call, which turns one slow dependency into a database-wide stall.
• Assuming ACID spans two databases or services. It does not.
• Reading "transaction" as "serializable". At read committed, a read-then-write can still lose an update.
• Confusing the C of ACID with the C of CAP.
• Leaving invariants in application code where concurrent requests can pass the check together.
• No retry on abort. Deadlocks and serialization failures are normal at scale.
• Turning off the durability wait for speed without saying so. It is a legitimate choice only if you are happy to lose the last moments after a crash.

Check yourself

• What failures does the two-line transfer have without a transaction, and which letter of ACID fixes each?
• Which letter says "the database refuses a change that breaks a declared rule", and why does it not protect a rule you never declared?
• Why is a commit slower than the same write without a transaction, and what setting trades that cost for risk?
• A service does SELECT balance, adds 100 in code, then UPDATEs. Two requests run at once at read committed. What can go wrong, and what would you write instead?
• A booking row is written and the card charge succeeds, but the charge response is lost and the client retries. What stops a double booking and a double charge?
• Why does a stricter isolation level mean your code needs a retry loop?