db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Atomic check for row existence and insert if doesn't exist
Date Sat, 10 Feb 2007 13:59:38 GMT
>>>>>>>>>>>> Ace Jayz wrote (2007-02-09 21:08:44):
> 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.

Not if you wrap this in a transaction with the proper isolation level.

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



-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message