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.