All of it, or none of it

Atomicity is the promise that stops the missing 200 pounds. All of it, or none of it.

How the database keeps the promise

Wrap the subtract and the add in one transaction and your database guarantees the world only ever sees both applied or neither. The power cut mid-transfer now leaves the money in savings, untouched.

Crash after your third statement, break a constraint on the fourth, lose the database process mid-commit, and everything already done rolls back as though the transaction never started.

Take the money transfer, which is two writes pretending to be one operation: take from account A, add to account B. Without atomicity, a crash between them creates or destroys money for you. With it the half-finished transfer evaporates and you try again.

Notice what atomicity does not promise. It says nothing about two transactions running at once, which belongs to isolation. It only promises that a partly applied transaction does not exist.

Underneath, the database keeps enough information to undo. Postgres writes new versions of each row tagged with the transaction that made them, and committing is the small act of marking that transaction committed in a status log. Abort instead and those versions are simply never treated as visible. Rolling back costs you almost nothing, because nothing was ever written over.

Watch the edge, because that is where atomicity stops. Your transaction that inserts an order and also calls a payment provider is not atomic, because the provider will not roll back when you do. The same goes for sending an email, publishing a message, or writing to a second database.

That gap is why the outbox pattern exists: write the side effect as a row inside the same transaction and deliver it afterwards. When a design has two systems changing state on one user action, ask where the atomic boundary sits. There is usually a bug living just outside it.

the shape of it

step 1 of 3

A failure at any step routes to rollback, so the transfer is either complete or invisible.

the transfer that cannot be half done

Java

// Without the transaction, a crash between the two updates
// leaves the money subtracted from one account and added to none.
tx.begin();
try {
  db.exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", 200, savings);
  db.exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", 200, current);
  tx.commit();          // both rows become visible at the same instant
} catch (Exception e) {
  tx.rollback();        // or neither of them ever happened
  throw e;
}

Worked example

Priya sends 50 dollars to Dev through a payments app. The naive version runs two separate statements: UPDATE priya SET balance = balance - 50, then UPDATE dev SET balance = balance + 50. One Friday the app server is killed by a deploy between the two, and 50 dollars leaves Priya without reaching Dev. Support finds 212 similar cases in the logs over three months. The fix is embarrassingly small: BEGIN, both updates, COMMIT. Rerunning the same deploy-kill test, the crash now lands mid-transaction, Postgres rolls it back, Priya's balance is untouched, and the app retries cleanly on restart. Two lines of SQL turned a reconciliation nightmare into a non-event, which is why this example opens every ACID explanation ever written.