8.2 What does ACID mean?

In the office: The accountant has four rules. A sale is recorded completely or not at all. The books always balance. Two clerks writing at once don't scribble over each other. And once written in ink, it survives even if the lights go out.

In the system: ACID is the set of promises a database makes about transactions: groups of changes treated as one unit.

Letter	Means	Bookstore example
Atomicity	All the changes in a transaction happen, or none do	Save the order and reduce the stock, or neither
Consistency	Every transaction leaves the data obeying its rules	Stock can never go below zero; every order points at a real user
Isolation	Transactions running at the same time don't see each other's half-done work	Two people buying the last copy don't both succeed (Chapter 17)
Durability	Once committed, the change survives a crash	The confirmed order is still there after a power cut

In code, a transaction looks like this:

sql

BEGIN;
INSERT INTO orders (user_id) VALUES (7);
INSERT INTO order_items VALUES (901, 42, 2, 499);
UPDATE books SET stock = stock - 2 WHERE id = 42;   -- fails if stock would go below 0
COMMIT;                                             -- or ROLLBACK: nothing happened

Note the C in ACID isn't the same "consistency" as in CAP (Chapter 16). Here it means "the database's rules still hold".

Memory code: "All or nothing, rules always hold, no peeking, written in ink." Atomic, Consistent, Isolated, Durable.

8. SQL vs NoSQL

Platform

Company

Legal