ACID

The Problem: Placing an Order Takes Several Steps

In our food delivery app, a customer pays ₹300 from their wallet (money stored in the app). Placing that order is not one change. It's three:

11. Deduct ₹300 from user 42's wallet
22. Create the order row
33. Mark the coupon FIRST50 as used

Now imagine the server crashes right after step 1:

11. Wallet: ₹500 → ₹200    ✓ done
2   💥 server crashes
32. Order created           ✗ never happened
43. Coupon marked used      ✗ never happened
5 
6→ Customer lost ₹300 and got no food

Or two requests from the same user arrive at the same moment, both see ₹500, and both deduct ₹300. The wallet ends at ₹200 instead of -₹100, so the user got two meals for the price of one.

We need a way to make several changes act as one safe unit. That's a transaction.

What a Transaction Is

A transaction is a group of database operations that succeed or fail together. You start it, make your changes, and then either:

• Commit: save all changes permanently.
• Rollback: undo all changes, as if nothing happened.

Think of an ATM withdrawal. The machine must deduct your balance and give you cash. If the cash gets stuck, your balance must go back. Never one without the other.

BEGIN;

UPDATE wallets SET balance = balance - 300 WHERE user_id = 42;

INSERT INTO orders (user_id, restaurant_id, amount, status)
VALUES (42, 7, 300, 'placed');

UPDATE coupons SET used = true
WHERE code = 'FIRST50' AND user_id = 42;

COMMIT;   -- or ROLLBACK; to undo everything

ACID properties describe how a database transaction should behave to keep data correct and reliable. In simple terms, a transaction is the operation, and ACID defines the rules that the operation should follow.

A: Atomicity

Atomicity means all or nothing. Either all three steps happen, or none of them do.

1BEGIN
2  wallet -300     ✓
3  insert order    ✓
4  💥 crash
5→ database restarts and undoes the wallet change
6→ wallet is back to ₹500, no order exists

The word comes from "atom", something that can't be split. The transaction can't be half-done.

How the database does it: it keeps a record of changes, so it can undo anything that wasn't committed.

Atomicity stops half-done work. But even a complete transaction can break the rules, which is where consistency comes in.

C: Consistency

Consistency means the data follows your rules before and after every transaction. If a transaction would break a rule, it's rejected and rolled back.

Rules in our app:

• A wallet balance can never go below ₹0.
• Every order must belong to a real user.

ALTER TABLE wallets
ADD CONSTRAINT balance_not_negative CHECK (balance >= 0);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users (id);

1Wallet: ₹200, order costs ₹300
2  UPDATE balance = 200 - 300 = -100
3  → violates CHECK (balance >= 0)
4  → whole transaction rolled back

The database enforces the rules you declare: constraints like CHECK, NOT NULL, UNIQUE, and foreign keys (a rule that a value must exist in another table). Business rules the database doesn't know about are your code's job.

Consistency protects one transaction at a time. When many run at once, we need isolation.

I: Isolation

Isolation means transactions running at the same time don't see each other's half-done work. Each one behaves as if it were running alone.

Without isolation, our double-order problem happens:

1Time   Request A                    Request B
2t1     read balance = ₹500
3t2                                  read balance = ₹500
4t3     500 - 300 → write ₹200
5t4                                  500 - 300 → write ₹200
6→ 2 orders placed, only ₹300 deducted (lost update)

With proper isolation, the database makes Request B wait for A, or makes B fail and retry. B then sees ₹200, and the CHECK constraint blocks the second order.

How the database does it: with locks (making others wait) and MVCC (giving each transaction its own snapshot of the data). Databases offer different isolation levels, which trade safety for speed. That's the next page.

The last guarantee is about what happens after a commit.

D: Durability

Durability means once the database says "committed", the data survives a crash, even a power cut one millisecond later.

How the database does it: before confirming a commit, it writes the change to a write-ahead log (WAL), an append-only file on disk. If the server crashes, it replays the log on restart.

1COMMIT
2  → write change to WAL on disk     (fast, append-only)
3  → reply "committed" to the app
4  → update actual table pages later
5 
6💥 crash before table pages are updated
7  → on restart, replay WAL → order is restored

PostgreSQL calls this the WAL. MongoDB calls it the journal. Same idea.

Now let's see what a transaction looks like in application code.

Transactions in Code

Our OrderService must run all three steps on one connection and roll back on any error:

BEGIN;                                       -- start transaction

UPDATE wallets
SET balance = balance - 300
WHERE user_id = 42;

INSERT INTO orders (user_id, restaurant_id, amount, status)
VALUES (42, 7, 300, 'placed');

UPDATE coupons
SET used = true
WHERE code = 'FIRST50' AND user_id = 42;

COMMIT;                                      -- all 3 saved together
-- ROLLBACK;                                 -- or undo everything

If the wallet would go negative, the CHECK constraint throws an error, the catch block rolls back, and no order is created.

Summary Table

Interview Angle

Common mistakes:

• Mixing up C in ACID with C in CAP. ACID consistency means "rules hold". CAP consistency means "every replica returns the latest data".
• Running transaction steps on different connections from a connection pool. Each connection is its own transaction, so the rollback won't undo the others.
• Saying isolation is "on or off". It comes in levels.

Summary

1Transaction  → group of steps that commit or roll back together
2Atomicity    → all or nothing          → undo on failure
3Consistency  → rules always hold       → constraints, foreign keys
4Isolation    → no half-done reads      → locks, MVCC
5Durability   → committed = permanent   → write-ahead log / journal
6In code      → same connection, commit at end, rollback on error

Isolation isn't all or nothing. Next, we'll look at isolation levels and the exact problems each one prevents.