ACID: unpacking four letters I kept seeing everywhere

Every introduction to relational databases eventually presents four letters:

A C I D

They expand into:

• Atomicity
• Consistency
• Isolation
• Durability

That part is easy to remember.

The harder part is understanding what each word actually promises.

My first interpretation was something vague and reassuring:

ACID means the database keeps everything safe and correct.

That is comforting.

It is also too imprecise to help me design anything.

Safe from what?

Correct according to which rules?

Isolated from whom?

Durable through which failures?

And what exactly receives these guarantees?

The answer begins with the transaction.

ACID describes properties of database transactions.

So before unpacking the four letters, I need something concrete to protect.

1. Transactions define the boundary

Suppose an order contains two products:

order 5001
│
├── 1 × Gopher reading chair
└── 2 × Linux penguin cushions

Creating that order might require several database changes:

INSERT INTO orders (
    order_id,
    customer_id,
    status,
    total_minor,
    currency
)
VALUES (
    5001,
    72,
    'pending',
    31300,
    'GBP'
);

INSERT INTO order_lines (
    order_id,
    product_id,
    quantity,
    unit_price_minor
)
VALUES
    (5001, 'chair-42', 1, 24900),
    (5001, 'cushion-17', 2, 3200);

INSERT INTO order_status_history (
    order_id,
    status
)
VALUES (
    5001,
    'pending'
);

These are separate statements, but from the application’s perspective they represent one business change:

Create order 5001.

I do not want the database to contain an order header without lines, lines without an order, or an order with no initial status history.

The statements belong together, so I can place them inside a transaction:

BEGIN;

INSERT INTO orders (...);

INSERT INTO order_lines (...);
INSERT INTO order_lines (...);

INSERT INTO order_status_history (...);

COMMIT;

Conceptually:

BEGIN
  │
  ├── create order
  ├── create order lines
  ├── create status history
  │
COMMIT

If something prevents the transaction from completing, I can roll it back:

ROLLBACK;

That transaction is the object to which ACID applies.

The application must choose the boundary

Everything between BEGIN and COMMIT belongs to one explicit transaction.

Without an explicit transaction block, many database clients use autocommit. Each successful statement becomes its own transaction.

This:

INSERT INTO orders (...);

INSERT INTO order_lines (...);

may therefore behave as two independent transactions:

transaction 1
    │
    └── insert order
        commit

transaction 2
    │
    └── insert order line
        commit

If the second statement fails, the first may already be committed.

The database cannot infer that I intended the two statements to form one indivisible business operation. I have to define that boundary.

ACID protects the transaction I actually create, not the larger workflow I merely had in mind.

2. Atomicity: all or nothing

Atomicity means the transaction takes effect as one unit.

Either all of its database changes commit, or none of them become committed changes.

For the order example, the successful outcome is:

• the order is created;
• its order lines are created;
• its initial status history is created.

The failed outcome is:

• no order is created;
• no order lines are created;
• no status history is created.

What atomicity prevents is a permanently committed partial result, such as an order with missing lines.

Atomicity is about the committed effect

The database still performs work over time:

statement 1
    │
    ▼
statement 2
    │
    ▼
statement 3

The statements do not physically happen in one indivisible instant.

Atomicity means their committed effect is all or nothing. Other transactions should not receive a permanently committed half-order because the application failed after statement two.

Database engines use mechanisms such as transaction logs, undo information and recovery processing to provide that guarantee across ordinary failures.

Rollback discards the transaction’s database changes

Suppose the application discovers that one order line is invalid:

BEGIN;

INSERT INTO orders (...);

INSERT INTO order_lines (...);

-- Product validation fails here.

ROLLBACK;

The transaction’s database changes are discarded.

That does not mean the database is rewound byte for byte. Some database-managed values, such as sequence numbers, may still advance even when a transaction rolls back. The important guarantee is that the transaction’s intended data changes do not become committed state.

Atomicity stops at the database boundary

Now imagine this workflow:

1. insert the order;
2. call a payment provider;
3. insert the payment reference;
4. commit.

Suppose the payment provider successfully charges the customer, then the database transaction rolls back.

The database can undo its own inserts. It cannot reach into the external payment provider and reverse the charge merely because SQL executed ROLLBACK.

