Repeatable read behavior in PostgreSQL - Deep Dive

Overview - Repeatable Read Behavior

What is it?

Repeatable Read is a transaction isolation level in databases that ensures a transaction sees a consistent snapshot of the data throughout its execution. This means that if you read the same data multiple times within one transaction, you will get the same results each time, even if other transactions modify the data concurrently. It prevents some types of data anomalies like non-repeatable reads but allows others like phantom reads depending on the database system.

Why it matters

Without Repeatable Read, transactions might see different data each time they read the same rows, leading to inconsistent results and bugs in applications. This isolation level helps maintain data integrity and predictability in concurrent environments, which is crucial for financial systems, booking platforms, and any application where consistent reads matter. Without it, users might see confusing or incorrect data during their operations.

Where it fits

Before learning Repeatable Read, you should understand basic database transactions and the concept of isolation levels like Read Committed. After mastering Repeatable Read, you can explore higher isolation levels like Serializable and learn about locking mechanisms and concurrency control in databases.

Mental Model

Core Idea

Repeatable Read ensures that once a transaction reads data, it will see the same data for that read throughout the transaction, preventing changes from other transactions from appearing mid-way.

Think of it like...

Imagine reading a book in a library where no one is allowed to change the pages while you are reading. Even if others want to update the book, you keep seeing the same pages until you finish reading.

┌───────────────────────────────┐
│ Transaction Start             │
│ ┌─────────────────────────┐ │
│ │ Snapshot of data taken  │ │
│ └─────────────────────────┘ │
│ Read data multiple times      │
│ ┌─────────────────────────┐ │
│ │ Same data shown each time│ │
│ └─────────────────────────┘ │
│ Transaction Commit/Abort      │
└───────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Transactions and Isolation

Concept: Introduce what a database transaction is and why isolation matters.

A transaction is a group of database operations executed as a single unit. Isolation means that transactions do not interfere with each other’s data while running concurrently. Without isolation, one transaction might see partial or inconsistent changes made by another.

Result

You understand that transactions need isolation to keep data consistent when multiple users access the database at the same time.

Understanding transactions and isolation is essential because it sets the stage for why different isolation levels, like Repeatable Read, exist.

2

Foundation

Basics of Isolation Levels

Concept: Explain the different isolation levels and their trade-offs.

Isolation levels define how visible changes from one transaction are to others. The common levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level offers a balance between performance and strictness of data consistency.

Result

You can distinguish between isolation levels and know that Repeatable Read is stricter than Read Committed but less strict than Serializable.

Knowing isolation levels helps you choose the right balance between performance and consistency for your application.

3

Intermediate

Repeatable Read Guarantees Explained

🤔Before reading on: do you think Repeatable Read prevents all data anomalies or only some? Commit to your answer.

Concept: Repeatable Read guarantees that rows read once will not change during the transaction, preventing non-repeatable reads.

In Repeatable Read, when a transaction reads data, it sees a snapshot of that data as it was at the start of the transaction. Even if other transactions update or delete those rows, your transaction will continue to see the original data. This prevents non-repeatable reads but may still allow phantom reads (new rows matching a query appearing).

Result

You understand that Repeatable Read prevents changes to rows you have read but does not fully prevent new rows from appearing.

Understanding the exact guarantees of Repeatable Read helps avoid mistaken assumptions about data consistency.

4

Intermediate

How PostgreSQL Implements Repeatable Read

🤔Before reading on: do you think PostgreSQL uses locks or snapshots to implement Repeatable Read? Commit to your answer.

Concept: PostgreSQL uses Multiversion Concurrency Control (MVCC) to provide Repeatable Read by giving each transaction a consistent snapshot of the database.

Instead of locking rows, PostgreSQL creates a snapshot of the database state at the start of the transaction. All reads within the transaction see this snapshot. Writes are checked at commit time to avoid conflicts. This approach allows high concurrency without blocking reads.

Result

You see that PostgreSQL’s Repeatable Read is snapshot-based, not lock-based, enabling efficient concurrent reads.

Knowing PostgreSQL’s MVCC snapshot approach explains why Repeatable Read can be efficient and how it differs from locking strategies.

