Securely Lock and Modify a Record in a Transaction

Transaction Using SELECT FOR UPDATE for Row Locking

Write a PostgreSQL query to lock a row for update within a transaction using SELECT FOR UPDATE.

Solution:

-- Begin the transaction.
BEGIN;

-- Select and lock the employee record for update.
SELECT * FROM Employees
WHERE employee_id = 101
FOR UPDATE;

-- Update the locked employee's salary.
UPDATE Employees
SET salary = salary * 1.10
WHERE employee_id = 101;

-- Commit the transaction to save changes.
COMMIT;

Explanation:

• Purpose of the Query:
• To prevent concurrent modifications by locking a specific row before updating it.
• Demonstrates row-level locking using SELECT FOR UPDATE.
• Key Components:
• SELECT ... FOR UPDATE : Locks the target row.
• UPDATE Employees ... : Performs the update after obtaining the lock.
• BEGIN and COMMIT : Define the transaction scope.
• Real-World Application:
• Useful in high-concurrency environments where data consistency is critical.

Notes:

• Locked rows remain unavailable to other transactions until the current transaction commits.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to lock a specific account record in the Accounts table using SELECT FOR UPDATE before updating its balance.
• Write a PostgreSQL query to lock rows in the Inventory table with SELECT FOR UPDATE before updating stock quantities.
• Write a PostgreSQL query to lock a customer record in the Customers table using SELECT FOR UPDATE before updating the customer's address.
• Write a PostgreSQL query to lock multiple rows in the Orders table with SELECT FOR UPDATE before marking them as processed.

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

Previous PostgreSQL Exercise: Transaction for Bulk Update with Rollback.