Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

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

share|improve this question
1  
Can you post the code for the trigger and the tables involved ( including any indexes ) ? –  druzin Apr 17 '13 at 3:25
add comment

2 Answers

up vote 4 down vote accepted
+50

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.

share|improve this answer
    
Thank you for the excellent answer, I'm back in the office Thurs so will try your suggestion of separating the update into two parts -see how I get on with that. I really can't post more about the triggers/indexes/execution plan (customers db). But I really appreciate your suggestion for tackling this. –  WooHoo Apr 17 '13 at 7:32
add comment

The two locks are on different tables, so you have the standard "two processes update two resources in oposite order" deadlock problem.

Change the order in which the locks are taken so that both procedures follow the same order.

share|improve this answer
    
it looks to me like the lock is on the same table? And it happens in a trigger. So updates are in the same order. –  WooHoo Apr 11 '13 at 12:43
    
Is the object name you blanked out actually the same? Are both processes executing the trigger? Can you post the deadlock-xml? –  Sebastian Meine Apr 11 '13 at 13:15
    
yes the blanked out bit refers to the same table. I will post the xml though will need to replace some of the db/table names. –  WooHoo Apr 11 '13 at 13:22
    
I've added a link to the deadlock xml. –  WooHoo Apr 11 '13 at 13:57
1  
can you publish the trigger as well? –  Sebastian Meine Apr 14 '13 at 23:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.