Explain the purpose of the SELECT FOR UPDATE construct in SQL and its use in transaction management.

The SELECT ... FOR UPDATE construct in SQL is used to lock the selected rows of a table for the duration of the current transaction. This prevents other transactions from modifying these rows simultaneously, ensuring data consistency.

For example, in implementing a bank transfer:

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 123 FOR UPDATE;
-- calculations and balance updates
UPDATE accounts SET balance = new_balance WHERE account_id = 123;
COMMIT;

Here, FOR UPDATE locks the row with account_id = 123, so that other transactions cannot modify it until the current transaction is completed.

Thus, SELECT FOR UPDATE helps manage concurrent access and prevents race conditions during parallel database operations.