Deadlock Problem because of an Update Lock.

Question

We have a deadlock issue we're trying to track down. I have an deadlock graph (xdl) generated from Profiler. It shows the losing SQL statement as a simple Select statement, not an Update, Delete or Insert statement. The graph shows the losing Select statement as requesting a Shared lock on a resource **but also owning an Update lock on a resource**. This is what is baffling me. Why would a Select statement that is not part of an Insert, Update or Delete ever hold an Update lock on a resource?

I should add that the Update lock it owns is on the table being selected against by the losing Select statement.

EDIT: Please don't suggest using NoLock. Yes that would solve the problem but introduces a new one - a dirty read issue. This query is hitting a production server. What I really want to know is why would a Select statement issue an Update lock.

Answer 1

A select statement does create a shared lock. try select with the nolock hint. It would solve the problem.

eg select * from table(nolock)

Answer 2

Maybe the select was invoked with UPDLOCK hint? Anyway, can you use snapshot isolation and eliminate the problem?

Answer 3

You sure the SELECT owns the U lock?

As you know, U and X locks (as well as serializable S locks) are held for the duration of the transaction, not the statement. So it is perfectly possible for a transaction that issued an write to own an U lock, from a previously executed statement. Why it would own an U lock, as opposed to an X lock (ie. it owns an U lock that was not upgraded to an X) is a bit of a puzzle in itself, but I can envision couple of ways this can happen.
So it is perfectly possible to have a SELECT statement to own X/U locks if it is part of a multi-statement transaction. A standalone SELECT with an U lock under its belt, that is a bit unusual I reckon.

The typical SELECT vs. UPDATE deadlock occurs on index access order scenarios, like the one described in Read/Write deadlock. I trust you did a diligent read of the deadlock graph, but if is not too much to ask, can you share it?

Oh, and btw, have you considered read committed snapshot?