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

You ought to read up on transaction isolation: http://msdn.microsoft.com/en-us/library/ms173763.aspx

Answer 2

I once bookmarked a good article about Advanced SQL Server locking at SQL-Server-Performance.com. That article goes beyond the classical deadlock situation you mentioned and might give you some insight in your problem.

Answer 3

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...

Answer 4

Locks between single queries can happen as they lock single rows, not the entire table:

The update query gets an update lock on a few rows in a table, and the select query gets a read lock on some other rows in the table. The update query then tries to get an update lock on the rows that are read locked, and the select query tries to get a read lock on the rows that are update locked.

It can get even more complicated with escalading locks, i.e. the database decides that there are too many single rows locked by a transaction so that it should be escalated into locking a section of the table or the entire table. This means that the lock may affect rows that aren't directly involved in the query.

Answer 5

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 6

Have you tried to run SQL Deadlock Detector (http://www.sqlsolutions.com/products/sql-deadlock-detector/index.html) to see what exact processes are involved in locks and what objects they are referencing?

This would be a good starting point.

Answer 7

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.

Answer 8

My guess is that the select-statement aquires a read-lock, when you come with the update-statement, then it needs to upgrade to a write-lock.

Upgrading to a write-lock requires that all other read-locks are removed (Their select-transactions completes). But if another process already have the brilliant idea to upgrade to a write-lock, then you suddenly have two processes waiting for each other to release the read-lock, so they can get the write-lock.

If using select-for-update (UPDLOCK) then it will aquire a write-lock from the beginning and then you don't have the deadlock issue.