DaveWentzel.com            All Things Data

Locks and Deadlocks


I'm sure you've heard of deadlocks where spid X holds a lock on resource A and waits to lock resource B, while spid Y holds the lock on B and is waiting for resource A to be freed.
Conversion Deadlocks and Update Locks
But there are more types of deadlocks. Consider this situation: Spid X has a shared lock on resource A, and so has spid Y. This is no problem, since two shared locks are compatible with each other. Now X wants to convert the shared lock to an exclusive lock to update the resource. X has to wait for Y to release the shared lock to do this, and while X waits, Y decides to do the same and convert its lock to an exclusive lock. X is waiting for Y, and Y is waiting for X. Deadlock.
This is a fairly common situation, and to avoid it, update locks were introduced. Update locks allow a connection to read a resource, and at the same time, advertise its intention to lock it at some later point in time in order to edit the resource. An issue with update locks is that SQL Server doesn't know in advance if a transaction will want to convert a shared lock to an exclusive lock, unless it's a single SQL statement, like an UPDATE statement, that first has to read the table before it can update it. To use update locks in a SELECT-statement, you must explicitly specify that you want SQL Server to use them, and you do this with the lock hint UPDLOCK.
Lock Escalation Deadlocks
Occur under situations where too many finer grain locks were acquired and the optimizer feels that a coarser grain lock would result in better memory utilization.  Of course when this happens you risk a deadlock if other processes are dependent on similar locking.  You can see this occassionally with Bookmark LookupsLock escalation always escalates to a table lock...never a page lock or anything else.  Even using a lock hint (ROWLOCK) only guarantees the original lock granularity.  It does NOT prevent lock escalation. 
Another Common Scenario
I don't know if this has a term (should probably research), but a case exists where an UPDATE to a table that changes the key in the clustered index will cause a deadlock.  In this case the clustered index key is migrating to a new key, which potentially moves the row to a new page.  Also, it will need to update each and every non-clustered index since the non-clustereds "carry the key".  If another spid is attempting to access data from one of the affected indexes at the same time you risk deadlocks. 
Good index design states that you should not have clustered indexes that have changing keys that also have nonclustered indexes.  Beware of this issue.