Example Scenario — The Phantom Read Problem | OneNoughtOne

0/131

Database Management SystemsPhantom Read Problem

The Phantom Read Problem

LevelIntermediate

Duration55 mins

TopicPhantom Read Problem

4 / 5

Example Scenario

Phantom Reads in Real-World Scenarios

Abstract definitions become concrete when we see phantom reads manifest in realistic application scenarios. This page presents detailed walkthroughs of common situations where phantom reads cause real problems—from financial systems to inventory management to security auditing. Each scenario traces the complete timeline of events, showing exactly how concurrent transactions interact to produce phantom behavior, and what consequences follow.

What You Will Learn

By the end of this page, you will be able to trace phantom read scenarios step-by-step, analyze their business and technical consequences, identify phantom-vulnerable patterns in your own applications, and understand why certain domains face higher phantom risk than others.

Scenario 1: Bank Balance Transfer Validation

Context: A banking system requires that the sum of all account balances in a branch always equals the branch's total recorded assets. An auditing transaction validates this invariant while normal banking operations continue.

Initial Database State
Account ID	Branch	Balance	Type
A001	Downtown	$50,000	Checking
A002	Downtown	$75,000	Savings
A003	Downtown	$25,000	Checking
A004	Downtown	$100,000	Business

Branch Metadata: Downtown Branch Total Assets: $250,000 The invariant is: SUM(balance) WHERE branch = 'Downtown' = branch_total_assets

bank-audit-phantom.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
-- ============================================
-- TIMELINE OF PHANTOM READ VIOLATION
-- ============================================
 
-- T1: Audit Transaction (running at READ COMMITTED)
-- T2: Account Opening Transaction
 
-- Time t0: Initial state
-- Branch Downtown: 4 accounts, $250,000 total
-- branch_total_assets = $250,000
 
-- ----------------------------------------
-- Time t1: T1 begins audit
-- ----------------------------------------
-- Transaction T1 (Audit)
BEGIN TRANSACTION;  
 
-- Time t2: T1 calculates sum from individual accounts
SELECT SUM(balance) as calculated_total
FROM accounts
WHERE branch = 'Downtown';
-- Result: $250,000 ✓
 
-- T1 now processing this result, preparing audit report...
 
-- ----------------------------------------
-- Time t3: T2 opens new account for VIP customer
-- ----------------------------------------
-- Transaction T2 (Account Opening)
BEGIN TRANSACTION;
 
INSERT INTO accounts (account_id, branch, balance, account_type)
VALUES ('A005', 'Downtown', 500000, 'Premium');
 
-- Update branch metadata
UPDATE branches 
SET total_assets = total_assets + 500000
WHERE branch_name = 'Downtown';
 
COMMIT;  -- T2 commits successfully at t3.5
 
-- ----------------------------------------
-- Time t4: T1 retrieves branch total for validation
-- ----------------------------------------
-- Back in Transaction T1
SELECT total_assets 
FROM branches
WHERE branch_name = 'Downtown';
-- Result: $750,000 (updated by T2)
 
-- ----------------------------------------
-- Time t5: T1 performs validation check
-- ----------------------------------------
-- T1 compares:
--   calculated_total from accounts = $250,000 (from t2)
--   branch_total_assets = $750,000 (from t4)
-- 
-- VALIDATION FAILS! $250,000 ≠ $750,000
-- 
-- The audit flags a $500,000 discrepancy!
 
-- T1's log: "CRITICAL: Branch Downtown has $500,000 
-- unexplained assets. Possible fraud or system error."
 
COMMIT;  -- T1 ends in alarmed state

Converting Mermaid diagram...

Business Impact

This phantom read triggered a false positive fraud alert, potentially leading to: branch investigation, regulatory notifications, customer account freezes, reputation damage, and significant staff time investigating a non-existent problem.

Scenario 2: Inventory Reservation System

Context: An e-commerce platform manages inventory by tracking available units. When processing orders, the system checks availability, reserves inventory, and then charges the customer. Concurrent order processing creates phantom opportunities.

Initial Inventory State
Product ID	Name	Available Units	Reserved Units
P101	Gaming Laptop	5	3
P102	USB Cable	500	50
P103	Wireless Mouse	25	10

inventory-phantom.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
-- ============================================
-- SCENARIO: Double-Booking of Last Available Item
-- ============================================
 
-- Products table has 5 Gaming Laptops available
-- Current reservations: 3 (so 2 actually available for new orders)
 
-- T1: Customer Alice's order (wants 1 laptop)
-- T2: Customer Bob's order (wants 1 laptop)
-- T3: Supplier replenishment transaction
 
