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

[Tyler-V]

Old thread but relevant to me now @borisdj

I have a solution that processes concurrent service bus messages in parallel through function apps resulting in the database getting hammered and an occasional deadlock.

Setting WithHoldlock to false does resolve my issue - are there any ramifications to setting this false if I am using BulkInsertAsync on new rows of data every time?

[borisdj]

HOLDLOCK means SERIALIZABLE isolation level, which locks the table, but can have negative effect on concurrency and also on performance.
So if WithHoldlock:false solves the issue it should be fine for Insert.
Simultaneous Update of same rows from multiple processes could possible have conflict or the question would be which process does first update.

Also there is config UseOptionLoopJoin - description in the ReadMe.