A rollback also does not automatically unsend an email, retract a message already published to a broker, reverse an HTTP request, or delete a file written elsewhere.

So atomicity means:

All or nothing for changes controlled by this database transaction.

It does not mean all or nothing for every side effect in a distributed workflow.

The transaction boundary is real. It is not magical elastic wrapped around the whole architecture.

3. Consistency: preserving declared invariants

Consistency is the letter I found most confusing.

The usual explanation is:

A transaction takes the database from one consistent state to another consistent state.

That sounds sensible until I ask:

What makes a state consistent?

The database does not know Borough Furniture Store’s business rules by intuition.

It only knows the rules expressed through its schema, constraints, transaction mechanisms, database logic and application behaviour.

Suppose an order-line quantity must be positive:

quantity integer NOT NULL CHECK (quantity > 0)

Suppose an order line must refer to an existing order:

FOREIGN KEY (order_id)
    REFERENCES orders(order_id)

Suppose each product may appear only once within an order:

PRIMARY KEY (order_id, product_id)

These constraints define part of what valid database state means.

Consistency depends on invariants

An invariant is a condition that must remain true when state changes.

Examples include:

• an order-line quantity must be positive;
• an order must refer to a valid customer;
• a capture request must not be applied twice;
• a reservation must not reduce unallocated stock below zero;
• an order total must match its accepted lines, taxes, discounts and charges.

A transaction should preserve the relevant invariants:

VALID STATE
    │
    │ transaction obeying rules
    ▼
VALID STATE

A transaction that violates an enforced constraint should not commit.

For example:

INSERT INTO order_lines (
    order_id,
    product_id,
    quantity
)
VALUES (
    5001,
    'chair-42',
    -3
);

If the schema contains CHECK (quantity > 0), the database rejects the change.

The constraint turns a business or structural rule into an enforceable database rule.

ACID does not invent business correctness

Suppose nothing prevents this:

UPDATE orders
SET total_minor = -5000
WHERE order_id = 5001;

The database may execute the statement perfectly.

The transaction can be atomic, isolated and durable while still containing nonsense according to the business.

ACID does not know that a normal order total should not be negative unless the model expresses that rule or the application prevents the change.

A database can reliably preserve an incorrect fact.

Durable nonsense is still nonsense, only now with excellent crash recovery.

A more precise way to think about consistency is:

Consistency assumes the database begins in a valid state and the transaction correctly implements the relevant invariants. The database can directly enforce only the rules expressed through its constraints and transaction mechanisms.

Application logic participates in consistency

Not every invariant fits neatly into one column constraint.

Suppose confirming a shippable order requires:

• at least one order line;
• a valid delivery address;
• an accepted total;
• a permitted current status.

The application may need to load the order, inspect its state, validate the transition, update several records and commit them together.

APPLICATION
    │
    ├── understands the business transition
    └── requests valid changes
             │
             ▼
DATABASE
    │
    ├── enforces encoded constraints
    ├── controls the transaction
    └── preserves committed state

Consistency is therefore a collaboration between a correct model, enforced constraints and correct transaction logic.

Consistency and isolation meet at concurrency

Atomicity and consistency are not the same.

Atomicity asks:

Did the transaction’s changes happen as one unit?

Consistency asks:

Did the resulting state obey the required rules?

But consistency-sensitive logic can also depend on isolation.

Two transactions might each observe that a shared total is below a limit, then both add values that collectively exceed it. Each transaction may appear valid on its own, while the concurrent result violates the invariant.

That is why some rules require not only correct validation logic, but also a suitable isolation level or an explicit locking strategy.

4. Isolation: controlling concurrency

Isolation matters when more than one transaction operates at the same time.

Suppose two customers attempt to reserve the final Gopher reading chair.

Initial state:

physical stock = 1
available quantity = 1
active reservations = 0

Transaction A reads available quantity = 1.

Transaction B also reads available quantity = 1.

Both decide that one chair can be reserved. Both create a reservation and write available quantity = 0.

The resulting state may be:

physical stock = 1
available quantity = 0
active reservations = 2

Each transaction looked reasonable by itself. Together they accepted two reservations for one chair.

This is a concurrency problem.

Concurrent transactions can interfere

Without suitable isolation, a transaction may experience anomalies such as:

• reading uncommitted changes;
• reading the same row twice and receiving different committed values;
• repeating a query and receiving a different set of rows;
• overwriting another transaction’s update;
• making a decision from a combination of states that could not have existed in any serial execution.

Isolation controls how concurrent transactions observe and affect one another.

The strongest standard isolation level, Serializable, aims to ensure that successfully committed concurrent transactions have the same effect as some order in which those transactions ran one at a time.

Isolation is not a giant lock

The database still wants concurrency. Running every transaction strictly one after another would waste opportunities for independent work to proceed together.

Database engines therefore use combinations of:

• locks;
• multiple row versions;
• snapshots;
• conflict detection;
• aborted transactions and retries.

Conceptually:

TRANSACTION A ───────────────►

        TRANSACTION B ───────────────►

        overlap in time

but the database controls
visibility and conflicts

Isolation is a concurrency-control contract, not simply a giant door lock placed on the entire database.

Isolation levels

SQL databases commonly expose these standard names:

The precise behaviour differs between database engines, so the engine’s own documentation matters. PostgreSQL, MySQL, SQL Server, Oracle and SQLite do not necessarily implement identically named levels in identical ways.

Stronger levels prevent more anomalies, but they can also introduce additional locking, waiting, conflicts, aborted transactions and required retries.

Common concurrency anomalies

A dirty read occurs when one transaction reads another transaction’s uncommitted changes.

For example, transaction A changes a price to £249 but has not committed. Transaction B reads £249 and makes a decision. Transaction A then rolls back. Transaction B acted on a value that never became committed state.

Read Committed prevents dirty reads, but it does not necessarily give every statement in one transaction the same snapshot. Each statement sees data according to that engine’s Read Committed semantics.

A non-repeatable read occurs when the same row is read twice and returns different committed values because another transaction changed it between the reads.

SELECT status
FROM orders
WHERE order_id = 5001;

Transaction A may first read pending. Transaction B commits confirmed. Transaction A repeats the query and, depending on its isolation level, may now see confirmed.

A phantom read concerns a changing result set rather than one existing row changing value.

SELECT *
FROM reservations
WHERE product_id = 'chair-42'
  AND status = 'active';

The first execution returns three rows. Another transaction inserts a matching reservation and commits. The repeated query returns four rows.

Isolation levels determine which of these phenomena may occur.

Serializable is a behavioural guarantee

Suppose transactions A and B overlap:

A begins
    B begins
A reads
    B reads
A writes
    B writes
A commits
    B attempts commit

If the combined outcome could not have occurred with A then B, or B then A, the database may reject one transaction.

That rejection is not the database malfunctioning. It is the database preserving the isolation guarantee.

transaction begins
      │
      ▼
perform reads and writes
      │
      ▼
serialization conflict?
   │             │
  no            yes
   │             │
   ▼             ▼
commit         rollback
                  │
                  ▼
             retry transaction

Strong isolation often moves complexity from subtle data anomalies into explicit retry handling.

Isolation cannot repair the wrong transaction boundary

Suppose an application performs:

1. transaction one checks stock;
2. transaction two creates the reservation.

Another customer can change the stock state between those transactions.

Both steps were transactional, but the business decision crossed two transaction boundaries. The isolation guarantee of transaction one ended when it committed.

To protect the decision, the check and state change may need to occur within one correctly isolated transaction:

BEGIN
  │
  ├── inspect relevant stock state
  ├── validate reservation
  └── update stock and reservation
COMMIT

Again, ACID protects the boundary I create. It does not reconstruct the boundary I should have created.

5. Durability: surviving covered failures

Durability concerns what happens after the database reports that a transaction committed.

Suppose the application receives:

COMMIT successful

Then the database process crashes immediately afterwards.

Durability means the committed transaction should survive recovery according to the database’s configured guarantees.

transaction commits
      │
      ▼
database acknowledges commit
      │
      ▼
process or machine fails
      │
      ▼
database recovers
      │
      ▼
committed transaction remains

Without durability, a customer could receive “Order 5001 confirmed” only for the order to disappear after a restart.

The acknowledgement would have described a state the system could not preserve.

How durable commit is commonly implemented

Database engines do not usually force every changed table page into its final location before acknowledging each transaction. That would make transaction processing painfully expensive.

