Task, TransactionScope and SQL Server Stored Procedure

I have to update some DataTable for which I am sending the data as XML. My stored procedure has transaction to abort the operation if something goes wrong. But the number of records I have to update is quite large and the XML reaches to 35 mb+. It is just in development and live data will be even larger.

To handle this I want to update by sending data in chunks, that is I will send XML for few hundred records at a time. I tried to use Task library to update the db in parallel like this.

var ret=0;
using(var ts = new TransactionScope(TransactionScopeOption.Required,
                                                      new TimeSpan(2,0,0))
{
    try
    {
        for(some condition)
        {
            get chunk of records
            generate xml
            create new task and call routing to push data to db
            var t = create new task and call routing to push data to db
            tasks.Add(t);
        }

        Task.WaitAll(tasks.ToArray());
        ts.Complete();
        foreach(var t in tasks)
            ret += t.Result;
    }
    catch(Exception ex)
    {
        //log exception and show user message
    }
}
return ret;

But I am getting the Exception that transaction has already been aborted.

What I will have to do to accomplish the update in single transaction, because I hav开发者_运维问答e to rollback any changes if any chunk is not able to update properly.

EDIT:- I'm using new TransactionScope(TransactionScopeOption.Required,new TimeSpan(2,0,0)) as suggested by Reed Copsey but still getting error System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.TimeoutException: Transaction Timeout even after one call to database that finished in 2-3 seconds.

for only one call

You need to wait until the tasks complete before you call ts.Complete(). This should look more like:

using(var ts = new TransactionScope())
{
    try
    {
        List<Task> tasks = new List<Task>();

        for(some condition)
        {
            // get chunk of records
            // generate xml
            // create new task and call routing to push data to db
            var task = PushDataAsync(theData); // make the task
            tasks.Add(task); // Keep a reference in a collection
        }

        // Wait until all tasks are done, so you can complete the transaction...
        // If any task raises an exception, you'll get an AggregateException here
        Task.WaitAll(tasks.ToArray());

        ts.Complete();
    }
    catch(Exception ex)
    {
        //log exception and show user message
    }
}

Have you even considered thread-safety? I wonder what's inside PushDataAsync.

TransactionScope doesn't work across threads out of the box. It uses thread local storage internally. The fact that the scope belongs to a single thread is called out for explicitely in the documentation.

Not sure if you're really going in the right direction, but have a look at DependentTransaction if you want to coordinate a transaction across multiple threads: http://msdn.microsoft.com/en-us/library/system.transactions.dependenttransaction.aspx