SQL Server 2008 Dead lock issue
- by user173552
I got into a deadlock issue where I am struggling find the root-cause...The Deadlock graph suggests that an UPDATE statement became the victim over a SELECT statement...
What puzzles me is that the UPDATE statement is trying to acquire an index on some other table that is never referred in update statement...
This is how my UPDATE statement looks like...
UPDATE Table set col1 = @P1 where col2 = @P2
This statement acquired a X lock on the col2 index, but also tries to acquire an index on a column defined in some other table that is no way related to the UPDATE statement...
And the SELECT statement that won the deadlock situation had nothing to do with the table or index in the update statement but tried to acquire an index on the table in UPDATE statement. eventually causing the DEADLOCK.
