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

SELECT FOR UPDATE and SELECT FOR SHARE are SQL constructs used for row locking during selection, but with different purposes and levels of locking.

• SELECT FOR UPDATE locks the selected rows for subsequent updates. This means that other transactions cannot modify or lock these rows until the current transaction is completed. It is used when data is planned to be changed.

• SELECT FOR SHARE locks the rows for reading, allowing other transactions to also read these rows but preventing their modification. This is a softer lock, intended to prevent data changes during reading.

Example:

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

Thus, FOR UPDATE is used for exclusive locking for modification, while FOR SHARE is for shared access with a prohibition on modification.