What is SELECT FOR UPDATE and why is it needed?

SELECT FOR UPDATE is an SQL construct used to lock selected rows in a database for the duration of a transaction. It ensures that other transactions cannot modify or lock these rows until the current transaction is completed.

This is useful for preventing issues with concurrent access, such as updating account balances or inventory, to avoid race conditions.

Example usage:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- here you can safely update data
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

In Go, using database/sql, this is usually done within a transaction to ensure atomicity of operations.