Re: Online & update races

I used to do it this way :

Suppose you have a web form to edit data from a table... you add a field
in your table which contains a version identifier for that data, then you
UPDATE ... WHERE ... AND version_id = the old version id. The version_id
is passed around in a session variable or in hidden form fields. The
hidden form fields are better because they will prevent breakage if the
same user edits the same data in different windows, or refreshes his
browser window at the wrong time.
Then, if the UPDATE has a rowcount of 1, you know it's OK, but if it has
a rowcount of 0, you know something happens, and can check if the record
still exists and its version id was modified, or if the record was deleted.
A version id can be a counter, a sequence... it can also be a MD5 of the
row contents for instance, its sole purpose being to detect change. Using
a sequence might be the easiest.
This way works but still looks like band-aid ; moreover, if you do a
complex operation which modifies several tables, you have to take care of
modification order, and the problem becomes more complex.
It would be nice to have a framework for that kind of thing which is
common in web apps.
One of postgresql's good points is that it does not lock things, thanks
to MVCC, unlike MySQL which locks the table on every write. This model is
in the same spirit than MVCC, because it will not prevent reads to records
which are being updated.
However, a recurrent problem in web applications is that there is no
"logout", logout can only be implemented with certainty using timeouts, so
you can't use locking, because you really don't know when the locks will
be released. If you use locking, some information will get locked waiting
for a timeout if a user closes his browser without explicitely logging out
; besides you'd have to have a cron to log users out as a disconnected
user, by definition makes no action to signal the fact that h's gone away.
You could implement this by adding a version_id serial field to the
relevant tables, and then an ON UPDATE trigger which would check that the
version_id of the updater is the same than the version_id in the updated
row, or else raise an exception. You can also have a special value to
bypass checks, to be able to update in all cases, and not get stuck if you
have a problem. The trigger would then increment the version_id before
updating.

What do you think ?

>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>