[Wellington-pm] Perl database stuff....

Andrew McMillan andrew at catalyst.net.nz
Thu Dec 28 00:02:21 PST 2006


On Tue, 2006-12-26 at 19:24 +1300, michael at diaspora.gen.nz wrote:
> >>>(c) In the perl you can make it "select, select, insert" as:
> >>> 1. select the record (and fail).
> >>> 2. select the sequence nextval
> >>> 3. insert the row using the sequence.
> 
> >> There is of course a race condition here; between the select record
> >> and select the sequence nextval, another thread could perform the full
> >> process.
> 
> >Ah, no.  We're talking about PostgreSQL here, so there isn't.
> 
> Sure there is.  I even qualified it by saying that the impact of the
> race would depend on your schema.
> 
> I understand that the "nextval" operation is atomic (and good on it;
> dealing with DBs that *don't* have that operation is a pain in the ass),
> but as I understood things, the model was something like (in pseudo code,
> but meant to be implemented in Perl):
> 
>     A: select 1 from table where business_key = ...; # off to database
>     if select returned nothing
>     begin
> 	B: select sequence.nextval into id; # off to database
> 	C: insert into table values (id, business_key); # off to database
>     end;
> 
> Between A and B, unless you wrap a lock around the operation, there's a
> race condition; you could find either C failing (if there's a uniqueness
> constraint on "business_key"), or duplicate values in "table" (if
> there isn't).

Sorry, yes.  I misunderstood exactly which code you were referring to,
thinking that you were suggesting nextval itself had a race condition.

And yes, Cliff's design very likely does have race conditions, which may
or may not occur in the real world.

You could work around that by doing that final insert as:

INSERT (... columns ...) SELECT ... values ... WHERE NOT EXISTS(SELECT 1
FROM table WHERE business key)

but I got the impression from Cliff's original post that actually having
an error if a row already existed for that business key was fine, so
maybe the race just results in the INSERT failing, an error gets thrown
and everyone is happy.

Or you could use transaction isolation-level serializable, but using
that can produce more problems than it solves, in my experience.

Cheers,
					Andrew.

> The fact that you'd need to do a select first implies that there is a
> business key, rather than just a surrogate ID generated from a sequence,
> so you need to worry about this.
> 
>     -- michael.
> _______________________________________________
> Wellington-pm mailing list
> Wellington-pm at pm.org
> http://mail.pm.org/mailman/listinfo/wellington-pm
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
       The truth is rarely pure, and never simple. -- Oscar Wilde
-------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://mail.pm.org/pipermail/wellington-pm/attachments/20061228/4248a57d/attachment.bin 


More information about the Wellington-pm mailing list