> ## Documentation Index
> Fetch the complete documentation index at: https://miliracle.online/llms.txt
> Use this file to discover all available pages before exploring further.

# What Are ACID Transactions?

> A bank transfer, a crash and two ATMs: a visual guide to atomicity, consistency, isolation and durability.

export const QUIZ = [{
  q: "Inside a transaction, the server crashes after taking $100 from Linh but before paying Marco. What happens on restart?",
  a: ["Linh stays $100 short", "Linh's update is rolled back", "Marco gets paid anyway"],
  c: 1,
  why: "Atomicity: the transaction never reached COMMIT, so the database undoes all of it."
}, {
  q: "Which property stops a transfer that would push Linh's balance below $0?",
  a: ["Durability", "Isolation", "Consistency"],
  c: 2,
  why: "Consistency: the database checks declared rules, like CHECK (balance >= 0), before it commits."
}, {
  q: "Two withdrawals read the same $100 balance at once, and both succeed. Which property was missing?",
  a: ["Isolation", "Atomicity", "Durability"],
  c: 0,
  why: "Isolation keeps concurrent transactions from acting on each other's stale or half-done data."
}, {
  q: "The app gets COMMIT OK, then the power fails. Is the transfer kept?",
  a: ["Only if the data file was already updated", "Yes, the log on disk is replayed on restart", "No, memory was lost"],
  c: 1,
  why: "Durability: COMMIT OK is only sent after the log record is safely on disk."
}, {
  q: "Which isolation level makes concurrent transactions behave as if they ran one at a time?",
  a: ["Read Committed", "Serializable", "Read Uncommitted"],
  c: 1,
  why: "Serializable is the strongest SQL level. Most databases default to a weaker one, such as Read Committed."
}];

export const SUMMARY = [{
  l: "A",
  n: "Atomicity",
  promise: "All or nothing.",
  without: "A crash leaves $100 missing (Figure 1).",
  how: "Rolls back any transaction that didn't commit."
}, {
  l: "C",
  n: "Consistency",
  promise: "Declared rules always hold.",
  without: "Linh's balance goes below $0 (Figure 2).",
  how: "Checks constraints before every commit."
}, {
  l: "I",
  n: "Isolation",
  promise: "Running together looks like one at a time.",
  without: "Two ATMs pay $160 from $100 (Figure 3).",
  how: "Locks, or cancel-and-retry, at the level you choose (Figure 4)."
}, {
  l: "D",
  n: "Durability",
  promise: "Committed means saved.",
  without: "A power cut erases a confirmed transfer (Figure 5).",
  how: "Writes a log to disk before COMMIT OK, replays it on restart."
}];

export const LAKE_STEPS = [{
  act: null,
  files: [["a.parquet", "live"], ["b.parquet", "live"]],
  log: [["000.json", "+ a.parquet", "setup"], ["001.json", "+ b.parquet", "setup"]],
  jobs: ["idle", "idle"],
  ver: 1,
  msg: "The table is at version 1. Each commit file in _delta_log lists the data files it added."
}, {
  act: "Job A writes c.parquet",
  files: [["a.parquet", "live"], ["b.parquet", "live"], ["c.parquet", "pend"]],
  log: [["000.json", "+ a.parquet", "setup"], ["001.json", "+ b.parquet", "setup"]],
  jobs: ["wrote c.parquet", "idle"],
  ver: 1,
  msg: "Job A wrote c.parquet, but no commit file names it yet, so readers can't see it. If Job A crashed now, the file would just be ignored."
}, {
  act: "Job B writes d.parquet",
  files: [["a.parquet", "live"], ["b.parquet", "live"], ["c.parquet", "pend"], ["d.parquet", "pend"]],
  log: [["000.json", "+ a.parquet", "setup"], ["001.json", "+ b.parquet", "setup"]],
  jobs: ["wrote c.parquet", "wrote d.parquet"],
  ver: 1,
  msg: "Job B writes d.parquet at the same time. Both jobs now want the next commit number: 002."
}, {
  act: "Both jobs try to create 002.json",
  files: [["a.parquet", "live"], ["b.parquet", "live"], ["c.parquet", "live"], ["d.parquet", "pend"]],
  log: [["000.json", "+ a.parquet", "setup"], ["001.json", "+ b.parquet", "setup"], ["002.json", "+ c.parquet", "Job A"]],
  jobs: ["created 002.json ✓", "002.json already exists ✗"],
  tone: ["ok", "bad"],
  ver: 2,
  ok: true,
  msg: "Only one file can be named 002.json, so only one job wins. Job A got there first, and readers now see version 2."
}, {
  act: "Job B checks for conflicts and retries",
  files: [["a.parquet", "live"], ["b.parquet", "live"], ["c.parquet", "live"], ["d.parquet", "live"]],
  log: [["000.json", "+ a.parquet", "setup"], ["001.json", "+ b.parquet", "setup"], ["002.json", "+ c.parquet", "Job A"], ["003.json", "+ d.parquet", "Job B"]],
  jobs: ["created 002.json ✓", "no conflict, created 003.json ✓"],
  tone: ["ok", "ok"],
  ver: 3,
  ok: true,
  msg: "Job B reads what 002 changed. Job A only added a different file, so there is no conflict, and Job B commits as 003. Had both jobs deleted the same file, Job B would fail instead."
}];

