Compare Transaction Snapshots Across Isolation Levels

Simulating Concurrent Transactions with Different Isolation Levels

Write a PostgreSQL query example to illustrate two concurrent transactions—one using READ COMMITTED and another using REPEATABLE READ.

Solution:

-- Transaction A (READ COMMITTED)
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Transaction A: Read current inventory.
SELECT product_id, quantity FROM Inventory WHERE product_id = 200;
-- (Hold open while Transaction B runs.)
COMMIT;

-- Transaction B (REPEATABLE READ)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Transaction B: Read inventory; should see a consistent snapshot.
SELECT product_id, quantity FROM Inventory WHERE product_id = 200;
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to contrast how two transactions with different isolation levels see data.
• Demonstrates that REPEATABLE READ provides a stable snapshot compared to READ COMMITTED.
• Key Components:
• Two separate transactions with different isolation level settings.
• SELECT statements to observe data differences.
• Real-World Application:
• Helps database administrators choose the right isolation level based on application requirements for consistency versus concurrency.

Notes:

• Concurrency behavior may vary depending on timing and data modifications in other transactions.
• This example is for illustration; real-world concurrent tests require simultaneous sessions.

For more Practice: Solve these Related Problems:

• Write two PostgreSQL queries simulating concurrent transactions on the Orders table, one with READ COMMITTED and one with REPEATABLE READ isolation, and compare their result sets.
• Write two PostgreSQL queries simulating concurrent updates on the Accounts table, one using READ COMMITTED isolation and the other using SERIALIZABLE isolation, then analyze the differences in behavior.
• Write a PostgreSQL query to simulate a scenario where a transaction using READ COMMITTED sees newly committed data while a concurrent transaction using REPEATABLE READ does not.
• Write a PostgreSQL query to simulate two concurrent transactions on the Inventory table with different isolation levels and demonstrate how their snapshots differ.

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

Previous PostgreSQL Exercise: Set Transaction Isolation Level to READ COMMITTED.
Next PostgreSQL Exercise: Preventing Lost Updates with REPEATABLE READ.