ACID

ACID stands for Atomicity, Consistency, Isolation and Durability: four guarantees a database makes about transactions. Together they mean your data never ends up half-updated or corrupted, even if the power fails mid-write or thousands of users change the same rows at once. They are the main reason relational databases are the default for money, orders and identity.

What is a transaction?#

A transaction is a group of operations that must succeed or fail as a single unit. Never some of them.

The classic example is a bank transfer of $500 from account A to account B:

If only operation 1 happens, A has lost $500, B never received it, and $500 has vanished.

In SQL you wrap both statements in a transaction:

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;

There are only three possible outcomes:

1. Everything succeeds → COMMIT → the changes are permanent.
2. Something fails → ROLLBACK → as if nothing happened.
3. The system crashes mid-transaction → on restart, the database rolls it back automatically.

Never a partial result. That is the whole point.

A real pipeline has the same shape. Storing a moderation result, updating the content's status and writing an audit log should happen together. If the result is saved but the status update fails, the data is inconsistent.

Relational databases make this easy with multi-statement transactions. Many NoSQL stores only guarantee atomicity for a single item by default. DynamoDB, for example, offers multi-item transactions through a separate transactional API, so you have to opt in deliberately.

A: Atomicity (all or nothing)#

A transaction either completes entirely or has no effect.

Without atomicity:

Result: $500 has disappeared.

With atomicity: on restart the database sees an unfinished transaction, undoes step 1, and A is back to $1000. Nothing happened, and the transfer can be retried.

How it works: the write-ahead log (WAL)#

The database doesn't change its data files first. It first appends a description of the change to a log on disk, and only then applies the change. Because the log is written ahead of the data, it's called a write-ahead log.

For the transfer, the WAL on disk holds these records, in order:

Only then are the changes applied to the table files, possibly later.

After a crash, the database replays the log:

The rule fits in two lines:

• COMMIT in the log → redo: make sure every change is applied.
• No COMMIT in the log → undo: roll everything back.

This undo/redo picture is the classic textbook model. Real engines vary in the details. PostgreSQL, for example, only replays (redoes) its log, because changes from uncommitted transactions are simply never treated as visible.

Why log first? If you wrote data first and crashed before logging, on restart you'd have no record of what was half done. With the log first, there is always enough information to finish or reverse the work. The log, not the data files, is the source of truth during recovery.

An analogy: when following a recipe, write down each ingredient before adding it. If you're interrupted, your notes tell you exactly where you stopped.

Distributed databases use the same idea. A write is recorded in a replicated log before it is applied to storage nodes, so a crashed node can recover from the log.

C: Consistency (valid state to valid state)#

A transaction moves the database from one valid state to another. All rules (constraints, cascades, triggers) must hold before and after. If a transaction would break a rule, it is rolled back.

For example, A has $300 and you transfer $500 from A to B:

1. Step 1 sets A.balance = 300 - 500 = -200.
2. The rule balance >= 0 is violated.
3. The whole transaction is rolled back.
4. A stays at $300, and B is unchanged.

Two levels of consistency#

Database-level constraints, enforced automatically:

CREATE TABLE accounts (
  id      INT PRIMARY KEY,
  email   VARCHAR(255) UNIQUE NOT NULL,
  balance DECIMAL(12, 2) CHECK (balance >= 0)
);

-- Rejected: email is NULL and balance is negative
INSERT INTO accounts (id, email, balance) VALUES (1, NULL, -500);

Application-level rules, which the database can't know about and your code must enforce:

• Total debits must equal total credits.
• A user may have at most five active orders.
• A discount can't exceed 50% of the price.

If your code has a bug, the database will happily store data that breaks these rules.

Consistency vs atomicity#

• Atomicity is mechanical: did the transaction run completely or not at all?
• Consistency is logical: is the result valid?

You can have one without the other. A transaction can complete fully (atomic) and still set a balance to -$200 (inconsistent) if no rule forbids it. Think of surgery: atomicity asks "did the operation finish?", consistency asks "is the patient healthy afterwards?".

Consistency is the weakest of the four guarantees in practice, because the database can only enforce structural rules. In stores with few built-in constraints (many NoSQL databases have no foreign keys, CHECK or secondary UNIQUE constraints), almost all of it is your application's job.

I: Isolation (work in private)#

When many transactions run at once, isolation stops them from interfering. A transaction shouldn't see another's in-progress changes, and two people shouldn't both be able to buy the last seat on a plane at the same instant.

Databases offer several isolation levels that trade safety for speed. That topic gets its own lesson: Isolation.

D: Durability (committed means permanent)#

Once the database says "committed", the data survives power loss, crashes and hardware failure.

Without durability:

1. COMMIT returns, and "Transfer successful!" is shown to the user.
2. The power fails one second later.
3. After reboot, A = $1000 and B = $200: the transfer is gone.

The user was told it succeeded, but it didn't.

With durability: the database writes to persistent storage before reporting success, so after reboot A = $500 and B = $700.

Three layers of protection#

1. WAL flushed to disk (survives crashes). The key rule: COMMIT is confirmed only after the WAL record is flushed to disk (with fsync), not just sitting in RAM. Data files can be updated later. If the crash happens before the flush, the transaction is lost, but the user was never told it succeeded, so no promise is broken.
2. Replication (survives a dead disk or machine). The WAL is also written to replicas on other machines. If the primary's disk dies, the data is still on the replicas.
3. Backups (survives losing a data centre). Regular backups to another region, plus continuously shipping WAL records, mean you can restore and replay after a disaster, losing at most the last few seconds.

Stacked up, the layers behind one confirmed COMMIT look like this:

All four together#

Here is what each property promises for the $500 transfer from A to B:

• Atomicity: both the debit and the credit happen, or neither does.
• Consistency: balances never go negative and total money is preserved.
• Isolation: others see the state before or after the transfer, never halfway.
• Durability: once committed, the transfer survives crashes and hardware failure.

And in general:

ACID vs BASE#

Many distributed NoSQL systems relax ACID for availability and speed. Their style is often summed up as BASE: basically available, soft state, eventually consistent.

This is a spectrum, not a strict SQL vs NoSQL split. Some NoSQL stores offer transactions, and relational databases can be run with weaker isolation.

Trade-offs#

• Isolation costs throughput. Stronger isolation means more locking or more aborted transactions under contention. Picking the right level (often Read Committed for general traffic, stronger for money movement) balances correctness against performance.
• Durability costs latency. Waiting for a disk flush, and even more for replicas to confirm, adds time to every commit.
• Choosing a relational database for ACID is a strong argument when you can name the exact guarantee you need: "an order must never be marked paid unless inventory was decremented" (atomicity), or "two shoppers must not both buy the last unit during a flash sale" (isolation).

Key takeaways#

• A transaction is all or nothing: commit everything or roll back everything.
• Atomicity and durability both rest on the write-ahead log. Log first, confirm commit after the log is on disk, and redo or undo on restart.
• Consistency means constraints hold. The database enforces structural rules, and your code enforces business rules.
• Isolation controls how concurrent transactions see each other's work, trading safety for throughput.
• Durability is layered: WAL on disk, then replication, then backups.