From: Robins Tharakan <tharakan@gmail.com>
To: chemuduguntar@gmail.com <chemuduguntar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Implications of Using Exceptions
Date: Thu, 3 Apr 2008 18:57:27 +0530
Message-ID: <36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.com> (raw)
In-Reply-To: <27de86ba-4756-4ec3-a63e-07a2bcb4a531@i12g2000prf.googlegroups.com>
References: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com>
<20080401005235.GM4999@tamriel.snowman.net>
<F0238EBA67824444BC1CB4700960CB4804F8B1BA@dmpeints002.isotach.com>
<47F3DC5A.30705@mansionfamily.plus.com>
<27de86ba-4756-4ec3-a63e-07a2bcb4a531@i12g2000prf.googlegroups.com>
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@gmail.com <
chemuduguntar@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tharakan@gmail.com, chemuduguntar@gmail.com
Subject: Re: Performance Implications of Using Exceptions
In-Reply-To: <36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox