Module 20 / Databases in depth

Module 20 · lesson 1 of 10 · Read 9 min

ACID, honestly

What atomicity, consistency, isolation and durability actually guarantee.

What you'll learn

  1. One checkout, four writes
  2. Atomicity: all or nothing
  3. Consistency: the odd letter out
  4. Isolation: the letter with the most fine print
  5. Durability: what "saved" really means
  6. What ACID doesn't cover

Every relational database says it is "ACID compliant". Most engineers can expand the acronym. Far fewer can say what each letter actually promises, and where the promise stops.

That gap matters. People assume "it's in a transaction" means "nothing can go wrong". Then they ship a bug that only shows up when two customers press the button in the same 50 milliseconds.

This lesson takes each letter in turn, says honestly what it gives you, and points out the fine print. It assumes you know basic SQL and that Tiffin runs on one Postgres database (Module 2). Nothing else is needed.

One checkout, four writes

Here's what happens when a Tiffin customer places an order. The server runs four statements (some columns are left out to keep it short):

BEGIN;
INSERT INTO orders (customer_id, restaurant_id, total_paise, status)
  VALUES (42, 7, 54000, 'placed') RETURNING id;          -- say 9001
INSERT INTO order_items (order_id, menu_item_id, quantity, price_paise)
  VALUES (9001, 311, 2, 22000), (9001, 318, 1, 10000);
UPDATE coupons SET uses_left = uses_left - 1 WHERE code = 'DINNER50';
INSERT INTO payments (order_id, amount_paise, status) VALUES (9001, 54000, 'pending');
COMMIT;

BEGIN and COMMIT wrap the four statements into one transaction. ACID describes what the database promises about that unit. Let's take the letters in order, keeping this checkout in mind.