---
title: 'ACID Properties'
source: 'https://academia.sh/en/courses/relational-theory/acid-properties'
course: 'Data Modeling and Relational Theory'
language: en
updated: '2026-08-23T07:00:46+00:00'
license: 'CC BY-SA 4.0'
---

# ACID Properties

The concept of a transaction and the definition of the atomicity, consistency, isolation, and durability guarantees; a runnable demonstration of rollback and durability; the client's responsibility in the face of an error.

The previous lesson relied on the same assumption twice: writing a loan record either
happens completely or not at all, and an analytical query never sees a half-written
state. These same assumptions also appeared in the course's first lesson — two processes
writing to a file at the same time could provide neither. This lesson's question is:
under what name, and within what limits, does the database management system give these
guarantees?

## Transaction

A **transaction** is a sequence of statements handled as a single unit against the
database. Transferring a copy from one branch to another requires two updates; the two
must happen together, because the state in between leaves the library short one copy.

A transaction's boundaries are stated explicitly: a start, followed by either a
**commit** or a **rollback**. When not stated, each statement counts as a transaction on
its own.

The guarantees a transaction gives are grouped under four headings and referred to by
their initials as **ACID**: atomicity, consistency, isolation, durability.

## Atomicity

**Atomicity** requires that a transaction either be applied in full or not at all. An
intermediate state is never made permanent.

```sh
sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE stock (branch_code TEXT NOT NULL, isbn TEXT NOT NULL, quantity INTEGER NOT NULL,
                   PRIMARY KEY (branch_code, isbn), CHECK (quantity >= 0));
INSERT INTO stock VALUES ('CEN', '975-01', 3), ('BHC', '975-01', 1);

BEGIN;
UPDATE stock SET quantity = quantity - 1 WHERE branch_code = 'CEN' AND isbn = '975-01';
UPDATE stock SET quantity = quantity + 1 WHERE branch_code = 'BHC' AND isbn = '975-01';
SELECT 'mid-transaction' AS moment, branch_code, quantity FROM stock ORDER BY branch_code;
ROLLBACK;
SELECT 'after rollback' AS moment, branch_code, quantity FROM stock ORDER BY branch_code;
SQL
```

```
┌─────────────────┬─────────────┬──────────┐
│     moment      │ branch_code │ quantity │
├─────────────────┼─────────────┼──────────┤
│ mid-transaction │ BHC         │ 2        │
│ mid-transaction │ CEN         │ 2        │
└─────────────────┴─────────────┴──────────┘
┌────────────────┬─────────────┬──────────┐
│     moment     │ branch_code │ quantity │
├────────────────┼─────────────┼──────────┤
│ after rollback │ BHC         │ 1        │
│ after rollback │ CEN         │ 3        │
└────────────────┴─────────────┴──────────┘
```

Both updates are visible inside the transaction; after the rollback, neither is.
Atomicity is not only for error conditions — a rollback can also be a decision the
application makes on purpose. The file example in the course's first lesson had no such
mechanism: a write left half-finished stayed half-finished in the file.

## Consistency

**Consistency** requires that a transaction move the database from one valid state to
another valid state. A state is "valid" when every constraint in the schema is satisfied.
A constraint can be temporarily broken in the middle of a transaction; it cannot remain
broken at the end of one.

```sh
rm -f library.db

sqlite3 library.db <<'SQL'
CREATE TABLE stock (branch_code TEXT NOT NULL, isbn TEXT NOT NULL, quantity INTEGER NOT NULL,
                   PRIMARY KEY (branch_code, isbn), CHECK (quantity >= 0));
INSERT INTO stock VALUES ('CEN', '975-01', 3), ('BHC', '975-01', 1);
SQL

sqlite3 library.db <<'SQL'
.bail on
BEGIN;
UPDATE stock SET quantity = quantity - 1 WHERE branch_code = 'BHC' AND isbn = '975-01';
UPDATE stock SET quantity = quantity - 1 WHERE branch_code = 'BHC' AND isbn = '975-01';
COMMIT;
SQL

sqlite3 library.db <<'SQL'
.headers on
.mode box
SELECT branch_code, quantity FROM stock ORDER BY branch_code;
SQL
```

