Setting Isolation Level to REPEATABLE READ in MySQL

Set Isolation Level to Repeatable Read

Write a MySQL query to set the isolation level to REPEATABLE READ and perform a read operation.

Solution:

-- Set the transaction isolation level to REPEATABLE READ
-- This ensures that if the same SELECT query is executed multiple times within the transaction, 
-- it will return the same result set, even if other transactions modify the data in the meantime.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Start a new transaction
START TRANSACTION;

-- Select all employees from DepartmentID = 2
-- Under the REPEATABLE READ isolation level, any rows retrieved by this query 
-- will be locked, preventing other transactions from modifying them until this transaction is completed.
SELECT * FROM Employees WHERE DepartmentID = 2;

-- Commit the transaction to make any changes (if there were any) permanent
-- This releases any locks held on the selected rows.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to ensure that repeated reads within the same transaction return consistent results.
• Key Components:
• SET TRANSACTION ISOLATION LEVEL REPEATABLE READ: Sets the isolation level.
• Why use Repeatable Read?:
• It prevents non-repeatable reads, ensuring consistent results within a transaction.
• Real-World Application:
• For example, in a financial system, you might use REPEATABLE READ to ensure consistent data during calculations.

For more Practice: Solve these Related Problems:

• Write a MySQL query that performs multiple reads within a transaction at the REPEATABLE READ isolation level and ensures consistency.
• Write a MySQL query to demonstrate how REPEATABLE READ prevents non-repeatable reads when multiple transactions are running.
• Write a MySQL query where a transaction reads an inventory stock level twice at the REPEATABLE READ level and verifies that it remains the same.
• Write a MySQL query to compare the effects of REPEATABLE READ and READ COMMITTED on concurrent data reads.

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

Previous MySQL Exercise: Rollback on Constraint Violation.
Next MySQL Exercise: Demonstrate Read Uncommitted Isolation Level.