export const WAL_STEPS = ["Before", "Commit", "Crash", "Restart"];

export const WAL_ACTIONS = ["Commit the transfer", "Crash the server", "Restart and replay the log"];

export const WAL_PHASES = [{
  mem: {
    Linh: 200,
    Marco: 50
  },
  log: 0,
  disk: {
    Linh: 200,
    Marco: 50
  },
  msg: "Before the transfer. Memory, log and data file all agree."
}, {
  mem: {
    Linh: 100,
    Marco: 150
  },
  log: 3,
  disk: {
    Linh: 200,
    Marco: 50
  },
  ok: true,
  msg: "The database wrote the change to the log on disk, then answered COMMIT OK. The data file is still old; it gets updated later."
}, {
  mem: null,
  log: 3,
  disk: {
    Linh: 200,
    Marco: 50
  },
  bad: true,
  msg: "Power cut. Everything in memory is gone, and the data file still says $200 and $50."
}, {
  mem: {
    Linh: 100,
    Marco: 150
  },
  log: 3,
  disk: {
    Linh: 100,
    Marco: 150
  },
  ok: true,
  replay: true,
  msg: "On restart, the database reads the log and redoes every committed change. The transfer survived."
}];

export const WAL_LOG = ["T7  Linh   −100", "T7  Marco  +100", "T7  COMMIT"];

export const ANOMALIES = [{
  name: "Dirty read",
  steps: [{
    t: 2,
    text: "Set Linh to $100. Not committed yet."
  }, {
    t: 1,
    text: "Read Linh",
    read: ["$100", "$200", "$200", "$200"]
  }, {
    t: 2,
    text: "ROLLBACK. Linh is $200 again."
  }],
  bad: [true, false, false, false],
  say: ["T1 saw $100, a change that was then rolled back. It acted on money that never existed.", "T1 saw $200, the last committed value. T2's unfinished change stayed hidden."]
}, {
  name: "Non-repeatable read",
  steps: [{
    t: 1,
    text: "Read Linh",
    read: ["$200", "$200", "$200", "$200"]
  }, {
    t: 2,
    text: "Set Linh to $100. COMMIT."
  }, {
    t: 1,
    text: "Read Linh again",
    read: ["$100", "$100", "$200", "$200"]
  }],
  bad: [true, true, false, false],
  say: ["T1 read the same row twice in one transaction and got $200, then $100.", "T1 got $200 both times. It keeps seeing the row as it was when it first read it."]
}, {
  name: "Phantom read",
  steps: [{
    t: 1,
    text: "Count accounts with balance > $0",
    read: ["2", "2", "2", "2"]
  }, {
    t: 2,
    text: "Insert Bao with $30. COMMIT."
  }, {
    t: 1,
    text: "Count again",
    read: ["3", "3", "3", "2"]
  }],
  bad: [true, true, true, false],
  say: ["T1 ran the same count twice and got 2, then 3. Bao's new row appeared in the middle of its transaction.", "T1 counted 2 both times, as if T2 had run after it."]
}];