```
Runtime error near line 4: CHECK constraint failed: quantity >= 0 (19)
┌─────────────┬──────────┐
│ branch_code │ quantity │
├─────────────┼──────────┤
│ BHC         │ 1        │
│ CEN         │ 3        │
└─────────────┴──────────┘
```

The second update was rejected because it would have driven the quantity negative; the
first one was rolled back as well, and the quantity returned to its starting value of
one.

This result has a condition that is easy to miss. The first line in the block tells the
client to stop at the first error; its name and syntax vary by engine and tool. Without
that line, the client would ignore the error, send `COMMIT`, and a half-finished piece of
work — **with only the first update applied** — would become permanent. The consistency
guarantee starts with the engine enforcing the constraints, but it is completed by the
client behaving correctly in the face of an error. A client that swallows the error takes
away the guarantee the engine gave.

## Isolation

**Isolation** requires that transactions running at the same time not see each other's
half-finished state. Its strictest reading is this: the result of transactions running
concurrently must be the same as some result obtainable by running the same transactions
one at a time, in some order.

This guarantee is expensive, and most systems stage it in levels rather than enforcing it
in full. Relaxing it frees up specific anomalies, and these have names: a transaction
seeing another transaction's uncommitted write, the same row giving a different value
across two reads, a set of rows matching a condition differing across two reads. Which
anomaly is visible at which level, the difference between locking and version-based
approaches, and deadlock conditions belong to later courses in this curriculum. Here the
treatment stays at the definition level: the lost update in the course's first lesson was
an example of what happens in an environment without isolation.

## Durability

**Durability** requires that a committed transaction's result stay permanent — even if
the system crashes immediately afterward. A commit acknowledgment is a promise: the data
is now secured in durable storage.

```sh
rm -f library.db

sqlite3 library.db <<'SQL'
CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL);
BEGIN;
INSERT INTO loan VALUES (1001, 41);
COMMIT;
BEGIN;
INSERT INTO loan VALUES (1002, 52);
ROLLBACK;
SQL

sqlite3 library.db <<'SQL'
.headers on
.mode box
SELECT loan_no, member_no FROM loan;
SQL
```

```
┌─────────┬───────────┐
│ loan_no │ member_no │
├─────────┼───────────┤
│ 1001    │ 41        │
└─────────┴───────────┘
```

The second call is a separate process; it inherits nothing from the memory of the process
that wrote the data. It sees the committed row and not the rolled-back one. The measure
is the transaction's boundary: nothing written before a commit counts as permanent, and
everything written after one does.

Durability's implementation usually rests on a log: changes are written to a sequential
**write-ahead log** before they are applied to the data pages, and that write is secured
on disk. On startup after a crash, the log is read; changes that were committed but not
yet applied to the pages are completed, and uncommitted ones are rolled back. **The log's
format, its name, and the point at which the disk write is secured vary by engine**; some
engines let this behavior be tuned, and relaxing it also relaxes the durability
guarantee.

## Cost and Limits

The four guarantees are not free. Atomicity and durability require extra writes;
isolation requires either locking or version storage. Keeping a transaction open longer
than necessary increases how much data is locked for how long — a transaction that waits
on user input is a common design mistake.

Two limits should also be stated. First, ACID applies within a single database; the same
guarantee does not come for free in a piece of work that spans two separate systems.
Second, the consistency promise is bounded by the constraints **written into the
schema**. A business rule not written into the schema can end up broken at the end of a
committed transaction, and the engine has no way of knowing it. This is the counterpart,
here, of the "write the rule into the schema" principle from the Integrity Constraints
lesson.

## Summary

- A transaction is a sequence of statements handled as a single unit; its boundary is set
  by a commit or a rollback.
- Atomicity requires that an intermediate state never become permanent; consistency
  requires that every constraint be satisfied at the end of a transaction.
- The engine rejecting a constraint is not enough on its own; a client that ignores the
  error and sends a commit makes half-finished work permanent.
- Isolation is concurrent transactions not seeing each other's half-finished state; its
  levels and implementation are the subject of a separate course.
- Durability is tied to the moment of commit and is usually provided by a write-ahead
  log; how strict the guarantee is varies by engine and can be tuned.

## Next Step

This lesson gave exact names to the promises a relational database makes. That same
naming also makes a comparison possible: some systems give up part of these promises and
offer other things in return. The course's final lesson takes up that choice — which
data and which access pattern require the relational model, when another data model is a
better fit, and what criteria the decision is made on?