Fwd: Atomar SQL Statement

On 7/7/06, Scott Marlowe <smarlowe(at)g2switchworks(dot)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