export const LEVELS = ["Read Uncommitted", "Read Committed", "Repeatable Read", "Serializable"];

export const RACES = [{
  key: "none",
  label: "Run without isolation",
  steps: [{
    t: 1,
    text: "Read balance: $100",
    bal: 100,
    cash: 0
  }, {
    t: 2,
    text: "Read balance: $100",
    bal: 100,
    cash: 0
  }, {
    t: 1,
    text: "$100 ≥ $80, so pay $80. Write $20.",
    bal: 20,
    cash: 80
  }, {
    t: 2,
    text: "$100 ≥ $80, so pay $80. Write $20.",
    bal: 20,
    cash: 160
  }],
  ok: false,
  end: "Both withdrawals went through. $160 left the account, but the balance only dropped by $80. T2 wrote over T1's change. This is a lost update."
}, {
  key: "iso",
  label: "Run with isolation",
  steps: [{
    t: 1,
    text: "Read balance: $100. Lock the row.",
    bal: 100,
    cash: 0
  }, {
    t: 2,
    text: "Read balance… wait for T1.",
    bal: 100,
    cash: 0,
    wait: true
  }, {
    t: 1,
    text: "Pay $80. Write $20. Commit.",
    bal: 20,
    cash: 80
  }, {
    t: 2,
    text: "Read balance: $20. $20 < $80, so refuse.",
    bal: 20,
    cash: 80
  }],
  ok: true,
  end: "Only one withdrawal went through. T2 saw T1's result, as if the two ran one after the other."
}];

export const SENDS = [100, 500];

export const TX_STEPS = [{
  sql: "BEGIN;",
  tx: true
}, {
  sql: "UPDATE accounts SET balance = balance - 100\nWHERE name = 'Linh';",
  who: "Linh",
  d: -100
}, {
  sql: "UPDATE accounts SET balance = balance + 100\nWHERE name = 'Marco';",
  who: "Marco",
  d: 100
}, {
  sql: "COMMIT;",
  tx: true
}];

export const SQL_KW = /\b(BEGIN|COMMIT|ROLLBACK|UPDATE|SET|WHERE|CHECK|AND)\b/;

export const money = n => n < 0 ? `−$${-n}` : `$${n}`;

export const START = {
  Linh: 200,
  Marco: 50
};

export const useOnceVisible = (ref, onEnter) => {
  const cb = useRef(onEnter);
  cb.current = onEnter;
  useEffect(() => {
    const el = ref.current;
    if (!el) return;
    if (typeof IntersectionObserver === "undefined") {
      cb.current();
      return;
    }
    const io = new IntersectionObserver(entries => {
      if (entries.some(e => e.isIntersecting)) {
        io.disconnect();
        cb.current();
      }
    }, {
      threshold: 0.35
    });
    io.observe(el);
    return () => io.disconnect();
  }, [ref]);
};

export const useReducedMotion = () => {
  const [reduce, setReduce] = useState(false);
  useEffect(() => {
    const mq = window.matchMedia("(prefers-reduced-motion: reduce)");
    setReduce(mq.matches);
    const on = e => setReduce(e.matches);
    mq.addEventListener("change", on);
    return () => mq.removeEventListener("change", on);
  }, []);
  return reduce;
};

> This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Source: adapted and expanded from Databricks' ["What Are ACID Transactions?"](https://www.databricks.com/blog/what-are-acid-transactions).

Linh sends Marco \$100 in a banking app. To Linh it is one tap. To the database it is two writes: take \$100 from Linh, then add \$100 to Marco. If the server dies between those two writes, \$100 disappears.

**ACID** is the name for four promises a database makes so that this cannot happen: **Atomicity**, **Consistency**, **Isolation** and **Durability**. A group of operations that gets all four promises is called a **transaction**.

<Tip>
  **In short**

  * A transaction groups several reads and writes into one unit of work.
  * **Atomicity**: all of it happens, or none of it does.
  * **Consistency**: every commit obeys the rules you declared, such as "a balance can't go below 0".
  * **Isolation**: transactions that run at the same time don't trip over each other's half-done work.
  * **Durability**: once the database says "committed", a crash cannot undo it.
