How Phantom Reads Affect MySQL Transactions

Demonstrate Phantom Read

Write a MySQL query to demonstrate a phantom read by reading new rows inserted by another transaction.

Solution:

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

-- Read all employees from Department 2
-- This query retrieves all employees from Department 2, including their names and salaries.
SELECT * FROM Employees WHERE DepartmentID = 2;

-- Transaction 2: Start another transaction to insert a new employee into Department 2
START TRANSACTION;

-- Insert a new employee into Department 2
-- Kamilla Njord is added with a salary of 50,000. This change is uncommitted and is pending until the COMMIT statement.
INSERT INTO Employees (Name, DepartmentID, Salary) 
VALUES ('Kamilla Njord', 2, 50000);

-- Commit Transaction 2: The new employee is now permanently added to the database
-- After committing, the new employee becomes part of the dataset and will be visible to other transactions.
COMMIT;

-- Transaction 1: Read employee data again from Department 2
-- This query will now see the new employee added by Transaction 2, depending on the isolation level.
SELECT * FROM Employees WHERE DepartmentID = 2;

-- Commit Transaction 1: End the transaction and make any changes permanent
COMMIT; 

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how phantom reads occur when new rows are inserted during a transaction.
• Key Components:
• SELECT * FROM Employees: Reads data before and after an insert.
• Why avoid Phantom Reads?:
• Phantom reads can lead to inconsistent results when new rows are added during a transaction.
• Real-World Application:
• For example, in an inventory system, you might use SERIALIZABLE to avoid phantom reads.

For more Practice: Solve these Related Problems:

• Write a MySQL query to insert new rows in one transaction and observe changes in another transaction.
• Write a MySQL query to prevent phantom reads using SERIALIZABLE isolation.
• Write a MySQL query to analyze the impact of phantom reads on aggregated queries.
• Write a MySQL query to compare the effect of different isolation levels on phantom reads.

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

Previous MySQL Exercise: Demonstrate Non-Repeatable Read.
Next MySQL Exercise: Use Explicit Locking with FOR UPDATE.