Nested transactions in LINQ to SQL

Question

I need help with realizing quite complex business logic which operates on many tables and executes quite a few SQL commands. However I want to be sure that the data will not be left in incosistent state and to this moment I don't see the solution which would not require nested transactions. I wrote a simple pseudo-code which illustrates a scenario similar to what I want to accomplish:

Dictionary<int, bool> opSucceeded = new Dictionary<int, bool> ();

for (int i = 0; i < 10; i++)
{
    try
    {   
        // this operation must be atomic
        Operation(dbContext, i);

        // commit (?)

        opSucceeded[i] = true;
    }
    catch
    {
        // ignore
    }
}

try
{
    // this operation must know which Operation(i) has succeeded;
    // it also must be atomic
    FinalOperation(dbContext, opSucceeded);

    // commit all
}
catch
{
    // rollback FinalOperation and operation(i) where opSucceeded[i] == true
}

The biggest problem for me is: how to ensure that if the FinalOperation fails, all operations Operation(i) which succeeded are rolled back? Note that I also would like to be able to ignore failures of single Operation(i).

Is it possible to achieve this by using nested TransactionScope objects and if not - how would you approach such problem?

Answer 1

If I am following your question, you want to have a series of operations against the database, and you capture enough information to determine if each operating succeeds or fails (the dictionary in your simplified code).

From there, you have a final operation that must roll back all of the successful operations from earlier if it fails itself.

It would seem this is exactly the type of case that a simple transaction is for. There is no need to keep track of the success or failure of the child/early operations as long as failure of the final operation rolls the entire transaction back (here assuming that FinalOperation isn't using that information for other reasons).

Simply start a transaction before you enter the block described, and commit or rollback the entire thing after you know the status of your FinalOperation. There is no need to nest the child operations as far as I can see from your current description.

Perhaps I a missing something? (Note, if you wanted to RETAIN the earlier/child operations, that would be something different entirely... but a failure of the final op rolling the whole package of operations back makes the simple transaction usable).