Re: Transaction vs. Savepoints

Andreas <maps(dot)on(at)gmx(dot)net> writes:
> A interactive application on the other hand would open records and have
> them in an edit-form as long as the user needs them.
> Probaply I just don't get it how to use transactions in such a
> interactive scenario the right way.

Right, a lot of novices think they should hold open a transaction for
the whole edit cycle. This is not good design: what if the user goes
off to lunch and meanwhile someone else wants to edit the same record?

Usually the best approach is to fetch the data without acquiring any
lock, allow the interactive editing to happen outside a transaction,
then when the user hits SAVE, perform a transaction that locks the
row(s), checks for conflicting changes, and commits if no conflict.
On conflict you might be able to resolve the discrepancy automatically,
but if not, you just fail and let the user deal with it ...

regards, tom lane