Prevention — The Phantom Read Problem | OneNoughtOne

0/131

Database Management SystemsPhantom Read Problem

The Phantom Read Problem

LevelIntermediate

Duration55 mins

TopicPhantom Read Problem

5 / 5

Prevention

Defending Against Phantom Reads

Understanding phantom reads is only half the battle—knowing how to prevent them is what separates theoretical knowledge from practical engineering capability. This page presents a comprehensive toolkit of prevention strategies, from the heavy-handed (table locks) to the sophisticated (Serializable Snapshot Isolation), with clear guidance on when to apply each approach. Effective phantom prevention requires balancing correctness against performance. The goal is not to eliminate all phantom possibilities everywhere, but to apply the right level of protection at the right points in your application.

What You Will Learn

By the end of this page, you will master database-level phantom prevention mechanisms, understand application-level mitigation patterns, implement proper verification and retry logic, design schemas that reduce phantom surface area, and choose the appropriate strategy for different use cases.

SERIALIZABLE Isolation Level

The most straightforward approach to phantom prevention is using SERIALIZABLE isolation—the highest level defined by the SQL standard. At this level, the database guarantees that concurrent transactions behave as if they executed serially (one after another), eliminating all concurrency anomalies including phantom reads.

How SERIALIZABLE Prevents Phantoms: Different database systems implement SERIALIZABLE isolation using different mechanisms: Strict Two-Phase Locking (S2PL) with Predicate Locks — Traditional approach: locks on predicates (search conditions) block conflicting operations. Used by SQL Server with key-range locks. Serializable Snapshot Isolation (SSI) — Modern approach: uses MVCC for reads, detects conflicts at commit time, aborts transactions with serialization conflicts. Used by PostgreSQL. Gap Locking — MySQL InnoDB's approach: locks the gaps between index keys, preventing insertions that would create phantoms.

serializable-implementation.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- ============================================
-- PostgreSQL: Serializable Snapshot Isolation (SSI)
-- ============================================
-- PostgreSQL uses optimistic concurrency control
-- Transactions proceed without blocking, conflicts detected at commit
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- PostgreSQL records that this predicate was read
-- Any concurrent insert/update matching this predicate
-- will cause a serialization failure at commit time
 
-- ... application logic ...
 
COMMIT;  -- May fail with serialization_failure if conflict detected
 
-- Proper retry pattern for SSI:
DO $$
DECLARE
    max_retries INT := 5;
    retry_count INT := 0;
BEGIN
    LOOP
        BEGIN
            -- Your transaction here
            PERFORM process_critical_operation();
            EXIT;  -- Success, exit loop
        EXCEPTION
            WHEN serialization_failure THEN
                retry_count := retry_count + 1;
                IF retry_count >= max_retries THEN
                    RAISE EXCEPTION 'Max retries exceeded';
                END IF;
                -- Backoff before retry
                PERFORM pg_sleep(0.1 * retry_count);
        END;
    END LOOP;
END $$;
 
-- ============================================
-- MySQL InnoDB: Gap Locking at SERIALIZABLE
-- ============================================
-- MySQL uses pessimistic locking with gap locks
-- Transactions may block waiting for conflicting locks
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
 
SELECT * FROM accounts WHERE balance > 100000 FOR UPDATE;
-- InnoDB acquires:
--   - X-locks on all matching rows
--   - Gap locks on ranges between matching values
--   - Next-key locks combining row and gap protection
-- Concurrent inserts into locked ranges will BLOCK
 
-- ... application logic ...
 
COMMIT;
 
-- ============================================
-- SQL Server: Key-Range Locks at SERIALIZABLE
-- ============================================
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
 
SELECT * FROM accounts WHERE balance BETWEEN 100000 AND 500000;
-- SQL Server acquires key-range locks on the index
-- Covers the range [100000, 500000]
-- Concurrent inserts/updates in this range block
 
COMMIT;

SERIALIZABLE Advantages

•Strongest guarantee — All anomalies prevented
•Simple mental model — Transactions appear serial
•No application changes — Just set isolation level
•Correct by construction — Can't accidentally miss phantoms

SERIALIZABLE Disadvantages

•Reduced concurrency — More blocking/aborts
•Performance overhead — Lock management cost
•Retry logic required — SSI may abort transactions
•Potential deadlocks — Lock-based implementations

When to Use SERIALIZABLE

Use SERIALIZABLE for: financial transactions, audit operations, inventory reservations, and any operation where phantom reads would cause data corruption or compliance failure. Avoid for: read-heavy analytics, reporting aggregations (where slight staleness is acceptable), and high-throughput OLTP with low phantom sensitivity.

Gap Locking and Next-Key Locking

Gap locks and next-key locks are the primary mechanisms by which lock-based databases prevent phantom reads. Understanding their mechanics enables you to predict locking behavior and optimize for both correctness and concurrency.

Lock Types in InnoDB: Record Lock — Locks a single index record. Prevents modification of that specific row. Gap Lock — Locks the gap between two index records. Prevents insertions into that gap. Two gap locks on the same gap don't conflict with each other. Next-Key Lock — Combination of record lock and gap lock on the gap before the record. This is InnoDB's default locking mode for index scans. Insert Intention Lock — A gap lock variant that signals intent to insert. Allows concurrent inserts if they don't conflict on the same position.

gap-lock-analysis.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- ============================================
-- Understanding Gap Lock Behavior in InnoDB
-- ============================================
 
-- Setup: Table with id column, values: 10, 20, 30, 40
CREATE TABLE t (id INT PRIMARY KEY);
INSERT INTO t VALUES (10), (20), (30), (40);
 
-- Session 1: Locks range with next-key locking
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
 
SELECT * FROM t WHERE id BETWEEN 15 AND 35 FOR UPDATE;
-- Returns: 20, 30
-- Locks acquired:
--   Gap lock: (10, 20)   - prevents insert of 11-19
--   Next-key lock on 20: record 20 + gap (10, 20)
--   Gap lock: (20, 30)   - prevents insert of 21-29
--   Next-key lock on 30: record 30 + gap (20, 30)
--   Gap lock: (30, 40)   - prevents insert of 31-39
 
-- Session 2: Attempts various operations
-- In another connection:
 
INSERT INTO t VALUES (25);  -- BLOCKS on gap lock (20, 30)
INSERT INTO t VALUES (15);  -- BLOCKS on gap lock (10, 20)
INSERT INTO t VALUES (5);   -- SUCCEEDS (before locked range)
INSERT INTO t VALUES (50);  -- SUCCEEDS (after locked range)
UPDATE t SET id = 25 WHERE id = 20;  -- BLOCKS (record locked)
 
-- ============================================
-- Gap Lock on Non-Existent Values
-- ============================================
BEGIN;
 
SELECT * FROM t WHERE id = 15 FOR UPDATE;
-- Returns: nothing (no row with id 15)
-- Still acquires gap lock on (10, 20)!
-- This prevents phantom: if another tx inserts 15,
-- a repeated query would return different results
 
-- Session 2:
INSERT INTO t VALUES (15);  -- BLOCKS on gap lock
INSERT INTO t VALUES (10);  -- Might block (depends on exact lock)
INSERT INTO t VALUES (20);  -- Might block (depends on exact lock)
 
COMMIT;
 
-- ============================================
-- Supremum Lock for Unbounded Ranges
-- ============================================
BEGIN;
 
SELECT * FROM t WHERE id > 30 FOR UPDATE;
-- Returns: 40
-- Locks:
--   Next-key lock on 40: record 40 + gap (30, 40)
--   Gap lock on (40, supremum): prevents ANY insert > 40
 
-- Session 2:
INSERT INTO t VALUES (50);   -- BLOCKS (in supremum gap)
INSERT INTO t VALUES (999);  -- BLOCKS (in supremum gap)
INSERT INTO t VALUES (25);   -- SUCCEEDS (below locked range)
 
COMMIT;

Gap Lock Gotchas

Gap locks are held even if no rows are found—querying for a non-existent value still locks the gap where it would be inserted. This can cause unexpected blocking. Also, gap locks don't prevent other gap locks (only INSERT intention locks), so SELECT...FOR UPDATE statements don't block each other on gaps.

Predicate Locking and Key-Range Locking

Predicate locking is the theoretical ideal for phantom prevention—locking the logical condition (predicate) rather than physical rows. While pure predicate locking is computationally expensive, key-range locking approximates it efficiently using index structures.

The Predicate Lock Concept: Instead of locking rows {r1, r2, r3}, lock the predicate P: salary > 100000 . Any operation that might insert a row satisfying P, or modify an existing row to satisfy P, must wait for the predicate lock. Why Pure Predicate Locking Is Hard: Predicate comparison complexity : Determining if two predicates "conflict" (could affect the same rows) requires solving the predicate satisfiability problem—potentially NP-hard. Infinite predicates : The number of possible predicates is infinite; tracking them all is infeasible. Dynamic evaluation : Some predicates include functions, subqueries, or correlated values that can't be statically analyzed. Key-Range Locking Approximation: SQL Server's key-range locking uses the index structure to approximate predicate locking: Predicates are translated to index key ranges Ranges are locked in the index Any operation touching a locked range waits

key-range-locking.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- ============================================
-- SQL Server Key-Range Locking
-- ============================================
 
-- Setup: Employees table with index on salary
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(12, 2),
    INDEX idx_salary (salary)
);
 
-- At SERIALIZABLE isolation, key-range locks are automatic
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
 
SELECT * FROM employees 
WHERE salary BETWEEN 100000 AND 150000;
-- SQL Server acquires key-range locks on idx_salary
-- Specifically: RangeS-S locks on the scanned range
-- 
-- Lock modes:
-- RangeS-S: Range shared, key shared (read range)
-- RangeX-X: Range exclusive, key exclusive (write range)
-- RangeI-N: Insert intention (compatible with others)
 
-- In this locked state:
-- - Another SELECT on overlapping range: ALLOWED (S compatible)
-- - INSERT with salary = 125000: BLOCKED (in locked range)
-- - UPDATE setting salary to 125000: BLOCKED (enters range)
-- - DELETE of row with salary 125000: BLOCKED (row in range)
-- - INSERT with salary = 50000: ALLOWED (outside range)
 
COMMIT;
 
-- ============================================
-- Viewing Key-Range Locks
-- ============================================
SELECT 
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
 
-- Example output:
-- | resource_type | resource_description    | request_mode | request_status |
-- |---------------|-------------------------|--------------|----------------|
-- | KEY           | (100000..150000)        | RangeS-S     | GRANT          |
-- | KEY           | (150000, supremum)      | RangeS-S     | WAIT           |
 
-- ============================================
-- Predicate Lock Approximation Trade-offs
-- ============================================
 
-- Complex predicate: salary > 100000 AND department = 'Engineering'
-- If index exists on (department, salary):
--   Efficient key-range lock on the combined key
 
-- If only separate indexes exist:
--   May acquire broader locks than necessary
--   Performance vs precision trade-off
 
-- No index on predicate columns:
--   Table scan required for predicate evaluation
--   May escalate to table-level lock

Index Design for Locking

Your index design directly affects locking granularity. Indexes that match your phantom-sensitive predicates enable precise key-range locking. Without appropriate indexes, the database may acquire broader locks (table locks) to ensure correctness, severely impacting concurrency.

Serializable Snapshot Isolation (SSI)

Serializable Snapshot Isolation (SSI), pioneered by PostgreSQL, represents a modern approach to preventing phantoms without the blocking overhead of traditional locking. SSI uses optimistic concurrency control combined with sophisticated conflict detection.

How SSI Works: Snapshot Reads : Each transaction reads from a consistent snapshot taken at transaction start. No read locks are acquired. Writes create new versions (MVCC). Conflict Detection : The database tracks which predicates each transaction reads (SIREAD locks—logical, not blocking). When a transaction writes data that would affect another transaction's reads, a potential conflict is noted. Dependency Graph Analysis : SSI maintains a graph of dependencies between transactions. It looks for "dangerous structures"—patterns of rw-conflicts (read-write) that could indicate serialization anomalies. Abort on Danger : When a dangerous structure is detected (typically two rw-conflicts forming a cycle), one of the transactions is aborted to maintain serializability. The Dangerous Structure: SSI specifically looks for: T1 reads something T2 will write (T1 \torw T2) T2 reads something T1 will write (T2 \torw T1) If both conditions hold (forming a cycle), at least one transaction must be aborted.

