Re: Online & update races

Lada 'Ray' Lostak wrote:
> I will also appreciate any links to web resources, talking about this
> problem. I didn't find anything usefull around.

I'm working with developing a fairly big warehouse management system, and
there we see this problem every day. We've settled (many years ago) for the
following:

loop
begin
select * from my_table where status = :status
(read into record structure)

update my_table set a=1, b=2 where and status = :status and
another_status = :status2 and lates_updater =:record.latest_updater and
latest_date = :record.latest_date and latest_time = :record.latest_time

commit
exit loop
exception
when transaction_conflict | no_such_row =>
rollback
delay small time
end loop;

transaction_conflict | no_such_row should be interpreted as no row matched.
The flaw this design has, is that you can't separate 'real transaction
conflicts' with situation where other conditions failed, as no row had
another_status = status2

However, we find the design good enough to keep using it. It has the
advantage that no table is ever looked, we don't use select for update at
all.

--
/Björn
-------------------------------------------------------------------
http://lundin.homelinux.net
Registered Linux User No. 267342 <http://counter.li.org>