Repeatable Read — Isolation Levels | OneNoughtOne

0/105

Database Management SystemsIsolation Levels

Isolation Levels

LevelIntermediate

Duration90 mins

TopicIsolation Levels

3 / 5

Repeatable Read

Consistency Within Your Transaction

What if you could freeze the rows you read, ensuring they remain unchanged for the duration of your transaction? Repeatable Read provides exactly this guarantee. Once you read a row, you can read it again and again throughout your transaction, confident that it will return the same values. This level addresses the non-repeatable read problem that plagues Read Committed. When your business logic spans multiple reads of the same data and requires those reads to be consistent, Repeatable Read ensures that consistency.

What You Will Learn

By the end of this page, you will understand Repeatable Read's core guarantee (preventing non-repeatable reads), its two implementation approaches (long-duration locks vs transaction-level snapshots), why phantoms may or may not be possible depending on implementation, and critical platform differences—especially MySQL's unusually strong Repeatable Read.

Formal Definition of Repeatable Read

Repeatable Read is the third isolation level in the SQL standard hierarchy. It provides a stronger guarantee than Read Committed: not only will you never see uncommitted data, but any row you read will remain unchanged for the duration of your transaction. SQL Standard Definition: Repeatable Read is defined by the phenomena it prevents: Prevents Dirty Reads : Uncommitted modifications are invisible Prevents Non-Repeatable Reads : A row read once will return the same values if read again within the same transaction Permits Phantom Reads : New rows matching a query predicate may appear (per SQL standard)

Repeatable Read: Anomaly Prevention
Anomaly Type	Read Uncommitted	Read Committed	Repeatable Read
Dirty Read	❌ Possible	✅ Prevented	✅ Prevented
Non-Repeatable Read	❌ Possible	❌ Possible	✅ Prevented
Phantom Read	❌ Possible	❌ Possible	❌ Possible*

* Note : The SQL standard permits phantom reads at Repeatable Read. However, many implementations (especially those using MVCC) actually prevent phantoms too. This is a critical platform-specific detail we'll explore.

set_isolation_repeatable_read.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
-- Setting Repeatable Read isolation level in various databases
 
-- SQL Server
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    SELECT * FROM Accounts WHERE account_id = 'ACC001';
    -- This row is now locked (S lock held until commit)
    -- Re-reading will always return the same values
    
    -- Long processing...
    
    SELECT * FROM Accounts WHERE account_id = 'ACC001';
    -- Guaranteed: same values as first read
COMMIT;
 
-- PostgreSQL (MVCC-based)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- First query establishes transaction snapshot
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    
    -- All subsequent reads use the same snapshot
    -- Even if other transactions commit changes
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- Same values, guaranteed
COMMIT;
 
-- MySQL/InnoDB (this is the DEFAULT level!)
-- No need to explicitly set it
START TRANSACTION;
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- Uses consistent read snapshot from first query
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- Same values, guaranteed
COMMIT;

MySQL's Default Is Repeatable Read

Unlike PostgreSQL, Oracle, and SQL Server (which default to Read Committed), MySQL/InnoDB defaults to Repeatable Read. This means MySQL applications may unknowingly depend on stronger isolation semantics. Migrating to other databases requires careful testing.

Locking-Based Implementation

In traditional lock-based databases (like SQL Server without snapshot isolation), Repeatable Read is achieved by holding read locks until transaction end . Once you acquire a shared (S) lock on a row, you keep it, preventing any other transaction from modifying that row. How Locking-Based Repeatable Read Works: When Transaction A reads a row \to Acquires S lock, holds it until COMMIT/ROLLBACK If Transaction B tries to UPDATE the same row \to Blocked waiting for A's S lock to release Transaction A can re-read the row \to Same values guaranteed (row is locked) On COMMIT \to S locks released \to Pending writers can proceed

Lock Duration: Read Committed vs Repeatable Read
Operation	Read Committed	Repeatable Read	Impact
S lock acquired	During read only	During read
S lock released	Immediately after read	At transaction end	RR blocks writers longer
Can re-read same row?	Yes, but values may differ	Yes, values guaranteed same
Writers blocked?	Only during momentary read	Until reader commits	RR increases contention

locking_repeatable_read.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
-- Locking behavior under Repeatable Read (SQL Server)
 
-- Session 1: Reader with Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Acquires S lock on this row, holds it
    SELECT balance FROM Accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000
    
    -- S lock is HELD throughout transaction
    -- Simulate long processing
    WAITFOR DELAY '00:00:30';
    
    -- Re-read the same row
    SELECT balance FROM Accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000 (guaranteed same value!)
