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?
Thanks,
Ace.
