Repeatable read behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - Repeatable read behavior

Start Transaction

↓

Read Data (Snapshot)

↓

Perform Operations

↓

Read Data Again

↓

Data Same as First Read?

No→Wait or Error

Yes↓

Commit or Rollback

In repeatable read, a transaction reads a consistent snapshot of data. All reads see the same data, preventing changes from other transactions during its lifetime.

Execution Sample

PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction updates balance
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

This transaction reads the balance twice under repeatable read isolation, ensuring both reads see the same value even if another transaction updates it.

Execution Table

Step	Action	Data Read	Other Transaction Update	Result
1	Begin transaction with repeatable read	N/A	N/A	Transaction snapshot taken
2	First SELECT balance	balance = 1000	No update yet	Reads initial balance 1000
3	Other transaction updates balance to 1200	N/A	Balance updated to 1200	Update not visible to this transaction
4	Second SELECT balance	balance = 1000	Update happened	Reads same balance 1000 as before
5	Commit transaction	N/A	N/A	Transaction ends, changes visible to others

💡 Transaction ends after commit; repeatable read ensures consistent snapshot despite concurrent updates

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
balance_snapshot	N/A	1000	1000	1000
other_tx_balance	N/A	N/A	1200	1200

Key Moments - 2 Insights

Why does the second SELECT still show the old balance even after the other transaction updated it?

Because repeatable read uses a snapshot taken at transaction start (see execution_table step 1 and 4), so it does not see changes made by other transactions after that snapshot.

What happens if the transaction tries to update the same row changed by another transaction?

The transaction will wait or get a serialization error because repeatable read prevents concurrent conflicting updates to maintain consistency.

Visual Quiz - 3 Questions

Look at the execution table, what value does the balance have at step 4?

A null

B 1200

C 1000

D Error

💡 Hint

Check the 'Data Read' column at step 4 in the execution_table

At which step does the other transaction update the balance?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look at the 'Other Transaction Update' column in the execution_table

If the isolation level was READ COMMITTED instead of REPEATABLE READ, what would the second SELECT likely return?

A 1000

B 1200

C null

D Error

💡 Hint

In READ COMMITTED, each SELECT sees the latest committed data, unlike repeatable read snapshot

Concept Snapshot

Repeatable Read Isolation Level in PostgreSQL:
- Transaction sees a consistent snapshot of data at start
- All reads return the same data even if others update
- Prevents non-repeatable reads and phantom reads
- Conflicting writes cause waits or errors
- Use: when consistent repeated reads needed within transaction

Full Transcript

Repeatable read isolation means a transaction works with a fixed snapshot of the database taken at its start. This snapshot ensures that all SELECT queries inside the transaction see the same data, even if other transactions change the data meanwhile. For example, if you read a balance twice, both reads show the same value, preventing surprises from concurrent updates. If another transaction updates the data, your transaction does not see those changes until it ends. This isolation prevents inconsistent reads but may cause waits or errors if you try to update the same data concurrently. It is useful when you want stable, repeatable reads inside a transaction.