[Solved] Consider this sequence in PostgreSQL:BEGIN;SELECT * FROM products WHERE id = 1;UPDATE products SET price = 20 WHERE id = 1;COMMIT;What does the SELECT see if another transaction updated the same row... | PostgreSQL

PostgreSQL - Transactions and Concurrency

Consider this sequence in PostgreSQL:

BEGIN;
SELECT * FROM products WHERE id = 1;
UPDATE products SET price = 20 WHERE id = 1;
COMMIT;

What does the SELECT see if another transaction updated the same row before this transaction started?

AAn error due to concurrent update

BThe new price updated by the other transaction

CNo rows returned because of the update

DThe old price before the other transaction's update

Step-by-Step Solution

Solution:

Step 1: Understand snapshot isolation in MVCC
The SELECT sees data as it was at transaction start, ignoring later committed changes.
Step 2: Apply to given scenario
Since another transaction updated before this one started, the snapshot at start excludes that committed update, so SELECT sees the old price.
Final Answer:
The old price before the other transaction's update -> Option D
Quick Check:
MVCC snapshot = data at tx start [OK]

Quick Trick: SELECT sees snapshot at transaction start, not later changes [OK]

Common Mistakes:

Master "Transactions and Concurrency" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Consider this sequence in PostgreSQL: