>> 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.
>>>>>>>>>> Bernt M. Johnsen wrote
> Not if you wrap this in a transaction with the proper isolation level.
Bernt, thanks for the response. I'm obviously new to writing database applications like this so bear with me. I've been trying to get my head around the isolation
levels supported by Derby and all of their implications. It would seem to me that perhaps REPEATABLE READ might do the trick here. Perhaps you could
validate my logic:
If REPEATABLE READ is in effect, then no other transaction would be able to delete the row between the insert and the select because this would result in
different results for the select statement if it were repeated. For example, the first execution of the select might return a row (if it existed) but a second execution
of the same select statement might return 0 results if a delete was allowed to sneak in before the select was executed. Is this correct?