SQL Server deadlocks between select/update or multiple selects

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.

sql-server sql-server-2005 deadlock

Edit Report

Read up properly on transactions and isolation levels: for a somewhat dense but fairly thorough and technology neutral work, see Principles of Transaction Processing. It rocked my world (and gave me quite a few headaches!).

I'm not sure what you're having trouble with, or what isolation level you are using. But consider this: for all the database engine knows, if you do reads in one transaction, how can it tell whether or not you are going to do a write later on? High isolation levels require locking whenever a read is done, possibly on the entire table to protect against phantom reads, since the data may affect a write later on.

Would you want the database to wait arbitrarily long for an exclusive lock on your data? Take a look at your isolation levels throughout, and whether you are unnecessarily running a series of reads as an isolated transaction. It's not always easy to determine how filthy reads you can tolerate, though...

answered 2009-03-19T12:16:00.413

SQL Server deadlocks between select/update or multiple selects

1 Answer

Your Answer