Senior

What is SELECT FOR UPDATE and SELECT FOR SHARE? What is the difference?

sobes.tech AI

Answer from AI

SELECT FOR UPDATE and SELECT FOR SHARE are SQL constructs used to lock rows when fetching data in transactions to ensure consistency and avoid conflicts during concurrent access.

SELECT FOR UPDATE locks the selected rows for subsequent updates. Other transactions cannot modify or lock these rows until the current transaction completes. This is an exclusive lock.
SELECT FOR SHARE (sometimes FOR SHARE MODE) sets a shared lock on the selected rows. Other transactions can read and set their shared locks, but cannot update or delete these rows while the lock is active.

Difference:

FOR UPDATE — lock for modification (exclusive), prevents any changes by other transactions.
FOR SHARE — lock for reading, allows shared reading but prohibits modifications.

Example:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- safe to update the record here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Previous Next