0

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.

Morey
  • 549
  • 7
  • 10
  • 2
    The difference is that you can get the values from before the update in the first version. – Barmar Jun 08 '23 at 20:20
  • 2
    If you don't need to do that, there's no need for the `SELECT`. – Barmar Jun 08 '23 at 20:21
  • 1
    You might use `SELECT FOR UPDATE` if you want to show the users the rows, ask them if it's OK to add 5, then do the update. – Barmar Jun 08 '23 at 20:23
  • Thank you for your response – Morey Jun 08 '23 at 20:42
  • 2
    @Barmar no, user interaction should never happen mid-transaction. but you might want to lock them, read some other data, and update based on that data – ysth Jun 08 '23 at 21:04
  • 2
    @ysth Yeah, I was trying to give a more concrete example, even though it's not what you would actually do. – Barmar Jun 08 '23 at 21:18
  • 1
    The application might want to display a before/after difference. So it does the SELECT FOR UPDATE, saves the results, does the update, SELECTs the new data. – Barmar Jun 08 '23 at 21:19

0 Answers0