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

michael at diaspora.gen.nz michael at diaspora.gen.nz
Mon Dec 25 22:24:23 PST 2006


>>>(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).

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.


More information about the Wellington-pm mailing list