Instead, engines commonly use a transaction log, such as a redo log or write-ahead log:

transaction changes database pages
          │
          ▼
record required changes in log
          │
          ▼
make commit record durable
          │
          ▼
acknowledge commit
          │
          ▼
write changed pages later

After a crash, the database can use the log to restore committed changes that had not yet reached their final data files.

The exact mechanism varies by engine. The important contract is that the database has persisted enough recovery information before declaring the transaction committed.

Durability depends on configuration and hardware

Durability is not created by the word COMMIT alone.

It depends on a chain involving:

• database configuration;
• transaction logging;
• operating-system writes;
• filesystem behaviour;
• storage-device caches;
• hardware;
• replication policy.

Some settings trade durability strength for performance. Delaying or reducing synchronous log flushing may improve throughput while increasing the amount of recently acknowledged work that can be lost during particular failures.

Useful questions include:

• What must be persisted before commit is acknowledged?
• Which failure types are covered?
• Does the operating system really flush the data?
• Does the storage device honour that request?
• Is replication synchronous or asynchronous?
• Which settings weaken the default guarantee?

Local durability and replicated durability are also different promises. A transaction may be durable on the primary yet still be absent from an asynchronous replica when the primary fails.

“Uses an ACID database” is not the end of the operational conversation.

What durability does not guarantee

Durability is not the same as a backup.

A durable transaction can survive a crash and still be lost later because somebody deleted it, a faulty migration corrupted it, ransomware encrypted the storage, the entire storage system was destroyed, or an operator restored the wrong snapshot.

The database may durably preserve the deletion or corruption because those were valid committed transactions from its perspective.

A durable database still needs backups, restore testing, retention policies and disaster-recovery planning.

A transaction can also be safely durable while the database remains unavailable for an hour. The data survived; users still cannot reach it.

Replication and failover may improve availability, but they introduce their own questions about lag, acknowledgement policy, promotion and possible data loss.

6. ACID across system boundaries

Suppose an order transaction commits, then the application attempts to publish OrderCreated to a message broker.

If the application crashes between those operations, the database contains the order but the message was never published.

DATABASE
    │
    └── durable order


MESSAGE BROKER
    │
    └── no OrderCreated event

The database transaction was durable. The wider workflow is incomplete.

This is why patterns such as the transactional outbox exist. The application stores both the business change and a record of the message to publish inside one local transaction.

BEGIN;

INSERT INTO orders (...);

INSERT INTO outbox_messages (
    message_type,
    aggregate_id,
    payload
)
VALUES (
    'OrderCreated',
    '5001',
    '{...}'
);

COMMIT;

ACID protects those local records together. A separate process publishes the outbox message later.

Even then, the message broker and consumers need delivery policies, retries and duplicate handling.

One local ACID transaction is a strong building block. It cannot make several independent systems share one effortless commit.

7. Using ACID well

Returning to order creation:

BEGIN;

INSERT INTO orders (...);

INSERT INTO order_lines (...);

INSERT INTO order_status_history (...);

COMMIT;

The four letters contribute different guarantees:

Together:

VALID INITIAL STATE
        │
        ▼
TRANSACTION
        │
        ├── atomic unit
        ├── preserves declared rules
        ├── controls concurrency
        └── creates a durable commit
        │
        ▼
VALID COMMITTED STATE

But ACID does not mean every transaction is well designed.

A transaction can still be:

• too large;
• too long-running;
• highly contended;
• missing required constraints;
• using an unsuitable isolation level;
• mixing unrelated business operations.

This would be a poor transaction:

BEGIN

load 100,000 orders
call external payment provider
wait for user input
update inventory
send email
generate report

COMMIT

It keeps transactional resources, and possibly locks, open while waiting on slow or unpredictable work.

A transaction should normally contain the database work that must form one local consistency boundary.

Useful design questions include:

• Which changes must commit together?
• Which invariants must be protected?
• Which rows may be accessed concurrently?
• Which isolation level supports those assumptions?
• How long can the transaction remain open?
• Which work belongs outside it?

ACID is not permission to place the entire afternoon inside BEGIN.

Transactions may need safe retries

Concurrency control can cause a transaction to abort.

A deadlock detector may choose one participant to roll back. Serializable isolation may reject an execution that cannot safely commit. A connection may fail before the application receives the commit result.

