Atomicity is one of the four ACID properties of a database transaction. What does it guarantee, and what happens to the work a transaction has already done if it fails halfway through?
answer
- all-or-nothing unit of work
- half-transfer state must not persist
- commit record = single flip point
- rollback discards uncommitted work, not a commit
- stops at the database boundary
basics
~20 sAtomicity means a transaction is all-or-nothing: either every change it made becomes visible together, or none does. If it fails partway, the engine reverses the work already done, leaving the database as if the transaction never ran.
solid answer
~60 sAtomicity is the all-or-nothing guarantee: a transaction's changes take effect as a single indivisible unit. There is no observable state in which half of a transfer happened — money debited but never credited. If the transaction fails partway, whether from an error, an explicit rollback, a lost connection, or a server crash, the engine undoes everything it had already applied. From the outside the database looks exactly as it did before the transaction started. Mechanically this rests on the engine recording enough information to reverse each change — undo records, or prior row versions kept in place — before or as the change is made, so reversal is always possible. Crash recovery uses the same machinery: a transaction with no durable commit record is rolled back during startup. Worth noting what it does not cover: atomicity says nothing about concurrent visibility (that is isolation) or about surviving a crash after commit (durability), and it stops at the database boundary — emails sent or HTTP calls made inside the transaction are not rolled back.
code
sql · 5 linesBEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
-- a crash anywhere before COMMIT leaves both rows unchangedgo deeper
State the all-or-nothing rule with the transfer example and mention that a crash before commit leaves nothing behind.
Add the mechanism — undo information plus a single durable commit record — and draw clean lines against isolation and durability.
Discuss the boundary of the guarantee: external side effects, cross-database work, transaction sizing, and the cost of aborting large transactions.
Reason about where the atomicity boundary should sit in a system design, when a distributed commit protocol is justified versus an outbox-style pattern, and what the organisation pays for each.
## The guarantee A transaction is a group of statements the database treats as one indivisible unit of work. Atomicity is the promise that this unit is applied entirely or not at all. There is no persistent outcome where some of its statements took effect and others did not. The canonical example is a transfer: 1. subtract 100 from account A 2. add 100 to account B If the system fails between the two, money has vanished. Atomicity ensures that outcome cannot survive: either both rows change or neither does. ## What counts as \"failing halfway\" Atomicity has to hold under several very different failures, which is why it needs real machinery rather than careful application code: - **A statement errors** — a constraint violation, a type error, a deadlock victim. The application (or the engine) ends the transaction and the work is discarded. - **The application decides to abort** — business logic finds a problem and rolls back deliberately. - **The session dies** — the client crashes or the network drops. The server eventually notices and rolls the transaction back; nothing it had done becomes visible. - **The server crashes** — on restart, any transaction without a durable commit record is undone as part of recovery. This is the case application code cannot possibly handle itself. In every case the end state is the same: as if the transaction never ran. ## How engines deliver it Two ingredients. **Reversal information.** Before a change becomes irreversible, the engine durably records how to undo it: a before-image of the row, an inverse operation, or — in multi-version engines — simply the prior version of the row, which stays in place while the new one is written. Rollback walks that information backwards. **A commit marker.** Committing is a single atomic act: writing the commit record. That one small durable write flips the transaction from \"nothing counts\" to \"everything counts\". This is the trick that makes an arbitrarily large set of changes atomic — the atomicity of the whole set is delegated to the atomicity of one record. Before it exists, recovery undoes the transaction; after it exists, recovery keeps and completes the transaction. Because of the second point, rollback is not really \"undoing a commit\" — nothing was ever committed. It is discarding uncommitted work. ## What atomicity is not Candidates lose points by conflating the four properties, so keep the boundaries sharp: - **Isolation** governs what *other* transactions see while yours is running. Atomicity alone would still permit another session to observe your half-finished state; it is isolation that forbids it. - **Durability** governs what survives a crash *after* commit. Atomicity says the unit is indivisible; durability says the committed unit does not evaporate. - **Consistency** in the ACID sense means the transaction moves the database from one valid state to another with respect to declared constraints. Atomicity is one of the mechanisms that helps, but a transaction can be perfectly atomic and still write nonsense. ## The boundary of the guarantee Atomicity covers changes *inside the database*. It does not extend to side effects performed by application code inside the transaction block: an email sent, a message published to a broker, a file written, a payment API called. If the transaction later rolls back, those effects remain. This is the dual-write problem, and it is why systems that need \"the row and the message both happen or neither\" write the message into a table in the same transaction and let a separate process deliver it. Similarly, a single transaction spanning two independent databases is not atomic by default; making it so needs a distributed commit protocol, with its own costs. ## Practical consequences Because abort is always available, the correct application pattern is to let errors propagate and roll back rather than to hand-write compensating updates. Compensation is error-prone precisely because it cannot cover the crash case. Because rollback must reverse work already done, aborting a very large transaction is not free — in some engines it costs as much as the original work. That is an operational argument for keeping write transactions bounded, not for avoiding transactions. And because the transaction is the unit of atomicity, its boundaries are a design decision: too wide and you hold locks and undo state for a long time; too narrow and operations that must be all-or-nothing get split across units and lose the guarantee. ## Interview framing \"All-or-nothing. Every change lands together or none does, including after a crash — because the engine keeps undo information and because commit is one atomic record that decides the fate of the whole unit.\"
- How is atomicity different from durability?Atomicity is about indivisibility: the transaction's changes all take effect or none do. Durability is about permanence after the fact: once a commit is acknowledged, those changes survive a crash. A system could in principle be atomic but not durable (a committed transaction is lost on power failure) or durable but not atomic (half a transfer is permanently written). ACID requires both.
- A transaction updates a row and also sends a confirmation email, then rolls back. What happens to the email?It has already been sent and cannot be recalled — atomicity covers only changes inside the database. This is the dual-write problem: two systems, one transaction, no shared commit. The usual fix is to write the intent into a table inside the same transaction and have a separate worker send the email after commit, so the row and the eventual send share the database's atomicity.
A wedding ceremony: many steps happen, but nobody is half-married. One declaration at the end makes the whole sequence count, and if it never comes, none of it does.
saying these in an interview costs you the question
- Saying atomicity means other transactions cannot see intermediate state (that is isolation)
- Saying atomicity means committed data survives a crash (that is durability)
- Believing rollback 'undoes a commit' rather than discarding uncommitted work
- Assuming external side effects like emails or HTTP calls are rolled back with the transaction
- Thinking application-level compensating updates are equivalent to a real rollback