ACID Properties Explained: How SQL Databases Guarantee Atomicity, Consistency, Isolation, and Durability

A transfer is one decision, even when the database performs many writes

Imagine a bank transferring $500 from account A to account B. The application must debit one row and credit another. A process crash between those writes must not leave money missing; two concurrent transfers must not both spend the same balance; and once the database confirms a commit, a restart must not erase it.

ACID names four guarantees that make this transaction dependable. They solve different problems, and no single setting or storage structure provides all four. We will follow one transfer through validation, concurrent execution, commit, and recovery to see where each guarantee comes from.

sequenceDiagram
    title Banking Transaction ACID Lifecycle
    participant App as Banking API
    participant DB as SQL Database
    participant WAL as Durable Log
    participant Disk as Data Files
    App->>DB: BEGIN; debit A; credit B
    DB->>DB: Check constraints and isolation
    DB->>WAL: Append transaction records
    WAL-->>DB: Commit record is durable
    DB-->>App: COMMIT acknowledged
    Note over WAL,Disk: Data pages may be written later; recovery replays durable log records

The important ordering is that a database can acknowledge a commit before every changed table page reaches its final location. It first makes the log record durable according to its configured guarantee. Recovery uses that record after a crash. This is why a database can combine fast sequential log writes with safe updates to many data pages.

Stage 1 — Atomicity: make the transfer all-or-nothing

The application starts a transaction, debits account A, credits account B, and commits. If the second update fails or the process aborts before commit, the database rolls back the first update. Atomicity describes the result visible to the transaction’s participants: both changes commit, or neither does.

Databases use transaction logs and recovery records to implement this behavior. PostgreSQL’s write-ahead log (WAL) records changes so crash recovery can redo committed work and discard incomplete work. InnoDB uses redo logging for recovery and undo records to roll back transactions and provide older row versions. The details differ by engine, but the application-level guarantee is the same.

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 'A' AND balance >= 500;

-- The application checks that exactly one row was updated.
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 'B';

COMMIT;

The first update’s predicate is part of the business rule, but the application must still verify the affected-row count and roll back if the debit did not happen. A transaction boundary cannot repair incorrect application logic by itself.

Stage 2 — Consistency: preserve invariants at transaction boundaries

Consistency means a committed transaction leaves the database in a state that satisfies the rules the application and schema define. It is not a promise that every business rule is automatically known to the database. Put durable invariants in constraints where possible, and validate the remaining rules in transaction logic.

For example, a CHECK (balance >= 0) constraint prevents a committed negative balance. A foreign key can ensure that a transfer references existing accounts. Some rules depend on multiple rows and need locking or serializable execution; a row-level check alone cannot enforce every cross-row invariant.

