Adding Transaction Scope to Parallel.Foreach

Question

I have a loop inserting records into a database (Firebird):

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
    taskList.Add(Task.Factory.StartNew(() =>
    {       
        Parallel.ForEach(objectList, c =>
        {
            DoInsert(c, id);
        });
    }));

    scope.Complete()
}

I would like the ability to rollback these inserts as a batch if the insert fails. However as I'm performing the insert within a Parallel.Foreach loop, the transaction scope isn't visible. I'm assuming that's because the loop is operating in a different thread. If I do this as a serial insert using TransactionScope, everything works.

I've tried to use a DependentTransaction but I can't seem to get the context to the DoInsert function. The DoInsert simply opens a connection and writes the contents of C to the database.

Any ideas?

Thanks,

Answer 1

You cannot do this using a single TransactionScope, for the reason you mentioned: TransactionScope relies on threadlocal storage, and you are in a multithreaded environment.

But more importantly, for this to work, every worker thread would need to open a connection, enlist it in a distributed transaction, do the insert, and close the connection.

If these are simple inserts, I'd be very surprised that creating all these connections would give you any performance benefit. In fact, I believe this program would be a lot faster if you did it on a single thread, and on a single connection. Obviously, that implies that the connection is opened (and closed) once, and passed into the DoInsert method as a parameter.

Even better, you might want to look at bulk insert.