ACID Properties

What ACID actually guarantees

ACID is the contract a database signs with you when you wrap statements in BEGIN; ... COMMIT;. Each letter buys you protection against a specific failure mode that would otherwise leave your data corrupt.

• Atomicity. Either every statement in the transaction takes effect, or none do. If the server crashes halfway through a money transfer, the debit is rolled back along with the credit. Implemented via undo logs or rollback segments.
• Consistency. The transaction moves the database from one valid state to another. Foreign keys, NOT NULL, CHECK constraints, and triggers all fire; if any fails, the transaction aborts. The database does not invent business rules — it enforces what you declared.
• Isolation. Concurrent transactions don't see each other's half-finished work. The strictest level (serializable) makes the result equivalent to running transactions one at a time. Weaker levels trade correctness for throughput.
• Durability. Once COMMIT returns success, the change survives a crash, power loss, or disk failure. Implemented by flushing the write-ahead log to non-volatile storage before acknowledging the commit.

The four are not independent. Atomicity is implemented using the same undo log that supports isolation; durability needs a flushed log that records both. In practice you implement ACID by implementing a write-ahead log and a concurrency-control scheme, and the four properties fall out.

Isolation levels — the dial you actually turn

Of the four letters, "I" is the one applications interact with most. SQL's standard defines four levels, plus snapshot isolation (added by every modern engine but never standardized). Each level rules out specific anomalies at a measurable throughput cost.

Level	Dirty read	Non-repeatable read	Phantom read	Write skew	Throughput vs read-committed
Read uncommitted	Possible	Possible	Possible	Possible	~1.05×
Read committed	Prevented	Possible	Possible	Possible	1× (baseline)
Repeatable read	Prevented	Prevented	Possible*	Possible	~0.7×
Snapshot	Prevented	Prevented	Prevented	Possible	~0.6×
Serializable	Prevented	Prevented	Prevented	Prevented	~0.1-0.2×

*PostgreSQL's repeatable-read is implemented as snapshot isolation, so phantoms are also prevented there. MySQL InnoDB blocks phantoms with next-key locks even at repeatable-read.

The throughput numbers come from TPC-C-style benchmarks at high contention. In low-contention workloads the gap narrows because serializable rarely needs to abort and retry. The lesson: pick the weakest level that still rules out the anomaly your application can't tolerate. Banking ledgers run serializable. Analytics dashboards happily run read-committed.

ACID vs BASE vs eventual consistency

	ACID	BASE	Eventual consistency
Reads after commit	Always see latest write	May see stale	Converge over time
Multi-row atomicity	Yes	Single-key only	None
Availability under partition	Sacrificed	Preserved	Preserved
Throughput ceiling	Limited by coordination	Linear with nodes	Linear with nodes
Conflict handling	Locks or aborts	Last-write-wins or CRDT	Vector clocks, gossip
Canonical engine	PostgreSQL, Oracle	Cassandra, DynamoDB	Riak, S3 (legacy)
Right for	Money, inventory	Like counts, sessions	DNS-like caches

Transaction with savepoint — JavaScript

// node-postgres: transaction with a savepoint and partial rollback
import { Pool } from 'pg';
const pool = new Pool();

async function transferWithBonus(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);

    // Try a bonus credit, but it's optional — roll it back without aborting the transfer.
    await client.query('SAVEPOINT bonus');
    try {
      await client.query('UPDATE accounts SET balance = balance + 1 WHERE id = $1 AND tier = $2', [toId, 'gold']);
    } catch (err) {
      await client.query('ROLLBACK TO SAVEPOINT bonus');
    }

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

The SAVEPOINT creates a nested rollback target. If the bonus update fails (constraint violation, deadlock), only the bonus is rolled back; the transfer commits intact. Without savepoints, any error inside a transaction forces you to roll back the entire batch.

Transaction with savepoint — Python

import psycopg
from psycopg.errors import SerializationFailure

