From: Aaron Bono <postgresql@aranya.com>
To: pgsql-sql@postgresql.org
Subject: Fwd: Atomar SQL Statement
Date: Fri, 7 Jul 2006 14:42:10 -0500
Message-ID: <bf05e51c0607071242h1c4f1977q52cd4bfe895c7a4a@mail.gmail.com> (raw)
In-Reply-To: <bf05e51c0607071241m123ef7e4h7d1501e6b188f4b0@mail.gmail.com>
References: <B8ADDBDA8696F546BDAC69B469D7F0512E06F3@kasmaiexcp01.iss.net>
<279E27B5-183F-4520-BC43-A726A50646EF@seespotcode.net>
<20060707105847.J77811@megazone.bigpanda.com>
<1152298181.22269.30.camel@state.g2switchworks.com>
<bf05e51c0607071241m123ef7e4h7d1501e6b188f4b0@mail.gmail.com>
On 7/7/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> On Fri, 2006-07-07 at 13:07, Stephan Szabo wrote:
> > On Fri, 7 Jul 2006, Michael Glaesemann wrote:
> >
> > >
> > > On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote:
> > >
> > > > My concern: in a multi threaded environment, can a second thread
> > > > interrupt this statement and eventually insert the same email
> > > > address in
> > > > the table with a different id? Or is this statement atomar?
> > >
> > > You're safe. Take a look at the FAQ entries on SERIAL:
> > >
> > > http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.2
> >
> > I don't think he is, because I don't think the issue is the SERIAL
> > behavior, but instead the NOT EXISTS behavior. Won't the NOT EXISTS in
> > read committed potentially be true for both concurrent sessions if the
> > second happens before the first commits, which then would mean that both
>
> > sessions will go on to attempt the insert (with their own respective ids
> > from the serial)? Without a unique constraint on email I think he can
> end
> > up with the same email address with two different ids.
>
> Yep, this is a possible race condition, if memory serves, and this is
> the reason for unique indexes. That way, should another transaction
> manage to sneak in between the two parts of this query, the unique index
> will still keep your data coherent.
I deal with these issues by first placing a unique constraint on the email
field (as was suggested above) and then synchronizing the code that does the
check and insert. I have also looked for instances where a unique
constraint error is thrown and had the system give the user a meaningful
error or responded appropriately.
-Aaron
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-sql@postgresql.org
Cc: postgresql@aranya.com
Subject: Re: Fwd: Atomar SQL Statement
In-Reply-To: <bf05e51c0607071242h1c4f1977q52cd4bfe895c7a4a@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