COMMIT;
-- S lock released on commit
 
-- Session 2: Writer (runs while Session 1 is active)
BEGIN TRANSACTION;
    -- Attempts to acquire X lock for update
    UPDATE Accounts SET balance = 500 WHERE account_id = 'ACC001';
    -- ⏳ BLOCKED! Waiting for Session 1's S lock to release
    -- This UPDATE will hang until Session 1 commits
COMMIT;
 
-- Timeline:
-- T0: Session 1 reads → acquires S lock
-- T1: Session 2 tries to update → blocked
-- T30: Session 1 commits → S lock released
-- T30+: Session 2's update proceeds

Increased Blocking and Deadlock Risk

Because S locks are held longer, Repeatable Read significantly increases contention. Writers wait for readers. This also raises deadlock risk: Transaction A locks rows 1,2 while Transaction B locks rows 3,4. If A then reads row 3 (blocked) while B reads row 1 (blocked), you have a deadlock.

MVCC-Based Implementation (Snapshot Isolation)

MVCC-based databases implement Repeatable Read using transaction-level snapshots . Rather than locking rows, the database captures a snapshot of the database at transaction start, and all reads within that transaction see the same consistent snapshot. How MVCC-Based Repeatable Read Works: Transaction A begins \to Snapshot captured (point-in-time view of all committed data) All reads by Transaction A see this snapshot, regardless of concurrent commits Transaction B commits changes \to A's snapshot is unaffected Transaction A re-reads \to Same values (snapshot hasn't changed) Transaction A commits \to Snapshot discarded

Converting Mermaid diagram...

mvcc_repeatable_read.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
-- MVCC-based Repeatable Read (PostgreSQL example)
 
-- Session 1: Reader with Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    -- First query establishes the snapshot
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000 (snapshot as of transaction start)
    
    -- Session 2 updates and commits during this time...
    -- (see below)
    
    -- Re-read - uses original snapshot
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000 (snapshot unchanged, even though $500 committed)
    
    -- Even aggregate queries use the same snapshot
    SELECT SUM(balance) FROM accounts;
    -- Returns sum based on T=100 snapshot
COMMIT;
 
