Parallel.Invoke(), TransactionScope() and SqlBulkCopy

Question

I have multiple methods inside a Parallel.Invoke() that need to run inside of a transaction. These methods all invoke instances of SqlBulkCopy The use-case is "all-or-none", so if one method fails nothing gets committed. I am getting a TransactionAbortedException ({"Transaction Timeout"}) when I call the Complete() method on the parent transaction.

This is the parent transaction:

using (var ts = new TransactionScope())
{
    var saveClone = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
    var saveErrorsClone = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
    var saveADClone = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
    var saveEnrollmentsClone = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete);
    Parallel.Invoke(_options, () =>
        {
            Save(data, saveClone);
        },
        () =>
        {
            SaveErrors(saveErrorsClone);
        },
        () =>
        {
            SaveEnrollments(data, saveEnrollmentsClone);
        });
ts.Complete();  
}//***** GET THE EXCEPTION HERE *****

Here's a dependent transaction that makes use of SqlBulkCopy (they're all the same structure). I'm passing-in the parent and assigning it to the child's TransactionScope

private void Save(IDictionary<string, string> data, Transaction transaction)
{
    var dTs = (DependentTransaction)transaction;
    if (transaction.TransactionInformation.Status != TransactionStatus.Aborted)
    {
        using (var ts = new TransactionScope(dTs))
        {
            _walmartData.Save(data);
            Debug.WriteLine("Completed Processing XML - {0}", _stopWatch.Elapsed);
            ts.Complete();
        }
    }
    else
    {
        Debug.WriteLine("Save Not Executed - Transaction Aborted - {0}", _stopWatch.Elapsed);    
        dTs.Complete();
    }
    dTs.Complete();
}

EDIT (added my SqlBulkCopy method...notice null for the transaction param)

private void SqlBulkCopy(DataTable dt, SqlBulkCopyColumnMappingCollection mappings)
{
    try
    {
        using (var sbc = new SqlBulkCopy(_conn, SqlBulkCopyOptions.TableLock, null))
        {
            sbc.BatchSize = 100;
            sbc.BulkCopyTimeout = 0;
            sbc.DestinationTableName = dt.TableName;
            foreach (SqlBulkCopyColumnMapping mapping in mappings)
            {
                sbc.ColumnMappings.Add(mapping);
            }
            sbc.WriteToServer(dt);
        }
    }
    catch (Exception)
    {
        throw;
    }
}

Besides fixing the error, I'm open to alternatives. Thanks.

Answer 1

You're creating a form of deadlock with your choice of DependentCloneOption.BlockCommitUntilComplete.

Parallel.Invoke blocks the calling thread until all of its processing is complete. The jobs trying to be completed by Parallel.Invoke are all blocking while waiting for the parent transaction to complete (due to the DependentCloneOption). So the 2 are waiting on each other... deadlock. The parent transaction eventually times out and releases the dependent transactions from blocking, which unblocks your calling thread.

Can you use DependentCloneOption.RollbackIfNotComplete ?

Answer 2

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.complete.aspx says that TransactionScope.Complete only commits the transaction it contains if it was the one that created it. Since you are creating the scope from an existing transaction I believe you will need to commit the transaction before calling complete on the scope.

From MSDN:

The actual work of commit between the resources manager happens at the End Using statement if the TransactionScope object created the transaction. If it did not create the transaction, the commit occurs whenever Commit is called by the owner of the CommittableTransaction object. At that point the Transaction Manager calls the resource managers and informs them to either commit or rollback, based on whether this method was called on the TransactionScope object

.

Answer 3

After a lot of pain, research, and lack of a valid answer, I've got to believe that it's not possible with the stack that I described in my question. The pain-point, I believe, is between TransactionScope and SqlBulkCopy. I put this answer here for the benefit of future viewers. If someone can prove that it can be done, I'll gladly remove this as the answer.

Answer 4

I believe that how you create your _conn-instance matters a lot, if you create it and open it within your TransactionScope-instance any SqlBulkCopy-related issues should be solved.

Have a look at Can I use SqlBulkCopy inside Transaction and Is it possible to use System.Transactions.TransactionScope with SqlBulkCopy? and see if it helps you.

void MyMainMethod()
{
 using (var ts = new TransactionScope())
 {
  Parallell.InvokeOrWhatNotOrWhatEver(() => DoStuff());
 }
}

void DoStuff() 
{
 using (var sqlCon = new SqlConnection(conStr))
 {
  sqlCon.Open(); // ensure to open it before SqlBulkCopy can open it in another transactionscope.
  using (var bulk = new SqlBulkCopy(sqlCon))
  {
    // Do you stuff
    bulk.WriteToServer...
  }      

  ts.Complete(); // finish the transaction, ie commit
 }
}

In short:

1. Create transaction scope
2. Create sql-connection and open it under the transaction scope
3. Create and use SqlBulkCopy-instance with above created conncection
4. Call transaction.Complete()
5. Dispose of everything :-)