PostgreSQL - Transactions and Concurrency
In PostgreSQL, under the REPEATABLE READ isolation level, if Transaction X reads a row, then Transaction Y updates and commits that row, what will Transaction X see when it reads the same row again?
In PostgreSQL, under the REPEATABLE READ isolation level, if Transaction X reads a row, then Transaction Y updates and commits that row, what will Transaction X see when it reads the same row again?
medium📝 query result Q4 of 15
Step-by-Step Solution
Solution:
Step 1: Understand REPEATABLE READ behavior
REPEATABLE READ guarantees that all reads within a transaction see a consistent snapshot taken at the start of the transaction.Step 2: Analyze the scenario
Transaction X reads a row, then Transaction Y updates and commits it. Transaction X's subsequent reads see the snapshot from its start, so it sees the original data.Step 3: Eliminate other options
The updated data committed by Transaction Y. is incorrect because updates committed after Transaction X started are not visible. An error indicating a serialization failure. is incorrect because no serialization failure occurs here. NULL, because the row is locked by Transaction Y. is incorrect because the row is not locked for reading.Final Answer:
The original data as of the first read, ignoring Transaction Y's update. -> Option AQuick Check:
REPEATABLE READ shows snapshot at transaction start [OK]
Quick Trick: REPEATABLE READ shows consistent snapshot from start [OK]
Common Mistakes:
- Assuming updated data is visible immediately
- Confusing with READ COMMITTED behavior
- Expecting serialization errors in this case
Master "Transactions and Concurrency" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes