Maintain Update Consistency with REPEATABLE READ

Preventing Lost Updates with REPEATABLE READ

Write a PostgreSQL query to demonstrate how REPEATABLE READ can help prevent lost updates in a concurrent environment.

Solution:

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

-- Select the current salary for an employee.
SELECT salary FROM Employees WHERE employee_id = 110;

-- Update the salary after performing local calculations.
UPDATE Employees
SET salary = salary + 500
WHERE employee_id = 110;

-- Commit the transaction.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to prevent lost updates by ensuring that the transaction works on a consistent snapshot.
• This demonstrates the use of REPEATABLE READ to avoid update conflicts.
• Key Components:
• The initial SELECT fetches the current salary in a consistent state.
• The subsequent UPDATE uses the retrieved value to adjust the salary.
• Real-World Application:
• Critical in payroll systems where simultaneous updates might otherwise overwrite each other.

Notes:

• REPEATABLE READ helps maintain consistency in scenarios where multiple transactions could update the same row concurrently.
• If a conflict is detected, the transaction will fail and should be retried.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to begin a transaction with REPEATABLE READ isolation, read an employee's current salary, and then update it, ensuring the snapshot remains unchanged.
• Write a PostgreSQL query to update a product's price in the Products table within a REPEATABLE READ transaction and verify that concurrent updates do not interfere.
• Write a PostgreSQL query to simulate a lost update scenario in the Orders table using REPEATABLE READ and ensure that multiple reads yield consistent data before an update.
• Write a PostgreSQL query to execute two consecutive SELECT statements in a REPEATABLE READ transaction on the Employees table, then update a record based on the snapshot, preventing lost updates.

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

Previous PostgreSQL Exercise: Simulating Concurrent Transactions with Different Isolation Levels.
Next PostgreSQL Exercise: Reset Session Isolation Level to Default.