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
That isn't SQL, or any syntax that PostgreSQL supports that I know of.
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.
I don't believe it is possible for it to fail - or serializable is going to actually result in errors.
Is that how PostgreSql works? Is that the generally recommended pattern? Impossible to tell from the docs as written.
That part of the issue with the documentation, they tend to simply say how things work and let the user decide. Recommendations are uncommon.
MVCC really relies on Select For Update to work for transactions, I think.
IIUC it is basically the difference between optimistic and pessimistic concurrency. You get to choose which cost/benefit package you want.
My impression is that if you are getting that deep into the bowels of concurrency you should learn and use the serializable isolation level to ensure a consistent linear flow without having to really deal with manual locking directly.
