Ensure Data Consistency with REPEATABLE READ Snapshot

Consistent Snapshot in REPEATABLE READ Mode

Write a PostgreSQL query to demonstrate that a transaction in REPEATABLE READ mode returns a consistent snapshot of data.

Solution:

-- Begin a transaction with REPEATABLE READ isolation.
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- First query to get a snapshot of product prices.
SELECT product_id, price FROM Products;

-- (Assume concurrent updates occur here in another transaction.)

-- Second query; results remain consistent with the snapshot.
SELECT product_id, price FROM Products;

-- Commit the transaction.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to illustrate that all queries within a REPEATABLE READ transaction see the same data snapshot.
• This demonstrates how data remains consistent throughout the transaction.
• Key Components:
• Two SELECT statements executed under the same isolation level.
• SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; ensures a consistent snapshot.
• Real-World Application:
• Ideal for reporting or analytical queries where data consistency is vital across multiple reads.

Notes:

• REPEATABLE READ prevents non-repeatable reads but may still allow phantom reads in some cases.
• The snapshot remains fixed even if concurrent transactions commit changes.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to begin a transaction with REPEATABLE READ isolation, execute two identical SELECT queries on the Products table, and verify that the results remain unchanged.
• Write a PostgreSQL query to start a transaction with REPEATABLE READ isolation and query the Sales table twice to confirm data consistency.
• Write a PostgreSQL query to begin a transaction with REPEATABLE READ isolation and compare the output of two sequential SELECT queries on the Inventory table.
• Write a PostgreSQL query to start a transaction with REPEATABLE READ isolation and repeatedly select records from the Orders table, ensuring that the snapshot remains fixed despite external changes.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Read Latest Committed Data in READ COMMITTED Mode.
Next PostgreSQL Exercise: Handling Serialization Errors in SERIALIZABLE Mode