ssi-in-action.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
-- ============================================
-- PostgreSQL SSI: Detecting Phantom Conflicts
-- ============================================
 
-- Session 1: Audit transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- PostgreSQL creates SIREAD lock on predicate: 
-- "accounts.branch = 'Downtown'"
-- This doesn't block anyone—just records the read
 
-- Session 2: Concurrent insert
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
INSERT INTO accounts (branch, balance) 
VALUES ('Downtown', 50000);
-- PostgreSQL notes: this write affects Session 1's predicate read
-- Creates rw-dependency: Session 1 →rw Session 2
 
-- If Session 2 also read something Session 1 will write,
-- we'd have a dangerous cycle
 
COMMIT;  -- May succeed or fail based on overall conflict pattern
 
-- Session 1 commits:
COMMIT;  -- May get: ERROR: could not serialize access 
          --         due to read/write dependencies among transactions
 
-- ============================================
-- SSI False Positive Scenario
-- ============================================
-- SSI may abort transactions that wouldn't actually 
-- violate serializability, because it uses conservative
-- conflict detection.
 
-- Example: Two transactions that happen to touch 
-- overlapping predicates but don't actually conflict
-- semantically may still be aborted.
 
-- Session 1: Reads high-balance accounts
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE balance > 100000;
-- Session 1 hasn't decided what to do yet
 
-- Session 2: Inserts a low-balance account
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO accounts (balance) VALUES (500);
COMMIT;  -- Probably succeeds
 
-- Session 3: Inserts a high-balance account  
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO accounts (balance) VALUES (200000);
COMMIT;  -- Conflicts with Session 1's read!
 
-- Now Session 1 tries to commit:
COMMIT;  -- Aborted due to serialization failure
-- Even if Session 1 was just going to report the data,
-- not modify anything, SSI doesn't know that.
 
-- ============================================
-- Proper SSI Retry Logic
-- ============================================
-- Application code must handle serialization failures
 
CREATE OR REPLACE FUNCTION transfer_funds(
    from_acc INT, to_acc INT, amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retries INT := 0;
    max_retries INT := 3;
BEGIN
    LOOP
        BEGIN
            -- Start serializable transaction
            SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
            
            -- Perform transfer
            UPDATE accounts SET balance = balance - amount 
            WHERE id = from_acc;
            UPDATE accounts SET balance = balance + amount 
            WHERE id = to_acc;
            
            -- If we get here, no conflict
            RETURN TRUE;
            
        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                retries := retries + 1;
                IF retries >= max_retries THEN
                    RAISE EXCEPTION 'Transfer failed after % retries', retries;
                END IF;
                -- Exponential backoff
                PERFORM pg_sleep(0.1 * power(2, retries));
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SSI vs Traditional Locking Comparison
Aspect	SSI (PostgreSQL)	S2PL with Gap Locks (MySQL/SQL Server)
Blocking behavior	Readers never block writers, or vice versa	Readers may block writers, writers always block
Phantom prevention	Detected at commit via dependency tracking	Prevented by acquiring gap/range locks
False positives	May abort transactions that wouldn't actually violate	No false positives, but may deadlock
Performance characteristic	Higher throughput under read-heavy loads	Better for write-heavy predictable patterns
Retry requirement	Mandatory retry logic for aborted transactions	Retry for deadlocks, less frequent
Lock memory	Lower (SIREAD locks are compact)	Higher (gap locks can be numerous)

SSI Advantages

SSI's optimistic approach often provides better throughput than pessimistic locking, especially for read-heavy workloads. However, applications must be designed to handle and retry serialization failures gracefully.

Application-Level Prevention Strategies

When database isolation levels are insufficient or too costly, application-level strategies can mitigate phantom risks. These approaches require careful implementation but offer flexibility and control.

Explicit ID Capture Strategy: Capturing explicit row IDs at the start of a transaction and using them consistently throughout prevents phantoms by anchoring to known rows rather than predicates.

snapshot-capture.sql
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
-- Snapshot Capture Pattern
BEGIN TRANSACTION;
 
-- Step 1: Capture the IDs that match our predicate
CREATE TEMP TABLE snapshot_ids AS
SELECT account_id
FROM accounts
WHERE branch = 'Downtown' AND status = 'active';
 
-- Step 2: All subsequent operations use the snapshot
SELECT SUM(balance) as total
FROM accounts a
INNER JOIN snapshot_ids s ON a.account_id = s.account_id;
-- This can only return rows that existed at capture time!
 
-- Step 3: Generate payments using snapshot
INSERT INTO payments (account_id, amount)
SELECT a.account_id, a.balance * 0.01
FROM accounts a
INNER JOIN snapshot_ids s ON a.account_id = s.account_id;
-- Guaranteed same set as Step 2
 
-- Step 4: Verify count consistency
SELECT 
    (SELECT COUNT(*) FROM snapshot_ids) as expected,
    (SELECT COUNT(*) FROM payments 
     WHERE created_in_this_batch = TRUE) as actual;
-- If these match, phantom-free within transaction
 
DROP TABLE snapshot_ids;
COMMIT;

When to Use Snapshot Capture

This pattern works well for batch operations (payroll, report generation, bulk updates) where you need consistent processing of a known set. It doesn't prevent the underlying database from having new rows—it just ensures your transaction processes a stable set.

Schema Design for Phantom Resistance

Thoughtful schema design can reduce phantom surface area by structuring data in ways that minimize the impact of concurrent modifications on critical operations.

Schema Design Principles

•Aggregate Tables — Maintain pre-computed aggregates that are atomically updated with each insert/delete. Queries read aggregates instead of computing from raw data, avoiding phantom-sensitive scans.
•Closed Periods — For time-based queries, mark periods as "closed" when they should no longer accept new entries. Queries on closed periods are phantom-free by design.
•Batch Identifiers — Assign batch IDs to related records. Process by specific batch ID rather than time ranges or predicates.
•Soft Deletes with Effective Dating — Instead of physical deletes, use effective dates. Phantoms become visible modifications rather than invisible disappearances.
•Narrow Predicate Surfaces — Design queries to use specific identifiers rather than broad predicates whenever possible.

phantom-resistant-schema.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
-- ============================================
-- PATTERN 1: Aggregate Tables
-- ============================================
-- Instead of: SELECT SUM(amount) FROM sales WHERE date = TODAY
-- Maintain a running aggregate
 
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    amount DECIMAL(12,2),
    sale_date DATE
);
 
