You are right!
I'm doing a select on Table1 and then I have a loop on the datareader: for
each record I do many queryies to preform insert or update on 4 other tables
(some selects are involved of course).
Table1 is used only to insert records (each application insert a new record
so each concurrent access does not affect the others as if there was no
concurrency).
From time to time I need to update the 4 other tables with data from Table1
where Date < X (X = Now) and after insertion I'll delete the inserted
records.
I've thought I can do the select outside the transactionscope, but:
- if I loop on the datareader inside the transactionscope, a distributed
transaction occurs?
- when I have to delete inserted records I cannot do DELETE FROM Table1
WHERE Date < X since between te select and the entering in the transaction
scope, a concurrent access could have inserted a row that matches the where
clause.
I have to do a "DELETE INSERTED ROW" instead of a global delete right?
Thanks a lot,
Luigi.