Parallel.Invoke(), TransactionScope() and SqlBulkCopy

c# multithreading sqlbulkcopy task-parallel-library transactionscope

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.

Accepted Answer

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.


Popular Answer

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 ?



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why