SQL Server 2008 Dead lock issue

Question

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.

Answer 1

The update transaction/lock will include things like:

• triggers
• foreign key validations (is col1 an fk?)
• check constraints (on col1 using udf)
• indexed views (that uses table.col1 or table.col2)

Any of these could cause the apparently unrelated table to have a lock

Answer 2

In addition to the other excellent answers, something to consider is a select typically acquires a shared read lock, which allows a series of selects to maintain a shared lock on the resource. The update statement may never have the opportunity to be granted an exclusive lock. Normally though the engine does a good job of preventing this type of starvation, but if you are using the update in a transaction with another statement(s) then this can complicate the problem. If this is the case provide more details of what is occuring in the transaction.