</Tip>

## What is a transaction?

Here is the transfer in SQL. Linh starts with \$200 and Marco with \$50. `BEGIN` opens a transaction and `COMMIT` closes it. Everything in between counts as one unit.

```sql theme={null}
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Linh';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Marco';
COMMIT;
```

Until `COMMIT`, the two changes are only *pending*. Other users can't see them yet, and the database can still throw them away. After `COMMIT`, they are final. The four ACID letters describe what the database guarantees along the way.

## Atomicity: all or nothing

Run the transfer in Figure 1, first without a transaction, then with one. Both times, the server crashes right after Linh pays.

Start: Linh \$200, Marco \$50, total \$250. Transfer \$100 from Linh to Marco.

| Scenario | Linh | Marco | Total | What happened |
| - | - | - | - | - |
| No transaction, crash after Linh's update | \$100 | \$50 | \$150 | Linh's update was saved on its own. \$100 is lost. |
| Transaction, crash after Linh's update | \$200 | \$50 | \$250 | No `COMMIT` was reached, so on restart the database rolled back Linh's update. |
| Transaction, no crash | \$100 | \$150 | \$250 | Both updates committed together. |

Without a transaction, each `UPDATE` is saved the moment it runs. Nothing links the two halves, so a crash leaves the money half moved.

With a transaction, the database knows the two updates belong together. If anything stops the transaction before `COMMIT`, such as a crash, an error, or the app sending `ROLLBACK`, the database undoes every pending change. This undo is called a **rollback**. That is atomicity: the transaction is one piece that can't be split, whatever number of statements it has.

## Consistency: the rules always hold

Linh has \$200. What should happen if the app asks to send \$500? The bank has a rule, written into the table itself:

```sql theme={null}
CREATE TABLE accounts (
  name    TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
```

Rule: `CHECK (balance >= 0)`. Start: Linh \$200, Marco \$50.

| Request | Check | Result | Linh | Marco |
| - | - | - | - | - |
| Send \$500 | \$200 − \$500 = −\$300 ✗ | Rejected, whole transaction rolled back | \$200 | \$50 |
| Send \$100 | \$200 − \$100 = \$100 ✓ | Committed | \$100 | \$150 |
| Send \$100 again | \$100 − \$100 = \$0 ✓ | Committed | \$0 | \$250 |
| Send \$100 a third time | \$0 − \$100 = −\$100 ✗ | Rejected | \$0 | \$250 |

The database checks every declared rule before it lets a transaction commit. If one rule fails, it rolls back the whole transaction, so Marco's half of the transfer is undone too. Rules can be `CHECK`, `NOT NULL`, `UNIQUE` or foreign keys. Consistency means the database moves from one valid state to another valid state, and never stops in between.

Consistency has a limit. The database only enforces rules it was told about. If the app sends \$50 when Linh meant \$5, every rule still holds, and the database saves the mistake. Of the four letters, C is the one that depends on you. A, I and D are the tools the database uses to keep your rules true.

## Isolation: one at a time, even when they're not

Linh's joint account has \$100. Linh and her brother each take out \$80, at the same moment, from two different ATMs. Each ATM runs the same steps: read the balance, check there is enough, pay out, write the new balance.

Without isolation (steps interleave):

| Time | T1 (ATM A, \$80) | T2 (ATM B, \$80) | Balance |
| - | - | - | - |
| 1 | Read balance: \$100 | | \$100 |
| 2 | | Read balance: \$100 | \$100 |
| 3 | \$100 ≥ \$80, pay \$80, write \$20 | | \$20 |
| 4 | | \$100 ≥ \$80, pay \$80, write \$20 | \$20 |

Result: \$160 paid out, balance \$20. T2 overwrote T1's write. This is a **lost update**.

With isolation (here, a row lock):

| Time | T1 (ATM A, \$80) | T2 (ATM B, \$80) | Balance |
| - | - | - | - |
| 1 | Read balance: \$100, lock the row | | \$100 |
| 2 | | Read balance: waits for T1 | \$100 |
| 3 | Pay \$80, write \$20, commit | | \$20 |
| 4 | | Read balance: \$20, \$20 \< \$80, refuse | \$20 |

