Why SQL Server Prefers Page Locking Over Row Locking

A create script for tableA including all indexes and the execution plans for the two UPDATE statements involved are required for a definitive solution. That being said, we can still combine the deadlock graph provided with knowledge of how SQL Server performs updates and have a very good chance of resolving this issue.

This deadlock involves two spids performing UPDATES on tableA, 58 & 59.

Spid 58's Query:

Update b set  syd_id=d.syd_id    
        from tableA b    
        inner join tableC d with (nolock) on b.syd_pers_id=d.syd_pers_id    
        --and b.game='es_lotto'    
        and b.game=d.game  and isnull(b.syd_id,0)=0     

Spid 59's Query:

Update b set Draw_Date=d.draw_date    
        from tableA b
        inner join tableB d with (nolock) on b.draw_no=d.draw_no    
        and left(b.game,2)='UK' and b.Draw_Date is null     

In this deadlock, there are two resources involved:

1. a "Key Lock" on the index IX_tableA_Draw_Date.
   • Owned by spid 58, mode = 'X' (Exclusive)
   • Requested by spid 59, mode = 'U' (Update)
2. a "Page" lock for a different, unspecified index on tableA
   • Owned by spid 59, mode = 'IX' (Intent Exclusive)
   • Requsted by spid 58, mode = 'U' (Update)

There are also 3 different lock ownership modes in use (U, X, IX):

1. "U" or Update Lock. Conceptually, the sql plan for an UPDATE statement has two parts: A "select" part in which the data to modify is identified, and a "modification" part where the modification actually happens. Update locks are taken during the "select" part to protect the rows from modification until we get around to actually modifying them (upon which time an X lock is acquired). Update locks are compatible with "S" (Shared) locks, so readers don't get blocked, however, they are incompatible with X, IX and other U locks.
2. "X" or Exclusive Lock. Exclusive locks are used to protect data being modified during inserts, updates and deletes. They are incompatible with all other lock types, and ensure that only one writer can access a resource at the same time.
3. "IX" or Intent Exclusive Lock. Intent Exclusive locks are taken on higher level resources before lower level resources get X locks. For example, before an X lock on a row is taken, an IX lock is taken on both the associated page and the entire table. IX locks indicate that X locks exist at a lower level. They can also be changed into X locks as part of lock escalation. IX locks are incompatible with X locks, so if two transactions have IX locks on a table, then neither transaction can escalate their page or row locks to the table level. Intent locks (including IX) are an optimization that allow SQL to check one higher level lock instead of checking thousands of lower level locks.

Now, let's examine spid 58's UPDATE query in detail. We know from the deadlock graph that spid 58 has an exclusive lock on a row in IX_tableA_Draw_Date. Therefore, we know that syd_id is either in the index key, in the clustered index key, or is an included column (I'm going to ignore the possibility that spid 58 already owned the lock from a previous statement in the trigger).

Spid 58 is also attempting to acquire an update lock on a page in a different index. This tells two things:

1. We know that the select portion of the update is still reading and locking data after the modification portion has started. This implies that there are no blocking operators affecting the rows from tableA in the plan. For example, if rows were read from tableA, sorted, and used in a MERGE join; then all the rows would be read (and U-locked) before any exclusive locks are taken. That isn't the case.
2. We're attempting to take an Update lock on a PAGE, not a ROW, so it's likely that we're doing an index scan.

We can also assume that a relatively small number of rows are expected to be updated (Due to the multi-column join combined with updating nulls or 0's in an insert trigger, this doesn't feel like a query that updates a large percentage of rows). Taking all this into consideration, it is likely that the UPDATE is reading rows from tableA using a scan on our mystery index (don't know if it's clustered or non-clustered ) and performing a LOOP JOIN against tableC to get the tableC.syd_id.

Performing a similiar analysis for spid 59, it is likely that we're seeking into IX_tableA_Draw_Date where "Draw_Date is null", reading (and taking update locks on ROWS), LOOP JOIN against tableB, then taking a X ROW lock and updating our "mystery" index (the IX lock on the page implies that there are X locks on rows).

Where are we? And what do we do next?
We know we've got two update statements. Both are reading and writing to the same pair of indexes (with the reading and writing swapped). Both are waiting for an update lock after they've already updated some rows. Spid 58 is reading pages, 59 is reading rows. Both are writing and locking rows. Lock escalation isn't a factor, as row locks -> table locks ( not page locks).

If Spid 58 was taking row locks instead of page locks on the mystery index, then you would only have deadlocks if both queries updated the same row. You could use a ROWLOCK query hint to make that happen, however you run the risk of increased lock escalation to table locks, which could be worse.

Another approach is to separate the update into two parts.

select id, --Not sure what the PK is...
    syd_id
into #tmp 
from tableA inner join tableB (nolock) on b.syd_pers_id=d.syd_pers_id    
    --and b.game='es_lotto'    
    and b.game=d.game  and isnull(b.syd_id,0)=0

UPDATE tableA SET syd_id = t.syd_id from tableA d inner join #tmp t on t.id = d.id 

This would change the order of locks taken as the non-clustered indexes will only be locked for writes, not reads.

Recommendation

Deadlocks are a natural by-product of RDBMS's. Even if you do everything "right" you can't always eliminate them. At 3-4 Deadlocks per day, the correct approach is probably to just wrap the statement in some error handling so that it is re-tried if a deadlock occurs.

http://msdn.microsoft.com/en-us/library/aa175791(v=sql.80).aspx has an example for SQL 2005. However, since this is in a trigger, you may need to add the error handling outside of the INSERT statement that fires the trigger.

I resolved the deadlocks by using a NOLOCK hint in the first select statement. No dirty reads as this is the only process that manipulates the rows and only does so once.