Using LOCK IN SHARE MODE to Lock Rows for Read-Only Access

Use Explicit Locking with LOCK IN SHARE MODE

Write a MySQL query to use LOCK IN SHARE MODE to lock rows for reading within a transaction.

Solution:

-- Start a new transaction to read employee data from Department 2
START TRANSACTION;

-- Select all employees from Department 2 and place a **shared lock** on the rows
-- The "LOCK IN SHARE MODE" clause allows other transactions to **read the data** but **prevents any updates** or modifications to the rows.
-- Other transactions can query the same rows but cannot change them until this transaction commits or rolls back.
SELECT * FROM Employees WHERE DepartmentID = 2 LOCK IN SHARE MODE;

-- Commit the transaction to make all changes permanent
-- After the COMMIT, the lock is released, and other transactions can modify the locked rows.
COMMIT; 

Explanation:

• Purpose of the Query:
• The goal is to lock rows for reading to prevent other transactions from modifying them.
• Key Components:
• SELECT ... LOCK IN SHARE MODE: Locks the selected rows for shared access.
• Why use LOCK IN SHARE MODE?:
• It allows multiple transactions to read the same rows but prevents modifications until the lock is released.
• Real-World Application:
• For example, in a reporting system, you might use LOCK IN SHARE MODE to ensure data consistency during analysis.

For more Practice: Solve these Related Problems:

• Write a MySQL query to lock a set of customer orders using LOCK IN SHARE MODE and demonstrate how other transactions can read but not modify them.
• Write a MySQL query where multiple transactions read product details using LOCK IN SHARE MODE while preventing price modifications during an audit.
• Write a MySQL query that demonstrates the use of LOCK IN SHARE MODE in a bank transaction system to ensure account balance consistency.
• Write a MySQL query to apply LOCK IN SHARE MODE on an inventory system, ensuring stock levels remain unchanged during data analysis.

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

Previous MySQL Exercise: Use Explicit Locking with FOR UPDATE.
Next MySQL Exercise: Demonstrate Lost Update Problem.