Wrap import/transfer steps with SQL transaction
For each of the following types of import/transfer requests, there is a set of steps which should be wrapped by a single SQL transaction, to either commit all or rollback all, to prevent data inconsitency:
-
Import Data: -
Merge data and observation attributes -
Merge group and series attributes -
Merge dataflow attributes
-
-
Import referential metadata: -
Merge non-dataflow level referential metadata -
Merge dataflow level referential metadata
-
-
Transfer data -
Merge data and observation attributes -
Merge group and series attributes -
Merge dataflow attributes
-
-
Transfer referential metadata -
Merge non-dataflow level referential metadata -
Merge dataflow level referential metadata
-
-
Transfer data & referential metadata -
Merge data and observation attributes -
Merge group and series attributes -
Merge dataflow attributes -
Merge non-dataflow level referential metadata -
Merge dataflow level referential metadata
-
-
Analyze the impact of wrapping all those steps into a single transaction: -
check amount of database logging -
check possible deadlocks -
check amount of resources used by transaction; Specially when merging large amounts of data (large -
staging tables and large destination tables). -
check TransactionScope parameters (e.g. the isolation level at TryNewTransaction method)
-
-
Introduce commit when all steps finish or rollback if one step fails.
Edited by Pedro Carranza