Atomicity

Definition

Atomicity guarantees that a transaction's operations are treated as one indivisible unit: either ALL of them take effect, or NONE do — there is no possible outcome where only some of a transaction's statements succeeded.

How It Works

sql
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;  -- succeeds
UPDATE accounts SET balance = balance + 200 WHERE id = 2;  -- suppose this fails (e.g. a crash)
COMMIT;

If the second UPDATE fails for any reason before COMMIT, atomicity guarantees the FIRST UPDATE's effect is also undone — the database automatically rolls back the entire transaction rather than leaving the debit applied with no matching credit. This is what makes the bank-transfer example safe: a partial transfer is never a real, visible outcome.

Edge Cases and Pitfalls

• Atomicity is about the TRANSACTION's own statements — it says nothing about coordinating with something OUTSIDE the database (like also calling an external payment API) — a transaction that commits successfully in the database but where a related external call fails is a real, harder problem (distributed transactions/sagas) beyond plain ACID atomicity's scope.
• A CHECK/FOREIGN KEY/UNIQUE constraint violation partway through a multi-statement transaction triggers exactly this atomicity guarantee — the whole transaction rolls back, not just the offending statement.
• Atomicity requires the storage engine to actually support it — as verified practically, MyISAM tables have NO atomicity guarantee across multiple statements; only a transactional engine like InnoDB provides it.

Key Takeaways

• Atomicity = all transaction statements succeed together, or none do — no partial completion is ever a visible outcome.
• A failure anywhere before COMMIT triggers automatic rollback of everything already done in that transaction.
• Atomicity only covers the database's own operations — coordinating with external systems needs additional patterns beyond plain ACID.