Demonstrating the Lost Update Problem in MySQL

Demonstrate Lost Update Problem

Write a MySQL query to demonstrate the lost update problem when two transactions update the same row concurrently.

Solution:

-- Transaction 1: Start a transaction to read and update the salary of Employee 1
START TRANSACTION;

-- Select the current salary of Employee 1
-- The query retrieves the current salary, which is assumed to be 50,000 at this point in the transaction.
SELECT Salary FROM Employees WHERE EmployeeID = 1;
-- Assume Salary = 50000

-- Update Employee 1's salary by adding 1000
-- The salary is updated from 50,000 to 51,000, and this change will be committed in the transaction.
UPDATE Employees SET Salary = 50000 + 1000 WHERE EmployeeID = 1;

-- Commit Transaction 1: Make the update permanent and release any locks
COMMIT;

-- Transaction 2: Start another transaction to read and update the salary of Employee 1 concurrently
START TRANSACTION;

-- Select the current salary of Employee 1
-- Transaction 2 reads the current salary of Employee 1, which is still assumed to be 50,000.
-- This occurs concurrently while Transaction 1 is executing.
SELECT Salary FROM Employees WHERE EmployeeID = 1;
-- Assume Salary = 50000

-- Update Employee 1's salary by adding 2000
-- The salary is updated from 50,000 to 52,000. Transaction 2 will commit the change in this transaction.
UPDATE Employees SET Salary = 50000 + 2000 WHERE EmployeeID = 1;

-- Commit Transaction 2: Make the update permanent and release any locks
COMMIT; 

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how the lost update problem occurs when two transactions overwrite each other's changes
• Key Components:
• Both transactions read the same initial value and update it independently.
• Why avoid Lost Updates?:
• Lost updates can lead to incorrect data when concurrent transactions overwrite changes..
• Real-World Application:
• For example, in a banking system, you might use row-level locking to prevent lost updates during balance adjustments.

For more Practice: Solve these Related Problems:

• Write a MySQL query where two transactions attempt to update the same stock quantity for a product simultaneously, causing a lost update.
• Write a MySQL query that demonstrates the lost update problem in a ticket booking system where two users try to book the same seat at the same time.
• Write a MySQL query to show how a lost update problem can occur in a banking system where two transactions attempt to withdraw money from the same account simultaneously.
• Write a MySQL query to simulate a lost update issue in an employee salary update system where two HR representatives modify the same salary data at the same time.

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

Previous MySQL Exercise: Use Explicit Locking with LOCK IN SHARE MODE.
Next MySQL Exercise: Prevent Lost Updates with FOR UPDATE.