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 — the result is as if transactions ran one at a time, even though they didn't.
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 prevents this by making the two transactions behave as if one ran completely before the other started, even though they overlapped in real time. In practice, the database achieves this with locking or multi-version concurrency control (MVCC) under the hood — the second transaction to touch that inventory row either waits or is forced to re-check the now-updated quantity, and one of the two orders is correctly rejected.

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