db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ace Jayz" <fourtl...@gmail.com>
Subject Atomic check for row existence and insert if doesn't exist
Date Sat, 10 Feb 2007 05:08:44 GMT
I've got a table with an identity column, P, as a primary key.  This table
has another column, C, with a uniqueness constraint.  I want to insert a row
into the table if no row has a value for C=c, and if a row does exist whose
column C=c I want to get the value of the identity column for storage in
another table as a foreign key.  Is there any way to do this with Derby that
will be atomic?  i.e. if the row exists, then the value of the identify
column should be retrieved without the possibility of that row being deleted
in the interim, if the row doesn't exist then the insert should succeed and
not be in a race with other threads on other connections attempting the same
operation.

A couple of obvious approached come to mind:

1)  - select * from t1 where C=c for update
     - if row returned, then get value of identity column, id
     - if row not returned, insert into t1 values(c), get identity column
from last insert, id
     - insert row with column value of id into second table

this would seem to have an insert race condition.

2) - insert into t1 where C=c
    - if insert fails, select id from t1 where C=c
    - if insert succeeds, get identity column from last insert, id
    - insert row with column value of id into second table

this would seem to be subject to the row being deleted between the insert
attempt and the select.

There must be a fool-proof way to do this, but I'm not quite sure what it
is.  Any help would be appreciated.

Ace.

Mime
View raw message