A retry should normally repeat the whole transaction, because its reads and decisions belonged to one attempted execution.

ATTEMPT 1

begin
read current state
make decision
write changes
commit fails due to conflict


ATTEMPT 2

begin again
read new current state
make decision again
write changes
commit

Reusing conclusions from the failed attempt may defeat the purpose of rerunning against current state.

External side effects must also not be repeated accidentally. Retrying a database transaction is different from safely retrying a payment request or email. Those need application-level idempotency.

A transaction is not a lock around reality

Suppose inventory says one chair is available.

A database transaction can protect the internal reservation state. It cannot guarantee that the chair was not physically damaged, that the warehouse counted correctly, that a supplier did not issue a recall, or that another external system holds no conflicting promise.

The database provides controlled state transitions for the information it owns.

It does not make the model identical to the physical world.

Consistency is always consistency of a represented system under defined rules.

That is still immensely valuable. It is simply not omniscience.

8. The mental model I am keeping

My old model was:

ACID means the database is safe.

The new model is:

Around all four sits the transaction boundary:

                   TRANSACTION
                        │
        ┌───────────────┼───────────────┐
        │               │               │
        ▼               ▼               ▼
   database work   encoded rules   concurrent work
        │               │               │
        └───────────────┼───────────────┘
                        ▼
                  committed result
                        │
                        ▼
                     recovery

The boundary determines what the database can protect.

The schema and application define the rules to preserve.

The isolation level determines which concurrent behaviours are permitted.

The durability configuration determines what “committed” means under failure.

The application still has to:

• choose the correct transaction boundary;
• express business invariants;
• choose an appropriate isolation level;
• handle concurrency failures;
• retry safely;
• coordinate external systems;
• maintain backups and recovery procedures.

ACID is neither a decorative database badge nor a complete architecture.

It is a set of powerful local guarantees.

Used carefully, those guarantees let an application make a meaningful promise:

Order 5001 either exists completely or does not exist, respects the order model, was committed under a defined concurrency policy, and will still exist after database recovery.

Four letters.

A much more specific promise than “the data should probably be fine”.

References and further reading

MySQL and the ACID model

MySQL 8.0 Reference Manual: InnoDB and the ACID Model
Connects atomicity, consistency, isolation and durability to InnoDB transactions, locking, crash recovery, redo behaviour, storage configuration and backup strategy.

PostgreSQL transactions

PostgreSQL 13: Transactions
Introduces transactions as all-or-nothing operations, explains BEGIN, COMMIT, ROLLBACK, autocommit and savepoints, and describes the visibility and durability expected from committed work.

PostgreSQL 13: BEGIN
Documents explicit transaction blocks and PostgreSQL’s default treatment of standalone statements as individual transactions.

PostgreSQL 13: COMMIT
Documents completion of the current transaction and making its changes durable and visible.

PostgreSQL 13: ROLLBACK
Documents aborting the current transaction and discarding its updates.

Transaction isolation

PostgreSQL 13: Transaction Isolation
Defines dirty reads, non-repeatable reads, phantom reads and serialization anomalies, then documents PostgreSQL’s Read Committed, Repeatable Read and Serializable behaviour.

PostgreSQL 13: Explicit Locking
Explains table-level, row-level and advisory locks and how transactions may wait for or conflict with concurrent work.

MySQL 8.0 Reference Manual: InnoDB Transaction Isolation Levels
Documents InnoDB’s supported isolation levels and the locking or consistent-read behaviour associated with each.

Durability and recovery

PostgreSQL 13: Write-Ahead Logging
Introduces write-ahead logging and the requirement that log records reach durable storage before changed data pages are written.

PostgreSQL 13: Reliability
Discusses the operating-system and storage behaviour required for reliable write-ahead logging and durable database operation.

MySQL 8.0 Reference Manual: InnoDB Redo Log
Explains the redo log used during crash recovery to replay changes from incomplete writes.

Atomic commit mechanics

SQLite: Atomic Commit
Provides a detailed walkthrough of how a database can make many physical storage writes appear as one atomic transaction, including recovery after crashes and power failures.

SQLite Is Transactional
Summarises SQLite’s atomic, consistent, isolated and durable transaction guarantees.