How Non-Repeatable Reads Happen in MySQL

Demonstrate Non-Repeatable Read

Write a MySQL query to demonstrate a non-repeatable read by reading different data in the same transaction.

Solution:

-- Transaction 1: Start a transaction to read employee data from Department 2
START TRANSACTION;

-- Read all employees from Department 2
-- This retrieves the current salary values before any updates are made by another transaction.
SELECT * FROM Employees WHERE DepartmentID = 2;

-- Transaction 2: Start another transaction to update employee salaries
START TRANSACTION;

-- Increase salaries by 10% for all employees in Department 2
-- This update is not yet committed, so it remains uncommitted (pending) until the COMMIT statement.
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Commit Transaction 2: The salary update is now permanent
-- Other transactions will now see the updated salaries.
COMMIT;

-- Transaction 1: Read employee data again from Department 2
-- If the isolation level is READ COMMITTED (default in many databases), this query will now see the updated salaries.
-- If the isolation level is REPEATABLE READ or SERIALIZABLE, this query will still return the old (pre-update) values.
SELECT * FROM Employees WHERE DepartmentID = 2;

-- Commit Transaction 1: Complete the transaction
-- Any changes made within this transaction (if any) are now permanent.
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how non-repeatable reads occur when data changes during a transaction.
• Key Components:
• SELECT * FROM Employees: Reads data twice within the same transaction.
• Why avoid Non-Repeatable Reads?:
• Non-repeatable reads can lead to inconsistent results within the same transaction.
• Real-World Application:
• For example, in a financial system, you might use a higher isolation level to avoid non-repeatable reads.

For more Practice: Solve these Related Problems:

• Write a MySQL query to execute a SELECT statement twice within a transaction and observe changes.
• Write a MySQL query to prevent non-repeatable reads using REPEATABLE READ isolation.
• Write a MySQL query to compare non-repeatable reads with phantom reads using concurrent transactions.
• Write a MySQL query to implement a locking mechanism that prevents non-repeatable reads.

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

Previous MySQL Exercise: Demonstrate Dirty Read.
Next MySQL Exercise: Demonstrate Phantom Read.