Thread: idiom for interactive client applications.
Applications that interactively display table information like web clients and gui applications, normally would have to cache the data in the table rows being displayed (e.g. have copies), that could be changed if there was a lesser degree of information locking ; is there an idiom that's commonly used to make sure that these applications don't overwrite concurrent earlier commits that haven't been seen by a client ? I know of an app that does xmin checking, but I was thinking that if before an update is issued, the original old value that was cached was compared with a newly re-read old value at the start of the update transaction, was found to be different, then the update should fail , because that means an update had been issued elsewhere that had been uncommunicated. Is that the usual idiom ? This would mean every write would need to be preceded by a select for the old value, and every old value which was originally cached needs to be stored separate from the current client's version of the value. What other ways are there of doing such an app?
--- syan tan <kittylitter@people.net.au> 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.
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
And close the previous transaction when a suitable granularity of focus changes, eg before a editable table gains input focus, a tab change , or another node of the tree is selected. On Sat Oct 13 4:28 , Syan Tan sent: > >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 > > > >---------------------------(end of broadcast)--------------------------- >TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate