C# Nested transaction in a loop, but rolling back if any of them fail

Question

The issue i'm trying to solve is as follows:

I have a process that ingests data from a file and inserts certain things in multiple tables, as it is now it is all done with one transaction, however with very large data sets the rollback or the commit time out and fail, no matter what i set the time out to (at least as far as all my attempts have shown me). So it was decided that I need to rewrite the functionality to "chop" the task up. As it currently stands the pseudo code for the current code looks something like (Pseudo code used to avoid unnecessary information)

variable = FunctionThatReadsFromAFile();

ITransactionManager transactionObject = new TransactionManager();
IDbTransaction dbTransaction = transactionObject.Get();

WriteToFirstTable(variable ,dbTransaction);
WriteToSecondtable(variable ,dbTransaction);
WriteToThirdTable(variable ,dbTransaction);

if(!Error)
transactionObject.Commit(dbTransaction);
else
transactionObject.Rollback(dbTransaction);

Like i said, this works fine for smaller data sets, but when the file has more than a particular amount of rows (dependent on time out) it fails on commit or rollback.

I can't just change the time out to 10,000 seconds for example, in fact due to the way the program is structured I can't change the time out at all beyond for testing purposes. So what i'm trying to do is have the program work on 100 rows at a time instead of the entire file at once, committing them , but rolling back everything if one of the "sets of hundred" fail, I've understood that this can be done with nested transactions, but doing this ;

using (TransactionScope outterTransaction = new TransactionScope())
{

    while(file.read()) 
    {

        using (TransactionScope innerTransaction = new TransactionScope())

        {

        variable = GetNextHundredOrLessRows(file); //100 rows at a time basically

        WriteToFirstTable(variable ,innerTransaction );

        WriteToSecondtable(variable ,innerTransaction );

        WriteToThirdTable(variable ,innerTransaction);


        if(!Error)
        innerTransaction.Complete();
        else
        innerTransaction.Rollback();

        }

    }

    if(!Error)
        outterTransaction.Complete();
        else
        outterTransaction.Rollback();

}

isn't working, any idea what i'm doing wrong?

Thank you all in advance for taking time to attempt to help me out.

Edit: Also is this the right track to take to solve the issue? I've read that nested transactions join the scope of the outer transaction , so would i still run into the same issue on .Complete?

evanmcdonnal · Answer 1 · 2013-05-02T17:12:39.520

I believe you need to commit the transaction at the bottom of the loop (this will cause other problems you need to consider, ie how to rollback). If you wait til you're outside of the inner transaction loop all the transactions will get bundled and committed at once which means the performance will actually be far worse than the performance of the first example.

EDIT: As noted this causes a problem where if one set of inserts fails you cannot rollback the changes. To solve this problem I think the simplest solution is to create a "RollBackStack". Inside the while loop you push some object with whatever data necessary to do a roll back onto a stack. You add a flag to indicate if something fails. If there is a failure, you switch the flag and break from the loop. Then you pop the stack undoing changes until the stack is empty. You then know that the db is in the state it was before you started. After that you can retry the insertion from group 1. Alternatively you can add retry logic to continue from group x where x is the group at which you failed before doing any roll back. If after a set number of retries you fail to complete a full insert you go into the rollback method where you pop the stack til empty.

Hello, thanks for your response. Well yes, I see what you mean by the performance, but I can sacrifice the performance as long as it actually does what i need it to do. If i commit the transaction at the bottom of the loop, then if at some point one of the sets fail, then the rest will still go through, which is what i want to avoid. I need them all to go in ONLY if none of them fail. — A.D, May 02 '13 at 16:37
@A.D I realize that and have something of a solution. However, performance is what's preventing example 1 from running, so it follows that example 2 is never going to work if you try to commit outside of the inner loop. I'll edit with an idea of how rollback, it isn't exactly pretty, but it's logically sound. — evanmcdonnal, May 02 '13 at 17:08

C# Nested transaction in a loop, but rolling back if any of them fail

1 Answers1