Result: \$80 paid out, balance \$20. Correct.

Each ATM did nothing wrong on its own. The bug came from the two running at the same time. T2 made its decision using a balance that T1 was about to change.

Isolation means each transaction behaves as if it were alone. The strongest form, **serializable** isolation, promises that the result is the same as running the transactions one after another in some order. A database can get there in two main ways. It can make T2 wait, using a lock, as in Figure 3. Or it can let both run and, at commit time, cancel the one that conflicts. The app then retries it.

### Isolation levels

Full serializable isolation costs speed, because transactions wait or retry more often. So SQL defines four **isolation levels**. Each weaker level allows some odd results, called anomalies, in exchange for more work running at once.

* **Dirty read**: you read a change that another transaction has not committed yet, and it may still be rolled back.
* **Non-repeatable read**: you read the same row twice in one transaction and get two different values, because someone committed in between.
* **Phantom read**: you run the same query twice and new rows appear, because someone inserted them in between.

Figure 4 plays each anomaly as a race between a reader, T1, and a writer, T2. Pick a level and watch what T1 sees.

| Level | Dirty read | Non-repeatable read | Phantom read |
| - | - | - | - |
| Read Uncommitted | possible | possible | possible |
| Read Committed | prevented | possible | possible |
| Repeatable Read | prevented | prevented | possible |
| Serializable | prevented | prevented | prevented |

The three races in Figure 4 (Linh starts at \$200; Linh and Marco are the only accounts):

| Anomaly | Time 1 | Time 2 | Time 3 | T1 sees when the anomaly is possible | T1 sees when it is prevented |
| - | - | - | - | - | - |
| Dirty read | T2 sets Linh to \$100, not committed | T1 reads Linh | T2 rolls back | \$100 (a value that never committed) | \$200 |
| Non-repeatable read | T1 reads Linh: \$200 | T2 sets Linh to \$100 and commits | T1 reads Linh again | \$100 (changed mid-transaction) | \$200 |
| Phantom read | T1 counts accounts with balance > \$0: 2 | T2 inserts Bao with \$30 and commits | T1 counts again | 3 (a new row appeared) | 2 |

