December 3, 2018 at 12:25 pm
Hello
We have a table with non-clustered index. When a process is run which does multiple updates on the same table on different rows, this sometimes creates a deadlock, Does creating clustered index on the pk of the table resolve this issue?
More to add on this, there is also a trigger on this table. When tried to trace deadlock this trigger is also captured as deadlock victim. Does it help if the trigger includes transaction isolation levels /no locks on the object.
The table is also used by multiple processes .
December 4, 2018 at 3:50 am
So, without seeing the details, this is a speculative answer.
All deadlocks are fundamentally performance issues. If the transactions clear fast enough, you'll never see a deadlock. So, if you can improve the performance of the query, it's likely that you'll reduce deadlocks. Now, with triggers tossed in to a heap table, yeah, it's pretty likely you have tuning opportunities. As to which ones are going to work, I can't say without knowing a lot more about the situation. It sounds like it's not simply a "add an index and it fixes everything" situation. It sounds like you probably have multiple paths through the data with multiple accesses, especially from the trigger, that leads to the deadlocks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply