I'm reposting this important question (deemed duplicate) because similar previous posts do not concisely answer the question of: is SELECT FOR UPDATE even necessary? For example, what is the difference between
BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;
UPDATE kv SET v = v + 5 WHERE k = 1;
COMMIT;
and
BEGIN;
UPDATE kv SET v = v + 5 WHERE k = 1;
COMMIT;
Doesn't the second case lock the required row before performing the update, anyway? Furthermore, the first case is useless in mid-transactions as there could be substantial time period between SELECT and UPDATE execution, resulting in long lock waits.