Blocking issue due to blocking update
We have a dead end problem that we are trying to track down. I have a dead end plot (xdl) generated from Profiler. It shows the played SQL statement as a simple Select statement, not an Update, Delete, or Insert statement. The graph shows a losing Select command as a request for a shared resource lock **but also owning an Update lock on a resource**
. This is what puzzles me. Why does a Select statement that is not part of an insert, update, or delete hold the update lock on the resource?
I should add that the update lock it owns is in the table that is being picked against the Lost command.
EDIT: Please don't suggest using NoLock. Yes, that would solve the problem, but it introduces a new one - the dirty reading problem. This request hits the production server. I really want to know why the Select statement is throwing an update lock.
a source to share
Are you sure SELECT owns the U lock?
As you know, U and X locks (as well as S serializable locks) are held throughout the entire transaction, not the statement. Thus, it is entirely possible that the transaction that issued the record must have a U lock from a previously executed statement. Why it will have a U lock as opposed to an X lock (i.e. it owns a U lock that hasn't been upgraded to X) is a bit of a puzzle in itself, but I can imagine a couple of ways it could happen ...
Thus, it is quite possible that a SELECT statement has its own X / U locks if it is part of a multi-op transaction. A standalone SELECT with a U lock under its belt, which is a bit unusual I find.
A typical SELECT and UPDATE deadlock occurs in index access order scripts, as described in "Lock Read / Write" . I hope you have taken a close look at the dead end schedule, but if not too much to ask, can you share it?
Oh, and by the way, did you think you read the captured photo?
a source to share