ACID, SOLID, and Design Patterns: A Complete Software Design Reference

Part 2 of 26 · ~3 min

Atomicity: All-or-Nothing Transactions

Picture a sold-out reunion show. A four-hundred-seat room, one night only, and a ticket link that goes live at noon. In the first ninety seconds, thousands of people are hammering "Buy" on a few hundred seats, and every single one of those clicks has to either fully succeed or fully fail — there is no acceptable state where a seat is half-sold.

Atomicity is the guarantee that a transaction happens completely or not at all, with no window where anyone — including the next statement in your own code — can observe it partly applied. Grabbing a seat for a fan takes two separate writes: flip the seat's status so nobody else can grab it, and record who's holding it and for how long.

BEGIN TRANSACTION;

UPDATE Seats SET status = 'held' WHERE id = 214 AND status = 'available';
INSERT INTO Holds (seat_id, customer_id, expires_at)
VALUES (214, 8831, datetime('now', '+3 minutes'));

COMMIT;

If the INSERT fails — say a stale duplicate hold already exists for that customer and a constraint rejects it — the database unwinds the UPDATE right along with it, as though neither statement had run. You never write the "put the seat back" logic yourself; that's the entire point of wrapping both writes in one transaction.

BEGIN TRANSACTION;

UPDATE Seats SET status = 'held' WHERE id = 214 AND status = 'available';

-- Suppose this violates a UNIQUE constraint (customer 8831 already holds a seat)
INSERT INTO Holds (seat_id, customer_id, expires_at)
VALUES (214, 8831, datetime('now', '+3 minutes'));

ROLLBACK; -- or the engine rolls back automatically on the error, depending on settings
-- Seat 214 is back to 'available'. Nobody saw it as anything else.

Now watch what happens without the wrapper — this is the real-world way atomicity actually gets lost, and it's never confusion about what happens once code is running inside a transaction. It's simply never opening one:

// Wrong — two independent statements. A crash between them leaves a
// seat marked 'held' with no Hold row pointing at it — nothing will
// ever release it, and nobody can ever buy it again for this show.
await db.ExecuteAsync("UPDATE Seats SET status = 'held' WHERE id = @id", new { id = 214 });
await db.ExecuteAsync(
    "INSERT INTO Holds (seat_id, customer_id, expires_at) VALUES (@id, @cust, @exp)",
    new { id = 214, cust = 8831, exp = DateTime.UtcNow.AddMinutes(3) });

// Right — both succeed or both roll back, together.
using var transaction = await db.BeginTransactionAsync();
try
{
    await db.ExecuteAsync("UPDATE Seats SET status = 'held' WHERE id = @id", new { id = 214 }, transaction);
    await db.ExecuteAsync(
        "INSERT INTO Holds (seat_id, customer_id, expires_at) VALUES (@id, @cust, @exp)",
        new { id = 214, cust = 8831, exp = DateTime.UtcNow.AddMinutes(3) }, transaction);
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Call that orphaned seat what it is: dead inventory. It's marked unavailable, nothing is holding it, nothing will ever expire and release it, and on a four-hundred-seat show, a handful of these silently shrinks the house. Exactly how a storage engine pulls off the rollback differs by product, but the recurring trick underneath most of them is recording every intended change in a separate log before it's allowed to touch the real table — the same mechanism, it turns out, that the Durability chapter below leans on for an entirely different guarantee.