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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.