CREATE TABLE daily_totals (
    sale_date DATE PRIMARY KEY,
    total_amount DECIMAL(14,2) DEFAULT 0,
    sale_count INT DEFAULT 0,
    last_updated TIMESTAMP
);
 
-- Trigger maintains aggregate
CREATE FUNCTION update_daily_total() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO daily_totals (sale_date, total_amount, sale_count, last_updated)
    VALUES (NEW.sale_date, NEW.amount, 1, NOW())
    ON CONFLICT (sale_date) DO UPDATE
    SET total_amount = daily_totals.total_amount + NEW.amount,
        sale_count = daily_totals.sale_count + 1,
        last_updated = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Query reads pre-computed total (single row, no phantom risk)
SELECT total_amount FROM daily_totals WHERE sale_date = CURRENT_DATE;
 
-- ============================================
-- PATTERN 2: Closed Periods
-- ============================================
CREATE TABLE reporting_periods (
    period_id SERIAL PRIMARY KEY,
    period_start DATE,
    period_end DATE,
    status VARCHAR(20) DEFAULT 'open',  -- 'open', 'closing', 'closed'
    closed_at TIMESTAMP,
    final_total DECIMAL(14,2)
);
 
-- Close a period atomically
CREATE FUNCTION close_period(p_period_id INT) RETURNS VOID AS $$
BEGIN
    -- Calculate final total at closing moment
    UPDATE reporting_periods
    SET status = 'closed',
        closed_at = NOW(),
        final_total = (
            SELECT SUM(amount) FROM sales 
            WHERE sale_date BETWEEN period_start AND period_end
            AND period_id = p_period_id
        )
    WHERE period_id = p_period_id;
    
    -- Future queries use closed_total, immune to phantoms
END;
$$ LANGUAGE plpgsql;
 
-- ============================================
-- PATTERN 3: Batch Processing with IDs
-- ============================================
CREATE TABLE payroll_batches (
    batch_id SERIAL PRIMARY KEY,
    batch_date DATE,
    status VARCHAR(20),  -- 'collecting', 'processing', 'complete'
    created_at TIMESTAMP
);
 
CREATE TABLE payroll_entries (
    entry_id SERIAL PRIMARY KEY,
    batch_id INT REFERENCES payroll_batches(batch_id),
    employee_id INT,
    amount DECIMAL(12,2)
);
 
-- Lock batch when ready to process
UPDATE payroll_batches SET status = 'processing' WHERE batch_id = 123;
 
-- Process by batch_id, not by predicate
SELECT * FROM payroll_entries WHERE batch_id = 123;
-- No phantom risk: batch membership is fixed once we lock it

Design for the Critical Path

Not all operations need phantom protection. Focus schema design efforts on the critical paths—financial summaries, compliance reports, inventory checks. Less critical operations (analytics, non-binding dashboards) may tolerate phantom risk in exchange for simplicity.

Table Hints and Explicit Locking

When you need precise control over locking behavior, many databases offer table hints or explicit locking commands that override default isolation level behavior.

explicit-locking-examples.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- ============================================
-- SQL Server Table Hints
-- ============================================
 
-- Force serializable behavior for specific query
SELECT * FROM accounts WITH (HOLDLOCK, SERIALIZABLE)
WHERE branch = 'Downtown';
-- HOLDLOCK: Hold locks until transaction end (like repeatable read)
-- SERIALIZABLE: Acquire key-range locks (phantom prevention)
 
-- Aggressive protection: exclusive lock entire table
SELECT * FROM accounts WITH (TABLOCKX, HOLDLOCK)
WHERE branch = 'Downtown';
-- No concurrent readers OR writers until commit
-- Maximum protection, minimum concurrency
 
-- ============================================
-- PostgreSQL Explicit Locking
-- ============================================
 
-- Lock table in share mode (allows reads, blocks writes)
LOCK TABLE accounts IN SHARE MODE;
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- No inserts/updates/deletes allowed until commit
 
-- Lock table exclusively (blocks everything)
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Complete isolation, but severe concurrency impact
 
-- Row-level FOR UPDATE/FOR SHARE
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR UPDATE;
-- Locks matching rows, PostgreSQL also uses predicate locks
-- at SERIALIZABLE level for phantom prevention
 
-- NOWAIT option: Fail immediately if lock unavailable
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR UPDATE NOWAIT;
-- Returns error if rows are locked by another transaction
 
-- SKIP LOCKED: Skip locked rows (useful for job queues)
SELECT * FROM accounts 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- Get unlocked rows only
 
-- ============================================
-- MySQL Locking Hints
-- ============================================
 
-- FOR UPDATE locks for write
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR UPDATE;
-- Acquires X-locks and gap locks at REPEATABLE READ
 
-- FOR SHARE locks for read
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR SHARE;
-- Acquires S-locks, allows concurrent S-locks
 
-- NOWAIT and SKIP LOCKED (MySQL 8.0+)
SELECT * FROM accounts WHERE status = 'pending'
FOR UPDATE SKIP LOCKED;
 
-- ============================================
-- Advisory Locks (PostgreSQL)
-- ============================================
-- Application-managed locks, not tied to rows
 
SELECT pg_advisory_lock(hashtext('accounts_branch_Downtown'));
-- Hold advisory lock for critical section
 
-- Perform phantom-sensitive operation
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- Other transactions calling pg_advisory_lock with same key will wait
 
SELECT pg_advisory_unlock(hashtext('accounts_branch_Downtown'));
-- Release the lock

Explicit Locking Trade-offs

Explicit locking provides precise control but increases complexity and deadlock risk. Over-aggressive locking can create severe contention. Use explicit locking surgically—only where database isolation levels are genuinely insufficient.

Database Management SystemsPhantom Read Problem

The Phantom Read Problem

LevelIntermediate

Duration55 mins

TopicPhantom Read Problem

5 / 5

Prevention

Defending Against Phantom Reads

