From: James Mansion <james@mansionfamily.plus.com>
To: Robins Tharakan <tharakan@gmail.com>
Cc: chemuduguntar@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Implications of Using Exceptions
Date: Sun, 06 Apr 2008 21:21:41 +0100
Message-ID: <47F930D5.5030607@mansionfamily.plus.com> (raw)
In-Reply-To: <36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.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>
<36af4bed0804030627i32b5d9cdk255c757ccff45c32@mail.gmail.com>
Robins Tharakan wrote:
>
> 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.
How does that help?
If the matching row doesn't exist at that point - what is there to get
locked?
The problem is that you need to effectively assert a lock on the primary
key so that you can update
the row (if it exists) or insert a row with that key (if it doesn't)
without checking and then inserting and
finding that some other guy you were racing performed the insert and you
get a duplicate key error.
How does Postgresql protect against this?
James
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: james@mansionfamily.plus.com, tharakan@gmail.com, chemuduguntar@gmail.com
Subject: Re: Performance Implications of Using Exceptions
In-Reply-To: <47F930D5.5030607@mansionfamily.plus.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