On Thu, Jul 29, 2021 at 8:04 AM PG Doc comments form <nore...@postgresql.org> wrote: > For all this documentation, it is completely unclear how to handle the most > common, simple case. I.e. > > Select balance into :bal ...where key =123; > Update set balance = :bal+100 where key = 100
I don't think that that's the most common or simple case. > The discussion of read committed for Updates is misleading, I am pretty sure > it will fail if the select is in a different statement, a common case. That's true. > For Oracle, I think that by default a Select will return values at the > beginning of a transaction, Select For Update will return the read committed > value, and Select For Update will wait until conflicting transactions > complete. I don't think that's true. I believe that the main difference between READ COMMITTED in Oracle is conflict handling: If an UPDATE needs to wait for another UPDATE, the entire statement will be rolled back before it is retried. While Postgres does something...more complicated. Both systems use a snapshot per statement in READ COMMITTED. And so any differences between the two systems here don't seem relevant. > So the answer is that the first Select would be a Select For > Update, which should be the normal pattern to be safe (with primary key > access) and minimize deadlocks. > > Is that how PostgreSql works? Is that the generally recommended pattern? > Impossible to tell from the docs as written. MVCC really relies on Select > For Update to work for transactions, I think. I suggest using a higher isolation level. Ideally SERIALIZABLE. -- Peter Geoghegan
