ACID - atomicity

General Questions~4 min read

ACID atomicity guarantees that a database transaction is treated as a single indivisible unit: it either completes entirely or not at all.

Theory

TL;DR

Think of mailing an envelope with cash: the envelope delivers both, or neither arrives if lost.
All operations write to a log before commit; any failure triggers a rollback to the pre-transaction state.
Two separate SQL statements outside a transaction have no atomicity guarantee.
Multi-step changes that depend on each other → wrap in a transaction. Read-only queries → skip it.

Quick example

sql

-- Bank transfer: atomicity in action (PostgreSQL)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Deduct from Alice
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Credit Bob
COMMIT;
-- Both succeed: Alice $900, Bob $1100

-- If the second UPDATE fails (e.g., constraint violation):
-- Engine auto-triggers ROLLBACK
-- Result: no changes. Alice $1000, Bob $1000

Both updates happen as one. If anything breaks between BEGIN and COMMIT, the database undoes everything automatically.

Without a transaction, two separate UPDATE statements are two independent operations. One can succeed and the other fail, leaving balances inconsistent. Wrapping them in BEGIN/COMMIT makes the database treat them as a single logical step with automatic rollback on any failure.

When to use

Multi-table updates that depend on each other → wrap in a transaction.
Bank transfers, order creation, inventory deduction → always atomic.
Single-row idempotent inserts → no transaction needed.
Read-heavy queries → skip to avoid holding unnecessary locks.
Distributed systems (microservices) → use 2PC or the saga pattern instead of a single DB transaction.

How atomicity works internally

PostgreSQL uses Write-Ahead Logging (WAL): before modifying any data page, the engine writes the operation to a log file and marks the transaction boundaries. On COMMIT, the log flushes to disk. If a crash happens before that flush, WAL replays only committed transactions on recovery, and incomplete ones get rolled back using before-images stored in undo logs. MVCC (Multi-Version Concurrency Control) creates snapshots so concurrent transactions see a consistent view throughout this process.

One thing that trips teams in production: a long-running transaction holds locks on every row it touches. Seen this cause a 10-minute outage on a Rails app where a batch job opened a BEGIN and never committed fast enough.

Common mistakes

Mistake: nested BEGIN without savepoints

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
BEGIN;  -- PostgreSQL ignores this and warns, but continues the outer tx
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;  -- Rolls back the ENTIRE outer transaction, not just the inner block

Most developers expect the inner ROLLBACK to undo only the second update. It does not. Use SAVEPOINT for partial rollbacks:

sql

BEGIN;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO sp1;  -- Undoes only the second update
COMMIT;

Mistake: forgetting client.release() in Node.js

javascript

const client = await pool.connect();
await client.query('BEGIN');
// ... queries ...
await client.query('COMMIT');
// No client.release() here → connection leak under load, pool exhausts

Always use finally { client.release(); }. No exceptions.

Mistake: long-running transactions

sql

BEGIN;
SELECT * FROM large_table;  -- Runs for 10 minutes, holds shared locks
-- All writers are blocked the entire time

Set a statement timeout. Use READ ONLY transactions where possible. Keep transactions under 30 seconds in production.

Mistake: assuming an app crash rolls back everything

A COMMIT that flushed to WAL survives a crash. If your app dies between sending COMMIT and receiving acknowledgment, the transaction may still be committed on the database side. Check pg_stat_activity after unexpected crashes, and use PREPARE TRANSACTION for distributed scenarios.

Real-world usage

PostgreSQL: WAL + 2PC for XA distributed transactions in banking systems.
MySQL (InnoDB): binlog + undo logs; Shopify uses this for order processing.
MongoDB: multi-document transactions since v4.0; Stripe uses them for payment atomicity.
SQL Server: TempDB versioning; common in enterprise ERP on Azure SQL.
Microservices: a DB transaction cannot span two services, so teams use sagas with Kafka Streams or the outbox pattern instead.

Follow-up questions

Q: How does atomicity interact with isolation?
A: Atomicity is about all-or-nothing for a single transaction. Isolation controls what concurrent transactions can see during execution. PostgreSQL provides both through MVCC snapshots.

Q: How does rollback actually work under the hood?
A: The engine reads the undo log (before-images of modified rows) and applies them in reverse order. WAL replays only committed transactions on crash recovery, so incomplete work is discarded.

Q: What is the difference between atomicity and idempotency?
A: Atomicity means a transaction either fully completes or fully reverts. Idempotency means repeating the same operation produces the same result. A POST /transfer endpoint can be idempotent (via a unique transfer ID) even if the underlying DB transaction is atomic.

Q: How do you handle atomicity across microservices?
A: A single DB transaction cannot span two services. The standard approaches are 2PC (prepare/commit phases, used in XA with Java Spring) or the saga pattern, where each service has a compensating transaction that undoes its step if a later step fails.

Q: What happens if a transaction exceeds the lock timeout?
A: The engine aborts the transaction with an error (code 40P01 for deadlocks in PostgreSQL). Design around this with NOWAIT or SKIP LOCKED. Monitor pg_locks to catch long wait chains before they become outages.

Examples

Basic: bank transfer with automatic rollback

sql

-- PostgreSQL: atomic fund transfer
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Alice: $1000 -> $900

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Bob: $1000 -> $1100

COMMIT;
-- Both rows updated. Total balance preserved: $2000

-- Simulate a failure:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 999;  -- id=999 does not exist
-- Constraint violation triggers automatic ROLLBACK
-- Alice stays at $1000. No partial state.

The key invariant here is total balance. After any transaction, successful or failed, the sum of all account balances must stay the same. Atomicity is what enforces that.

Intermediate: e-commerce order creation (Node.js + PostgreSQL)

javascript

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function createOrder(userId, items) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Reserve inventory for each item
    for (const item of items) {
      await client.query(
        'UPDATE inventory SET stock = stock - $1 WHERE product_id = $2 AND stock >= $1',
        [item.quantity, item.productId]
      );
    }

    // Create the order record
    const result = await client.query(
      'INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id',
      [userId, items.reduce((sum, i) => sum + i.price * i.quantity, 0)]
    );

    await client.query('COMMIT');
    return result.rows[0].id;  // orderId=123, inventory already deducted
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;  // No inventory change, no order record. Clean state.
  } finally {
    client.release();  // Always release, even on error
  }
}

If inventory deduction succeeds but the order insert fails, the catch block rolls back all inventory changes. The user sees an error. Nothing is half-applied.

Senior: deadlock in concurrent transfers

sql

-- Session 1 (Tx1):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;  -- Acquires lock on row 1
UPDATE accounts SET balance = balance + 50 WHERE id = 2;  -- Waits for row 2

-- Session 2 (Tx2, running simultaneously):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- Acquires lock on row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- Waits for row 1
-- Deadlock: Tx1 waits for Tx2, Tx2 waits for Tx1

-- PostgreSQL detects the cycle, aborts Tx2 with error 40P01
-- Tx1 commits. Tx2 must retry.

Deadlocks are a direct consequence of atomicity plus isolation: each transaction holds locks until it commits. The fix is simple but easy to forget. Always lock rows in the same order across transactions. Lock id=1 before id=2 in every transfer, and the cycle cannot form.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?