Re: Online & update races

Hi,

> 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.

Thank you for reply. I think this is basically the 'only' way how to solve
the problem. Have some 'row changes count'. This scheme is easy
implementable for smaller systems. But as you wrote above, if you prepare
'user datas' for various tables, joins, whatever, it is very hard to take
care of this 'modified serial'. I would like to move this way to 'database'
itselfs somehow.

I was thinking abotu something like this... But I don't know PgSql
internals, so, I don't know if it is possible...

I suppose DB engine have to 'hold' some kind of row version' (chnages count,
timestamp, combination, whatever) - because it should be needed while
transactions. If DB engine can 'collect' within one transaction these ID's
which were used while selecting datas, I can keep then in 'POST' data and
verify (while transaction) if they are still valid. I have no clue if this
is possible with PlSQL or another server-side scripting language or some
improvement of PqSql is needed. And if improvement is needed, if it is
possible. And if it is possible, if some PG developer will want to do that -
and if they will like this kind of improvement. I am open to pay for solving
this problem, because the system we are doing will be used commerically. And
I think, this is general problem, which every bigger system have to solve.
Sooner or later.

I fully agree with you, that locking is not a way. But is there any other
'more automatized' way than take care of row versions by ourself ?

But before contacting developers, I would like to ask other about
opinions...

Have a nice day,
Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net

--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.