Tell me about SELECT FOR UPDATE — what is it used for and how does it work?

SELECT FOR UPDATE is an SQL construct used to lock selected rows of a table for the duration of a transaction.

Purpose:

• To prevent race conditions during concurrent data access.
• To ensure that other transactions cannot modify or delete these rows until the current transaction is completed.

How it works:

• When executing SELECT ... FOR UPDATE, the DBMS locks the selected rows for writing.
• Other transactions attempting to modify these rows will wait for the lock to be released.
• The lock is released upon commit or rollback of the transaction.

Example:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- here you can safely update data, knowing no one else will change this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

This is important for ensuring data integrity in concurrent scenarios, such as implementing bank transfers or reservations.