is an update which joins on itself guaranteed to be atomic?-CodePudding

I have an update to execute in postgresql - which needs to update the table's value based on its old (current) value, and return the old value and the new value, and it must use the value locked for the update in the update as its old value (i.e. for my purposes, I cannot allow it to use a stale prior value!)

basically:

UPDATE mytable new
SET balance=master.balance
FROM master, mytable old
WHERE new.id=$1 AND old.id=new.id AND master.id=new.masterid
RETURNING old.balance, new.balance

I need to know for certain that old.x is the values in new.x at the time of this action -- that it cannot be interleaved between selecting that value and updating itself -- but I need to refer to both (the prior and new balances).

Can anyone direct me to where I can know for certain? And if this is not true (old can indeed be stale) - then do you have a suggestion as to how to enforce that it is essentially select for update that works syntactically?

CodePudding user response：

I would still love to know if the above is or is not guaranteed to be atomic (nothing can change for that row on mytable between the read & write)...

However, I am going with this for now which I do believe has that guarantee:

WITH old AS
  (SELECT * FROM mytable WHERE id=$1 FOR UPDATE)
UPDATE mytable new
SET balance=master.balance
FROM old, master
WHERE new.id=old.id AND master.id=old.masterid
RETURNING old.balance, new.balance

CodePudding user response：

It is possible that your query will return stale values for old. If an update is in progress while your statement is executing, the query can see the old value, but the update will block and see the new value once the concurrent transaction is done. The exact behavior probably depends on the execution plan chosen.

Your answer shows one way to avoid the problem. The other, perhaps simpler, way is to use the REPEATABLE READ transaction isolation level. Then you get a serialization error if the old values would be stale.