Row-level locking (FOR UPDATE, FOR SHARE) in PostgreSQL

Introduction

Row-level locking helps prevent conflicts when multiple people try to change the same data at the same time. It locks specific rows so others can't change them until you're done.

When you want to update a row but ensure no one else changes it until your update finishes.

When you want to read data and make sure it doesn't change while you are working with it.

When you are handling money transfers and need to lock account rows to avoid errors.

When multiple users might edit the same record and you want to avoid overwriting each other's changes.

Syntax

PostgreSQL

SELECT columns FROM table WHERE condition FOR UPDATE;

SELECT columns FROM table WHERE condition FOR SHARE;

FOR UPDATE locks the selected rows for writing (updates, deletes).

FOR SHARE locks the selected rows for reading, allowing others to read but not update them.

Examples

This locks the account with id 1 so no other transaction can update or delete it until this transaction finishes.

PostgreSQL

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

This locks the rows with pending orders for reading, so others can also read but cannot update or delete them until the lock is released.

PostgreSQL

SELECT * FROM orders WHERE status = 'pending' FOR SHARE;

Starts a transaction, locks product with id 10 for update, then commits changes releasing the lock.

PostgreSQL

BEGIN;
SELECT * FROM products WHERE id = 10 FOR UPDATE;
-- do some updates
COMMIT;

Sample Program

This example locks the employee row with id 5 so no other transaction can change it while we update the salary. After the update, the lock is released with COMMIT.

PostgreSQL

BEGIN;
SELECT * FROM employees WHERE id = 5 FOR UPDATE;
-- Imagine we update the employee's salary here
UPDATE employees SET salary = salary + 1000 WHERE id = 5;
COMMIT;

Important Notes

Row-level locks only last during the transaction. Always use BEGIN and COMMIT to control locking.

FOR UPDATE blocks other transactions trying to update or delete the locked rows.

FOR SHARE allows others to read but prevents updates or deletes until the lock is released.

Summary

Row-level locking controls access to specific rows during transactions.

Use FOR UPDATE to lock rows for writing.

Use FOR SHARE to lock rows for reading.