ACID Properties

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.

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.

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.

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?