[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