>>> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> The tricky part is when an UPDATE with a search condition reads,
> modifies, and writes a value that is used in a search condition for
> another UPDATE.
>
> Every DBMS will block waiting for the first UPDATE to finish. Then
> what?
Either it's totally safe to proceed, or the second UPDATE was rolled
back.
> Do you re-run the query to find new tuples that might now satisfy
> the search condition that didn't before?
There can't be any. Blocks taken during the reading of rows so far
have not been released, and would preclude the update from changing
results read so far.
> Do you update the new value in all the tuples you originally found,
> regardless of whether they still match the search condition?
They have to still match, your locks would preclude their
modification.
> Do you update the new value in all the tuples that
> you found that still match the search condition?
They can't have a new value. Locks.
> Do you update the old value, perhaps overwriting changes made by the
> first UPDATE?
If you haven't gotten to reading rows yet, you're still OK by the time
the other transaction's locks are released. Look over those rules and
try some thought experiments to convince yourself. It really is safe,
if potentially slow.
-Kevin