Understanding Dirty Reads in MySQL Transactions

Demonstrate Dirty Read

Write a MySQL query to demonstrate a dirty read by reading uncommitted data from another transaction.

Solution:

-- Transaction 1: Start a transaction to update employee salaries
START TRANSACTION;

-- Increase the salary of all employees in Department 2 by 10%
-- The update is not yet committed, meaning these changes are uncommitted and may be seen by other transactions if isolation levels allow.
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Transaction 2: Set the isolation level to READ UNCOMMITTED
-- This allows reading uncommitted data (dirty reads) from other transactions.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Start a new transaction for reading employee data
START TRANSACTION;

-- Select all employees from Department 2
-- Because of the READ UNCOMMITTED isolation level, this query might retrieve the salary changes
-- made by Transaction 1, even though they have not been committed yet.
SELECT * FROM Employees WHERE DepartmentID = 2;

-- Commit Transaction 2: The SELECT query completes, but since no data was modified, this has no real effect.
COMMIT;

-- Transaction 1: Roll back the salary update
-- Since Transaction 1 is being rolled back, the salary increase is undone.
-- However, Transaction 2 may have already read the uncommitted (now discarded) salary changes, leading to a dirty read issue.
ROLLBACK;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how dirty reads occur when reading uncommitted data.
• Key Components:
• SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED: Allows dirty reads.
• SELECT * FROM Employees: Reads uncommitted data.
• Why avoid Dirty Reads?:
• Dirty reads can lead to inconsistent data if the uncommitted changes are rolled back.
• Real-World Application:
• For example, in a reporting system, you might avoid dirty reads to ensure data accuracy.

For more Practice: Solve these Related Problems:

• Write a MySQL query to show the difference between dirty reads and committed reads.
• Write a MySQL query to read uncommitted data from another transaction and analyze the inconsistency.
• Write a MySQL query to simulate a scenario where dirty reads lead to incorrect reporting.
• Write a MySQL query to demonstrate how dirty reads can be prevented using a higher isolation level.

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

Previous MySQL Exercise: Use Savepoints in a Transaction.
Next MySQL Exercise: Demonstrate Non-Repeatable Read.