ACID Transactions Explained

the problem ACID solves

A PlateRoute order involves several writes at once: debit the customer's stored balance, decrement the restaurant's inventory for that item, and insert the order row. If the process crashes after the first write but before the second, the customer has been charged for food that was never marked as ordered. ACID is the set of guarantees a relational database gives so that a group of writes like this behaves as a single, safe unit called a transaction.

the four letters

Letter	Property	What it guarantees
A	Atomicity	All the writes in a transaction succeed, or none of them do — there's no partial state where the charge happened but the order didn't get created.
C	Consistency	A transaction only ever moves the database from one valid state to another — it can't leave data violating a defined rule, like an order referencing a restaurant ID that doesn't exist.
I	Isolation	Concurrent transactions don't see each other's half-finished work. How much more than that you get depends on the isolation level: "as if they ran one at a time" is specifically the `SERIALIZABLE` guarantee, not what every level provides.
D	Durability	Once a transaction is confirmed committed, it survives a crash immediately after — it's on stable storage, not just in memory.

atomicity, concretely


BEGIN TRANSACTION;
  UPDATE customers SET balance = balance - 24.50 WHERE id = 910;
  UPDATE restaurant_inventory SET qty = qty - 1 WHERE item_id = 'pz_104';
  INSERT INTO orders (customer_id, item_id, total) VALUES (910, 'pz_104', 24.50);
COMMIT;

If any statement inside this block fails — say the inventory update violates a "qty can't go negative" constraint because the last pizza just sold out — the database rolls back every statement in the block, including the balance debit that already succeeded. The customer's balance ends up untouched, exactly as if the transaction had never been attempted.

isolation: the property that prevents two people buying the last pizza

Say two customers both try to order the last pizza (qty = 1) at nearly the same instant. Without isolation, both transactions could read qty = 1, both decide the purchase is valid, and both commit — leaving qty = -1 and two customers who paid for food that doesn't exist.

Isolation is what stops this, but it matters which isolation you asked for. Under SERIALIZABLE, the two transactions are forced to behave as if one ran completely before the other, and one of the orders is correctly rejected. Under the more common default of READ COMMITTED, the read-then-decide-then-write sequence above is not prevented: both transactions can read qty = 1 before either writes.

Two things rescue it at the weaker level, and real systems use one of them. Doing the check inside the write itself — UPDATE inventory SET qty = qty - 1 WHERE id = 42 AND qty > 0 — makes the second transaction block on the row lock and then re-evaluate against the updated value, so it matches zero rows and the order is rejected. Explicitly locking the row on read (SELECT ... FOR UPDATE) achieves the same by serializing the two transactions on that row. Underneath both sits locking or multi-version concurrency control (MVCC).

Different isolation levels (read-committed, repeatable-read, serializable) trade strictness for performance — stricter isolation prevents more subtle bugs but forces more transactions to wait on each other, exactly the same latency-vs-correctness trade-off that shows up throughout this track.

why NoSQL databases usually relax this

Full ACID transactions, especially across multiple rows or tables, require coordination that gets expensive at scale — which is exactly why many NoSQL databases only guarantee atomicity for a single record, not across several. This is a direct trade for the horizontal scalability discussed in SQL vs. NoSQL: a document database that only needs to atomically update one document can shard and scale far more easily than one that has to coordinate a transaction spanning documents on different machines.

The practical implication for a design: if a feature genuinely needs multi-row ACID guarantees (charging a card and updating inventory together), that's a strong signal to keep that data on a relational database, even if the rest of the system leans NoSQL.

reference

PostgreSQL Docs — Transactions