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.

Practice

1.

What does the REPEATABLE READ isolation level guarantee in PostgreSQL?

easy

A. It ensures all queries in a transaction see the same data snapshot.

B. It allows reading uncommitted changes from other transactions.

C. It locks all rows in the database for the transaction duration.

D. It automatically commits after each query in the transaction.

Solution

1. Step 1: Understand Repeatable Read isolation

   Repeatable Read ensures that all queries in a transaction see the same snapshot of data, preventing changes made by others from appearing during the transaction.

2. Step 2: Compare options with definition

   It ensures all queries in a transaction see the same data snapshot. matches this definition exactly. Options B, C, and D describe behaviors not related to Repeatable Read.

3. Final Answer:

   It ensures all queries in a transaction see the same data snapshot. -> Option A

4. Quick Check:

   Repeatable Read = Same snapshot [OK]

Hint: Repeatable Read = stable snapshot during transaction [OK]

Common Mistakes:

• Confusing Repeatable Read with Read Uncommitted
• Thinking it locks all rows
• Assuming auto-commit after each query

2.

Which of the following is the correct way to start a transaction with REPEATABLE READ isolation level in PostgreSQL?

BEGIN;
-- your queries
COMMIT;

easy

A. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN;

B. BEGIN ISOLATION LEVEL REPEATABLE READ;

C. BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

D. BEGIN; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Solution

1. Step 1: Recall correct syntax for setting isolation level

   In PostgreSQL, you start the transaction with BEGIN, then set the isolation level for that transaction using SET TRANSACTION ISOLATION LEVEL.

2. Step 2: Match options to syntax

   BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; correctly shows BEGIN; then SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; sets isolation before BEGIN which is invalid. BEGIN ISOLATION LEVEL REPEATABLE READ; uses invalid syntax. BEGIN; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; sets session level, not transaction level.

3. Final Answer:

   BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -> Option C

4. Quick Check:

   Set isolation after BEGIN = BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; [OK]

Hint: Set isolation level after BEGIN with SET TRANSACTION [OK]

Common Mistakes:

• Trying to set isolation before BEGIN
• Using BEGIN with isolation level directly
• Confusing session and transaction level commands

3.

Consider this sequence in PostgreSQL with REPEATABLE READ isolation:

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- returns 100

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Back to Transaction 1
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

What will be the result of the second SELECT in Transaction 1?

medium

A. Error due to concurrent update

B. 100

C. NULL

D. 200

Solution

1. Step 1: Understand snapshot behavior in Repeatable Read

   Transaction 1 sees a consistent snapshot from its start. Changes committed by Transaction 2 after Transaction 1 began are not visible.

2. Step 2: Apply to the SELECT query

   The first SELECT returned 100. The second SELECT in the same transaction will also return 100, ignoring the update committed by Transaction 2.

3. Final Answer:

   100 -> Option B

4. Quick Check:

   Repeatable Read = same snapshot = 100 [OK]

Hint: Repeatable Read ignores later commits in same transaction [OK]

Common Mistakes:

• Expecting updated value 200 inside same transaction
• Thinking it causes error on concurrent update
• Assuming NULL if data changed

4.

Given this transaction in PostgreSQL:

BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE products SET stock = stock - 1 WHERE id = 10;
SELECT stock FROM products WHERE id = 10;
COMMIT;

But you get an error: ERROR: syntax error at or near "ISOLATION". What is the fix?

medium

A. Change to BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

B. Remove the isolation level, just use BEGIN;

C. Use START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

D. Use SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; before BEGIN

Solution

1. Step 1: Identify syntax error cause

   PostgreSQL does not support specifying isolation level directly in BEGIN statement.

2. Step 2: Correct syntax to set isolation level

   You must first BEGIN; then run SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; before queries.

3. Final Answer:

   Change to BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -> Option A

4. Quick Check:

   Set isolation after BEGIN, not inside [OK]

Hint: Set isolation after BEGIN with SET TRANSACTION [OK]

Common Mistakes:

• Trying to put isolation level inside BEGIN
• Using unsupported START TRANSACTION syntax
• Setting session level instead of transaction level

5.

You want to run multiple SELECT queries in a transaction and ensure the data does not change during the transaction, but you also want to allow other transactions to update data concurrently without blocking. Which isolation level should you choose in PostgreSQL?

hard

A. READ COMMITTED

B. READ UNCOMMITTED

C. SERIALIZABLE

D. REPEATABLE READ

Solution

1. Step 1: Understand isolation levels and concurrency

   READ COMMITTED allows seeing changes committed during the transaction, so data can change between queries. SERIALIZABLE is strict and may block or abort concurrent updates. READ UNCOMMITTED is not supported in PostgreSQL.

2. Step 2: Match requirement to isolation level

   REPEATABLE READ provides a stable snapshot for all queries in the transaction, preventing data changes from appearing, while allowing concurrent updates without blocking reads.

3. Final Answer:

   REPEATABLE READ -> Option D

4. Quick Check:

   Stable snapshot + concurrency = REPEATABLE READ [OK]

Hint: Repeatable Read = stable snapshot without blocking writes [OK]

Common Mistakes:

• Choosing SERIALIZABLE which blocks more
• Thinking READ COMMITTED prevents data changes
• Assuming READ UNCOMMITTED exists in PostgreSQL