>>>>>>>>>>>> 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
signature.asc
Description: Digital signature