CREATE TABLE accounts (
  account_id text PRIMARY KEY,
  balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfers (
  transfer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_account text NOT NULL REFERENCES accounts(account_id),
  to_account text NOT NULL REFERENCES accounts(account_id),
  amount numeric(12, 2) NOT NULL CHECK (amount > 0)
);

The C in ACID is about application-defined validity of committed data. It is different from consistency in the CAP theorem, which concerns whether distributed replicas return the same view during network partitions.

Stage 3 — Isolation: define what concurrent transactions can observe

While the transfer runs, another request may read the accounts or attempt a competing withdrawal. Isolation controls which effects concurrent transactions can observe and which anomalies the database prevents. Implementations commonly combine locks, multiversion concurrency control (MVCC), or both.

With MVCC, a reader can often see a stable snapshot while writers create newer row versions. This improves read/write concurrency, but the exact anomalies prevented depend on the isolation level and database engine. PostgreSQL REPEATABLE READ uses snapshot isolation and can still allow write skew; PostgreSQL SERIALIZABLE uses Serializable Snapshot Isolation to detect dangerous dependency patterns and abort one transaction when necessary. Other engines define their isolation behavior differently, so applications should consult the engine’s documentation rather than infer guarantees from the level name alone.

sequenceDiagram
    title Serializable Transaction Isolation Validation
    participant T1 as Transaction 1
    participant T2 as Transaction 2
    participant DB as Database
    T1->>DB: Read invariant and current rows
    T2->>DB: Read overlapping snapshot
    T1->>DB: Write row A
    T2->>DB: Write row B
    DB->>DB: Validate conflicts at serializable commit
    DB-->>T1: Commit, or serialization failure
    DB-->>T2: Commit, or serialization failure

Serializable transactions may be retried after a serialization failure. That retry must repeat the whole transaction from a fresh snapshot and must not duplicate external side effects. If the application sends a payment-provider request inside the transaction, use an idempotency key or an outbox-style handoff so a database retry does not charge twice.

Stage 4 — Durability: make an acknowledged commit survive the failures you promise to handle

Durability means committed work survives failures covered by the database’s operating and storage assumptions. In PostgreSQL, WAL records are flushed before the corresponding changed data pages need to be written. A restart replays durable records to reconstruct committed state. Group commit can amortize a flush across multiple transactions.

The guarantee depends on configuration and infrastructure. For example, PostgreSQL synchronous_commit and InnoDB innodb_flush_log_at_trx_commit affect when commit acknowledgements wait for log flushing. Disabling or weakening a flush may improve latency, but can lose recently acknowledged transactions after a host or power failure. Replication durability is a separate choice: local log durability does not by itself guarantee that another region has received the commit.

`` A[Transaction changes rows] --> B[Append log records] B --> C{Commit record flushed?} C -->|Yes| D[Return commit acknowledgement] C -->|No| E[Do not claim durable commit] D --> F[Write data pages later] F --> G[Checkpoint limits recovery work] H[Crash and restart] --> I[Replay durable committed log] I --> J[Discard incomplete transaction effects] ```

How the four guarantees work together

ACID is therefore a composition of transaction boundaries, constraints, concurrency control, and recovery. A transaction can be atomic but still commit a logically invalid result if the application never checked the right invariant. A schema can enforce consistency while a weak isolation level still permits a race. A correct isolated transaction can still lose acknowledged work if durability settings do not match the failure promise.

Choosing isolation and durability deliberately

Start with the invariant and failure model, then choose settings that satisfy them. Use constraints for row-local rules. Use explicit locking or serializable isolation when correctness depends on concurrent decisions across rows. Handle serialization failures as expected retryable outcomes. Keep durable commit flushing enabled for transactions whose acknowledgements must survive a host crash, and document any weaker guarantee for disposable or rebuildable data.

Measure the actual workload before trading correctness for throughput. WAL flush latency, lock contention, transaction length, checkpoint activity, and retry rates point to different bottlenecks. Group commit, batching, shorter transactions, and appropriate indexes can improve throughput without silently changing the failure guarantee.

What this scenario does not guarantee

ACID applies to the database transaction’s committed state. It does not automatically make a payment provider call atomic with the database, guarantee cross-region replication, prevent every application bug, or make backups unnecessary. Those require additional design: idempotency, outbox/inbox processing, replication policies, validation, and tested backups.

The transfer is safe when its schema expresses durable invariants, its transaction includes all database writes that must succeed together, its isolation level prevents the relevant races, and its commit configuration matches the failure promise made to callers. That is the practical meaning of ACID: a precise contract between the application, database engine, and storage system.

• ACID is four contracts, not one feature: atomicity, consistency, isolation, and durability each protect a different failure class.
• Put durable invariants in constraints where you can; isolation and application logic must cover the rest.
• Isolation level names are not portable across engines — verify what anomalies your database actually prevents.
• Durability is configuration-dependent: a commit acknowledgement only survives the failures your fsync and replication settings promise.
• Prefer shorter transactions, correct constraints, and deliberate isolation over weakening commit durability for speculative throughput gains.