REPEATABLE READ Isolation Level: Preventing Non-Repeatable Read

Imagine you’re playing an online game, and some cheater is messing with its code to buff their player—or you’re reading a book in the library, and someone could be swapping out pages, adding new chapters, or even replacing the whole book while you’re reading. Pretty annoying, right? That’s exactly the kind of “surprises” the REPEATABLE READ isolation level protects you from.

REPEATABLE READ guarantees that the data you see inside a single transaction will stay the same until the end of that transaction. Even if another transaction tries to update that data, your transaction is shielded from those changes.

Key features:

• Prevents Dirty Read (reading data that hasn’t been committed yet).
• And most importantly, it prevents Non-Repeatable Read. That means if you read a set of data at the start of your transaction, you’ll get the same data if you read it again—even if someone else changed it in the meantime.

In the SQL standard REPEATABLE READ doesn’t protect you from Phantom Read. However, PostgreSQL’s implementation is stricter than the standard: the REPEATABLE READ level uses snapshot isolation and does not allow phantom reads within a transaction — you see data as of the transaction start, and new rows from other transactions don’t appear. If you need full serializability (protection against write skew and similar anomalies), use SERIALIZABLE, but we’ll talk about that later.

How to Set REPEATABLE READ Isolation Level

Before we jump into examples, let’s see how to enable this isolation level in PostgreSQL. There are two main ways:

1. Set the isolation level for a specific transaction:

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN;
   -- Your queries
   COMMIT;
   

2. Set the isolation level for the current session:

   SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   

In the second case, all transactions in the current session will use REPEATABLE READ.

Example: Preventing Non-Repeatable Read

Let’s say we have a table called accounts with the following structure:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (customer_name, status)
VALUES ('Alice', 'pending'), ('Bob', 'pending');

Let’s start with a basic scenario where one transaction changes data and another reads it.

Scenario without REPEATABLE READ (using READ COMMITTED level)

Transaction 1 starts:

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Result: 100

Meanwhile, Transaction 2 changes the data:

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Transaction 1 continues:

SELECT balance FROM accounts WHERE account_id = 1;
-- Result: 150 (the data changed!)
COMMIT;

As you can see, with READ COMMITTED level, data can change between two reads in the same transaction. That’s what Non-Repeatable Read is all about.

Scenario with REPEATABLE READ

Now let’s try the same example, but with REPEATABLE READ isolation level.

Transaction 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Result: 100

Transaction 2:

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Transaction 1 keeps going:

SELECT balance FROM accounts WHERE account_id = 1;
-- Still get: 100 (data didn’t change!)
COMMIT;

No matter what changes another transaction makes, transaction 1 sees the data as it was at the start. So, Non-Repeatable Read is prevented.

How REPEATABLE READ Works

PostgreSQL uses a thing called MVCC (Multi-Version Concurrency Control) to implement the REPEATABLE READ isolation level. The main idea behind MVCC is that every transaction gets a stable “snapshot” of the database that doesn’t change until the transaction ends. This is done by creating and managing multiple versions of rows.

When a transaction starts, it sees the data as it was at that moment. If another transaction makes changes, PostgreSQL creates a new version of the row, but the old version sticks around for any transactions that still need it.

That’s why transactions can be slow and eat up a lot of memory. And that’s why not many folks use the strictest isolation level: it’s the most reliable, but it slows down your database the most.

Limitations of REPEATABLE READ: Phantom Read

Important clarification: the SQL standard allows Phantom Read at REPEATABLE READ, but PostgreSQL’s implementation (snapshot isolation) does not allow it. To clearly see the difference between PostgreSQL’s implementation and the standard behavior, let’s look at an example with queries that work on data ranges.

Suppose we have a table called orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    amount NUMERIC NOT NULL
);

INSERT INTO orders (amount)
VALUES (50), (100), (150);

Transaction 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Result: 2

Transaction 2:

BEGIN;
INSERT INTO orders (amount) VALUES (200);
COMMIT;

Transaction 1 keeps going:

SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Still: 2 — thanks to snapshot isolation in PostgreSQL.
COMMIT;

By the SQL standard this query would return 3 (the phantom row with amount = 200), but thanks to snapshot isolation PostgreSQL fully isolates the transaction from changes made by other transactions after it began.

If you need protection not just from phantoms but also from write skew and similar concurrency anomalies, use the SERIALIZABLE level — but that involves a performance tradeoff.

Pros and Cons of REPEATABLE READ

The REPEATABLE READ isolation level is a great choice when you want to be sure your data won’t change while your transaction is running. Once you read something, that value will stay the same until COMMIT, even if someone else tries to change it in another transaction.

This approach prevents both dirty reads and non-repeatable reads. You’re working with the same data you started with—no surprise updates “on the fly.” That’s especially useful when you’re generating reports or making decisions where consistency is key.

On the flip side, REPEATABLE READ doesn’t handle so-called “phantoms” (phantom read)—when new rows show up in the result of a query you already ran in the same transaction. Also, under heavy load, this level can cause conflicts between transactions, especially if they’re hitting the same data a lot. That can lead to locks and rollbacks, even if your queries are totally legit.

All in all, REPEATABLE READ is a solid balance of reliability and performance, but in high-concurrency scenarios, you might need to tweak things and pay extra attention.

Pro Tips and Common Mistakes

• Remember, your choice of isolation level affects performance. Use REPEATABLE READ only when you really need to be sure your data won’t change.
• Mixing up REPEATABLE READ and SERIALIZABLE is a common mistake. If you see new rows in a repeated query, that’s expected behavior for REPEATABLE READ.
• When working with long transactions, watch out for possible lock conflicts. Long-running transactions can block other operations.

PostgreSQL gives you a bunch of tools to manage transaction isolation. The REPEATABLE READ level is perfect when you need to be sure the data you’ve already read in a transaction won’t change.