Repeatable read behavior in PostgreSQL

Introduction

Repeatable read helps keep your data consistent during a transaction by making sure you see the same data each time you look, even if others change it.

When you want to run multiple queries in a transaction and need the data to stay the same throughout.

When you want to avoid seeing changes made by others while you work on your data.

When you want to prevent some types of data conflicts without locking the whole table.

When you want to ensure reports or calculations use stable data during their run.

Syntax

PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- your queries here
COMMIT;

Use BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; to start a transaction with repeatable read isolation.

All queries inside this transaction will see the same snapshot of data.

Examples

This transaction reads the same data twice and will see no changes between the two SELECTs.

PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE user_id = 1;
SELECT * FROM accounts WHERE user_id = 1;
COMMIT;

Updates and reads happen in the same transaction, so the SELECT sees the updated balance.

PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;
SELECT balance FROM accounts WHERE user_id = 1;
COMMIT;

Sample Program

This example shows that inside the repeatable read transaction, both SELECT queries see the same balance value, even if another session changes it between the queries.

PostgreSQL

CREATE TABLE accounts (user_id INT PRIMARY KEY, balance INT);
INSERT INTO accounts VALUES (1, 1000);

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE user_id = 1;
-- Imagine another session updates balance to 2000 here
SELECT balance FROM accounts WHERE user_id = 1;
COMMIT;

Important Notes

Repeatable read prevents non-repeatable reads but does not prevent phantom reads (new rows appearing).

It uses a snapshot of the database taken at the start of the transaction.

Other transactions can still change data, but you won't see those changes inside your transaction.

Summary

Repeatable read keeps your data consistent during a transaction by showing the same snapshot.

It helps avoid seeing changes made by others while you work.

Use it when you need stable data for multiple queries in one transaction.