SQL Server deadlocks between select/update or multiple selects?

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.

Dead link. Does anyone have an update? – Jeff Davis Aug 24 at 15:25 updated link... – MicSim Aug 24 at 15:56.

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. blogs.msdn.com/bartd/archive/2006/09/25/... 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.

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.

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.

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

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.

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

I think the posters question was more specific than a general rant on isolation levels may cure. – eckes Sep 13 at 16:06 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. – Pontus Gagge Sep 14 at 15:30.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.