Online & update races

Hello there,

I am thinking how to solve another typical problem of online systems
with combination of thin client... Imagine simple case, 2 users are going to
edit 'same' datas. Both see on the 'screen' the same, after they started
edit them. First one changes datas and submit changes (sucessfully).
Database (set tables - inserts/updates/deleting) was changed. At this point,
datas which second user is watching are not valid anymore. They are outdated
and he should refresh or 'merge' changes. If he will 'submit' his datas, he
can delete change of first user. These time races can occurs accross various
part of complex system, so, there is no way how to 'lock' function - e.g.
second user will see "wait, someone edits right now".

What is the best way to solve this general problem - on DB layer as
possible ? (using PlSql, triggers, ...) - anyone have experimence, or is
there any project, which allready did something like this ? Database server
is PgSql, as you probably expect :) I am not too much into PgSql internals
but I belive, there is way how to solve these problems better than
application layer...

Locking records/tables is not possible at all. It is yeasy to add some
counter and check version in system itselfs, but something more general
(e.g. UPDATE SQL statement will return error, if record
'timestamp/changeid/...' will be mismatched, whatever) is much nicer
solution. It makes 'system' itselfs simpler, which is good.

Another solution is more 'organisation work flow'. I belive, combination
of system (DB) and workflow is the best solution.

All these time-related changes are done within one transaction.

I will also appreciate any links to web resources, talking about this
problem. I didn't find anything usefull around.

Thank you again,

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.