Forum Discussion
Deadlocks on High Frequency Updates
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.
Thanks for your answer. I have a primary key on the columns of the WHERE clause, so it's a clustered index. I've also tried having a primary key with a non clustered index but it didn't solve the bug.
I didn't measure (yet) if there is a significant difference in the number of deadlocks created between the clustered and non clustered primary key though. But as it varies a lot between each test, so it might be difficult to evaluate this precisely.
Sorry. I missed the PK definition containing all the columns in the WHERE clause.
Are all the concurrent UPDATE threads touching the same record or handful of records?