Lock escalation problem on a trigger

I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day.

I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger.

The trigger consists of 10 updates to another table for slightly different criteria. The deadlock occurs in the trigger.

When a person makes an application and the job is running that's when the deadlock occurs. The application inserts into the same table as the scheduled job.

Looking at the trace it seems to happen when process 1 obtains a key lock, process 2 obtains a page lock, then process 1 escalates the key lock to a page lock and process 2 tries to obtain a key lock.

I've added missing indexes which seems to have helped but its still happening. I'm not a DBA so any advice on an approach to solving this would be appreciated.

I've added a link to the deadlock xml - this is from a test I did to duplicate the problem.

deadlock xml

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:

• a "Key Lock" on the index IX_tableA_Draw_Date.

• Owned by spid 58, mode = 'X' (Exclusive)

• Requested by spid 59, mode = 'U' (Update)

• 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):

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

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

• "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:

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

• 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

StackExchange Database Administrators Q#39747, answer score: 5