Transaction was deadlocked on lock resources

Trying to run an update on table. I get the following

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

Query looks like this.. any ideas?  

    UPDATE dbo.ae_dt2
    SET
      field3 = Src.Item
    FROM Reps Src, dbo.ae_dt2 Tgt
    WHERE
      Src.RepNo = Tgt.field3
LVL 1
JElsterAsked:
Who is Participating?
 
PadawanDBAOperational DBACommented:
So there are two+ players in a deadlock.  The victim and the winnars!  You're going to need all of those to figure out who was locking what resource and how they were all deadlocked.  At that point, you're going to probably be looking at a few options, namely: reducing the duration of the queries (performance tuning), ensuring that all transactions are using the smallest necessary transaction size (smallest unit of work that should be committed or rolled back as a unit), playing with isolation levels, etc.  Here's a ton of information on deadlocks and how to identify/troubleshoot them: https://www.sqlskills.com/blogs/jonathan/category/deadlock/
0
 
Scott PletcherSenior DBACommented:
That UPDATE could update every row in the table, assuming they all have a match in the other table.  That would lock the entire table while it runs.

If possible, you should do the update in chunks of rows rather than the entire table at once.  You'll need another unique column in table ae_dt2, such as an identifier or guid column, to control the UPDATEing.
0
 
Anthony PerkinsCommented:
Make sure there are indexes on Reps .RepNo = dbo.ae_dt2 .field3 and re-write it (for clarity) as:
   UPDATE   Tgt
   SET      field3 = Src.Item
   FROM     Reps Src
            INNER JOIN dbo.ae_dt2 Tgt ON Src.RepNo = Tgt.field3

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.