Getting Deadlock Issues while doing Bulk insert

[srini1978]

We are using the bulk insert package and in regular tests, the inserts go through fine but in load tests where multiple web jobs are trying to do bulk inserts, we are getting deadlocks. Any pointers or settings that you can tell us how to solve the issue

[borisdj]

That's a tricky issue, and the problem is in the SqlBulkCopy itself.

According to How to increase performance of SqlBulkCopy
You could set TableLock (in BulkConfig.SqlBulkCopyOptions), then do test again and see if it helps.
You may also try setting WithHoldlock to false (true is default) that is used by MERGE, but I'm not sure if Holdlock is connected to this issue.

--
EDIT:
Another way that might help with deadlock is adding lock segment to your code, in order to encapsulate calls to Bulk method:

class SomeClass
{
  Object lockObject = new Object();

  void SomeMethod()
  {
    lock(lockObject )
    {
      // BulkMethod call and other code ...
    }
  }
}

--

Also in previous link and these:
MSDN: Deadlock when using sqlbulkcopy
Importing Data in Parallel with TableLock
it is said that removing Indexes (clustered one as well) from destination table should eliminate lock problems. However library currently does not have options that would remove Indexes and then restore them after import. Also it's questionable whether removing Indexes is ideal solution. But you can test it by removing them manually just to check if that is one of solutions.

If you want to dig further:
SO: sqlbulkcopy-causing-deadlock
enabling trace flags on the SQL Server and log the deadlock graph to the ErrorLog
Detecting and Ending Deadlocks
snapshot-isolation-in-sql-server
RG: sql-server-deadlocks-by-example

More info:
ADev: sqlbulkcopy-to-sql-server
WP: SqlBulkCopy-Performance
Q: SQL-Server-deadlocks
Q: how-to-avoid-sql-deadlock
SE: what-are-the-main-causes-of-deadlocks-and-can-they-be-prevented
SE: tablock-hint-triggers-deadlocks

[muratcanoguzhan]

I am thinking apply the object locking solution that you mentioned . Do you think object locking could cause another issues ? @borisdj