SQL Server - Multiple inserts on single table causing deadlocks RRS feed

  • Question

  • Hi All,

    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).

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

    If not possible to respond here, can you please help or provide your email address where I can contact you?

    Tuesday, July 16, 2019 12:13 PM

All replies

  • Do you have a clustered key on the identity column?

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 12:26 PM
  • Do you have an example of a deadlock trace that we can look at? Without any information about the deadlock as such, it is difficult to comment.

    Erland Sommarskog, SQL Server MVP,

    Tuesday, July 16, 2019 9:10 PM
  • An interesting question, but first, were you also (accidentally or intentionally) actually submitting some duplicate keys to be inserted, before and after the change?

    Are you still doing so and now getting error messages, and handling the error cases correctly?

    If it's just a setting and there never actually were any duplicate keys then, um, I don't know.  I would suppose it was generating different locks depending on the setting, maybe setting range locks before and now using just row locks that are better at avoiding deadlocks.

    What is your transaction isolation level, and how many rows were inserted by each insert, and was it part of a multi-statement transaction, or were there concurrent multi-statement transactions to deadlock with?


    • Edited by JRStern Tuesday, July 16, 2019 11:46 PM
    Tuesday, July 16, 2019 11:46 PM