Atomicity guarantees that a transaction either completes in its entirety, or has no effect on the database at all — there is no partial, half-applied state visible to anyone. The canonical example is a funds transfer: debit account A, credit account B. If the process crashes after the debit but before the credit, atomicity is what guarantees the database rolls the debit back too, rather than leaving money that simply vanished.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
If the second UPDATE fails for any reason — a constraint violation, a crash, a deadlock — the database rolls the entire transaction back, including the first UPDATE, as though neither statement had ever run. You never have to write manual "undo the first update" logic; that's precisely the guarantee atomicity is providing.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- Suppose this violates a CHECK constraint (balance can't go negative)
UPDATE accounts SET balance = balance + 999999 WHERE id = 'B_TYPO';
ROLLBACK; -- or the engine does this automatically on error, depending on settings
-- Account A's balance is back to what it was before BEGIN TRANSACTION.
Atomicity is implemented differently depending on the storage engine, but the common mechanism is logging every change before it's applied to the real data files (see the Durability section below) so an incomplete transaction can always be unwound. In application code, the practical failure mode is forgetting to wrap multiple related writes in a single transaction at all — doing two separate UPDATE statements outside a transaction block means a crash between them leaves the database in exactly the half-finished state atomicity was supposed to prevent.
// Wrong — no transaction. A crash between these two calls leaves inconsistent data.
await db.ExecuteAsync("UPDATE accounts SET balance = balance - 100 WHERE id = @a", new { a = "A" });
await db.ExecuteAsync("UPDATE accounts SET balance = balance + 100 WHERE id = @b", new { b = "B" });
// Right — both succeed or both roll back together.
using var transaction = await db.BeginTransactionAsync();
try
{
await db.ExecuteAsync("UPDATE accounts SET balance = balance - 100 WHERE id = @a", new { a = "A" }, transaction);
await db.ExecuteAsync("UPDATE accounts SET balance = balance + 100 WHERE id = @b", new { b = "B" }, transaction);
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}