Concurrent update in PostgreSQL 11

Question

I have about 10 queries that concurrently update a row, so I want to know what is the difference between

UPDATE account SET balance = balance + 1000
WHERE id = (SELECT id FROM account
            where id = 1 FOR UPDATE);

and

BEGIN;

SELECT balance FROM account WHERE id = 1 FOR UPDATE;

-- compute $newval = $balance + 1000

UPDATE account SET balance = $newval WHERE id = 1;

COMMIT;

I am using PosgreSQL 11, so what is the right solution and what will happen with multi transactions in these two solutions?

Answer 1

Both versions will have exactly the same effect, and both prevent anomalies in the face of concurrency, because the row is locked before it is modified.

The first method is preferable, because there is only one client-server round trip, so the transaction is shorter and the lock is held for a shorter time, which improves concurrency.

The best way to do this and be safe from concurrent data modifications is:

UPDATE account
SET balance = balance + 1000
WHERE id = 1;

This does the same, because an UPDATE automatically puts an exclusive lock on the affected row, and a blocked query will see the updated version of the row when the lock is gone.