Transferring $500 from Alice to Bob: (1) Check Alice balance >= 500, (2) Deduct 500 from Alice, (3) Add 500 to Bob, (4) Record transaction record. If the server loses power between steps 2 and 3, crash recovery reads WAL undo logs on reboot to roll back the deduction from Alice, preventing money disappearance.
-- Transactional Balance Transfer in PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Step 1: Lock Alice's row and verify balance
SELECT balance FROM accounts
WHERE account_id = 'alice-123'
FOR UPDATE;
-- Step 2: Deduct $500 from Alice
UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 'alice-123' AND balance >= 500.00;
-- Step 3: Credit $500 to Bob
UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 'bob-456';
-- Step 4: Record audit log entry
INSERT INTO transfer_audit_log (sender_id, recipient_id, amount, status)
VALUES ('alice-123', 'bob-456', 500.00, 'COMPLETED');
-- Commit flushes WAL record to non-volatile disk (fsync)
COMMIT;Visual representation of control loops, memory layout, and execution flow for Transactions, ACID Guarantees & Write-Ahead Logging (WAL).
1. Active: Initial state where transaction executes read/write statements. 2. Partially Committed: Final statement executed, but changes reside in volatile memory buffer pools; not yet synced to disk. 3. Committed: Transaction writes commit log to WAL and calls fsync(); permanently durable. 4. Failed: Execution discovers error (constraint violation, deadlock) or hardware crashes. 5. Aborted: Engine executes Rollback using Undo Logs to restore state, then either restarts or terminates transaction.
Atomicity ("All or Nothing"): Entire sequence commits successfully or entire batch rolls back via Undo Logs. Consistency: Transaction transforms database from one valid state satisfying all schema rules, constraints, and foreign keys to another valid state. Isolation: Concurrent transactions execute without mutual interference, simulating sequential execution. Durability: Once COMMIT acknowledges, changes survive power outages, server crashes, and OS panics via Redo Logs.
Steal Policy: Can the buffer manager write dirty uncommitted pages to disk to free RAM? (STEAL requires UNDO logging to revert uncommitted disk writes on crash; NO-STEAL requires massive RAM). Force Policy: Must the buffer manager flush ALL modified table pages to disk before COMMIT returns? (FORCE destroys write throughput; NO-FORCE allows lazy page flushing, requiring REDO logging to reconstruct committed data on crash). Modern high-performance engines (PostgreSQL, MySQL InnoDB, Oracle) use STEAL / NO-FORCE + WAL for maximum throughput!
WAL Protocol: Log records containing before-image (Undo) and after-image (Redo) MUST be flushed to disk before the corresponding dirty data page is written to disk. ARIES 3-Phase Crash Recovery: 1. Analysis Phase: Scans WAL forward from last checkpoint to identify dirty pages in buffer pool and active (loser) transactions at crash time. 2. Redo Phase: Repeats history by rolling forward all logged actions to restore state exactly as it was prior to crash. 3. Undo Phase: Rolls backward to undo actions of uncommitted active transactions in reverse chronological order.
| Feature / Dimension | Undo Log (Rollback / Before-Image) | Redo Log (Durability / After-Image) |
|---|---|---|
| Core Purpose | Enforces Atomicity and MVCC by storing previous state to revert uncommitted transactions | Enforces Durability by storing new state to re-apply committed changes after crash |
| Recovery Action | Scanned during Undo Phase of crash recovery to reverse uncommitted modifications | Scanned during Redo Phase of crash recovery to replay committed modifications |
| Buffer Policy Need | Required because of the STEAL policy (uncommitted dirty pages flushed to disk) | Required because of the NO-FORCE policy (committed pages not yet flushed to disk) |
Detailed answers, interviewer pro tips, key takeaway summaries, and code examples formulated for technical rounds.
✅ Correction: Atomicity means "all or nothing" execution (reverting partial updates). Consistency means preserving business invariants, integrity constraints, and schema rules.
✅ Correction: COMMIT only flushes the sequential WAL log buffer to disk (via fsync). Actual table data pages are flushed asynchronously in the background by dirty page writers.
Atomic execution abstractions and write-ahead log recovery protocols.