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.

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

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

Solution 3

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.

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

Solution 5

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

View more solutions

Comments

[Prashant]

I think the posters question was more specific than a general rant on isolation levels may cure.

[Pontus Gagge]

I wouldn't exactly characterize PoTP as a rant. Rob specifically asked for help in understanding transactionality, and seemed to have some trouble understanding the tradeoffs involved. If pointing out useful literature is ranting, then I'm probably foaming at the mouth all the time! YMMV.

[Mahmood Dehghan]

Rolf Kristensen explained WITH (UPDLOCK) perfectly in his answer.

[Ehsan Mirsaeedi]

Read And Update locks are compatible. So, how can this scenario happen?

[Radu Damian]

The link is no longer active, but you can read the article here: web.archive.org/web/20061210085214/http://blogs.msdn.com/bar‌​td/…