5

Intermediate

Phantom Reads and Repeatable Read Limits

🤔Before reading on: do you think Repeatable Read prevents phantom reads completely? Commit to your answer.

Concept: Phantom reads occur when new rows matching a query appear during a transaction. Repeatable Read in PostgreSQL prevents this through MVCC snapshots.

While Repeatable Read generally prevents non-repeatable reads, phantom reads can still happen in some databases. PostgreSQL’s implementation prevents phantom reads through MVCC snapshots, making it stronger than the standard definition. However, this is a subtle detail and depends on the database system.

Result

You learn that PostgreSQL’s Repeatable Read is stronger than in some other systems, preventing phantom reads as well.

Understanding phantom reads and how PostgreSQL handles them clarifies the practical strength of Repeatable Read in real systems.

6

Advanced

Conflict Handling and Serialization Failures

🤔Before reading on: do you think Repeatable Read transactions can fail due to conflicts? Commit to your answer.

Concept: In PostgreSQL, Repeatable Read transactions may abort with serialization failures if concurrent updates conflict, requiring retry.

Because PostgreSQL uses MVCC snapshots, if two transactions modify overlapping data, one may be forced to abort to maintain consistency. This is called a serialization failure. Applications must be prepared to catch this error and retry the transaction.

Result

You understand that Repeatable Read can cause transaction aborts due to conflicts, which is a normal part of concurrency control.

Knowing about serialization failures prepares you to handle transaction retries gracefully in production.

7

Expert

Repeatable Read vs Serializable in PostgreSQL

🤔Before reading on: do you think Repeatable Read and Serializable behave the same in PostgreSQL? Commit to your answer.

Concept: PostgreSQL’s Repeatable Read is almost as strict as Serializable but differs in subtle ways related to predicate locking and conflict detection.

PostgreSQL’s Repeatable Read provides a snapshot isolation level that prevents many anomalies but allows some anomalies that Serializable prevents. Serializable adds extra checks to ensure full serializability, sometimes causing more transaction aborts. Understanding these differences helps choose the right isolation level for your needs.

Result

You grasp the nuanced differences between Repeatable Read and Serializable in PostgreSQL.

Understanding these subtle differences helps optimize for performance versus strict correctness in complex applications.

Under the Hood

PostgreSQL uses Multiversion Concurrency Control (MVCC) to implement Repeatable Read. When a transaction starts, it takes a snapshot of the database state, marking which transactions are visible. All reads use this snapshot, so data appears consistent throughout the transaction. Writes are tracked and checked at commit time to detect conflicts. If conflicts arise, the transaction may abort to maintain consistency.

Why designed this way?

MVCC was designed to allow high concurrency without locking readers, which can block other transactions. Snapshot isolation balances performance and consistency by letting readers see a stable view while writers proceed concurrently. This design avoids many locking bottlenecks and deadlocks common in lock-based systems.

┌───────────────┐
│ Transaction A │
│ ┌───────────┐ │
│ │ Snapshot  │ │
│ │ taken at  │ │
│ │ start     │ │
│ └───────────┘ │
│ Reads see    │
│ consistent  │
│ snapshot    │
└─────┬─────────┘
      │
      ▼
┌───────────────┐
│ Transaction B │
│ Concurrent    │
│ writes data   │
└───────────────┘

Commit time checks for conflicts → Abort if conflict detected

Myth Busters - 4 Common Misconceptions

Quick: Does Repeatable Read prevent phantom reads completely in all databases? Commit yes or no.

Common Belief: Repeatable Read always prevents phantom reads.

Reality: In many databases, Repeatable Read does not prevent phantom reads; only Serializable does. PostgreSQL is an exception where Repeatable Read prevents phantoms through snapshot isolation.

Why it matters: Assuming phantom reads are prevented everywhere can lead to data anomalies and bugs when switching databases or expecting consistent behavior.

Quick: Can Repeatable Read transactions run without ever aborting due to conflicts? Commit yes or no.

Common Belief: Repeatable Read transactions never fail due to concurrency conflicts.

Reality: In PostgreSQL, Repeatable Read transactions can abort with serialization failures if concurrent conflicting writes occur.

