In what cases in SQL is the command SELECT FOR UPDATE used and for what purposes?

The SELECT FOR UPDATE command is used in SQL to lock the selected rows of a table for the duration of a transaction. This prevents other transactions from modifying these rows simultaneously, ensuring data integrity during concurrent access.

Main use cases:

• When you need to read data and then update it, guaranteeing that no one else will change this data between the read and update.
• In scenarios where the sequence of operations is important and conflicts need to be prevented when working with the same records in parallel.

Example:

BEGIN;
SELECT * FROM accounts WHERE account_id = 123 FOR UPDATE;
-- here you can safely update the selected record
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
COMMIT;

In this example, the selected record is locked until the transaction is completed to avoid race conditions when updating the balance.