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

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, retrieve 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 indexes and changing the batch sizes of the bulk copies have got around the full lock in some of my tests but they are temperamental.

c++sharpsqlasp.netsql-serversqlbulkcopy


Solution 1:[1]

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.

Solution 2:[2]

One thing you can do to reduce the blocking time is to bulk-copy into a (unique) staging table rather than directly to your target table. This doesn't have to be in a transaction at all. Once all your data is in your staging table, copy it into your target table in a transaction. This will not completely stop the possibility of blocks. However copying data from within a database will generally be fast, especially as the data is likely to be cached. The only potentially tricky bit is creating uique staging tables (if they are not unique you just move the problem from one table to another).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 John Denniston

Related Questions