Preventing Lost Updates with FOR UPDATE in MySQL

Prevent Lost Updates with FOR UPDATE

Write a MySQL query to prevent the lost update problem using FOR UPDATE.

Solution:

-- Transaction 1
START TRANSACTION;
SELECT Salary FROM Employees WHERE EmployeeID = 1 FOR UPDATE;
-- Assume Salary = 50000
UPDATE Employees SET Salary = 50000 + 1000 WHERE EmployeeID = 1;
COMMIT;

-- Transaction 2 (waits for Transaction 1 to complete)
START TRANSACTION;
SELECT Salary FROM Employees WHERE EmployeeID = 1 FOR UPDATE;
-- Now Salary = 51000
UPDATE Employees SET Salary = 51000 + 2000 WHERE EmployeeID = 1;
COMMIT;

Explanation:

• Purpose of the Query:
• The goal is to prevent lost updates by locking the row for update.
• Key Components:
• SELECT ... FOR UPDATE: Locks the row to prevent concurrent updates.
• Why use FOR UPDATE?:
• It ensures that only one transaction can update the row at a time, preventing lost updates.
• Real-World Application:
• For example, in an inventory system, you might use FOR UPDATE to prevent overselling products.

For more Practice: Solve these Related Problems:

• Write a MySQL query to update an inventory stock level while preventing lost updates using FOR UPDATE.
• Write a MySQL query where two concurrent transactions attempt to update an employee's work hours, ensuring correctness using FOR UPDATE.
• Write a MySQL query that prevents a lost update in a ride-sharing system where multiple drivers attempt to accept the same ride request.
• Write a MySQL query that prevents lost updates when multiple users modify the same order status in an e-commerce system.

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

Previous MySQL Exercise: Demonstrate Lost Update Problem.
Next MySQL Exercise: Use Transactions to Ensure Atomicity.