3

I'm currently using EntityFramework.BulkInsert and this is wrapped within a using block with a transaction scope to produce batch saving of entities. e.g.

using(var tranScope = new TransactionScope())
{
    using(var context = new EFContext())
    {
        context.BulkInsert(entities, 100); // batching in size of 100
        context.Save();
    }
    tranScope.Complete();
}

I need to determine if there is a dependency on using the BulkInsert to do bulk insert and MSDTC. I have done a bit of testing changing the Max Pool Size to a variety of low and high numbers and running load tests with 10-100 concurrent users with the MSDTC service turned off (all on local box at the moment). So far I cannot get it to throw any 'require MSDTC turned on' type of exceptions. I am using SQL2014, EF6.X and .net 4.6, MVC 5. I understand that SQL2014 is likely using lightweight transactions in this case, I have used perfmon to confirm that if Max Pool Size is set to X in the connection string then the perf counter NumberOfPooledConnections reflects the same number X, and when I change it in the connection string to something else this is also reflected in the counter (so at least that is working as expected...). Other info - Im using integrated security and have not set anything in the connection string for Enlist=...

The bulk insert package is located here https://efbulkinsert.codeplex.com/ and under the hood its looks to be using sqlBulkCopy. I'm concerned that even though I cannot reproduce the dependency on MSDTC in my testing, and even though Im not explicitly opening 2 connections within the same transaction scope, there is a still a dependency on MSDTC just by the pure nature of the batching?

Can anyone confirm a yay or nay..., thanks.

dvr
  • 51
  • 7

1 Answers1

0

What you are using is a lightweight transaction so it does not need MS DTC. All the work will be handled by 1 SQL Server machine. If one server started the transaction and then another server does the rest, then MS DTC is required. In your case it is not. Here is a quote from MSDN:

A promotable transaction is a special form of a System.Transactions transaction that effectively delegates the work to a simple SQL Server transaction.

If more than 1 physical computer is needed to perform the transaction then you need MS DTC.

You should be fine.

CodingYoshi
  • 25,467
  • 4
  • 62
  • 64
  • thanks for the input, I dont believe its down to the number of machines but rather number of connections and transactions on those connections. For example a client machine that has a loop wrapped with a transaction, if in the iteration of the loop the code news up a SqlConnection - it is likely going to promote the transactions if each new connection doesnt explicitly utilize the same connection from the pool. My concern is the batch functionality and the guarantee that each batch will utilize the same/original connection - I'm just not certain of that at the moment, I need the guarantee... – dvr Nov 22 '16 at 04:49
  • there are some good examples in this article http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines – dvr Nov 22 '16 at 04:52