Understanding phantom reads is only half the battle—knowing how to prevent them is what separates theoretical knowledge from practical engineering capability. This page presents a comprehensive toolkit of prevention strategies, from the heavy-handed (table locks) to the sophisticated (Serializable Snapshot Isolation), with clear guidance on when to apply each approach. Effective phantom prevention requires balancing correctness against performance. The goal is not to eliminate all phantom possibilities everywhere, but to apply the right level of protection at the right points in your application.

What You Will Learn

By the end of this page, you will master database-level phantom prevention mechanisms, understand application-level mitigation patterns, implement proper verification and retry logic, design schemas that reduce phantom surface area, and choose the appropriate strategy for different use cases.

SERIALIZABLE Isolation Level

The most straightforward approach to phantom prevention is using SERIALIZABLE isolation—the highest level defined by the SQL standard. At this level, the database guarantees that concurrent transactions behave as if they executed serially (one after another), eliminating all concurrency anomalies including phantom reads.

How SERIALIZABLE Prevents Phantoms: Different database systems implement SERIALIZABLE isolation using different mechanisms: Strict Two-Phase Locking (S2PL) with Predicate Locks — Traditional approach: locks on predicates (search conditions) block conflicting operations. Used by SQL Server with key-range locks. Serializable Snapshot Isolation (SSI) — Modern approach: uses MVCC for reads, detects conflicts at commit time, aborts transactions with serialization conflicts. Used by PostgreSQL. Gap Locking — MySQL InnoDB's approach: locks the gaps between index keys, preventing insertions that would create phantoms.

serializable-implementation.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- ============================================
-- PostgreSQL: Serializable Snapshot Isolation (SSI)
-- ============================================
-- PostgreSQL uses optimistic concurrency control
-- Transactions proceed without blocking, conflicts detected at commit
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- PostgreSQL records that this predicate was read
-- Any concurrent insert/update matching this predicate
-- will cause a serialization failure at commit time
 
-- ... application logic ...
 
COMMIT;  -- May fail with serialization_failure if conflict detected
 
-- Proper retry pattern for SSI:
DO $$
DECLARE
    max_retries INT := 5;
    retry_count INT := 0;
BEGIN
    LOOP
        BEGIN
            -- Your transaction here
            PERFORM process_critical_operation();
            EXIT;  -- Success, exit loop
        EXCEPTION
            WHEN serialization_failure THEN
                retry_count := retry_count + 1;
                IF retry_count >= max_retries THEN
                    RAISE EXCEPTION 'Max retries exceeded';
                END IF;
                -- Backoff before retry
                PERFORM pg_sleep(0.1 * retry_count);
        END;
    END LOOP;
END $$;
 
-- ============================================
-- MySQL InnoDB: Gap Locking at SERIALIZABLE
-- ============================================
-- MySQL uses pessimistic locking with gap locks
-- Transactions may block waiting for conflicting locks
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
 
SELECT * FROM accounts WHERE balance > 100000 FOR UPDATE;
-- InnoDB acquires:
--   - X-locks on all matching rows
--   - Gap locks on ranges between matching values
--   - Next-key locks combining row and gap protection
-- Concurrent inserts into locked ranges will BLOCK
 
-- ... application logic ...
 
COMMIT;
 
-- ============================================
-- SQL Server: Key-Range Locks at SERIALIZABLE
-- ============================================
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
 
SELECT * FROM accounts WHERE balance BETWEEN 100000 AND 500000;
-- SQL Server acquires key-range locks on the index
-- Covers the range [100000, 500000]
-- Concurrent inserts/updates in this range block
 
COMMIT;

SERIALIZABLE Advantages

•Strongest guarantee — All anomalies prevented
•Simple mental model — Transactions appear serial
•No application changes — Just set isolation level
•Correct by construction — Can't accidentally miss phantoms

SERIALIZABLE Disadvantages

•Reduced concurrency — More blocking/aborts
•Performance overhead — Lock management cost
•Retry logic required — SSI may abort transactions
•Potential deadlocks — Lock-based implementations

When to Use SERIALIZABLE

Use SERIALIZABLE for: financial transactions, audit operations, inventory reservations, and any operation where phantom reads would cause data corruption or compliance failure. Avoid for: read-heavy analytics, reporting aggregations (where slight staleness is acceptable), and high-throughput OLTP with low phantom sensitivity.

Gap Locking and Next-Key Locking

Gap locks and next-key locks are the primary mechanisms by which lock-based databases prevent phantom reads. Understanding their mechanics enables you to predict locking behavior and optimize for both correctness and concurrency.

Lock Types in InnoDB: Record Lock — Locks a single index record. Prevents modification of that specific row. Gap Lock — Locks the gap between two index records. Prevents insertions into that gap. Two gap locks on the same gap don't conflict with each other. Next-Key Lock — Combination of record lock and gap lock on the gap before the record. This is InnoDB's default locking mode for index scans. Insert Intention Lock — A gap lock variant that signals intent to insert. Allows concurrent inserts if they don't conflict on the same position.

gap-lock-analysis.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- ============================================
-- Understanding Gap Lock Behavior in InnoDB
-- ============================================
 
-- Setup: Table with id column, values: 10, 20, 30, 40
CREATE TABLE t (id INT PRIMARY KEY);
INSERT INTO t VALUES (10), (20), (30), (40);
 
-- Session 1: Locks range with next-key locking
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
 
SELECT * FROM t WHERE id BETWEEN 15 AND 35 FOR UPDATE;
-- Returns: 20, 30
-- Locks acquired:
--   Gap lock: (10, 20)   - prevents insert of 11-19
--   Next-key lock on 20: record 20 + gap (10, 20)
--   Gap lock: (20, 30)   - prevents insert of 21-29
--   Next-key lock on 30: record 30 + gap (20, 30)
--   Gap lock: (30, 40)   - prevents insert of 31-39
 
-- Session 2: Attempts various operations
-- In another connection:
 
INSERT INTO t VALUES (25);  -- BLOCKS on gap lock (20, 30)
INSERT INTO t VALUES (15);  -- BLOCKS on gap lock (10, 20)
INSERT INTO t VALUES (5);   -- SUCCEEDS (before locked range)
INSERT INTO t VALUES (50);  -- SUCCEEDS (after locked range)
UPDATE t SET id = 25 WHERE id = 20;  -- BLOCKS (record locked)
 
-- ============================================
-- Gap Lock on Non-Existent Values
-- ============================================
BEGIN;
 
