ACID Properties: The Foundation of Reliable Transactions

Write-Ahead Logging Process:
 
                    Memory                              Disk
┌─────────────────────────────────┐      ┌─────────────────────────────┐
│                                 │      │                             │
│  ┌──────────────┐              │      │  ┌────────────────────────┐ │
│  │ Transaction  │   (1) Write   │      │  │                        │ │
│  │ Operations   ├──────────────────────►│ WAL Log                 │ │
│  └──────────────┘   Log Record  │      │  │ (Sequential writes)   │ │
│        │                        │      │  │                        │ │
│        │                        │      │  └───────────┬────────────┘ │
│        │                        │      │              │  ▲          │
│        ▼                        │      │          (3) │  │ (5)      │
│  ┌──────────────┐              │      │    Recovery │  │ Replay    │
│  │ Buffer Pool  │              │      │              ▼  │          │
│  │ (Data Pages  │              │      │  ┌────────────────────────┐ │
│  │  in Memory)  │   (4) Lazy    │      │  │                        │ │
│  └──────┬───────┤   Checkpoint  ├──────►│ Data Files              │ │
│         │       │              │      │  │ (Random writes)        │ │
│         ▼       │              │      │  │                        │ │
│  (2) Modify     │              │      │  └────────────────────────┘ │
│     in memory   │              │      │                             │
│                                 │      │                             │
└─────────────────────────────────┘      └─────────────────────────────┘
 
1. Transaction writes to WAL first (force to disk on commit)
2. Data pages modified in memory only
3. On crash, recovery reads WAL to know what was committed
4. Background checkpoint writes dirty pages to disk
5. WAL is replayed to redo any committed changes not in data files

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Conceptual structure of WAL log records
-- (Actual binary format varies by database)
 
-- Transaction begins
LSN: 000001  TxnID: T1  Type: BEGIN  Timestamp: 2024-01-15 10:30:00.001
 
-- Update operation with before/after images  
LSN: 000002  TxnID: T1  Type: UPDATE
    PageID: accounts_page_42  Offset: 384  Length: 64
    BeforeImage: {id: 1, balance: 1000, ...}
    AfterImage:  {id: 1, balance: 700, ...}
 
-- Another update in same transaction
LSN: 000003  TxnID: T1  Type: UPDATE
    PageID: accounts_page_107  Offset: 192  Length: 64  
    BeforeImage: {id: 2, balance: 500, ...}
    AfterImage:  {id: 2, balance: 800, ...}
 
-- Commit record - THE DURABILITY GUARANTEE
-- Once this is on disk, the transaction is committed forever
LSN: 000004  TxnID: T1  Type: COMMIT  Timestamp: 2024-01-15 10:30:00.047
-- CRITICAL: COMMIT record must be fsync'd before acknowledging to client

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- PostgreSQL: Configure commit behavior for durability vs. performance
 
-- Full durability (default) - synchronous fsync on every commit
-- Safest, but lowest throughput
SET synchronous_commit = on;
 
-- Commit returns after WAL is sent to synchronous standby
-- Provides durability across server failure (if standby survives)
SET synchronous_commit = remote_apply;
 
-- Commit returns before fsync completes (DANGEROUS!)
-- Data may be lost on crash but not visible to other transactions
-- Only use when you can tolerate data loss
SET synchronous_commit = off;
 
-- PostgreSQL group commit: delay to batch commits
-- Increase for throughput at cost of latency
SET commit_delay = 10;  -- microseconds to wait before fsync
SET commit_siblings = 5; -- minimum concurrent transactions to trigger delay
 
-- MySQL InnoDB: Similar concept
-- innodb_flush_log_at_trx_commit = 1  (full durability, default)
-- innodb_flush_log_at_trx_commit = 2  (flush to OS buffer each commit, fsync once/second)
-- innodb_flush_log_at_trx_commit = 0  (no flush, dangerous)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- PostgreSQL checkpoint configuration
 
-- Maximum time between automatic checkpoints (seconds)
-- Lower = faster recovery, more I/O overhead
-- Higher = longer recovery, less I/O overhead
SET checkpoint_timeout = '5min';
 
