ACID — SDE-1 Interview Questions#

A — Atomicity — all or nothing. Every operation in a transaction succeeds together, or none of them happen. No partial state ever persists.

C — Consistency — the database always moves from one valid state to another valid state. No transaction can leave the database in a state that violates its own rules (constraints, foreign keys, business rules).

I — Isolation — two concurrent transactions must feel like they're executing independently. One transaction cannot see the partial state of another mid-execution.

D — Durability — once a transaction is committed, that data survives permanently. Crashes, power loss, hardware failure — none of it can undo a committed transaction.

The strongest single example is a bank transfer — it touches all four properties and the failure mode is immediately visible to the user.

Atomicity violated:

Alice sends $100 to Bob
→ debit Alice succeeds
→ server crashes before crediting Bob
→ Alice loses $100, Bob gets nothing
→ user sees money gone with no explanation

Each property has a distinct failure mode:

Atomicity broken   → partial transfer — money debited, never credited
Consistency broken → account balance goes negative (violates DB constraint)
Isolation broken   → two users book the last hotel room simultaneously, both get a confirmation
Durability broken  → payment succeeds, server crashes, transaction lost on restart

WAL stands for Write-Ahead Log. It is an append-only file on disk where every change is recorded before it is applied to the actual data files. "Write-ahead" means the log always precedes the action — you write to the log first, then apply the change.

Transaction starts
  → WAL entry: "about to deduct $100 from Alice (current: $500)"
  → deduct $100 from Alice in data file
  → WAL entry: "about to add $100 to Bob (current: $300)"
  CRASH ← before credit applied

On restart:
  → DB reads WAL
  → finds incomplete transaction (no COMMIT marker)
  → uses WAL entry to reverse: Alice restored to $500
  → clean state ✓

The WAL is what makes both atomicity and durability possible:

• Atomicity — uses WAL to undo incomplete transactions on crash (rollback)
• Durability — uses WAL to redo committed transactions on crash (recovery)

Without the WAL, a crash mid-transaction would leave the database in corrupt partial state with no way to recover.

They use the same mechanism (the WAL) but protect against completely different failure scenarios:

Atomicity  → crash DURING transaction  → rollback, clean state, as if it never happened
Durability → crash AFTER commit        → data survives, committed state is permanent

Atomicity is about incomplete work — if a transaction is only halfway done when the server dies, atomicity ensures the partial changes are reversed. The WAL records what was done so it can be undone.

Durability is about completed work — if a transaction fully committed and then the server dies, durability ensures that committed data is still there on restart. The WAL records the commit so it can be replayed.

Same WAL, opposite direction:

Incomplete transaction → WAL used to UNDO  (atomicity)
Committed transaction  → WAL used to REDO  (durability)

When the database tells the OS to write to disk, the OS doesn't do it immediately. It puts the data in a RAM buffer and says "done ✓" — because batching writes is faster. The database thinks the write succeeded, but the data is still in RAM.

Without fsync:
DB → "write WAL to disk" → OS → RAM buffer → "done ✓"  ← OS is lying
                                      ↓ maybe later → actual disk

fsync() forces the OS to flush the RAM buffer to physical disk before returning success. The database blocks and waits until the data has physically hit the disk — this forced round-trip is what makes writes feel slow.

With fsync:
DB → "write WAL to disk" → OS → RAM buffer → fsync() → physical disk → "done ✓"

With fsync=off, a power cut wipes RAM entirely — the OS buffer is gone, the WAL entry is gone, and a transaction the user was told succeeded is permanently lost.

Process crash + fsync=off  → risky but often survives (OS still running, flushes eventually)
Power cut    + fsync=off  → guaranteed data loss (RAM wiped, nothing left to flush)
Power cut    + fsync=on   → safe (data was on disk before commit returned)