Forum Discussion

sebd's avatar
sebd
Copper Contributor
Jan 22, 2025
Solved

Deadlocks on High Frequency Updates

Using SQL Server 2022, I'm stress testing an UPDATE statement. I'm using a python script to send parallel requests to the database. The problem is that, as soon as the number of parallel requests exc...
deadlock
sql
testing
  • VladDBA's avatar
    VladDBA
    Jan 23, 2025

    Right off the bat: do you have a nonclustered index on the columns in the UPDATE statement's WHERE clause?

    CREATE INDEX UsersAnswer_ncix ON UsersAnswer(For_Question,For_Quiz,FK_Answer);

    If not, create it and monitor for changes in deadlock occurrence.

    P.S. I'd recommend against turning the table into a heap by turning the PK into a NC index.

olafhelper's avatar
olafhelper
Bronze Contributor
Jan 24, 2025

Rerun the transaction. 

I don't understand what here the problem is? Deadlocks can occur in several different situation.

Application should be aware of and should do, what the error message already says: Rerun the transaction. 

  • sebd's avatar
    sebd
    Copper Contributor
    Jan 24, 2025

    I was mainly worried that I misconfigured the database in terms of concurrency, or that the index was missing some parameter. This error slows down my platform, but I guess if the only solution is to rerun the transaction, than I need to upgrade the CPU of the server and maybe rerun the transactions with exponential backoff.

Resources