-- ----------------------------------------
-- Time t1: T1 checks availability
-- ----------------------------------------
-- Transaction T1 (Alice's Order)
BEGIN TRANSACTION;
 
SELECT (available_units - reserved_units) as available
FROM inventory
WHERE product_id = 'P101';
-- Result: 5 - 3 = 2 units available ✓
 
-- T1 notes: 2 available, Alice wants 1, proceed to reserve...
 
-- ----------------------------------------
-- Time t2: T2 checks availability AND reserves
-- ----------------------------------------
-- Transaction T2 (Bob's Order)
BEGIN TRANSACTION;
 
SELECT (available_units - reserved_units) as available
FROM inventory
WHERE product_id = 'P101';
-- Result: 2 units available ✓
 
-- Bob wants 2 units, 2 available, reserve immediately
UPDATE inventory
SET reserved_units = reserved_units + 2
WHERE product_id = 'P101';
 
COMMIT;  -- Bob's order confirmed
 
-- Inventory now: available = 5, reserved = 5 (0 truly available)
 
-- ----------------------------------------
-- Time t3: T1 attempts to reserve (still in original txn)
-- ----------------------------------------
-- Back in T1 (Alice's Order)
 
-- Alice trusts her earlier availability check (showed 2)
UPDATE inventory
SET reserved_units = reserved_units + 1
WHERE product_id = 'P101';
 
-- Inventory now: available = 5, reserved = 6
-- OVERBOOKING! reserved > available
 
COMMIT;  -- Problem: Alice's order also confirmed
 
-- ============================================
-- RESULT: We've "sold" 6 reservations for 5 items!
-- ============================================
 
-- This isn't strictly a phantom (the row existed), 
-- but here's where a true phantom compounds it:
 
-- ----------------------------------------
-- Time t4: Supplier adds inventory (T3)
-- ----------------------------------------
-- Transaction T3 (Supplier Replenishment)
BEGIN TRANSACTION;
 
-- Add a new product variant that wasn't there before
INSERT INTO inventory (product_id, name, available_units, reserved_units)
VALUES ('P101-RED', 'Gaming Laptop (Red)', 3, 0);
 
COMMIT;
 
-- ----------------------------------------
-- Time t5: Fulfillment queries all laptop variants
-- ----------------------------------------
-- Transaction T4 (Fulfillment Report)
BEGIN TRANSACTION;
 
SELECT product_id, SUM(available_units - reserved_units) as total_available
FROM inventory
WHERE product_id LIKE 'P101%'
GROUP BY product_id;
 
-- At t1: Would have shown just P101
-- At t5: Shows P101 AND P101-RED (phantom!)
 
-- Report now shows "3 Gaming Laptops available" 
-- but these are RED variant, not what was ordered!
 
COMMIT;

Combined Impact

This scenario shows both the classic race condition (T1/T2 overbooking) and phantom behavior (T3/T4 new variant). In fulfillment, staff might ship the wrong color, or believe they have inventory when actual ordered SKUs are exhausted.

Scenario 3: Payroll Processing

Context: A company runs payroll at the end of each month. The payroll system must ensure that the total disbursements match the approved payroll budget. During payroll processing, HR might still be onboarding new employees who need immediate pay.

payroll-phantom.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
-- ============================================
-- PAYROLL PHANTOM SCENARIO
-- ============================================
 
-- Payroll budget for January: $500,000
-- Approved salary pool for 50 employees: $500,000
 
-- T1: Payroll Processing Transaction
-- T2: HR Onboarding Transaction (new hire needs immediate pay)
 
-- ----------------------------------------
-- Time t1: T1 calculates total payroll
-- ----------------------------------------
-- Transaction T1 (Payroll)
BEGIN TRANSACTION;
 
-- Step 1: Get total salaries for this month
SELECT 
    COUNT(*) as employee_count,
    SUM(monthly_salary) as total_payroll
FROM employees
WHERE status = 'active'
  AND payroll_eligible = TRUE
  AND start_date <= '2024-01-31';
-- Result: 50 employees, $500,000 total
 
-- Step 2: Verify against budget
SELECT approved_budget 
FROM payroll_budgets
WHERE month = '2024-01';
-- Result: $500,000 ✓
 
-- Validation passes, proceed to generate payments...
 
-- ----------------------------------------
-- Time t2: T2 onboards new executive
-- ----------------------------------------
-- Transaction T2 (HR Onboarding)
BEGIN TRANSACTION;
 
INSERT INTO employees (
    employee_id, name, monthly_salary, status, 
    payroll_eligible, start_date, department
) VALUES (
    'E051', 'New VP Sales', 50000, 'active',
    TRUE, '2024-01-15', 'Sales'
);
 
-- Also need to update budget approval (backdated)
UPDATE payroll_budgets
SET approved_budget = approved_budget + 50000
WHERE month = '2024-01';
 
COMMIT;
 
-- ----------------------------------------
-- Time t3: T1 generates payment records
-- ----------------------------------------
-- Back in T1 (Payroll)
 
-- Generate individual payment records for all eligible employees
INSERT INTO payments (employee_id, amount, payment_date)
SELECT employee_id, monthly_salary, '2024-01-31'
FROM employees
WHERE status = 'active'
  AND payroll_eligible = TRUE
  AND start_date <= '2024-01-31';
-- Inserts 51 payment records! (includes E051)
 
-- Step 4: Calculate total payments for verification
SELECT COUNT(*), SUM(amount)
FROM payments
WHERE payment_date = '2024-01-31';
-- Result: 51 employees, $550,000 total
 
-- ----------------------------------------
-- DISCREPANCY DETECTED
-- ----------------------------------------
-- T1's initial calculation: $500,000 (50 employees)
-- T1's generated payments: $550,000 (51 employees)
-- 
-- The INSERT statement "saw" the new employee that
-- wasn't visible during the initial SELECT!
 
COMMIT;  -- But what happens to the mismatch?

The Insidious Detail: Notice that the phantom occurred within T1 between two different statements: SELECT query (t1): 50 employees, $500,000 INSERT ... SELECT (t3): 51 employees, $550,000 Both are in the same transaction, but at READ COMMITTED isolation, each statement sees the current committed state. T2 committed between T1's statements, making E051 visible to T1's later statement. This means T1 validated the budget against 50 employees but generated payments for 51.

Payroll Phantom Consequences
Consequence	Impact	Resolution
Budget overrun	$50,000 over approved budget	Emergency approval needed
Audit failure	Pre/post counts don't match	Investigation required
Cash flow issue	More disbursed than projected	Treasury adjustment
Compliance risk	Payroll records inconsistent	Regulatory concern
Employee confusion	New VP correctly paid, but flagged	HR clarification needed

The Multi-Statement Phantom

This scenario illustrates that phantoms can occur between statements within a single transaction, not just between repeated identical queries. Any time you use result set information to drive subsequent operations, you're vulnerable to inter-statement phantoms.

Scenario 4: Security Access Control Audit

Context: A security audit system periodically captures a snapshot of all users with elevated privileges. This snapshot is used for compliance verification and incident investigation. During the snapshot, administrative operations may continue creating or modifying user permissions.

security-audit-phantom.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
-- ============================================
-- SECURITY AUDIT PHANTOM SCENARIO
-- ============================================
 
-- T1: Security Audit Transaction (generates compliance report)
-- T2: Admin creates emergency access account
-- T3: Automated role escalation (threshold triggered)
 
-- ----------------------------------------
-- Time t1: T1 begins comprehensive audit
-- ----------------------------------------
-- Transaction T1 (Security Audit)
BEGIN TRANSACTION;
 
-- Capture all users with admin privileges
CREATE TEMP TABLE audit_snapshot AS
SELECT 
    user_id,
    username,
    role,
    created_at,
    last_modified_at
FROM users
WHERE role IN ('admin', 'superadmin', 'security_officer');
-- Result: 12 privileged users captured
 
-- Log the snapshot
INSERT INTO audit_logs (audit_type, timestamp, summary)
VALUES ('PRIVILEGE_SNAPSHOT', NOW(), '12 privileged users captured');
 
-- Generate detailed report...
 
-- ----------------------------------------
-- Time t2: T2 creates emergency access (legitimate)
-- ----------------------------------------
-- Transaction T2 (Emergency Access)
BEGIN TRANSACTION;
 
-- Security incident occurring, need emergency admin
INSERT INTO users (user_id, username, role, created_at)
VALUES ('U999', 'emergency_admin', 'admin', NOW());
 
INSERT INTO audit_trail (action, details, timestamp)
VALUES ('EMERGENCY_CREATE', 'Incident response: emergency admin', NOW());
 
COMMIT;
 
-- ----------------------------------------
-- Time t3: T3 automated role escalation
-- ----------------------------------------
-- Transaction T3 (Automated Process)
BEGIN TRANSACTION;
 
-- System detects threshold and escalates user
UPDATE users
SET role = 'admin'
WHERE user_id = 'U050'
  AND (SELECT COUNT(*) FROM user_achievements 
       WHERE user_id = 'U050') >= 10;
-- U050 promoted to admin
 
COMMIT;
 
-- ----------------------------------------
-- Time t4: T1 verifies audit completeness
-- ----------------------------------------
-- Back in T1
 
-- Cross-check: current privileged users vs snapshot
SELECT u.user_id, u.username
FROM users u
WHERE u.role IN ('admin', 'superadmin', 'security_officer')
  AND u.user_id NOT IN (SELECT user_id FROM audit_snapshot);
 
-- Returns:
-- | user_id | username        |
-- |---------|-----------------|
-- | U999    | emergency_admin |  ← INSERT phantom
-- | U050    | promoted_user   |  ← UPDATE phantom
 
-- ----------------------------------------
-- AUDIT INTEGRITY COMPROMISED
-- ----------------------------------------
 
-- T1's audit log says: "12 privileged users captured"
-- But there are now 14 privileged users!
-- 
-- The snapshot is incomplete, but the audit log
-- claims it was comprehensive.
 
COMMIT;

Security Implications

•Audit gap — Two privileged accounts exist that aren't in the "comprehensive" snapshot. If either account is compromised, the audit trail won't connect them to the snapshot period.
•Compliance failure — Regulatory requirements often mandate complete privilege inventories. The incomplete snapshot violates this requirement.
•Incident investigation blind spot — If emergency_admin or promoted_user performed malicious actions, investigators checking the audit snapshot won't find them.
•False assurance — Report consumers believe they have a complete picture when they don't. This is more dangerous than having no audit—it creates unfounded confidence.
•Temporal defense compromise — "We had only 12 admins at that time" is now provably false, undermining the entire audit's credibility.

The Silent Compromise

Unlike the earlier scenarios where discrepancies might be caught by validation checks, this audit phantom may go undetected indefinitely. The audit claims completeness, and unless someone specifically re-queries during incident investigation, the gap remains hidden.

Scenario 5: Airline Seat Assignment

Context: An airline reservation system allows passengers to select seats. The system must prevent double-booking while handling high concurrency during popular flight releases and check-in windows.

airline-phantom.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
-- ============================================
-- AIRLINE SEAT PHANTOM SCENARIO
-- ============================================
 
-- Flight AA100: 180 seats, currently 150 booked
-- Premium rows 1-5 (30 seats): 20 booked
 
-- T1: Passenger wants window seat in premium rows
-- T2: Airline blocks row 5 for VIP (bulk insert)
-- T3: Another passenger simultaneously selecting
 
-- ----------------------------------------
-- Time t1: T1 queries available premium window seats
-- ----------------------------------------
-- Transaction T1 (Passenger Selection)
BEGIN TRANSACTION;
 
SELECT seat_number, seat_type
FROM seats
WHERE flight_id = 'AA100'
  AND row_number <= 5
  AND seat_type = 'window'
  AND status = 'available';
-- Result:
-- | seat_number | seat_type |
-- | 1A          | window    |
-- | 3A          | window    |
-- | 5A          | window    |
-- | 5F          | window    |
 
-- Display these 4 options to passenger...
-- Passenger is reviewing options (takes 30 seconds)
 
-- ----------------------------------------
-- Time t2: T2 blocks entire row 5 for VIP
-- ----------------------------------------
-- Transaction T2 (VIP Block)
BEGIN TRANSACTION;
 
UPDATE seats
SET status = 'blocked', block_reason = 'VIP_HOLD'
WHERE flight_id = 'AA100'
  AND row_number = 5;
-- Affects 6 seats including 5A and 5F
 
COMMIT;
 
-- ----------------------------------------
-- Time t3: T3 takes seat 3A
-- ----------------------------------------
-- Transaction T3 (Concurrent Passenger)
BEGIN TRANSACTION;
 
UPDATE seats
SET status = 'booked', passenger_id = 'P9999'
WHERE flight_id = 'AA100'
  AND seat_number = '3A'
  AND status = 'available';
 
COMMIT;
 
-- ----------------------------------------
-- Time t4: T1 passenger selects 5A
-- ----------------------------------------
-- Back in T1 (after 30 second delay)
 
-- Passenger clicks on 5A from the displayed list
UPDATE seats
SET status = 'booked', passenger_id = 'P1234'
WHERE flight_id = 'AA100'
  AND seat_number = '5A'
  AND status = 'available';
-- Rows affected: 0 (seat is now 'blocked', not 'available')
 
-- T1 checks result...
-- Update failed, need to requery
 
SELECT seat_number, seat_type
FROM seats
WHERE flight_id = 'AA100'
  AND row_number <= 5
  AND seat_type = 'window'
  AND status = 'available';
-- Result:
-- | seat_number | seat_type |
-- | 1A          | window    |
 
-- PHANTOM (inverse): 5A, 5F, 3A disappeared!
-- From 4 options to 1 option
 
COMMIT;

User Experience Degradation: This scenario demonstrates DELETE/UPDATE phantoms (disappearance). The passenger was shown 4 window seat options, spent time considering them, and upon selection found 3 had vanished. While the system correctly prevented overbooking, the user experience is poor: Stale display : The seat map shown to the user was already outdated when rendered Wasted decision time : 30 seconds of consideration for options that no longer exist Reduced choice : Only 1 option remains instead of 4 Potential abandonment : Frustrated passenger might abandon the booking entirely

The UI/Backend Phantom Gap

Web applications inherently create phantom windows: between when data is queried for display and when the user acts on that display, the underlying data may change. This is a fundamental challenge in real-time systems and requires strategies like optimistic locking, real-time updates via WebSockets, or explicit seat holds during selection.

Comparative Analysis Across Scenarios

These five scenarios reveal common patterns and varying characteristics of phantom reads across different domains. Let's analyze them comparatively:

Scenario Comparison Matrix
Scenario	Phantom Type	Detection Ease	Business Severity	Prevention Approach
Bank Audit	INSERT	Easy (count mismatch)	High (fraud alert)	SERIALIZABLE, snapshot
Inventory	INSERT	Medium (fulfillment)	High (oversell)	Pre-reservation locks
Payroll	INSERT (inter-stmt)	Hard (delayed)	Critical (money)	SERIALIZABLE, cutoff
Security Audit	INSERT + UPDATE	Hard (silent)	Critical (compliance)	Snapshot isolation
Airline Seats	DELETE phantom	Easy (UI failure)	Medium (UX)	Optimistic + real-time

Pattern Insights: 1. Financial systems face the highest phantom sensitivity because they often validate totals or invariants across multiple queries. Any phantom introduces immediate discrepancy detection or silent corruption. 2. Audit/compliance systems are uniquely vulnerable because phantoms create silent gaps that may never be detected. Unlike financial systems where discrepancies surface, audit phantoms hide. 3. Reservation/booking systems experience phantoms primarily as UX degradation. The systems typically handle the concurrency correctly (preventing double-booking) but show stale options to users. 4. Aggregate-heavy operations (payroll, reporting) compound phantom effects. A single phantom row changes counts, sums, and averages—rippling through all dependent calculations.

The Common Thread

All scenarios share a common vulnerability: they make decisions or take actions based on a point-in-time query result, while the underlying data continues to change. The time gap between query and action is the phantom window.

Isolation Level Impact Analysis

How would each scenario behave under different SQL isolation levels? Understanding this mapping helps choose appropriate isolation for specific use cases.

Scenario Behavior by Isolation Level
Scenario	READ COMMITTED	REPEATABLE READ*	SERIALIZABLE
Bank Audit	❌ Phantom occurs	❌ Likely occurs	✅ Prevented
Inventory	❌ Phantom occurs	✅ Usually prevented (MVCC)	✅ Prevented
Payroll	❌ Inter-stmt phantom	❌ May still occur**	✅ Prevented
Security Audit	❌ Phantom occurs	✅ Usually prevented (MVCC)	✅ Prevented
Airline Seats	❌ Disappearance phantom	❌ Can still occur	✅ Prevented (maybe blocked)

Notes: *REPEATABLE READ behavior varies by database: PostgreSQL/Oracle : Uses MVCC snapshot isolation, preventing most phantoms MySQL InnoDB : Uses gap locking, preventing most phantoms SQL Server : Standard locking with key-range locks (configurable) ANSI Standard : Actually allows phantoms at REPEATABLE READ **The Payroll inter-statement phantom is particularly tricky because REPEATABLE READ typically ensures repeated SELECT statements return identical results, but INSERT...SELECT may behave differently since it's a write operation that might acquire different locks.

isolation-level-demo.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
-- ============================================
-- Demonstrating isolation level effects
-- ============================================
 
-- PostgreSQL: REPEATABLE READ uses snapshot isolation
-- Each transaction sees data as of transaction start
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- 100
-- Another transaction inserts 10 orders and commits
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- Still 100 (snapshot)
COMMIT;
 
-- MySQL InnoDB: REPEATABLE READ uses snapshot + gap locks
-- Similar snapshot behavior, but write operations may
-- interact differently with concurrent transactions
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- 100
-- Gap locks prevent insertions into the viewed range
-- Another transaction's INSERT would block or fail
COMMIT;
 
-- SQL Server: SERIALIZABLE with key-range locks
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- 100
-- Key-range locks held on the date index
-- Another transaction's INSERT blocks until this commits
COMMIT;
 
-- The trade-off: SERIALIZABLE provides strongest protection
-- but also causes the most blocking/contention

Don't Assume Portability

Application code that relies on REPEATABLE READ phantom prevention will behave differently across database engines. If portability matters, either use SERIALIZABLE explicitly or implement application-level verification for phantom-sensitive operations.

Database Management SystemsPhantom Read Problem

The Phantom Read Problem

LevelIntermediate

Duration55 mins

TopicPhantom Read Problem

4 / 5

Example Scenario

Phantom Reads in Real-World Scenarios

Abstract definitions become concrete when we see phantom reads manifest in realistic application scenarios. This page presents detailed walkthroughs of common situations where phantom reads cause real problems—from financial systems to inventory management to security auditing. Each scenario traces the complete timeline of events, showing exactly how concurrent transactions interact to produce phantom behavior, and what consequences follow.

What You Will Learn

By the end of this page, you will be able to trace phantom read scenarios step-by-step, analyze their business and technical consequences, identify phantom-vulnerable patterns in your own applications, and understand why certain domains face higher phantom risk than others.

Scenario 1: Bank Balance Transfer Validation

Context: A banking system requires that the sum of all account balances in a branch always equals the branch's total recorded assets. An auditing transaction validates this invariant while normal banking operations continue.

Initial Database State
Account ID	Branch	Balance	Type
A001	Downtown	$50,000	Checking
A002	Downtown	$75,000	Savings
A003	Downtown	$25,000	Checking
A004	Downtown	$100,000	Business

Branch Metadata: Downtown Branch Total Assets: $250,000 The invariant is: SUM(balance) WHERE branch = 'Downtown' = branch_total_assets

bank-audit-phantom.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
-- ============================================
-- TIMELINE OF PHANTOM READ VIOLATION
-- ============================================
 
-- T1: Audit Transaction (running at READ COMMITTED)
-- T2: Account Opening Transaction
 
-- Time t0: Initial state
-- Branch Downtown: 4 accounts, $250,000 total
-- branch_total_assets = $250,000
 
-- ----------------------------------------
-- Time t1: T1 begins audit
-- ----------------------------------------
-- Transaction T1 (Audit)
BEGIN TRANSACTION;  
 
-- Time t2: T1 calculates sum from individual accounts
SELECT SUM(balance) as calculated_total
FROM accounts
WHERE branch = 'Downtown';
-- Result: $250,000 ✓
 
-- T1 now processing this result, preparing audit report...
 
-- ----------------------------------------
-- Time t3: T2 opens new account for VIP customer
-- ----------------------------------------
-- Transaction T2 (Account Opening)
BEGIN TRANSACTION;
 
INSERT INTO accounts (account_id, branch, balance, account_type)
VALUES ('A005', 'Downtown', 500000, 'Premium');
 
-- Update branch metadata
UPDATE branches 
SET total_assets = total_assets + 500000
WHERE branch_name = 'Downtown';
 
COMMIT;  -- T2 commits successfully at t3.5
 
-- ----------------------------------------
-- Time t4: T1 retrieves branch total for validation
-- ----------------------------------------
-- Back in Transaction T1
SELECT total_assets 
FROM branches
WHERE branch_name = 'Downtown';
-- Result: $750,000 (updated by T2)
 
-- ----------------------------------------
-- Time t5: T1 performs validation check
-- ----------------------------------------
-- T1 compares:
--   calculated_total from accounts = $250,000 (from t2)
--   branch_total_assets = $750,000 (from t4)
-- 
-- VALIDATION FAILS! $250,000 ≠ $750,000
-- 
-- The audit flags a $500,000 discrepancy!
 
-- T1's log: "CRITICAL: Branch Downtown has $500,000 
-- unexplained assets. Possible fraud or system error."
 
COMMIT;  -- T1 ends in alarmed state

Converting Mermaid diagram...

Business Impact

This phantom read triggered a false positive fraud alert, potentially leading to: branch investigation, regulatory notifications, customer account freezes, reputation damage, and significant staff time investigating a non-existent problem.

Scenario 2: Inventory Reservation System

Context: An e-commerce platform manages inventory by tracking available units. When processing orders, the system checks availability, reserves inventory, and then charges the customer. Concurrent order processing creates phantom opportunities.

Initial Inventory State
Product ID	Name	Available Units	Reserved Units
P101	Gaming Laptop	5	3
P102	USB Cable	500	50
P103	Wireless Mouse	25	10

inventory-phantom.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
-- ============================================
-- SCENARIO: Double-Booking of Last Available Item
-- ============================================
 
-- Products table has 5 Gaming Laptops available
-- Current reservations: 3 (so 2 actually available for new orders)
 
-- T1: Customer Alice's order (wants 1 laptop)
-- T2: Customer Bob's order (wants 1 laptop)
-- T3: Supplier replenishment transaction
 
-- ----------------------------------------
-- Time t1: T1 checks availability
-- ----------------------------------------
-- Transaction T1 (Alice's Order)
BEGIN TRANSACTION;
 
SELECT (available_units - reserved_units) as available
FROM inventory
WHERE product_id = 'P101';
-- Result: 5 - 3 = 2 units available ✓
 
-- T1 notes: 2 available, Alice wants 1, proceed to reserve...
 
-- ----------------------------------------
-- Time t2: T2 checks availability AND reserves
-- ----------------------------------------
-- Transaction T2 (Bob's Order)
BEGIN TRANSACTION;
 
SELECT (available_units - reserved_units) as available
FROM inventory
WHERE product_id = 'P101';
-- Result: 2 units available ✓
 
-- Bob wants 2 units, 2 available, reserve immediately
UPDATE inventory
SET reserved_units = reserved_units + 2
WHERE product_id = 'P101';
 
COMMIT;  -- Bob's order confirmed
 
-- Inventory now: available = 5, reserved = 5 (0 truly available)
 
-- ----------------------------------------
-- Time t3: T1 attempts to reserve (still in original txn)
-- ----------------------------------------
-- Back in T1 (Alice's Order)
 
-- Alice trusts her earlier availability check (showed 2)
UPDATE inventory
SET reserved_units = reserved_units + 1
WHERE product_id = 'P101';
 
-- Inventory now: available = 5, reserved = 6
-- OVERBOOKING! reserved > available
 
COMMIT;  -- Problem: Alice's order also confirmed
 
-- ============================================
-- RESULT: We've "sold" 6 reservations for 5 items!
-- ============================================
 
-- This isn't strictly a phantom (the row existed), 
-- but here's where a true phantom compounds it:
 
-- ----------------------------------------
-- Time t4: Supplier adds inventory (T3)
-- ----------------------------------------
-- Transaction T3 (Supplier Replenishment)
BEGIN TRANSACTION;
 
-- Add a new product variant that wasn't there before
INSERT INTO inventory (product_id, name, available_units, reserved_units)
VALUES ('P101-RED', 'Gaming Laptop (Red)', 3, 0);
 
COMMIT;
 
-- ----------------------------------------
-- Time t5: Fulfillment queries all laptop variants
-- ----------------------------------------
-- Transaction T4 (Fulfillment Report)
BEGIN TRANSACTION;
 
SELECT product_id, SUM(available_units - reserved_units) as total_available
FROM inventory
WHERE product_id LIKE 'P101%'
GROUP BY product_id;
 
-- At t1: Would have shown just P101
-- At t5: Shows P101 AND P101-RED (phantom!)
 
-- Report now shows "3 Gaming Laptops available" 
-- but these are RED variant, not what was ordered!
 
COMMIT;

Combined Impact

This scenario shows both the classic race condition (T1/T2 overbooking) and phantom behavior (T3/T4 new variant). In fulfillment, staff might ship the wrong color, or believe they have inventory when actual ordered SKUs are exhausted.

Scenario 3: Payroll Processing

Context: A company runs payroll at the end of each month. The payroll system must ensure that the total disbursements match the approved payroll budget. During payroll processing, HR might still be onboarding new employees who need immediate pay.

payroll-phantom.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
-- ============================================
-- PAYROLL PHANTOM SCENARIO
-- ============================================
 
-- Payroll budget for January: $500,000
-- Approved salary pool for 50 employees: $500,000
 
-- T1: Payroll Processing Transaction
-- T2: HR Onboarding Transaction (new hire needs immediate pay)
 
-- ----------------------------------------
-- Time t1: T1 calculates total payroll
-- ----------------------------------------
-- Transaction T1 (Payroll)
BEGIN TRANSACTION;
 
-- Step 1: Get total salaries for this month
SELECT 
    COUNT(*) as employee_count,
    SUM(monthly_salary) as total_payroll
FROM employees
WHERE status = 'active'
  AND payroll_eligible = TRUE
  AND start_date <= '2024-01-31';
-- Result: 50 employees, $500,000 total
 
-- Step 2: Verify against budget
SELECT approved_budget 
FROM payroll_budgets
WHERE month = '2024-01';
-- Result: $500,000 ✓
 
-- Validation passes, proceed to generate payments...
 
-- ----------------------------------------
-- Time t2: T2 onboards new executive
-- ----------------------------------------
-- Transaction T2 (HR Onboarding)
BEGIN TRANSACTION;
 
INSERT INTO employees (
    employee_id, name, monthly_salary, status, 
    payroll_eligible, start_date, department
) VALUES (
    'E051', 'New VP Sales', 50000, 'active',
    TRUE, '2024-01-15', 'Sales'
);
 
-- Also need to update budget approval (backdated)
UPDATE payroll_budgets
SET approved_budget = approved_budget + 50000
WHERE month = '2024-01';
 
COMMIT;
 
-- ----------------------------------------
-- Time t3: T1 generates payment records
-- ----------------------------------------
-- Back in T1 (Payroll)
 
-- Generate individual payment records for all eligible employees
INSERT INTO payments (employee_id, amount, payment_date)
SELECT employee_id, monthly_salary, '2024-01-31'
FROM employees
WHERE status = 'active'
  AND payroll_eligible = TRUE
  AND start_date <= '2024-01-31';
-- Inserts 51 payment records! (includes E051)
 
-- Step 4: Calculate total payments for verification
SELECT COUNT(*), SUM(amount)
FROM payments
WHERE payment_date = '2024-01-31';
-- Result: 51 employees, $550,000 total
 
-- ----------------------------------------
-- DISCREPANCY DETECTED
-- ----------------------------------------
-- T1's initial calculation: $500,000 (50 employees)
-- T1's generated payments: $550,000 (51 employees)
-- 
-- The INSERT statement "saw" the new employee that
-- wasn't visible during the initial SELECT!
 
COMMIT;  -- But what happens to the mismatch?

The Insidious Detail: Notice that the phantom occurred within T1 between two different statements: SELECT query (t1): 50 employees, $500,000 INSERT ... SELECT (t3): 51 employees, $550,000 Both are in the same transaction, but at READ COMMITTED isolation, each statement sees the current committed state. T2 committed between T1's statements, making E051 visible to T1's later statement. This means T1 validated the budget against 50 employees but generated payments for 51.

Payroll Phantom Consequences
Consequence	Impact	Resolution
Budget overrun	$50,000 over approved budget	Emergency approval needed
Audit failure	Pre/post counts don't match	Investigation required
Cash flow issue	More disbursed than projected	Treasury adjustment
Compliance risk	Payroll records inconsistent	Regulatory concern
Employee confusion	New VP correctly paid, but flagged	HR clarification needed

The Multi-Statement Phantom

This scenario illustrates that phantoms can occur between statements within a single transaction, not just between repeated identical queries. Any time you use result set information to drive subsequent operations, you're vulnerable to inter-statement phantoms.

Scenario 4: Security Access Control Audit

Context: A security audit system periodically captures a snapshot of all users with elevated privileges. This snapshot is used for compliance verification and incident investigation. During the snapshot, administrative operations may continue creating or modifying user permissions.

security-audit-phantom.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
-- ============================================
-- SECURITY AUDIT PHANTOM SCENARIO
-- ============================================
 
-- T1: Security Audit Transaction (generates compliance report)
-- T2: Admin creates emergency access account
-- T3: Automated role escalation (threshold triggered)
 
-- ----------------------------------------
-- Time t1: T1 begins comprehensive audit
-- ----------------------------------------
-- Transaction T1 (Security Audit)
BEGIN TRANSACTION;
 
-- Capture all users with admin privileges
CREATE TEMP TABLE audit_snapshot AS
SELECT 
    user_id,
    username,
    role,
    created_at,
    last_modified_at
FROM users
WHERE role IN ('admin', 'superadmin', 'security_officer');
-- Result: 12 privileged users captured
 
-- Log the snapshot
INSERT INTO audit_logs (audit_type, timestamp, summary)
VALUES ('PRIVILEGE_SNAPSHOT', NOW(), '12 privileged users captured');
 
-- Generate detailed report...
 
-- ----------------------------------------
-- Time t2: T2 creates emergency access (legitimate)
-- ----------------------------------------
-- Transaction T2 (Emergency Access)
BEGIN TRANSACTION;
 
-- Security incident occurring, need emergency admin
INSERT INTO users (user_id, username, role, created_at)
VALUES ('U999', 'emergency_admin', 'admin', NOW());
 
INSERT INTO audit_trail (action, details, timestamp)
VALUES ('EMERGENCY_CREATE', 'Incident response: emergency admin', NOW());
 
COMMIT;
 
-- ----------------------------------------
-- Time t3: T3 automated role escalation
-- ----------------------------------------
-- Transaction T3 (Automated Process)
BEGIN TRANSACTION;
 
-- System detects threshold and escalates user
UPDATE users
SET role = 'admin'
WHERE user_id = 'U050'
  AND (SELECT COUNT(*) FROM user_achievements 
       WHERE user_id = 'U050') >= 10;
-- U050 promoted to admin
 
COMMIT;
 
-- ----------------------------------------
-- Time t4: T1 verifies audit completeness
-- ----------------------------------------
-- Back in T1
 
-- Cross-check: current privileged users vs snapshot
SELECT u.user_id, u.username
FROM users u
WHERE u.role IN ('admin', 'superadmin', 'security_officer')
  AND u.user_id NOT IN (SELECT user_id FROM audit_snapshot);
 
-- Returns:
-- | user_id | username        |
-- |---------|-----------------|
-- | U999    | emergency_admin |  ← INSERT phantom
-- | U050    | promoted_user   |  ← UPDATE phantom
 
-- ----------------------------------------
-- AUDIT INTEGRITY COMPROMISED
-- ----------------------------------------
 
-- T1's audit log says: "12 privileged users captured"
-- But there are now 14 privileged users!
-- 
-- The snapshot is incomplete, but the audit log
-- claims it was comprehensive.
 
COMMIT;

Security Implications

•Audit gap — Two privileged accounts exist that aren't in the "comprehensive" snapshot. If either account is compromised, the audit trail won't connect them to the snapshot period.
•Compliance failure — Regulatory requirements often mandate complete privilege inventories. The incomplete snapshot violates this requirement.
•Incident investigation blind spot — If emergency_admin or promoted_user performed malicious actions, investigators checking the audit snapshot won't find them.
•False assurance — Report consumers believe they have a complete picture when they don't. This is more dangerous than having no audit—it creates unfounded confidence.
•Temporal defense compromise — "We had only 12 admins at that time" is now provably false, undermining the entire audit's credibility.

The Silent Compromise

Unlike the earlier scenarios where discrepancies might be caught by validation checks, this audit phantom may go undetected indefinitely. The audit claims completeness, and unless someone specifically re-queries during incident investigation, the gap remains hidden.

Scenario 5: Airline Seat Assignment

Context: An airline reservation system allows passengers to select seats. The system must prevent double-booking while handling high concurrency during popular flight releases and check-in windows.

airline-phantom.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
-- ============================================
-- AIRLINE SEAT PHANTOM SCENARIO
-- ============================================
 
-- Flight AA100: 180 seats, currently 150 booked
-- Premium rows 1-5 (30 seats): 20 booked
 
-- T1: Passenger wants window seat in premium rows
-- T2: Airline blocks row 5 for VIP (bulk insert)
-- T3: Another passenger simultaneously selecting
 
-- ----------------------------------------
-- Time t1: T1 queries available premium window seats
-- ----------------------------------------
-- Transaction T1 (Passenger Selection)
BEGIN TRANSACTION;
 
SELECT seat_number, seat_type
FROM seats
WHERE flight_id = 'AA100'
  AND row_number <= 5
  AND seat_type = 'window'
  AND status = 'available';
-- Result:
-- | seat_number | seat_type |
-- | 1A          | window    |
-- | 3A          | window    |
-- | 5A          | window    |
-- | 5F          | window    |
 
-- Display these 4 options to passenger...
-- Passenger is reviewing options (takes 30 seconds)
 
-- ----------------------------------------
-- Time t2: T2 blocks entire row 5 for VIP
-- ----------------------------------------
-- Transaction T2 (VIP Block)
BEGIN TRANSACTION;
 
UPDATE seats
SET status = 'blocked', block_reason = 'VIP_HOLD'
WHERE flight_id = 'AA100'
  AND row_number = 5;
-- Affects 6 seats including 5A and 5F
 
COMMIT;
 
-- ----------------------------------------
-- Time t3: T3 takes seat 3A
-- ----------------------------------------
-- Transaction T3 (Concurrent Passenger)
BEGIN TRANSACTION;
 
UPDATE seats
SET status = 'booked', passenger_id = 'P9999'
WHERE flight_id = 'AA100'
  AND seat_number = '3A'
  AND status = 'available';
 
COMMIT;
 
-- ----------------------------------------
-- Time t4: T1 passenger selects 5A
-- ----------------------------------------
-- Back in T1 (after 30 second delay)
 
-- Passenger clicks on 5A from the displayed list
UPDATE seats
SET status = 'booked', passenger_id = 'P1234'
WHERE flight_id = 'AA100'
  AND seat_number = '5A'
  AND status = 'available';
-- Rows affected: 0 (seat is now 'blocked', not 'available')
 
-- T1 checks result...
-- Update failed, need to requery
 
SELECT seat_number, seat_type
FROM seats
WHERE flight_id = 'AA100'
  AND row_number <= 5
  AND seat_type = 'window'
  AND status = 'available';
-- Result:
-- | seat_number | seat_type |
-- | 1A          | window    |
 
-- PHANTOM (inverse): 5A, 5F, 3A disappeared!
-- From 4 options to 1 option
 
COMMIT;

User Experience Degradation: This scenario demonstrates DELETE/UPDATE phantoms (disappearance). The passenger was shown 4 window seat options, spent time considering them, and upon selection found 3 had vanished. While the system correctly prevented overbooking, the user experience is poor: Stale display : The seat map shown to the user was already outdated when rendered Wasted decision time : 30 seconds of consideration for options that no longer exist Reduced choice : Only 1 option remains instead of 4 Potential abandonment : Frustrated passenger might abandon the booking entirely

The UI/Backend Phantom Gap

Web applications inherently create phantom windows: between when data is queried for display and when the user acts on that display, the underlying data may change. This is a fundamental challenge in real-time systems and requires strategies like optimistic locking, real-time updates via WebSockets, or explicit seat holds during selection.

Comparative Analysis Across Scenarios

These five scenarios reveal common patterns and varying characteristics of phantom reads across different domains. Let's analyze them comparatively:

Scenario Comparison Matrix
Scenario	Phantom Type	Detection Ease	Business Severity	Prevention Approach
Bank Audit	INSERT	Easy (count mismatch)	High (fraud alert)	SERIALIZABLE, snapshot
Inventory	INSERT	Medium (fulfillment)	High (oversell)	Pre-reservation locks
Payroll	INSERT (inter-stmt)	Hard (delayed)	Critical (money)	SERIALIZABLE, cutoff
Security Audit	INSERT + UPDATE	Hard (silent)	Critical (compliance)	Snapshot isolation
Airline Seats	DELETE phantom	Easy (UI failure)	Medium (UX)	Optimistic + real-time

Pattern Insights: 1. Financial systems face the highest phantom sensitivity because they often validate totals or invariants across multiple queries. Any phantom introduces immediate discrepancy detection or silent corruption. 2. Audit/compliance systems are uniquely vulnerable because phantoms create silent gaps that may never be detected. Unlike financial systems where discrepancies surface, audit phantoms hide. 3. Reservation/booking systems experience phantoms primarily as UX degradation. The systems typically handle the concurrency correctly (preventing double-booking) but show stale options to users. 4. Aggregate-heavy operations (payroll, reporting) compound phantom effects. A single phantom row changes counts, sums, and averages—rippling through all dependent calculations.

The Common Thread

All scenarios share a common vulnerability: they make decisions or take actions based on a point-in-time query result, while the underlying data continues to change. The time gap between query and action is the phantom window.

Isolation Level Impact Analysis

How would each scenario behave under different SQL isolation levels? Understanding this mapping helps choose appropriate isolation for specific use cases.

Scenario Behavior by Isolation Level
Scenario	READ COMMITTED	REPEATABLE READ*	SERIALIZABLE
Bank Audit	❌ Phantom occurs	❌ Likely occurs	✅ Prevented
Inventory	❌ Phantom occurs	✅ Usually prevented (MVCC)	✅ Prevented
Payroll	❌ Inter-stmt phantom	❌ May still occur**	✅ Prevented
Security Audit	❌ Phantom occurs	✅ Usually prevented (MVCC)	✅ Prevented
Airline Seats	❌ Disappearance phantom	❌ Can still occur	✅ Prevented (maybe blocked)

Notes: *REPEATABLE READ behavior varies by database: PostgreSQL/Oracle : Uses MVCC snapshot isolation, preventing most phantoms MySQL InnoDB : Uses gap locking, preventing most phantoms SQL Server : Standard locking with key-range locks (configurable) ANSI Standard : Actually allows phantoms at REPEATABLE READ **The Payroll inter-statement phantom is particularly tricky because REPEATABLE READ typically ensures repeated SELECT statements return identical results, but INSERT...SELECT may behave differently since it's a write operation that might acquire different locks.

isolation-level-demo.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
-- ============================================
-- Demonstrating isolation level effects
-- ============================================
 
-- PostgreSQL: REPEATABLE READ uses snapshot isolation
-- Each transaction sees data as of transaction start
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- 100
-- Another transaction inserts 10 orders and commits
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- Still 100 (snapshot)
COMMIT;
 
-- MySQL InnoDB: REPEATABLE READ uses snapshot + gap locks
-- Similar snapshot behavior, but write operations may
-- interact differently with concurrent transactions
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- 100
-- Gap locks prevent insertions into the viewed range
-- Another transaction's INSERT would block or fail
COMMIT;
 
-- SQL Server: SERIALIZABLE with key-range locks
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;  -- 100
-- Key-range locks held on the date index
-- Another transaction's INSERT blocks until this commits
COMMIT;
 
-- The trade-off: SERIALIZABLE provides strongest protection
-- but also causes the most blocking/contention

Don't Assume Portability

Application code that relies on REPEATABLE READ phantom prevention will behave differently across database engines. If portability matters, either use SERIALIZABLE explicitly or implement application-level verification for phantom-sensitive operations.

Example Scenario

Phantom Reads in Real-World Scenarios

Scenario 1: Bank Balance Transfer Validation

Scenario 2: Inventory Reservation System

Scenario 3: Payroll Processing

Scenario 4: Security Access Control Audit

Scenario 5: Airline Seat Assignment

Comparative Analysis Across Scenarios

Isolation Level Impact Analysis

There’s more to understand.

Example Scenario

Phantom Reads in Real-World Scenarios

Scenario 1: Bank Balance Transfer Validation

Scenario 2: Inventory Reservation System

Scenario 3: Payroll Processing

Scenario 4: Security Access Control Audit

Scenario 5: Airline Seat Assignment

Comparative Analysis Across Scenarios

Isolation Level Impact Analysis

There’s more to understand.