1

Willing to understand a scenario when deadlock is occurring in a table with 1 clustered and 1 non-clustered index due to multiple insert statements.

The solution was weird, where ignoreDuplicateKey was set to False, which resolved the problem. It was TRUE by mistake and deadlock occurred when it was true. I read a lot about index and locking, insert might cause the lock, but how come this property solved the problem?

Scenario - 1 table, 5 threads in parallel attempting to insert using Spring batch (5 multiple partitions), 1000 is the commit interval. 2 indexes - 1 primary key unique clustered index, 1 non-clustered unique index (with IgnoreDuplicateKey=True).

No triggers on the table.

~150 inserts failed out of 40000 due to deadlock. Number of deadlock failures increased with increased volume.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Harnish Shah
  • 11
  • 2
  • The table DDL, the insert statement, sample data and expected results would go a very long way to helping others to help you! – Mark Kram Jul 16 '19 at 13:33
  • Compare execution plans of an `INSERT` statement on a table with an index that has `IGNORE_DUP_KEY = ON` to one on a table that has `IGNORE_DUP_KEY = OFF`. You'll see the former has to perform more work because the server must insert additional steps to filter out duplicates first, and specifically, this will require locking on the unique index before it locks on the clustered index. This locking pattern (index, clustered + index) is susceptible to deadlocks if multiple statements occur in parallel, whereas the single operation you get with `IGNORE_DUP_KEY = OFF` isn't (or far less so). – Jeroen Mostert Jul 17 '19 at 07:45

0 Answers0