When to use SELECT FOR UPDATE and when to use UPDATE ... RETURNING? Provide business examples.

SELECT FOR UPDATE is used when you need to lock selected rows in the database to prevent them from being modified by other transactions until the current one is completed. This is useful if you plan to update the data after selecting it and want to avoid race conditions.

UPDATE ... RETURNING simultaneously updates rows and returns the modified data, which is convenient when you need to get the result of the update without an additional query.

Business examples:

• SELECT FOR UPDATE: In a banking system, when transferring money, the sender's and receiver's account records are first selected and locked to prevent simultaneous balance changes.

• UPDATE ... RETURNING: In a ticket booking system, the seat status is updated to "reserved" and the reservation information is immediately returned to show the user a confirmation.

Example with SELECT FOR UPDATE:

BEGIN;
SELECT balance FROM accounts WHERE id = 123 FOR UPDATE;
-- calculations
UPDATE accounts SET balance = balance - 100 WHERE id = 123;
COMMIT;

Example with UPDATE ... RETURNING:

UPDATE tickets SET status = 'reserved' WHERE id = 456 RETURNING id, status, reserved_at;