> I find myself having to do this in Sybase, but it sucks because there's
> a race - if there's no row updated then there's no lock and you race
> another thread doing the same thing. So you grab a row lock on a
> sacrificial row used as a mutex, or just a table lock. Or you just
> accept that sometimes you have to detect the insert fail and retry the
> whole transaction. Which is sucky however you look at it.
hmm should I be worried ?
I am doing an 'update if not found insert', in some cases I have found
that I need to select anyway, for e.g. take away 20 dollars from this
person;
(all variables prefixed with _ are local variables)
select into _money money from person_money where person_id = _person;
if (not found) then
insert into person_money (person_id, money) values (_person, -
_requested_amount);
else
update person_money set money = money - _requested_amount where
person_id = _person;
-- return new quantity
return _money - _requested_quantity; -- <- i need the quantity so I
have to select here.
end if;
if I am not mistaken your are saying that between the select and the
if (not found) then ... end if; block ... another concurrent process
could be executing the same thing and insert ... while in the first
thread found is still 'false' and so it ends up inserting and over
writing / causing a unique violation or some kind?
BTW, I did a benchmark with and without exceptions, the exceptions
version was very slow, so slow that I ended up killing it ... I am
sure it would have taken atleast 5 hours (was already 3 hours in) ...
versus, 25 mins! I guess the trouble was that I was using exceptions
to overload 'normal' flow ... i.e. update if exists else update is not
an exceptional circumstance and so exceptions are a bad choice.
It would be interesting to see how much overhead exception containing
functions present when they do not throw any exceptions ... for never
to every few records to all the time ... maybe I will try it with my
parsing functions (which catch exceptions thrown by substring()).
