Re: [Gnumed-devel] Comments on 0.2

[Tim Churches]

Karsten Hilbert wrote:
> On Tue, Jun 20, 2006 at 09:18:33PM +1000, Ian Haywood wrote:
> 
>> However you seem to be solving this 3 ways at once:
>> 1- recieving NOTIFY events to reload widgets
>> 2- using SELECT .. FOR UPDATE
>> 3- checking xmin on UPDATE and DELETE
> 
> - 1) is unrelated to 2) and 3) and unrelated to concurrency/locking issues
> 
> - 2) select for update
> 
>   - whenever I want to make sure I can safely write to a row I must do
>     "select for update" on that row

What is wrong with just using a transaction. If the row is locked by
another transaction writing to it, then the transaction will abort. No
need to mess around with explicit row locks, is there?

>   - this will prevent other transactions from *writing* to that row
>     until I release that lock by "commit"

I am pretty sure that Postgres offers more fine-grained concurrency
control than this. Doing this row-level locking is ignoring the MVCC
feature of Postgres, which is one of the main reasons for using it? I
thought that SELECT FOR UPDATE is only supported in Postgres in order to
allow applications designed for less sophisticated database backends to
be ported to PG... let's see if I can find the presentation in which I
read this... googles... yup, here it is:
http://www.postgresql.org/files/developer/transactions.pdf

> - 3) checking XMIN
> 
>   - after my "select for update" no one else can *write* that row
>     until I commit
>   - therefore, the timespan between "select for update" and commit
>     should be minimized
>   - therefore I only "select for update" right before actually writing

I am fairly sure that if you use a transaction PG handles all this for
you and will abort the transaction if another transaction has a
write-lock on the row in question.

>   - however, the user has seen data on screen from much earlier, namely
>     when the initial, simple "select" was done
>   - other people might have overwritten that data in the database
>     in the meantime (yes, I have seen it happen)
>   - they, too, properly locked the data but the lock is released again
>     because they already finished their write
>   - now I want to write *my changes* to the backend
>   - if no one else has locked the row I can do that *regardless*
>     of *what* is actually in the database *right now*
>   - that way I could overwrite other people's interim changes
>     without ever knowing they existed
>   - this is unacceptable
>   - therefore I check XMIN before I write to the backend
>   - it must be the same XMIN as when I initially read the data
>   - or else someone has overwritten the data in the meantime
>   - XMIN is a PostgreSQL internal "timestamp" telling me who
>     last *modified* a given row

I thought XMIN was the transaction ID of the last or current transaction
for that row? Perhaps that's what you mean by "timestamp"?

>   - if XMIN changed between me reading/displaying the data and me
>     wanting to save changes I *know* someone else modified it
>   - at which point I need to inform the user about that fact

You really need to show the user what the changes to the data were,
while still preserving the user's own changes. That becomes a very
difficult user interface issue, one which very few applications attempt
to solve (perhaps wisely), and instead they disallow multiple sessions
to edit the one record simultaneously, or they use embrace multi-user
collaborative editing from the outset using all sorts of fancy callbacks
and notifiers in the UI - but that is mainly used in collaborative
document editing, not databases apps.

Tim C

>> Why all 3? (I'm sure there's a good reason, I just can't figure it out)
> I cannot for the life of it explain it any more clearly than that.
> 
>>> That is a misconception. However, it may very well be
>>> sufficient to put a wrapper around them which handles them
>>> in an invisible, default way such that you don't have to
>>> worry about them. As if they were not there. But they are
>>> still handled. Just that you don't have any control over
>>> them.
>> How to encapsulate cleanly is the big issue.
> I fully agree. I have up to now only been able to come up
> with the encapsulation you currently see in the middleware.
> Not pretty but - AFAICT - safe (yes, it has already
> prevented inconsistent data from entering the database).
> 
>>>> [And the refusal to delete my old unused code is just infuriating.
>>>> we're using CVS for god's sake: we can always get it back!]
>>> Look at client/gmDemographicsWidgets.py again :-)
>> Ok, thanks.
>> The code is now very garrulous, but at least sane.
> I fully agree it is garrulous !  But that's stock wxPython
> code (hence I don't really agree it is sane ;-) Dabo might
> make it more palatable.
> 
> Karsten