Re: Performance Implications of Using Exceptions

I think James was talking about Sybase. Postgresql on the other hand has a
slightly better way to do this.

SELECT ... FOR UPDATE allows you to lock a given row (based on the SELECT
... WHERE clause) and update it... without worrying about a concurrent
modification. Of course, if the SELECT ... WHERE didn't bring up any rows,
you would need to do an INSERT anyway.

Read more about SELECT ... FOR UPDATE here:
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-FOR-UPDATE-SHARE

*Robins*

On Thu, Apr 3, 2008 at 2:48 PM, chemuduguntar(at)gmail(dot)com <
chemuduguntar(at)gmail(dot)com> wrote:

> > 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()).
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>