SELECT * FROM t WHERE id = 15 FOR UPDATE;
-- Returns: nothing (no row with id 15)
-- Still acquires gap lock on (10, 20)!
-- This prevents phantom: if another tx inserts 15,
-- a repeated query would return different results
 
-- Session 2:
INSERT INTO t VALUES (15);  -- BLOCKS on gap lock
INSERT INTO t VALUES (10);  -- Might block (depends on exact lock)
INSERT INTO t VALUES (20);  -- Might block (depends on exact lock)
 
COMMIT;
 
-- ============================================
-- Supremum Lock for Unbounded Ranges
-- ============================================
BEGIN;
 
SELECT * FROM t WHERE id > 30 FOR UPDATE;
-- Returns: 40
-- Locks:
--   Next-key lock on 40: record 40 + gap (30, 40)
--   Gap lock on (40, supremum): prevents ANY insert > 40
 
-- Session 2:
INSERT INTO t VALUES (50);   -- BLOCKS (in supremum gap)
INSERT INTO t VALUES (999);  -- BLOCKS (in supremum gap)
INSERT INTO t VALUES (25);   -- SUCCEEDS (below locked range)
 
COMMIT;

Gap Lock Gotchas

Gap locks are held even if no rows are found—querying for a non-existent value still locks the gap where it would be inserted. This can cause unexpected blocking. Also, gap locks don't prevent other gap locks (only INSERT intention locks), so SELECT...FOR UPDATE statements don't block each other on gaps.

Predicate Locking and Key-Range Locking

Predicate locking is the theoretical ideal for phantom prevention—locking the logical condition (predicate) rather than physical rows. While pure predicate locking is computationally expensive, key-range locking approximates it efficiently using index structures.

The Predicate Lock Concept: Instead of locking rows {r1, r2, r3}, lock the predicate P: salary > 100000 . Any operation that might insert a row satisfying P, or modify an existing row to satisfy P, must wait for the predicate lock. Why Pure Predicate Locking Is Hard: Predicate comparison complexity : Determining if two predicates "conflict" (could affect the same rows) requires solving the predicate satisfiability problem—potentially NP-hard. Infinite predicates : The number of possible predicates is infinite; tracking them all is infeasible. Dynamic evaluation : Some predicates include functions, subqueries, or correlated values that can't be statically analyzed. Key-Range Locking Approximation: SQL Server's key-range locking uses the index structure to approximate predicate locking: Predicates are translated to index key ranges Ranges are locked in the index Any operation touching a locked range waits

key-range-locking.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- ============================================
-- SQL Server Key-Range Locking
-- ============================================
 
-- Setup: Employees table with index on salary
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(12, 2),
    INDEX idx_salary (salary)
);
 
-- At SERIALIZABLE isolation, key-range locks are automatic
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
 
SELECT * FROM employees 
WHERE salary BETWEEN 100000 AND 150000;
-- SQL Server acquires key-range locks on idx_salary
-- Specifically: RangeS-S locks on the scanned range
-- 
-- Lock modes:
-- RangeS-S: Range shared, key shared (read range)
-- RangeX-X: Range exclusive, key exclusive (write range)
-- RangeI-N: Insert intention (compatible with others)
 
-- In this locked state:
-- - Another SELECT on overlapping range: ALLOWED (S compatible)
-- - INSERT with salary = 125000: BLOCKED (in locked range)
-- - UPDATE setting salary to 125000: BLOCKED (enters range)
-- - DELETE of row with salary 125000: BLOCKED (row in range)
-- - INSERT with salary = 50000: ALLOWED (outside range)
 
COMMIT;
 
-- ============================================
-- Viewing Key-Range Locks
-- ============================================
SELECT 
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
 
-- Example output:
-- | resource_type | resource_description    | request_mode | request_status |
-- |---------------|-------------------------|--------------|----------------|
-- | KEY           | (100000..150000)        | RangeS-S     | GRANT          |
-- | KEY           | (150000, supremum)      | RangeS-S     | WAIT           |
 
-- ============================================
-- Predicate Lock Approximation Trade-offs
-- ============================================
 
-- Complex predicate: salary > 100000 AND department = 'Engineering'
-- If index exists on (department, salary):
--   Efficient key-range lock on the combined key
 
-- If only separate indexes exist:
--   May acquire broader locks than necessary
--   Performance vs precision trade-off
 
-- No index on predicate columns:
--   Table scan required for predicate evaluation
--   May escalate to table-level lock

Index Design for Locking

Your index design directly affects locking granularity. Indexes that match your phantom-sensitive predicates enable precise key-range locking. Without appropriate indexes, the database may acquire broader locks (table locks) to ensure correctness, severely impacting concurrency.

Serializable Snapshot Isolation (SSI)

Serializable Snapshot Isolation (SSI), pioneered by PostgreSQL, represents a modern approach to preventing phantoms without the blocking overhead of traditional locking. SSI uses optimistic concurrency control combined with sophisticated conflict detection.

How SSI Works: Snapshot Reads : Each transaction reads from a consistent snapshot taken at transaction start. No read locks are acquired. Writes create new versions (MVCC). Conflict Detection : The database tracks which predicates each transaction reads (SIREAD locks—logical, not blocking). When a transaction writes data that would affect another transaction's reads, a potential conflict is noted. Dependency Graph Analysis : SSI maintains a graph of dependencies between transactions. It looks for "dangerous structures"—patterns of rw-conflicts (read-write) that could indicate serialization anomalies. Abort on Danger : When a dangerous structure is detected (typically two rw-conflicts forming a cycle), one of the transactions is aborted to maintain serializability. The Dangerous Structure: SSI specifically looks for: T1 reads something T2 will write (T1 \torw T2) T2 reads something T1 will write (T2 \torw T1) If both conditions hold (forming a cycle), at least one transaction must be aborted.

ssi-in-action.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
-- ============================================
-- PostgreSQL SSI: Detecting Phantom Conflicts
-- ============================================
 
-- Session 1: Audit transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- PostgreSQL creates SIREAD lock on predicate: 
-- "accounts.branch = 'Downtown'"
-- This doesn't block anyone—just records the read
 
-- Session 2: Concurrent insert
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
 
