Suggest an edit

Improve this article

Refine the answer for “ACID - atomicity”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

**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.

### Key difference

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.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts1395 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.