Why it matters: Not handling transaction aborts can cause application errors and data inconsistencies in production.

Quick: Does Repeatable Read lock all rows read to prevent changes? Commit yes or no.

Common Belief: Repeatable Read locks all rows read to prevent changes by others.

Reality: PostgreSQL uses MVCC snapshots, not locks, for reads in Repeatable Read, allowing concurrent access without blocking readers.

Why it matters: Misunderstanding locking behavior can lead to incorrect assumptions about performance and concurrency.

Quick: Is Repeatable Read the strictest isolation level available? Commit yes or no.

Common Belief: Repeatable Read is the highest isolation level and prevents all anomalies.

Reality: Serializable is stricter than Repeatable Read and prevents all anomalies including phantom reads in all databases.

Why it matters: Choosing Repeatable Read when Serializable is needed can cause subtle data anomalies.

Expert Zone

1

PostgreSQL’s Repeatable Read provides snapshot isolation to prevent phantom reads, which is stronger than the standard Repeatable Read definition.

2

Serialization failures under Repeatable Read require careful application design to retry transactions without causing user-visible errors.

3

The difference between Repeatable Read and Serializable in PostgreSQL is subtle and often misunderstood, involving how conflicts are detected and resolved.

When NOT to use

Avoid Repeatable Read when your application requires full serializability guarantees; use Serializable instead. Also, if your workload is read-heavy and can tolerate some anomalies, Read Committed may offer better performance. For extremely high concurrency with minimal locking, consider Read Committed or application-level consistency controls.

Production Patterns

In production, Repeatable Read is often used in financial and booking systems where consistent reads are critical but full serializability is too costly. Applications implement retry logic to handle serialization failures gracefully. Monitoring transaction abort rates helps tune isolation levels and concurrency settings.

Connections

Multiversion Concurrency Control (MVCC)

Repeatable Read builds on MVCC to provide consistent snapshots for transactions.

Understanding MVCC explains how Repeatable Read achieves consistency without locking readers.

Optimistic Concurrency Control

Repeatable Read in PostgreSQL uses optimistic concurrency by detecting conflicts at commit time.

Knowing optimistic concurrency helps understand why transactions may abort and need retries.

Version Control Systems (e.g., Git)

Both use snapshots and conflict detection to manage concurrent changes safely.

Recognizing this similarity helps grasp how databases manage concurrent data changes like code changes.

Common Pitfalls

#1 Assuming Repeatable Read prevents all anomalies including phantom reads in all databases.

Wrong approach:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE status = 'pending';
-- Later in the same transaction
SELECT * FROM orders WHERE status = 'pending';
-- Expect no new rows to appear but in some DBs they do

Correct approach:

Use SERIALIZABLE isolation level if phantom reads must be prevented:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM orders WHERE status = 'pending';
-- Later in the same transaction
SELECT * FROM orders WHERE status = 'pending';

Root cause:

Misunderstanding the guarantees of Repeatable Read and differences between database implementations.

#2 Not handling serialization failures leading to application crashes.

Wrong approach:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- No error handling for serialization failure

Correct approach:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- Catch serialization failure error and retry transaction

Root cause:

Ignoring that Repeatable Read transactions can abort due to concurrent conflicts.

#3 Believing Repeatable Read locks rows on read causing blocking.

Wrong approach:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM products WHERE id = 10 FOR SHARE;
-- Think this locks rows for reading always

Correct approach:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM products WHERE id = 10;
-- Reads use MVCC snapshot, no locking needed

Root cause:

Confusing MVCC snapshot reads with explicit locking mechanisms.

Key Takeaways

Repeatable Read isolation level ensures a transaction sees the same data for repeated reads, preventing non-repeatable reads.

PostgreSQL implements Repeatable Read using MVCC snapshots, allowing high concurrency without locking readers.

Repeatable Read may cause serialization failures requiring transactions to be retried to maintain consistency.

Phantom reads are prevented in PostgreSQL’s Repeatable Read due to its snapshot isolation, but this is not true in all databases.

Understanding the subtle differences between Repeatable Read and Serializable helps choose the right isolation level for your application.