def transfer_with_bonus(conn, from_id, to_id, amount):
    for attempt in range(5):
        try:
            with conn.transaction():               # BEGIN ... COMMIT/ROLLBACK
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                             (amount, from_id))
                conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                             (amount, to_id))

                # Nested transaction = SAVEPOINT under the hood
                try:
                    with conn.transaction():
                        conn.execute(
                            "UPDATE accounts SET balance = balance + 1 "
                            "WHERE id = %s AND tier = 'gold'", (to_id,))
                except Exception:
                    pass   # bonus failed; transfer still commits
            return
        except SerializationFailure:
            continue       # serializable conflict — retry
    raise RuntimeError("transfer failed after 5 retries")

Note the retry loop. Serializable transactions can fail at COMMIT with a serialization error even if every statement succeeded — the database aborts the transaction whose commit would have violated serializability. Applications must be prepared to retry; production code without a retry loop will see spurious 1-2% failure rates under contention.

Variants — how each engine implements ACID

• PostgreSQL: default isolation is read-committed. REPEATABLE READ is implemented as snapshot isolation; SERIALIZABLE uses Serializable Snapshot Isolation (SSI), which detects dangerous read-write cycles and aborts the offending transaction.
• MySQL InnoDB: default is repeatable-read with next-key locks (gap locks on indexes), which prevents phantoms at the cost of more lock contention than Postgres.
• Oracle: default is read-committed; "serializable" is actually snapshot isolation. Has no true serializable mode out of the box.
• SQL Server: default is read-committed with locking; offers READ COMMITTED SNAPSHOT as an MVCC alternative, plus a true serializable mode using key-range locks.
• SQLite: serializable by default (single-writer model — only one transaction can write at a time). Cheap to reason about, but doesn't scale beyond a single host.
• CockroachDB / FoundationDB / Spanner: serializable by default at the cluster level, paying for it with two-phase commit and synchronous replication.

What the guarantees cost

Every promise in ACID has a price. Atomicity costs an undo log on every write. Durability costs an fsync on commit (~1ms on SSD, ~10ms on spinning disk) — this is why batch inserts are 100× faster than single-row inserts wrapped in their own transactions. Isolation costs locks or version chains; on a 64-core machine running TPC-C, raising isolation from read-committed to serializable typically drops throughput from ~80k tx/s to ~10-15k tx/s.

The good news: durability cost amortizes across batched commits via group commit, and isolation cost shrinks under low contention. The bad news: you usually find out about these costs only when the system is already in production.

Common bugs and edge cases

• Phantom read at repeatable-read. SQL standard allows phantoms at this level. A range query WHERE age BETWEEN 20 AND 30 can return a different row count if a concurrent transaction inserts a matching row. Postgres and MySQL both prevent it, but the standard doesn't require them to.
• Write skew at snapshot isolation. Two transactions read the same data, each writes a different row, and both commit. Classic example: two on-call doctors both check "are there other doctors on duty?" (yes, the other one) and both go off-duty. Snapshot isolation allows this; serializable forbids it.
• Long-running transactions. Holding a snapshot open for hours blocks vacuum/garbage collection of old row versions, bloating the database. Set statement and idle-in-transaction timeouts.
• Implicit commits on DDL. In MySQL, CREATE TABLE inside a transaction silently commits the transaction first. Postgres handles DDL transactionally; MySQL doesn't (mostly).
• Trusting "serializable" labels. Oracle's serializable isn't. MySQL's repeatable-read isn't repeatable-read in the SQL-standard sense. Read your engine's docs, not the keyword.
• Forgetting to handle serialization failures. Code that issues BEGIN ISOLATION LEVEL SERIALIZABLE without a retry loop hits spurious 40001 errors in production and looks like a flaky bug.

When ACID is worth the cost

• Money, inventory, bookings — anything where a lost or duplicated update is a bug a human has to fix.
• Workflows that span multiple rows or tables and must be undone together on failure.
• Reporting that needs a consistent snapshot, not a moving target.

Skip ACID — or downgrade to BASE — when stale reads are tolerable, single-key writes are enough, and your scale demands more nodes than two-phase commit can coordinate. Twitter's like counter doesn't need serializability. Your bank's ledger does.