What is SELECT FOR UPDATE?

SELECT FOR UPDATE is an SQL construct used to lock selected rows in a database to prevent them from being modified by other transactions until the current one is completed.

When you execute a query:

SELECT * FROM table_name WHERE condition FOR UPDATE;

• Rows that satisfy the condition are locked for writing.
• Other transactions cannot modify or lock these rows until the current transaction ends (commit or rollback).

This is used to implement optimistic or pessimistic locking mechanisms to avoid conflicts during concurrent data access.

Example of usage:

BEGIN;
SELECT * FROM accounts WHERE account_id = 123 FOR UPDATE;
-- perform operations on the selected record
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
COMMIT;

Thus, SELECT FOR UPDATE guarantees that the data the transaction works with will not be changed by others until it is finished.