-- Session 2: Writer (commits during Session 1's transaction)
BEGIN;
    UPDATE accounts SET balance = 500 WHERE account_id = 'ACC001';
COMMIT;
-- This commit succeeds immediately - no blocking!
-- But Session 1 won't see this change (snapshot isolation)
 
-- Key difference from locking:
-- LOCKING: Session 2's UPDATE would BLOCK until Session 1 commits
-- MVCC: Session 2's UPDATE succeeds immediately, Session 1 sees old data

MVCC Advantages for Repeatable Read

•Readers Never Block Writers: Updates proceed without waiting for snapshot readers
•Writers Never Block Readers: Snapshot provides consistent view without locks
•Reduced Deadlock Risk: Read operations don't acquire locks that could participate in deadlock cycles
•Phantoms Prevented: Same snapshot used for all queries, so row sets are consistent too
•Time-Travel Debugging: You see the database as it was at transaction start

Snapshot vs Transaction-Level

The key difference between Read Committed (MVCC) and Repeatable Read (MVCC) is snapshot granularity. Read Committed takes a new snapshot for each statement. Repeatable Read takes one snapshot for the entire transaction. Same mechanism, different scope.

Phantoms: SQL Standard vs Implementation Reality

Here's where theory and practice diverge significantly. The SQL standard states that Repeatable Read permits phantom reads . However, most modern MVCC implementations actually prevent phantoms at this level because the transaction-level snapshot freezes the entire visible dataset. Why the SQL Standard Permits Phantoms: The original SQL standard was written with locking implementations in mind. With locking: S locks protect individual rows from modification But S locks on specific rows can't prevent new rows from being inserted Therefore, new rows (phantoms) can appear in subsequent queries

Phantom Prevention: Standard vs Implementation
Database	Implementation	Phantoms at RR?	Why?
SQL Standard	(theoretical)	Permitted	Only specifies row-level consistency
SQL Server	Locking	Possible	S locks don't prevent inserts to range
PostgreSQL	MVCC	Prevented	Snapshot includes full result sets
MySQL/InnoDB	MVCC + Gap Locks	Prevented	Gap locks + consistent read snapshot
Oracle	MVCC	Prevented	Read-consistent snapshot

phantom_behavior.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
-- SQL Server: Phantoms CAN occur at Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    SELECT COUNT(*) FROM Employees WHERE department = 'Engineering';
    -- Returns: 5
    
    -- Another transaction inserts new Engineering employee and commits
    
    SELECT COUNT(*) FROM Employees WHERE department = 'Engineering';
    -- Returns: 6 (phantom row appeared!)
COMMIT;
 
-- PostgreSQL: Phantoms CANNOT occur at Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5
    
    -- Another transaction inserts and commits
    
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5 (snapshot prevents phantoms)
COMMIT;
 
-- MySQL/InnoDB: Phantoms CANNOT occur (uses gap locking)
START TRANSACTION;
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5
    -- Gap lock prevents inserts into this range
    
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5 (no phantoms possible)
COMMIT;

Portability Implications

If your PostgreSQL or MySQL application relies on phantom prevention at Repeatable Read, it will behave differently on SQL Server. The same isolation level name doesn't guarantee the same behavior across platforms. Always test on your target database.

MySQL's Gap Locking Mechanism

MySQL/InnoDB takes a unique approach to preventing phantoms: gap locking . In addition to locking actual rows, InnoDB locks the "gaps" between index entries, preventing insertions into those gaps. What is a Gap Lock? A gap lock is a lock on the space between two index entries. If you search for WHERE department = 'Engineering', InnoDB locks not just the matching rows but also the spaces before, after, and between them, preventing new 'Engineering' rows from being inserted.

gap_locking_example.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
-- MySQL InnoDB Gap Locking Demonstration
 
-- Session 1: SELECT with range condition
START TRANSACTION;
    -- This query locks:
    -- 1. All existing rows where id BETWEEN 10 AND 20
    -- 2. Gaps between those rows
    -- 3. Gap before id=10 and after id=20 within the range
    SELECT * FROM products WHERE id BETWEEN 10 AND 20 FOR UPDATE;
    
    -- Gap locks are held until transaction ends
COMMIT;
 
-- Session 2: Tries to insert into the range
START TRANSACTION;
    -- This INSERT will BLOCK if Session 1 is active
    INSERT INTO products (id, name) VALUES (15, 'New Product');
    -- ⏳ Blocked by Session 1's gap lock on the range 10-20
COMMIT;
 
-- Understanding gap lock ranges:
-- If products table has rows with id: 5, 10, 20, 25
-- A query for WHERE id BETWEEN 10 AND 20 locks:
-- - Record lock on id=10
-- - Gap lock on (10, 20) - the space between 10 and 20
-- - Record lock on id=20
-- Inserts into id=11, 12, 13... 19 are all blocked
 
-- To see current locks in MySQL:
SELECT * FROM performance_schema.data_locks 
WHERE OBJECT_NAME = 'products';

Next-Key Locks: InnoDB actually uses "next-key locks," which combine a record lock with a gap lock on the gap before that record. This prevents inserts on both sides of each locked row.

InnoDB Lock Types
Lock Type	What It Locks	Purpose
Record Lock	Individual index record (row)	Prevent modification of specific row
Gap Lock	Space between index records	Prevent inserts into range
Next-Key Lock	Record + gap before it	Both row protection and gap protection
Insert Intention Lock	Gap where insert will occur	Signal intent to insert without blocking other insert intentions

Gap Locking Side Effects

Gap locks can cause unexpected blocking. A SELECT ... FOR UPDATE on a range blocks all inserts into that range, even if the SELECT returns zero rows. This is sometimes surprising to developers who expect 'no rows matched' to mean 'nothing is locked.'

Write Conflicts in MVCC Systems

While MVCC-based Repeatable Read allows reads to proceed without blocking, write conflicts still occur. If two transactions try to modify the same row, the database must decide what happens. First-Committer-Wins Rule: Most MVCC databases implement "first-committer-wins" (or "first-updater-wins" in some cases). If Transaction A and Transaction B both try to update the same row: Transaction A updates the row (creates new version) Transaction B attempts to update the same row The database detects the conflict \to Transaction B receives an error and must retry or rollback

write_conflict_handling.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
-- Write Conflict in PostgreSQL Repeatable Read
 
-- Session 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 'ACC001';
    -- Creates new version, holds lock
    
    -- Continue processing...
 
-- Session 2 (starts after Session 1 began)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    UPDATE accounts SET balance = balance + 50 WHERE account_id = 'ACC001';
    -- ⏳ Blocks waiting for Session 1's lock
    
-- If Session 1 commits first:
-- Session 2 receives: ERROR: could not serialize access due to concurrent update
-- Session 2 must ROLLBACK and retry with a new transaction
 
-- This differs from Read Committed behavior:
-- At Read Committed: Session 2 would wait, then update the new value
-- At Repeatable Read: Session 2 waited, but then fails because the row changed
 
-- MySQL/InnoDB handles this with lock waiting + deadlock detection
-- After lock wait, the UPDATE proceeds (unless deadlock detected)
 
-- Oracle handles this with ORA-08177 error:
-- "can't serialize access for this transaction"

Implications for Application Design: At Repeatable Read (in MVCC systems), applications must be prepared to handle serialization failures. This is fundamentally different from lower isolation levels where updates simply wait and then proceed.

Handling Serialization Failures

•Catch and Retry: Wrap transactions in retry logic that handles serialization errors
•Limit Retry Attempts: Set maximum retries to prevent infinite loops in high-contention scenarios
•Exponential Backoff: Add increasing delays between retries to reduce collision probability
•Application-Level Locks: For highly contested resources, consider advisory locks to serialize access proactively
•Re-evaluate Isolation Need: If retries are frequent, consider if full Repeatable Read is necessary

PostgreSQL's Strict Approach

PostgreSQL is particularly strict about serialization failures at Repeatable Read. Any update to a row that was read earlier in the transaction (even without explicit locking) can fail if another transaction modified that row. This ensures true snapshot consistency but requires careful retry handling.

Practical Use Cases for Repeatable Read

Repeatable Read is the right choice when your transaction requires consistent reads—when you need to read data multiple times and depend on those reads being identical. Ideal Scenarios for Repeatable Read:

Well-Suited Workloads

•Report Generation: Multi-query reports that must show internally consistent data
•Multi-Step Calculations: Operations that read values, compute, then use those values again
•Inventory Verification: Checking stock levels multiple times during an order process
•Balance Reconciliation: Reading account balances that need to match across calculations
•Batch Processing with Validation: Processing batches where validation runs need stable data

repeatable_read_patterns.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
-- Pattern 1: Report generation with multiple queries
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Header: total sales
    SELECT SUM(amount) AS total_sales FROM orders WHERE date = '2024-01-15';
    -- Returns: $50,000
    
    -- Detail: individual orders
    SELECT order_id, amount FROM orders WHERE date = '2024-01-15';
    -- Returns 100 orders
    
    -- Footer: average order value
    SELECT AVG(amount) AS avg_order FROM orders WHERE date = '2024-01-15';
    -- Sum / Count = $50,000 / 100 = $500
    -- Consistent! Header total matches sum of details
COMMIT;
 
-- Pattern 2: Multi-step business calculation
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Read discount rate (might be read multiple times)
    DECLARE @DiscountRate DECIMAL(5,2);
    SELECT @DiscountRate = rate FROM DiscountTiers WHERE tier = @CustomerTier;
    
    -- Calculate line items using discount
    SELECT product_id, quantity, unit_price,
           unit_price * (1 - @DiscountRate) AS discounted_price
    FROM CartItems WHERE cart_id = @CartId;
    
    -- Verify discount is still valid (defensive check)
    IF @DiscountRate <> (SELECT rate FROM DiscountTiers WHERE tier = @CustomerTier)
        RAISERROR('Discount changed during calculation', 16, 1);
    -- At Repeatable Read, this check is redundant - rate cannot change
COMMIT;
 
-- Pattern 3: Balance transfer with verification
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    DECLARE @SourceBalance DECIMAL(18,2), @TargetBalance DECIMAL(18,2);
    
    -- Read both balances
    SELECT @SourceBalance = balance FROM Accounts WHERE id = @SourceId;
    SELECT @TargetBalance = balance FROM Accounts WHERE id = @TargetId;
    
    IF @SourceBalance < @Amount
        RAISERROR('Insufficient funds', 16, 1);
    
    -- Perform transfer
    UPDATE Accounts SET balance = balance - @Amount WHERE id = @SourceId;
    UPDATE Accounts SET balance = balance + @Amount WHERE id = @TargetId;
    
    -- The reads were consistent throughout - amount was verified correctly
COMMIT;

Snapshot Reporting Alternative

For long-running reports that don't modify data, consider using explicit snapshot transactions (where available) or running against a read replica. This avoids the overhead and potential conflicts of Repeatable Read in the main transaction processing path.

Database Management SystemsIsolation Levels

Isolation Levels

LevelIntermediate

Duration90 mins

TopicIsolation Levels

3 / 5

Repeatable Read

Consistency Within Your Transaction

What if you could freeze the rows you read, ensuring they remain unchanged for the duration of your transaction? Repeatable Read provides exactly this guarantee. Once you read a row, you can read it again and again throughout your transaction, confident that it will return the same values. This level addresses the non-repeatable read problem that plagues Read Committed. When your business logic spans multiple reads of the same data and requires those reads to be consistent, Repeatable Read ensures that consistency.

What You Will Learn

By the end of this page, you will understand Repeatable Read's core guarantee (preventing non-repeatable reads), its two implementation approaches (long-duration locks vs transaction-level snapshots), why phantoms may or may not be possible depending on implementation, and critical platform differences—especially MySQL's unusually strong Repeatable Read.

Formal Definition of Repeatable Read

Repeatable Read is the third isolation level in the SQL standard hierarchy. It provides a stronger guarantee than Read Committed: not only will you never see uncommitted data, but any row you read will remain unchanged for the duration of your transaction. SQL Standard Definition: Repeatable Read is defined by the phenomena it prevents: Prevents Dirty Reads : Uncommitted modifications are invisible Prevents Non-Repeatable Reads : A row read once will return the same values if read again within the same transaction Permits Phantom Reads : New rows matching a query predicate may appear (per SQL standard)

Repeatable Read: Anomaly Prevention
Anomaly Type	Read Uncommitted	Read Committed	Repeatable Read
Dirty Read	❌ Possible	✅ Prevented	✅ Prevented
Non-Repeatable Read	❌ Possible	❌ Possible	✅ Prevented
Phantom Read	❌ Possible	❌ Possible	❌ Possible*

* Note : The SQL standard permits phantom reads at Repeatable Read. However, many implementations (especially those using MVCC) actually prevent phantoms too. This is a critical platform-specific detail we'll explore.

set_isolation_repeatable_read.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
-- Setting Repeatable Read isolation level in various databases
 
-- SQL Server
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    SELECT * FROM Accounts WHERE account_id = 'ACC001';
    -- This row is now locked (S lock held until commit)
    -- Re-reading will always return the same values
    
    -- Long processing...
    
    SELECT * FROM Accounts WHERE account_id = 'ACC001';
    -- Guaranteed: same values as first read
COMMIT;
 
-- PostgreSQL (MVCC-based)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- First query establishes transaction snapshot
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    
    -- All subsequent reads use the same snapshot
    -- Even if other transactions commit changes
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- Same values, guaranteed
COMMIT;
 
-- MySQL/InnoDB (this is the DEFAULT level!)
-- No need to explicitly set it
START TRANSACTION;
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- Uses consistent read snapshot from first query
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- Same values, guaranteed
COMMIT;

MySQL's Default Is Repeatable Read

Unlike PostgreSQL, Oracle, and SQL Server (which default to Read Committed), MySQL/InnoDB defaults to Repeatable Read. This means MySQL applications may unknowingly depend on stronger isolation semantics. Migrating to other databases requires careful testing.

Locking-Based Implementation

In traditional lock-based databases (like SQL Server without snapshot isolation), Repeatable Read is achieved by holding read locks until transaction end . Once you acquire a shared (S) lock on a row, you keep it, preventing any other transaction from modifying that row. How Locking-Based Repeatable Read Works: When Transaction A reads a row \to Acquires S lock, holds it until COMMIT/ROLLBACK If Transaction B tries to UPDATE the same row \to Blocked waiting for A's S lock to release Transaction A can re-read the row \to Same values guaranteed (row is locked) On COMMIT \to S locks released \to Pending writers can proceed

Lock Duration: Read Committed vs Repeatable Read
Operation	Read Committed	Repeatable Read	Impact
S lock acquired	During read only	During read
S lock released	Immediately after read	At transaction end	RR blocks writers longer
Can re-read same row?	Yes, but values may differ	Yes, values guaranteed same
Writers blocked?	Only during momentary read	Until reader commits	RR increases contention

locking_repeatable_read.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
-- Locking behavior under Repeatable Read (SQL Server)
 
-- Session 1: Reader with Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Acquires S lock on this row, holds it
    SELECT balance FROM Accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000
    
    -- S lock is HELD throughout transaction
    -- Simulate long processing
    WAITFOR DELAY '00:00:30';
    
    -- Re-read the same row
    SELECT balance FROM Accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000 (guaranteed same value!)
COMMIT;
-- S lock released on commit
 
-- Session 2: Writer (runs while Session 1 is active)
BEGIN TRANSACTION;
    -- Attempts to acquire X lock for update
    UPDATE Accounts SET balance = 500 WHERE account_id = 'ACC001';
    -- ⏳ BLOCKED! Waiting for Session 1's S lock to release
    -- This UPDATE will hang until Session 1 commits
COMMIT;
 
-- Timeline:
-- T0: Session 1 reads → acquires S lock
-- T1: Session 2 tries to update → blocked
-- T30: Session 1 commits → S lock released
-- T30+: Session 2's update proceeds

Increased Blocking and Deadlock Risk

Because S locks are held longer, Repeatable Read significantly increases contention. Writers wait for readers. This also raises deadlock risk: Transaction A locks rows 1,2 while Transaction B locks rows 3,4. If A then reads row 3 (blocked) while B reads row 1 (blocked), you have a deadlock.

MVCC-Based Implementation (Snapshot Isolation)

MVCC-based databases implement Repeatable Read using transaction-level snapshots . Rather than locking rows, the database captures a snapshot of the database at transaction start, and all reads within that transaction see the same consistent snapshot. How MVCC-Based Repeatable Read Works: Transaction A begins \to Snapshot captured (point-in-time view of all committed data) All reads by Transaction A see this snapshot, regardless of concurrent commits Transaction B commits changes \to A's snapshot is unaffected Transaction A re-reads \to Same values (snapshot hasn't changed) Transaction A commits \to Snapshot discarded

