CODEWITHSUNDEEP
sql-servert-sqldeadlock
user173552
user173552

Reputation: 1229

SQL Server 2008 Dead lock issue

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.

Upvotes: 1

Views: 718

Answers (2)

AaronLS
AaronLS

Reputation: 38367

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.

Upvotes: 0

gbn
gbn

Reputation: 432200

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

Upvotes: 4

Related Questions