CODEWITHSUNDEEP
c#asp.netsql-serverwcfentity-framework
Ricketts
Ricketts

Reputation: 5213

Database table unresponsive while insert/updates run using Entity Framework w/ SqlBulkCopy

I'm using Entity Framework 5 on .NET 4.5 with SQL Server 2012. I have a function that downloads a lot of data from a 3rd party source and parses it into my database. It downloads, parses to my entities and inserts/updates to my db in batches of 2,500 records.

For the insert/update to my database, I'm making use of the SqlBulkCopy class. When it is doing the insert/update to my database, the table it's inserting/updating to seems to be partially unresponsive. If I run a simple select query from SSMS it will pull some records and then hang until the app has finished the bulk insert/update. This data in my db is fed to multiple sources via WCF service and if I try to load the data at one of the sources, it times out due to the database not responding with the data. Once the insert/update finishes, the WCF service and SSMS queries both continue to run fine.

I thought this may be due to the SqlBulkCopy so I removed the use of that class and just called a context.SaveChanges() which produces the exact same result. I then ran a test taking EF completely out of the picture and used classic ADO.NET (without SqlBulkCopy) and everything works great. I plugged the SqlBulkCopy back in, left EF out, and again, the table seems to be locked until it finishes.

So I am assuming the culprit here is both EF and SqlBulkCopy. Any ideas on how I can get around this and still use EF and SqlBulkCopy? or what may be causing this to happen?

I don't think examples of my code are necessary here, but if you would like to see, just let me know. Thanks in advance.

Upvotes: 0

Views: 1227

Answers (1)

Jeff S
Jeff S

Reputation: 7484

I'm thinking this is because of transactions. When you use SqlBukCopy or context.SaveChanges(), the inserts happen within a transaction. When you replaced that code with classic ADO.NET, did you wrap the inserts in a transaction or did you let each Insert occur inside it's own implicit transaction?

When a transaction is pending, it locks portions of the table being updated. If it inserts enough data, it might lock a lot of pages in the table or the whole table (this is called lock escalation).

The best way around this is to split the update into smaller chunks. There are a few other options (e.g. reading uncommitted data, etc.) but those have significant downsides and should typically be avoided.

Upvotes: 1

Related Questions