Re: idiom for interactive client applications.

select for update just protects from having the transaction being aborted later
on, otherwise
it will block if someone else is updating. it doesn't address the issue of having
client cached
values being changed outside of a transaction. holding one transaction only to
interactively update a complex object would be more headache due to cascading
abort, then just issuing an exclusive lock on all items related to the object (
e.g by writing a lock into a lookup table against the object's id), which isnt
what's wanted. opening the transaction just before an update means each write
needs to be checked manually for a conflict, because the user only sees a cached
old value, which might have changed because no transaction was open whilst the
user was browsing of the object.
It's probably to do with the complexity of the user interface structure; some
user interfaces only display one page of one view at the most , so that at most
opening a transaction with each user page involves locking only a few rows of one
or two tables, so it would make sense to do as below, but for an application that
edits data for a complex obect composed of 10 or more tables,
it might not be sensible.
I suppose there's the answer then, open up a transaction every time the tab page
is changed
or a tree node is selected, and select for update everything that is being
displayed and editable.

On Fri Oct 12 20:49 , Richard Broersma Jr sent:

>--- syan tan wrote:
>> What other ways are there of doing such an app?
>
>The two ways that I know of are:
>
>BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
>--if the tuple was modified by another transaction, this transaction will fail
on commit.
>
>SELECT FOR UPDATE... -- pre-lock the tuple of interest from other commits.
>
>Regards,
>Richard Broersma Jr.
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate