>>> Jeff Davis <pg...@j-davis.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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
