>>>>>>>>>>>> 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
|