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, [EMAIL PROTECTED] < [EMAIL PROTECTED]> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
