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.

HolgerRachut's avatar
HolgerRachut
Copper Contributor
Jan 23, 2025

I'm curious. What happens if you set the primary key to nonclustered?
 CONSTRAINT [PK_UsersAnswer] PRIMARY KEY NONCLUSTERED

sebd's avatar
sebd
Copper Contributor
Jan 23, 2025

Thanks for your reply! Unfortunately, it doesn't solve the problem.

I also tried on a different table that has a clustered primary key not composite (single column) and it gave the same error. Also note that I don't reach 100% of my CPU while stress testing (so the database is not even under extreme stress). I really don't get the problem here... I'll try on another machine just to check.