How to Use SELECT FOR UPDATE in PostgreSQL: Syntax and Examples

In PostgreSQL, use SELECT ... FOR UPDATE to lock selected rows so other transactions cannot modify them until your transaction ends. This is useful to prevent race conditions when updating data concurrently.

Syntax

The basic syntax of SELECT FOR UPDATE is:

• SELECT columns FROM table: Choose the rows you want.
• FOR UPDATE: Locks the selected rows for the current transaction.
• Optionally, you can add WHERE to filter rows.

This lock prevents other transactions from updating or deleting these rows until your transaction commits or rolls back.

sql

SELECT column1, column2 FROM table_name WHERE condition FOR UPDATE;

Example

This example shows how to lock a user's row before updating their balance to avoid conflicts with other transactions.

sql

BEGIN;

SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;

-- Now safely update the balance
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

COMMIT;

Output

 balance 
---------
  500
(1 row)

-- After update, balance is increased by 100

Common Pitfalls

Common mistakes when using SELECT FOR UPDATE include:

• Not wrapping the query in a transaction block (BEGIN ... COMMIT), so locks are released immediately.
• Locking too many rows unintentionally, causing performance issues.
• Forgetting that FOR UPDATE locks rows only for updates and deletes, not selects.

Always use FOR UPDATE inside a transaction and filter rows carefully.

sql

/* Wrong: No transaction, lock released immediately */
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;

/* Right: Use transaction to hold lock */
BEGIN;
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;
-- do updates
COMMIT;

Quick Reference

Clause	Description
SELECT ... FOR UPDATE	Locks selected rows for update or delete by current transaction
WHERE condition	Filters rows to lock only specific ones
BEGIN ... COMMIT	Transaction block to hold locks until commit or rollback
FOR NO KEY UPDATE	Locks rows but allows some concurrent updates
FOR SHARE	Locks rows for shared access, allowing reads but blocking writes

Key Takeaways

Use SELECT ... FOR UPDATE inside a transaction to lock rows for safe updates.

Locks prevent other transactions from modifying locked rows until commit or rollback.

Always filter rows carefully to avoid locking too many rows and hurting performance.

FOR UPDATE locks rows for update/delete but does not block reads.

Wrap your locking queries in BEGIN and COMMIT to control lock duration.