INSERT INTO accounts (branch, balance) 
VALUES ('Downtown', 50000);
-- PostgreSQL notes: this write affects Session 1's predicate read
-- Creates rw-dependency: Session 1 →rw Session 2
 
-- If Session 2 also read something Session 1 will write,
-- we'd have a dangerous cycle
 
COMMIT;  -- May succeed or fail based on overall conflict pattern
 
-- Session 1 commits:
COMMIT;  -- May get: ERROR: could not serialize access 
          --         due to read/write dependencies among transactions
 
-- ============================================
-- SSI False Positive Scenario
-- ============================================
-- SSI may abort transactions that wouldn't actually 
-- violate serializability, because it uses conservative
-- conflict detection.
 
-- Example: Two transactions that happen to touch 
-- overlapping predicates but don't actually conflict
-- semantically may still be aborted.
 
-- Session 1: Reads high-balance accounts
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE balance > 100000;
-- Session 1 hasn't decided what to do yet
 
-- Session 2: Inserts a low-balance account
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO accounts (balance) VALUES (500);
COMMIT;  -- Probably succeeds
 
-- Session 3: Inserts a high-balance account  
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO accounts (balance) VALUES (200000);
COMMIT;  -- Conflicts with Session 1's read!
 
-- Now Session 1 tries to commit:
COMMIT;  -- Aborted due to serialization failure
-- Even if Session 1 was just going to report the data,
-- not modify anything, SSI doesn't know that.
 
-- ============================================
-- Proper SSI Retry Logic
-- ============================================
-- Application code must handle serialization failures
 
CREATE OR REPLACE FUNCTION transfer_funds(
    from_acc INT, to_acc INT, amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retries INT := 0;
    max_retries INT := 3;
BEGIN
    LOOP
        BEGIN
            -- Start serializable transaction
            SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
            
            -- Perform transfer
            UPDATE accounts SET balance = balance - amount 
            WHERE id = from_acc;
            UPDATE accounts SET balance = balance + amount 
            WHERE id = to_acc;
            
            -- If we get here, no conflict
            RETURN TRUE;
            
        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                retries := retries + 1;
                IF retries >= max_retries THEN
                    RAISE EXCEPTION 'Transfer failed after % retries', retries;
                END IF;
                -- Exponential backoff
                PERFORM pg_sleep(0.1 * power(2, retries));
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SSI vs Traditional Locking Comparison
Aspect	SSI (PostgreSQL)	S2PL with Gap Locks (MySQL/SQL Server)
Blocking behavior	Readers never block writers, or vice versa	Readers may block writers, writers always block
Phantom prevention	Detected at commit via dependency tracking	Prevented by acquiring gap/range locks
False positives	May abort transactions that wouldn't actually violate	No false positives, but may deadlock
Performance characteristic	Higher throughput under read-heavy loads	Better for write-heavy predictable patterns
Retry requirement	Mandatory retry logic for aborted transactions	Retry for deadlocks, less frequent
Lock memory	Lower (SIREAD locks are compact)	Higher (gap locks can be numerous)

SSI Advantages

SSI's optimistic approach often provides better throughput than pessimistic locking, especially for read-heavy workloads. However, applications must be designed to handle and retry serialization failures gracefully.

Application-Level Prevention Strategies

When database isolation levels are insufficient or too costly, application-level strategies can mitigate phantom risks. These approaches require careful implementation but offer flexibility and control.

Explicit ID Capture Strategy: Capturing explicit row IDs at the start of a transaction and using them consistently throughout prevents phantoms by anchoring to known rows rather than predicates.

snapshot-capture.sql
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
-- Snapshot Capture Pattern
BEGIN TRANSACTION;
 
-- Step 1: Capture the IDs that match our predicate
CREATE TEMP TABLE snapshot_ids AS
SELECT account_id
FROM accounts
WHERE branch = 'Downtown' AND status = 'active';
 
-- Step 2: All subsequent operations use the snapshot
SELECT SUM(balance) as total
FROM accounts a
INNER JOIN snapshot_ids s ON a.account_id = s.account_id;
-- This can only return rows that existed at capture time!
 
-- Step 3: Generate payments using snapshot
INSERT INTO payments (account_id, amount)
SELECT a.account_id, a.balance * 0.01
FROM accounts a
INNER JOIN snapshot_ids s ON a.account_id = s.account_id;
-- Guaranteed same set as Step 2
 
-- Step 4: Verify count consistency
SELECT 
    (SELECT COUNT(*) FROM snapshot_ids) as expected,
    (SELECT COUNT(*) FROM payments 
     WHERE created_in_this_batch = TRUE) as actual;
-- If these match, phantom-free within transaction
 
DROP TABLE snapshot_ids;
COMMIT;

When to Use Snapshot Capture

This pattern works well for batch operations (payroll, report generation, bulk updates) where you need consistent processing of a known set. It doesn't prevent the underlying database from having new rows—it just ensures your transaction processes a stable set.

Schema Design for Phantom Resistance

Thoughtful schema design can reduce phantom surface area by structuring data in ways that minimize the impact of concurrent modifications on critical operations.

Schema Design Principles

•Aggregate Tables — Maintain pre-computed aggregates that are atomically updated with each insert/delete. Queries read aggregates instead of computing from raw data, avoiding phantom-sensitive scans.
•Closed Periods — For time-based queries, mark periods as "closed" when they should no longer accept new entries. Queries on closed periods are phantom-free by design.
•Batch Identifiers — Assign batch IDs to related records. Process by specific batch ID rather than time ranges or predicates.
•Soft Deletes with Effective Dating — Instead of physical deletes, use effective dates. Phantoms become visible modifications rather than invisible disappearances.
•Narrow Predicate Surfaces — Design queries to use specific identifiers rather than broad predicates whenever possible.

phantom-resistant-schema.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
-- ============================================
-- PATTERN 1: Aggregate Tables
-- ============================================
-- Instead of: SELECT SUM(amount) FROM sales WHERE date = TODAY
-- Maintain a running aggregate
 
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    amount DECIMAL(12,2),
    sale_date DATE
);
 
CREATE TABLE daily_totals (
    sale_date DATE PRIMARY KEY,
    total_amount DECIMAL(14,2) DEFAULT 0,
    sale_count INT DEFAULT 0,
    last_updated TIMESTAMP
);
 