Converting Mermaid diagram...

mvcc_repeatable_read.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
-- MVCC-based Repeatable Read (PostgreSQL example)
 
-- Session 1: Reader with Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    -- First query establishes the snapshot
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000 (snapshot as of transaction start)
    
    -- Session 2 updates and commits during this time...
    -- (see below)
    
    -- Re-read - uses original snapshot
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns: $1,000 (snapshot unchanged, even though $500 committed)
    
    -- Even aggregate queries use the same snapshot
    SELECT SUM(balance) FROM accounts;
    -- Returns sum based on T=100 snapshot
COMMIT;
 
-- Session 2: Writer (commits during Session 1's transaction)
BEGIN;
    UPDATE accounts SET balance = 500 WHERE account_id = 'ACC001';
COMMIT;
-- This commit succeeds immediately - no blocking!
-- But Session 1 won't see this change (snapshot isolation)
 
-- Key difference from locking:
-- LOCKING: Session 2's UPDATE would BLOCK until Session 1 commits
-- MVCC: Session 2's UPDATE succeeds immediately, Session 1 sees old data

MVCC Advantages for Repeatable Read

•Readers Never Block Writers: Updates proceed without waiting for snapshot readers
•Writers Never Block Readers: Snapshot provides consistent view without locks
•Reduced Deadlock Risk: Read operations don't acquire locks that could participate in deadlock cycles
•Phantoms Prevented: Same snapshot used for all queries, so row sets are consistent too
•Time-Travel Debugging: You see the database as it was at transaction start

Snapshot vs Transaction-Level

The key difference between Read Committed (MVCC) and Repeatable Read (MVCC) is snapshot granularity. Read Committed takes a new snapshot for each statement. Repeatable Read takes one snapshot for the entire transaction. Same mechanism, different scope.

Phantoms: SQL Standard vs Implementation Reality

Here's where theory and practice diverge significantly. The SQL standard states that Repeatable Read permits phantom reads . However, most modern MVCC implementations actually prevent phantoms at this level because the transaction-level snapshot freezes the entire visible dataset. Why the SQL Standard Permits Phantoms: The original SQL standard was written with locking implementations in mind. With locking: S locks protect individual rows from modification But S locks on specific rows can't prevent new rows from being inserted Therefore, new rows (phantoms) can appear in subsequent queries

Phantom Prevention: Standard vs Implementation
Database	Implementation	Phantoms at RR?	Why?
SQL Standard	(theoretical)	Permitted	Only specifies row-level consistency
SQL Server	Locking	Possible	S locks don't prevent inserts to range
PostgreSQL	MVCC	Prevented	Snapshot includes full result sets
MySQL/InnoDB	MVCC + Gap Locks	Prevented	Gap locks + consistent read snapshot
Oracle	MVCC	Prevented	Read-consistent snapshot

phantom_behavior.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
-- SQL Server: Phantoms CAN occur at Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    SELECT COUNT(*) FROM Employees WHERE department = 'Engineering';
    -- Returns: 5
    
    -- Another transaction inserts new Engineering employee and commits
    
    SELECT COUNT(*) FROM Employees WHERE department = 'Engineering';
    -- Returns: 6 (phantom row appeared!)
COMMIT;
 
-- PostgreSQL: Phantoms CANNOT occur at Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5
    
    -- Another transaction inserts and commits
    
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5 (snapshot prevents phantoms)
COMMIT;
 
-- MySQL/InnoDB: Phantoms CANNOT occur (uses gap locking)
START TRANSACTION;
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5
    -- Gap lock prevents inserts into this range
    
    SELECT COUNT(*) FROM employees WHERE department = 'Engineering';
    -- Returns: 5 (no phantoms possible)
COMMIT;

Portability Implications

If your PostgreSQL or MySQL application relies on phantom prevention at Repeatable Read, it will behave differently on SQL Server. The same isolation level name doesn't guarantee the same behavior across platforms. Always test on your target database.

MySQL's Gap Locking Mechanism

MySQL/InnoDB takes a unique approach to preventing phantoms: gap locking . In addition to locking actual rows, InnoDB locks the "gaps" between index entries, preventing insertions into those gaps. What is a Gap Lock? A gap lock is a lock on the space between two index entries. If you search for WHERE department = 'Engineering', InnoDB locks not just the matching rows but also the spaces before, after, and between them, preventing new 'Engineering' rows from being inserted.

gap_locking_example.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
-- MySQL InnoDB Gap Locking Demonstration
 
-- Session 1: SELECT with range condition
START TRANSACTION;
    -- This query locks:
    -- 1. All existing rows where id BETWEEN 10 AND 20
    -- 2. Gaps between those rows
    -- 3. Gap before id=10 and after id=20 within the range
    SELECT * FROM products WHERE id BETWEEN 10 AND 20 FOR UPDATE;
    
    -- Gap locks are held until transaction ends
COMMIT;
 
-- Session 2: Tries to insert into the range
START TRANSACTION;
    -- This INSERT will BLOCK if Session 1 is active
    INSERT INTO products (id, name) VALUES (15, 'New Product');
    -- ⏳ Blocked by Session 1's gap lock on the range 10-20
COMMIT;
 
-- Understanding gap lock ranges:
-- If products table has rows with id: 5, 10, 20, 25
-- A query for WHERE id BETWEEN 10 AND 20 locks:
-- - Record lock on id=10
-- - Gap lock on (10, 20) - the space between 10 and 20
-- - Record lock on id=20
-- Inserts into id=11, 12, 13... 19 are all blocked
 
-- To see current locks in MySQL:
SELECT * FROM performance_schema.data_locks 
WHERE OBJECT_NAME = 'products';

Next-Key Locks: InnoDB actually uses "next-key locks," which combine a record lock with a gap lock on the gap before that record. This prevents inserts on both sides of each locked row.

InnoDB Lock Types
Lock Type	What It Locks	Purpose
Record Lock	Individual index record (row)	Prevent modification of specific row
Gap Lock	Space between index records	Prevent inserts into range
Next-Key Lock	Record + gap before it	Both row protection and gap protection
Insert Intention Lock	Gap where insert will occur	Signal intent to insert without blocking other insert intentions

Gap Locking Side Effects

Gap locks can cause unexpected blocking. A SELECT ... FOR UPDATE on a range blocks all inserts into that range, even if the SELECT returns zero rows. This is sometimes surprising to developers who expect 'no rows matched' to mean 'nothing is locked.'

Write Conflicts in MVCC Systems

While MVCC-based Repeatable Read allows reads to proceed without blocking, write conflicts still occur. If two transactions try to modify the same row, the database must decide what happens. First-Committer-Wins Rule: Most MVCC databases implement "first-committer-wins" (or "first-updater-wins" in some cases). If Transaction A and Transaction B both try to update the same row: Transaction A updates the row (creates new version) Transaction B attempts to update the same row The database detects the conflict \to Transaction B receives an error and must retry or rollback

write_conflict_handling.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
-- Write Conflict in PostgreSQL Repeatable Read
 
-- Session 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 'ACC001';
    -- Creates new version, holds lock
    
    -- Continue processing...
 
-- Session 2 (starts after Session 1 began)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    UPDATE accounts SET balance = balance + 50 WHERE account_id = 'ACC001';
    -- ⏳ Blocks waiting for Session 1's lock
    
-- If Session 1 commits first:
-- Session 2 receives: ERROR: could not serialize access due to concurrent update
-- Session 2 must ROLLBACK and retry with a new transaction
 
-- This differs from Read Committed behavior:
-- At Read Committed: Session 2 would wait, then update the new value
-- At Repeatable Read: Session 2 waited, but then fails because the row changed
 
-- MySQL/InnoDB handles this with lock waiting + deadlock detection
-- After lock wait, the UPDATE proceeds (unless deadlock detected)
 
-- Oracle handles this with ORA-08177 error:
-- "can't serialize access for this transaction"

Implications for Application Design: At Repeatable Read (in MVCC systems), applications must be prepared to handle serialization failures. This is fundamentally different from lower isolation levels where updates simply wait and then proceed.

Handling Serialization Failures

•Catch and Retry: Wrap transactions in retry logic that handles serialization errors
•Limit Retry Attempts: Set maximum retries to prevent infinite loops in high-contention scenarios
•Exponential Backoff: Add increasing delays between retries to reduce collision probability
•Application-Level Locks: For highly contested resources, consider advisory locks to serialize access proactively
•Re-evaluate Isolation Need: If retries are frequent, consider if full Repeatable Read is necessary

PostgreSQL's Strict Approach

PostgreSQL is particularly strict about serialization failures at Repeatable Read. Any update to a row that was read earlier in the transaction (even without explicit locking) can fail if another transaction modified that row. This ensures true snapshot consistency but requires careful retry handling.

Practical Use Cases for Repeatable Read

Repeatable Read is the right choice when your transaction requires consistent reads—when you need to read data multiple times and depend on those reads being identical. Ideal Scenarios for Repeatable Read:

Well-Suited Workloads

•Report Generation: Multi-query reports that must show internally consistent data
•Multi-Step Calculations: Operations that read values, compute, then use those values again
•Inventory Verification: Checking stock levels multiple times during an order process
•Balance Reconciliation: Reading account balances that need to match across calculations
•Batch Processing with Validation: Processing batches where validation runs need stable data

repeatable_read_patterns.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
-- Pattern 1: Report generation with multiple queries
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Header: total sales
    SELECT SUM(amount) AS total_sales FROM orders WHERE date = '2024-01-15';
    -- Returns: $50,000
    
    -- Detail: individual orders
    SELECT order_id, amount FROM orders WHERE date = '2024-01-15';
    -- Returns 100 orders
    
    -- Footer: average order value
    SELECT AVG(amount) AS avg_order FROM orders WHERE date = '2024-01-15';
    -- Sum / Count = $50,000 / 100 = $500
    -- Consistent! Header total matches sum of details
COMMIT;
 
-- Pattern 2: Multi-step business calculation
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Read discount rate (might be read multiple times)
    DECLARE @DiscountRate DECIMAL(5,2);
    SELECT @DiscountRate = rate FROM DiscountTiers WHERE tier = @CustomerTier;
    
    -- Calculate line items using discount
    SELECT product_id, quantity, unit_price,
           unit_price * (1 - @DiscountRate) AS discounted_price
    FROM CartItems WHERE cart_id = @CartId;
    
    -- Verify discount is still valid (defensive check)
    IF @DiscountRate <> (SELECT rate FROM DiscountTiers WHERE tier = @CustomerTier)
        RAISERROR('Discount changed during calculation', 16, 1);
    -- At Repeatable Read, this check is redundant - rate cannot change
COMMIT;
 
-- Pattern 3: Balance transfer with verification
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    DECLARE @SourceBalance DECIMAL(18,2), @TargetBalance DECIMAL(18,2);
    
    -- Read both balances
    SELECT @SourceBalance = balance FROM Accounts WHERE id = @SourceId;
    SELECT @TargetBalance = balance FROM Accounts WHERE id = @TargetId;
    
    IF @SourceBalance < @Amount
        RAISERROR('Insufficient funds', 16, 1);
    
    -- Perform transfer
    UPDATE Accounts SET balance = balance - @Amount WHERE id = @SourceId;
    UPDATE Accounts SET balance = balance + @Amount WHERE id = @TargetId;
    
    -- The reads were consistent throughout - amount was verified correctly
COMMIT;

Snapshot Reporting Alternative

For long-running reports that don't modify data, consider using explicit snapshot transactions (where available) or running against a read replica. This avoids the overhead and potential conflicts of Repeatable Read in the main transaction processing path.

Repeatable Read

Consistency Within Your Transaction

Formal Definition of Repeatable Read

Locking-Based Implementation

MVCC-Based Implementation (Snapshot Isolation)

Phantoms: SQL Standard vs Implementation Reality

MySQL's Gap Locking Mechanism

Write Conflicts in MVCC Systems

Practical Use Cases for Repeatable Read

There’s more to understand.

Repeatable Read

Consistency Within Your Transaction

Formal Definition of Repeatable Read

Locking-Based Implementation

MVCC-Based Implementation (Snapshot Isolation)

Phantoms: SQL Standard vs Implementation Reality

MySQL's Gap Locking Mechanism

Write Conflicts in MVCC Systems

Practical Use Cases for Repeatable Read

There’s more to understand.