SQL Server deadlocks between select/update or multiple selects

Question

All of the documentation on SQL Server deadlocks talks about the scenario in which operation 1 locks resource A then attempts to access resource B and operation 2 locks resource B and attempts to access resource A.

However, I quite often see deadlocks between a select and an update or even between multiple selects in some of our busy applications. I find some of the finer points of the deadlock trace output pretty impenetrable but I would really just like to understand what can cause a deadlock between two single operations. Surely if a select has a read lock the update should just wait before obtaining an exclusive lock and vice versa?

This is happening on SQL Server 2005 not that I think this makes a difference.

Answer 1

This can happen because a select takes a lock out on two different indexes, meanwhile an update takes a lock out on the same indexes in the opposite order. The select needs two indexes because the first index doesn't cover all of the columns it needs to access; the update needs two indexes because if you update an index's key column you need to take a lock on it.

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx has a fantastic explanation. Suggested fixes include adding an index that covers all of the columns the select needs, switching to snapshot isolation, or explicitly forcing the select to grab an update lock that it wouldn't normally need.

Answer 2

I'm surprised no one has mentioned the WITH (UPDLOCK) locking hint. It's very useful if you have deadlocks involving e.g. two select-insert pairs running in parallel.

In SQL Server, if you issue the selects with WITH (UPDLOCK), the second select will wait until the first select is finished. Otherwise they get shared locks, and when they simultaneously try to upgrade to exclusive locks, they deadlock.