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.
 Update Operation.TrTable
 Set    RecordId = RecordTABLE.newRecordId
 From  Operation.TrTable tr
    Inner Join (
        SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
        From  Operation.Record r
        WHERE r.KeyM > 0
      ) RecordTABLE
      ON RecordTABLE.MappingKey = tr.KeyM
 WHERE tr.KeyM > 0  



UPDATE Operation.Record SET KeyM = 0 
WHERE KeyM > 0 

UPDATE Operation.TrTable SET KeyM = 0
WHERE KeyM > 0

The error is:

Transaction (Process ID 93) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

share|improve this question

closed as unclear what you're asking by Paul White, Mark Storey-Smith, StanleyJohns, dezso, Nick Chammas Jul 11 '13 at 9:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Do you have the deadlock graph (XML) to post? –  Thomas Stringer Apr 11 '13 at 12:14
    
sorry it was on the remote server i dont have deadlock graph yet but it is very strange to have this deadlock :( –  user22410 Apr 11 '13 at 12:21
    
the error is : Transaction (Process ID 93) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. –  user22410 Apr 11 '13 at 12:22
    
Is it R2 or 'plain' 2008? –  dezso Apr 11 '13 at 13:20
    
This link will help you find the deadlock graph. In it you will be able to see both sides of the deadlock. sqlservercentral.com/articles/deadlock/65658 –  Kenneth Fisher Jun 10 '13 at 18:39

1 Answer 1

[EDIT: I've modified the lock suggestion to XLOCK, per the comments below.]

Without really understanding any of the details about your particular situation (you haven't shared any!), I'd guess you are running the above code in multiple sessions concurrently. You may be able to prevent deadlocks by modifying your code as such:

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Operation.TrTable
    SET    RecordId = RecordTABLE.newRecordId
    FROM  Operation.TrTable tr WITH (XLOCK)
        INNER JOIN 
        (
            SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
            FROM  Operation.Record r WITH (XLOCK)
            WHERE r.KeyM > 0
        ) RecordTABLE ON RecordTABLE.MappingKey = tr.KeyM
    WHERE tr.KeyM > 0; 

    UPDATE Operation.Record SET KeyM = 0 
    WHERE KeyM > 0;

    UPDATE Operation.TrTable SET KeyM = 0
    WHERE KeyM > 0;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    /*
        handle any errors here - perhaps send an email
        or raise a custom error messsage
    */
END CATCH
COMMIT TRANSACTION;

Note I've added WITH (XLOCK) to your FROM clauses, since the same records you are SELECTing will later be updated. WITH (XLOCK) will place an exclusive lock on the selected records preventing them from being read by other sessions until the current transaction completes. As Jon Seigel said in his comment below, we really need more information about your exact issue to make an accurate diagnosis.

share|improve this answer
1  
Update locks don't prevent concurrent reads. In fact, allowing concurrent reads is the entire reason update locks exist. Also, using the UPDLOCK hint specifies to take real update (U) locks, not intent-update (IU) locks (I'm not sure if you actually meant that or not). The lone UPDLOCK hint on Operation.TrTable is default behaviour as it's an UPDATE statement for that table. In any event, I think we need to see a deadlock graph before drawing any conclusions. –  Jon Seigel Apr 11 '13 at 17:22
    
Thanks for pointing out my mistake, Jon. If I were to replace the UPDLOCK with XLOCK, that would have the desired effect, would it not? –  Max Vernon Apr 11 '13 at 17:24
    
Yep, that would do it. Then, assuming the rows in the query are accessed in the same order from execution to execution (I'm not sure if this is a good assumption or not), that should prevent deadlocks from happening. –  Jon Seigel Apr 11 '13 at 17:31
2  
...assuming the problem is what you identified as the problem. (We don't know.) –  Jon Seigel Apr 11 '13 at 17:39

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