w3resource

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.

Go to:


PREV : Demonstrate Lost Update Problem.
NEXT : Use Transactions to Ensure Atomicity.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.