Repeatable read behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Repeatable Read: Consistent Snapshot Query

Consider two concurrent transactions in PostgreSQL using REPEATABLE READ isolation level. Transaction A reads a row, then Transaction B updates that row and commits. Transaction A reads the same row again.

What will Transaction A see on the second read?

PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- returns 100
-- Concurrently, another transaction updates balance to 200 and commits
SELECT balance FROM accounts WHERE id = 1; -- What is the output here?

A 100 (the original value, snapshot is consistent)

B 200 (the updated value from the other transaction)

C NULL (the row is locked and not visible)

D Error: serialization failure

💡 Hint

Think about how REPEATABLE READ isolation keeps a consistent snapshot for the duration of the transaction.

In REPEATABLE READ, the transaction sees a consistent snapshot of the database as of the start of the transaction. Even if another transaction commits changes, the first transaction will not see those changes until it ends and starts a new transaction.

🧠 Conceptual

intermediate

Understanding Phantom Reads in Repeatable Read

Which of the following statements about phantom reads under REPEATABLE READ isolation level in PostgreSQL is true?

A Phantom reads can occur because new rows inserted by other transactions are visible.

B Phantom reads cause immediate transaction abort in REPEATABLE READ.

C Phantom reads are prevented by locking all rows in the table at the start.

D Phantom reads cannot occur because the transaction sees a consistent snapshot including all rows at start.

💡 Hint

Consider how PostgreSQL's MVCC handles visibility of new rows in REPEATABLE READ.

PostgreSQL's REPEATABLE READ uses MVCC to provide a snapshot that includes all rows visible at the start of the transaction, so phantom reads do not occur. New rows inserted after the transaction starts are not visible.

📝 Syntax

advanced

Setting Repeatable Read Isolation Level Syntax

Which of the following SQL commands correctly sets the transaction isolation level to REPEATABLE READ in PostgreSQL for the current transaction?

A BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

B START TRANSACTION WITH ISOLATION LEVEL REPEATABLE READ;

C SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

D SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

💡 Hint

Focus on the command that sets isolation level for the current transaction before it starts.

In PostgreSQL, BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; (or START TRANSACTION ISOLATION LEVEL REPEATABLE READ;) sets the isolation level when starting the transaction. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; must follow an explicit BEGIN;.

❓ optimization

advanced

Avoiding Serialization Failures in Repeatable Read

In PostgreSQL, under REPEATABLE READ isolation, serialization failures can occur due to concurrent updates. Which approach best reduces serialization failures while keeping repeatable read guarantees?

A Switch to READ COMMITTED isolation level to avoid serialization failures.

B Use explicit row-level locking (SELECT ... FOR UPDATE) to serialize access.

C Use advisory locks to coordinate transactions externally.

D Increase max_connections to reduce contention.

💡 Hint

Think about how locking can prevent concurrent conflicting updates.

Explicit row-level locking with SELECT ... FOR UPDATE ensures that concurrent transactions serialize access to rows, reducing serialization failures while maintaining repeatable read guarantees.

🔧 Debug

expert

Diagnosing Serialization Failure in Repeatable Read

Two concurrent transactions under REPEATABLE READ isolation update the same row and commit. One transaction gets a serialization failure error. What is the most likely cause?

A The row was locked by a long-running SELECT query without FOR UPDATE.

B The transactions used different isolation levels causing inconsistent visibility.

C Both transactions tried to update the same row concurrently, causing a conflict detected by PostgreSQL's serialization mechanism.

D The database ran out of disk space during the commit.

💡 Hint

Consider how PostgreSQL detects conflicts under REPEATABLE READ.

PostgreSQL detects serialization conflicts when concurrent transactions update the same data under REPEATABLE READ. One transaction must abort with a serialization failure to maintain consistency.

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