Locking Rows in MySQL Transactions with FOR UPDATE

Use Explicit Locking with FOR UPDATE

Write a MySQL query to use FOR UPDATE to lock rows for update within a transaction.

Solution:

-- Start a new transaction to modify employee data
START TRANSACTION;

-- Select all employees from Department 2, and lock the rows for update
-- The "FOR UPDATE" clause places a **shared lock** on the rows returned by the query.
-- Other transactions will be unable to modify or update these rows until this transaction is committed or rolled back.
SELECT * FROM Employees WHERE DepartmentID = 2 FOR UPDATE;

-- Update salaries for all employees in Department 2
-- The salary of employees in this department is increased by 10%. 
-- Because the rows are locked by the "FOR UPDATE" clause, no other transaction can update them until this transaction is complete.
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Commit the transaction to make all changes permanent
-- After the COMMIT, the updated salaries are saved to the database, and the locks are released.
COMMIT; 

Explanation:

• Purpose of the Query:
• The goal is to lock rows for update to prevent other transactions from modifying them.
• Key Components:
• SELECT ... FOR UPDATE: Locks the selected rows.
• Why use FOR UPDATE?:
• FOR UPDATE ensures that no other transaction can modify the locked rows until the current transaction completes.
• Real-World Application:
• For example, in a booking system, you might use FOR UPDATE to lock seats during a reservation.

For more Practice: Solve these Related Problems:

• Write a MySQL query to lock multiple rows using FOR UPDATE and update them within a transaction.
• Write a MySQL query to demonstrate the impact of FOR UPDATE on concurrent transactions.
• Write a MySQL query to use explicit row locking with NOWAIT to prevent long waits in a transaction.
• Write a MySQL query to compare the performance of row locking with table-level locks.

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

Previous MySQL Exercise: Demonstrate Phantom Read.
Next MySQL Exercise: Use Explicit Locking with LOCK IN SHARE MODE.