Securely Lock a Row to Prevent Concurrent Updates

Lock a Row with SELECT FOR UPDATE

Write a PostgreSQL query to lock a specific row in a table using SELECT FOR UPDATE.

Solution:

-- Lock the row of the employee with ID 101 for update.
SELECT * FROM Employees -- Specify the table to query.
WHERE employee_id = 101
FOR UPDATE; -- Lock the selected row.

Explanation:

• Purpose of the Query:
• The goal is to prevent other transactions from modifying the selected row until the current transaction completes.
• This demonstrates how to use SELECT FOR UPDATE to acquire a row-level lock.
• Key Components:
• SELECT ... FOR UPDATE : Instructs PostgreSQL to lock the retrieved row(s) for the duration of the transaction.
• WHERE employee_id = 101 : Filters the specific row to be locked.
• Real-World Application:
• Useful in financial or inventory systems where updating a record (e.g., an account balance) must be done without concurrent interference.

Notes:

• Ensure the transaction is committed or rolled back to release the lock.
• Row-level locks reduce contention compared to locking the entire table.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to lock a specific product row in the Products table using SELECT FOR UPDATE where product_id = 500.
• Write a PostgreSQL query to lock a customer record in the Customers table for update, filtering by customer_id = 200.
• Write a PostgreSQL query to lock a single order in the Orders table using SELECT FOR UPDATE where order_status = 'pending' and order_id = 123.
• Write a PostgreSQL query to lock an employee record in the Employees table using SELECT FOR UPDATE for employee_id = 75.

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

Previous PostgreSQL Exercise: Locking mechanisms and deadlock prevention Exercises Home.
Next PostgreSQL Exercise: Lock an Entire Table Using LOCK TABLE.