SqlBulkCopy inserts when inside a transaction prevents any other writes on a table

asp.net c# sql sqlbulkcopy sql-server

Question

In my web app users can insert lots of data at once, to improve performance I am using the SqlBulkCopy class. It runs multiple times for a single operation inserting into two different tables. If the user cancels the operation or it fails then I need the data to roll back so I wrap everything in a transaction using Isolation level Snapshot.

It was my understanding that using Snapshot isolation would allow other users to write/read to the tables simultaneously. However while one data upload is occurring it will block any other writes to the table until the entire parent transaction is complete.

Here is some simplified code that shows the issue. I am excluding a lot of the functions but the idea remains the same. I iterate over some number of an in memory collection, bulkcopy them to a table, retireve them back, and bulk copy to another table.

using (var transaction = myDbContext.Database.BeginTransaction(
System.Data.IsolationLevel.Snapshot))
 {
   var myCollectionOfObjects;

   while(!GetData(ref myCollectionOfObjects))  
   {     

    SqlBulkCopy bulkCopy = new SqlBulkCopy(myCon, transaction);

    //Sets the columns + rows
    SetUp(bulkCopy);

    bulkCopy.WriteToServer();
    //After the bulkcopy operation is complete
    // we retrieve the rows inserted and do another bulk copy to a different table

    var recentlyAddedRows = GetRecentlyAddedRow();

    SqlBulkCopy otherTableBulkCopy = new SqlBulkCopy(myCon, transaction);


    SetUpBulkCopyForOtherTable(otherTableBulkCopy);

    otherTableBulkCopy.WriteToServer();         
     }
    transaction.Commit();
 }

So if one user is currently inside this transaction, even if it is rolling back, all other write transactions to the table will be blocked so other users doing the same function or attempting to write to the table will be blocked.

Is this the expected behaviour and is there a way to get around this?

Edit

By looking at the locks applied in SQL it seems to be due to the bulkcopy class resulting in an exclusive lock (X) being set on the table object where as if you were to insert them one at a time there is only an intent lock applied on the table (IX). Still not sure if there is a way around this but I assume this is due to lock escalation.

Changing the Allow page lock on the tables indexs and changing the batch sizes of the bulk copies have got around the full lock in some of my tests but they are temperamental.

Popular Answer

The IsolationLevel only refers to reads and not to writes. If one of your client is writing data, the other client should be able to read the data as it was before the start of the transaction, however, it will not be able to write at the same time.




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