-- Trigger maintains aggregate
CREATE FUNCTION update_daily_total() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO daily_totals (sale_date, total_amount, sale_count, last_updated)
    VALUES (NEW.sale_date, NEW.amount, 1, NOW())
    ON CONFLICT (sale_date) DO UPDATE
    SET total_amount = daily_totals.total_amount + NEW.amount,
        sale_count = daily_totals.sale_count + 1,
        last_updated = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Query reads pre-computed total (single row, no phantom risk)
SELECT total_amount FROM daily_totals WHERE sale_date = CURRENT_DATE;
 
-- ============================================
-- PATTERN 2: Closed Periods
-- ============================================
CREATE TABLE reporting_periods (
    period_id SERIAL PRIMARY KEY,
    period_start DATE,
    period_end DATE,
    status VARCHAR(20) DEFAULT 'open',  -- 'open', 'closing', 'closed'
    closed_at TIMESTAMP,
    final_total DECIMAL(14,2)
);
 
-- Close a period atomically
CREATE FUNCTION close_period(p_period_id INT) RETURNS VOID AS $$
BEGIN
    -- Calculate final total at closing moment
    UPDATE reporting_periods
    SET status = 'closed',
        closed_at = NOW(),
        final_total = (
            SELECT SUM(amount) FROM sales 
            WHERE sale_date BETWEEN period_start AND period_end
            AND period_id = p_period_id
        )
    WHERE period_id = p_period_id;
    
    -- Future queries use closed_total, immune to phantoms
END;
$$ LANGUAGE plpgsql;
 
-- ============================================
-- PATTERN 3: Batch Processing with IDs
-- ============================================
CREATE TABLE payroll_batches (
    batch_id SERIAL PRIMARY KEY,
    batch_date DATE,
    status VARCHAR(20),  -- 'collecting', 'processing', 'complete'
    created_at TIMESTAMP
);
 
CREATE TABLE payroll_entries (
    entry_id SERIAL PRIMARY KEY,
    batch_id INT REFERENCES payroll_batches(batch_id),
    employee_id INT,
    amount DECIMAL(12,2)
);
 
-- Lock batch when ready to process
UPDATE payroll_batches SET status = 'processing' WHERE batch_id = 123;
 
-- Process by batch_id, not by predicate
SELECT * FROM payroll_entries WHERE batch_id = 123;
-- No phantom risk: batch membership is fixed once we lock it

Design for the Critical Path

Not all operations need phantom protection. Focus schema design efforts on the critical paths—financial summaries, compliance reports, inventory checks. Less critical operations (analytics, non-binding dashboards) may tolerate phantom risk in exchange for simplicity.

Table Hints and Explicit Locking

When you need precise control over locking behavior, many databases offer table hints or explicit locking commands that override default isolation level behavior.

explicit-locking-examples.sql
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- ============================================
-- SQL Server Table Hints
-- ============================================
 
-- Force serializable behavior for specific query
SELECT * FROM accounts WITH (HOLDLOCK, SERIALIZABLE)
WHERE branch = 'Downtown';
-- HOLDLOCK: Hold locks until transaction end (like repeatable read)
-- SERIALIZABLE: Acquire key-range locks (phantom prevention)
 
-- Aggressive protection: exclusive lock entire table
SELECT * FROM accounts WITH (TABLOCKX, HOLDLOCK)
WHERE branch = 'Downtown';
-- No concurrent readers OR writers until commit
-- Maximum protection, minimum concurrency
 
-- ============================================
-- PostgreSQL Explicit Locking
-- ============================================
 
-- Lock table in share mode (allows reads, blocks writes)
LOCK TABLE accounts IN SHARE MODE;
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- No inserts/updates/deletes allowed until commit
 
-- Lock table exclusively (blocks everything)
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Complete isolation, but severe concurrency impact
 
-- Row-level FOR UPDATE/FOR SHARE
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR UPDATE;
-- Locks matching rows, PostgreSQL also uses predicate locks
-- at SERIALIZABLE level for phantom prevention
 
-- NOWAIT option: Fail immediately if lock unavailable
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR UPDATE NOWAIT;
-- Returns error if rows are locked by another transaction
 
-- SKIP LOCKED: Skip locked rows (useful for job queues)
SELECT * FROM accounts 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- Get unlocked rows only
 
-- ============================================
-- MySQL Locking Hints
-- ============================================
 
-- FOR UPDATE locks for write
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR UPDATE;
-- Acquires X-locks and gap locks at REPEATABLE READ
 
-- FOR SHARE locks for read
SELECT * FROM accounts 
WHERE branch = 'Downtown'
FOR SHARE;
-- Acquires S-locks, allows concurrent S-locks
 
-- NOWAIT and SKIP LOCKED (MySQL 8.0+)
SELECT * FROM accounts WHERE status = 'pending'
FOR UPDATE SKIP LOCKED;
 
-- ============================================
-- Advisory Locks (PostgreSQL)
-- ============================================
-- Application-managed locks, not tied to rows
 
SELECT pg_advisory_lock(hashtext('accounts_branch_Downtown'));
-- Hold advisory lock for critical section
 
-- Perform phantom-sensitive operation
SELECT SUM(balance) FROM accounts WHERE branch = 'Downtown';
-- Other transactions calling pg_advisory_lock with same key will wait
 
SELECT pg_advisory_unlock(hashtext('accounts_branch_Downtown'));
-- Release the lock

Explicit Locking Trade-offs

Explicit locking provides precise control but increases complexity and deadlock risk. Over-aggressive locking can create severe contention. Use explicit locking surgically—only where database isolation levels are genuinely insufficient.

Prevention

Defending Against Phantom Reads

SERIALIZABLE Isolation Level

Gap Locking and Next-Key Locking

Predicate Locking and Key-Range Locking

Serializable Snapshot Isolation (SSI)

Application-Level Prevention Strategies

Schema Design for Phantom Resistance

Table Hints and Explicit Locking

There’s more to understand.

Prevention

Defending Against Phantom Reads

SERIALIZABLE Isolation Level

Gap Locking and Next-Key Locking

Predicate Locking and Key-Range Locking

Serializable Snapshot Isolation (SSI)

Application-Level Prevention Strategies

Schema Design for Phantom Resistance

Table Hints and Explicit Locking

There’s more to understand.