The standard sets the **minimum** each level must prevent. Many databases prevent more: [PostgreSQL's **Repeatable Read** also blocks phantom reads](https://www.postgresql.org/docs/current/transaction-iso.html), for example.

Most databases do **not** use **Serializable** by default:

| Database | Default level | Source |
| - | - | - |
| PostgreSQL | **Read Committed** | [docs](https://www.postgresql.org/docs/current/transaction-iso.html) |
| Oracle | **Read Committed** | [docs](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html) |
| SQL Server | **Read Committed** | [docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql) |
| MySQL InnoDB | **Repeatable Read** | [docs](https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html) |

Azure SQL Database also defaults to Read Committed, but reads from row versions (`READ_COMMITTED_SNAPSHOT`) instead of taking locks.

So at the default level of most databases, the read-then-write in Figure 3 can still cause a **lost update**. The paper that sharpened these definitions lists lost update as *possible* at **Read Committed** ([Berenson et al., "A Critique of ANSI SQL Isolation Levels", 1995](https://www.microsoft.com/en-us/research/publication/a-critique-of-ansi-sql-isolation-levels/)). There are three common fixes. They are **alternatives: pick one**. Each is a complete withdrawal on its own (PostgreSQL syntax).

In all three, the app still checks that Linh has \$80. That check is the **bank's rule**, and no isolation level makes it for you. Isolation only makes sure the check sees the **real balance**, not a stale one. In Figure 3, both ATMs did check. The bug was that ATM B checked an old \$100.

<CodeGroup>
  ```sql Fix 1: Serializable theme={null}
  BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE name = 'Linh';
  -- app checks balance >= 80
  UPDATE accounts SET balance = balance - 80
  WHERE name = 'Linh';
  COMMIT;
  -- If both ATMs run at once, one gets
  -- a serialization error. The app retries
  -- it from BEGIN, reads $20, and refuses.
  ```

  ```sql Fix 2: Lock the row theme={null}
  BEGIN;
  SELECT balance FROM accounts WHERE name = 'Linh'
  FOR UPDATE;
  -- ATM B waits here until ATM A commits
  -- app checks balance >= 80
  UPDATE accounts SET balance = balance - 80
  WHERE name = 'Linh';
  COMMIT;
  -- ATM B then reads $20 and refuses.
  ```

  ```sql Fix 3: One statement theme={null}
  UPDATE accounts SET balance = balance - 80
  WHERE name = 'Linh' AND balance >= 80;
  -- 1 row updated: pay out.
  -- 0 rows updated: refuse.
  ```
</CodeGroup>

* **Fix 1** lets both ATMs run freely and **cancels one** if they collide. The app must be ready to **retry**.
* **Fix 2** makes ATM B **wait** at the `SELECT` until ATM A commits.
* **Fix 3** puts the check inside the `UPDATE`, and works even at **Read Committed**. When two `UPDATE`s hit the same row, the second one **waits** for the first to commit, then **re-checks its `WHERE` clause** against the new value ([PostgreSQL docs](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED)). ATM B finds \$20, so `balance >= 80` is false, and nothing is taken.

## Durability: committed means saved

The app gets `COMMIT OK`. One millisecond later, the power goes out. Is the transfer still there after the restart?

| Step | Memory (RAM) | Log on disk | Data file on disk |
| - | - | - | - |
| 0. Before | Linh \$200, Marco \$50 | empty | Linh \$200, Marco \$50 |
| 1. Commit | Linh \$100, Marco \$150 | `T7 Linh −100`, `T7 Marco +100`, `T7 COMMIT` | Linh \$200, Marco \$50 (not updated yet) |
| 2. Crash | empty (lost) | same three records | Linh \$200, Marco \$50 |
| 3. Restart and replay | Linh \$100, Marco \$150 | same three records | Linh \$100, Marco \$150 |

`COMMIT OK` is sent only after step 1's log records are safely on disk.

Rewriting whole data files on every commit would be slow. So most databases first add a short record of each change to a **write-ahead log** (WAL), and force that log onto the disk. Only then do they answer `COMMIT OK`. The data files are brought up to date later, in the background.

After a crash, the database reads the log from a known safe point. It redoes every change from committed transactions and throws away changes from transactions that never committed. The same log is what makes the rollback in Figure 1 possible after a crash.

Durability is only as strong as the disk. If the disk itself is destroyed, you need copies elsewhere: replicas or backups.

## ACID on a data lake

ACID started in databases that own their storage. A data lake is different: it is a large set of files, often Parquet, in cloud object storage such as Amazon S3. Many jobs write those files at once, and a reader can catch a job halfway through. Table formats such as **Delta Lake**, Apache Iceberg and Apache Hudi add a transaction layer on top of the files.

Delta Lake does it with an **ordered transaction log**: a folder named `_delta_log` that holds numbered commit files (`00000000000000000000.json`, `…001.json`, and so on). Each commit file lists the data files that one transaction added or removed.

Two jobs write to one Delta table (commit file names shortened):

| Step | What happens | `_delta_log` | Reader sees |
| - | - | - | - |
| 0 | Table at version 1 | `000.json` (+a), `001.json` (+b) | version 1: a, b |
| 1 | Job A writes `c.parquet`; no commit names it yet | unchanged | version 1: a, b |
| 2 | Job B writes `d.parquet` at the same time | unchanged | version 1: a, b |
| 3 | Both try to create `002.json`; Job A wins, Job B is rejected | + `002.json` (+c, Job A) | version 2: a, b, c |
| 4 | Job B checks 002 for conflicts; none (different files), so it commits as `003.json` | + `003.json` (+d, Job B) | version 3: a, b, c, d |

If Job B's changes had conflicted (for example, both jobs deleted the same file), Job B would fail instead of retrying.

* **Atomicity**: a job writes its new data files first. They don't count until the job adds the next commit file. If the job crashes before that, readers never see its files.
* **Isolation**: a reader picks the latest commit number and reads exactly that version of the table, a *snapshot*. Writers use *optimistic concurrency*: if two writers race to create the same next commit file, only one wins. The other checks whether the winner's changes conflict with its own. If not, it retries at the next number. If so, it fails.
* **Durability**: commit files live in the same durable object storage as the data.
* **Consistency**: Delta checks the table schema and any declared `NOT NULL` or `CHECK` constraints on every write.

By default, Delta Lake guarantees that *writes* are serializable, a level it calls `WriteSerializable`. Full `Serializable`, which also covers reads, is a table setting you can turn on. In open-source Delta Lake, each commit covers one table.

Sources: [Delta Lake's transaction log](https://www.databricks.com/blog/2019/08/21/diving-into-delta-lake-unpacking-the-transaction-log.html) and [Databricks isolation levels](https://docs.databricks.com/aws/en/optimizations/isolation/isolation-levels).

## Summary

One transfer, four promises:

| Letter | Promise | Without it | How the database keeps it |
| - | - | - | - |
| **A**tomicity | All or nothing | A crash leaves \$100 missing (Figure 1) | Rolls back any transaction that didn't commit |
| **C**onsistency | Declared rules always hold | Linh's balance goes below \$0 (Figure 2) | Checks constraints before every commit |
| **I**solation | Running together looks like one at a time | Two ATMs pay \$160 from \$100 (Figure 3) | Locks, or cancel-and-retry, at the level you choose (Figure 4) |
| **D**urability | Committed means saved | A power cut erases a confirmed transfer (Figure 5) | Writes a log to disk before `COMMIT OK`, replays it on restart |

Three things to take away:

* **Put writes that belong together in one transaction.** Without `BEGIN` … `COMMIT`, there is nothing to roll back.
* **Know your isolation level.** Most databases default to **Read Committed**, which still allows lost updates.
* **Declare your rules.** Consistency only protects rules the database knows about.

## Check yourself

Questions this document answers:

* What is a transaction? A group of reads and writes that the database treats as one unit, opened with `BEGIN` and finished with `COMMIT` or `ROLLBACK`.
* A transaction crashes after taking \$100 from Linh but before paying Marco. What happens on restart? Atomicity: the uncommitted change is rolled back.
* Which property stops a balance from going below \$0? Consistency, through a declared rule such as `CHECK (balance >= 0)`.
* Can consistency catch a wrong amount that breaks no rule? No. The database only enforces rules it was told about.
* Two withdrawals read the same balance at once and both succeed. Which property was missing? Isolation. The bug is called a lost update.
* Which isolation level makes concurrent transactions behave as if they ran one at a time? Serializable.
* What is the default isolation level in PostgreSQL? Read Committed (also Oracle and SQL Server; MySQL InnoDB uses Repeatable Read).
* How does a database keep a committed change after a power cut? Durability: it writes the change to a write-ahead log on disk before it answers `COMMIT OK`, and replays the log on restart.
* How does Delta Lake provide ACID on files? An ordered transaction log in `_delta_log`, snapshot reads and optimistic concurrency for writers.

## Glossary

| Term | Meaning |
| - | - |
| Transaction | A group of operations the database treats as one unit of work. |
| Commit | Make a transaction's changes final and visible to others. |
| Rollback | Undo every change a transaction made. |
| Constraint | A declared rule, such as `CHECK` or `UNIQUE`, the data must always obey. |
| Lost update | Two transactions read the same value; the second write silently replaces the first. |
| Dirty read | Reading another transaction's change before it commits. |
| Isolation level | How much one running transaction may see of others; Serializable is the strongest. |
| Serializable | The result equals some one-at-a-time order of the transactions. |
| Write-ahead log (WAL) | A file on disk where changes are recorded before the data files are updated. |
| Optimistic concurrency | Let transactions run freely and check for conflicts only at commit time. |

## For AI agents

You are reading the Markdown export of `/writing/database/acid-transactions`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`.

***

Adapted and expanded from Databricks' ["What Are ACID Transactions?"](https://www.databricks.com/blog/what-are-acid-transactions). The isolation levels, write-ahead log and Delta Lake details go beyond that article. Figures are simplified teaching models; the balances are illustrative.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.