-- Maximum WAL size before forcing a checkpoint
-- Limits how much log must be replayed on recovery
SET max_wal_size = '1GB';
 
-- Spread checkpoint I/O over this fraction of checkpoint_timeout
-- 0.5 = spread writes over half the checkpoint interval
-- Reduces I/O spikes
SET checkpoint_completion_target = 0.9;
 
-- Force a manual checkpoint (useful before maintenance)
CHECKPOINT;
 
-- MySQL InnoDB equivalent concepts:
-- innodb_log_file_size - determines recovery replay time
-- innodb_max_dirty_pages_pct - triggers flushing when buffer pool gets too dirty
-- innodb_adaptive_flushing - adjusts flush rate based on workload

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

Recovery Example:
 
WAL Contents at Crash:
─────────────────────────────────────────────────────────────────────
LSN 1000: CHECKPOINT (all pages clean at this point)
LSN 1001: T1 BEGIN
LSN 1002: T1 UPDATE Page_A (balance: 1000 → 700)
LSN 1003: T2 BEGIN
LSN 1004: T2 UPDATE Page_B (quantity: 50 → 45)
LSN 1005: T1 COMMIT  ← T1 is committed (durable)
LSN 1006: T2 UPDATE Page_C (status: 'pending' → 'processing')
<<< CRASH >>> (T2 never committed)
 
Analysis Phase:
- T1: Has COMMIT at LSN 1005 → Will be redone if necessary
- T2: No COMMIT → Will be undone (active at crash)
- Dirty pages: A, B, C potentially need work
 
Redo Phase (forward from checkpoint):
- LSN 1002: Check Page_A's LSN. If < 1002, redo the update
- LSN 1004: Check Page_B's LSN. If < 1004, redo the update
- LSN 1006: Check Page_C's LSN. If < 1006, redo the update
(We redo ALL operations to get data files current, even for T2)
 
Undo Phase (backward for uncommitted transactions):
- T2 at LSN 1006: Undo Page_C (restore 'pending')
- T2 at LSN 1004: Undo Page_B (restore 50)
- Write CLR records for each undo operation
 
Final State:
- Page_A: balance = 700 (T1's committed change preserved)
- Page_B: quantity = 50 (T2's change undone)
- Page_C: status = 'pending' (T2's change undone)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- PostgreSQL: Configure synchronous replication for durability
 
-- On primary: require confirmation from at least one synchronous standby
-- before reporting commit success
SET synchronous_standby_names = 'first 1 (standby1, standby2)';
 
-- Commit modes:
-- 'on' (default): Wait for WAL to be flushed on primary
-- 'remote_write': Wait for WAL to reach standby's memory
-- 'remote_apply': Wait for WAL to be applied on standby (strongest)
SET synchronous_commit = 'remote_apply';
 
-- This configuration means:
-- 1. Primary writes WAL to local disk
-- 2. Primary sends WAL to standby
-- 3. Standby writes WAL to disk and applies it
-- 4. Standby acknowledges to primary
-- 5. Primary acknowledges commit to client
-- 
-- If primary fails after step 5, standby has all committed data
-- Client has received confirmation only after data exists in two places

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

#!/bin/bash
# Simple durability test script
 
# Insert a record with a unique timestamp
TEST_ID=$(date +%s)
psql -c "INSERT INTO durability_test (id, created_at) VALUES ($TEST_ID, NOW());"
echo "Inserted test record: $TEST_ID"
 
# Wait for any async replication
sleep 2
 
# Verify the record exists
COUNT=$(psql -t -c "SELECT COUNT(*) FROM durability_test WHERE id = $TEST_ID;")
if [ "$COUNT" -eq 1 ]; then
    echo "Record confirmed in database"
else
    echo "ERROR: Record not found after insert!"
    exit 1
fi
 
# Now: manually kill the database server (kill -9 or power off)
# Then: restart and verify the record still exists
 
# After restart:
# COUNT=$(psql -t -c "SELECT COUNT(*) FROM durability_test WHERE id = $TEST_ID;")
# if [ "$COUNT" -eq 1 ]; then
#     echo "PASS: Record survived crash"
# else
#     echo "FAIL: Record lost - durability compromised!"
# fi