Please help to understanding deadlock problem ...

[nmalhot]

I think I understand the basics of what causes deadlocks but I keep having a deadlock on this update statement and I can't understand why or how to fix the problem. Any insights would be much appreciated!


UPDATE player
SET money = money + a.salary
FROM #wages a (NOLOCK), player b (NOLOCK)
WHERE a.playerID = b.playerID

The temp table #wages is populated as needed with playerID and salary columns.

I thought this update would only lock the single resource (player) and so I'm not understanding how it could cause a deadlock.

There's constant activity against my player table by other activity but there is definitely no other activity for #wages because it is created within this stored procedure.

*very confused*

 

[ptheriault]

If you look in the SQL Log it will give you the spid that you are deadlocking with. also you can use nolock on an update. It must lock the table to update it. You could try to provide a lock hint like page lock but it very unlikely that it can accomplish the update with just a page lock

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Oops, I had a type O. That should read you can't use nolock with an update

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

after further review I have found the reason for your deadlock. You are deadlocking with yourself.

This should work without a deadlock

UPDATE player
   SET money = money + #wages.salary
  FROM #wages 
 WHERE #wages.playerID = player.playerID

- Paul
- Database performance looks fine, it must be the Network!

 

[nmalhot]

Awesome. Paul, thanks so much for your time and insight. I'm making the change and keeping my fingers crossed!

 

[gmmastros]

You should also consider using ANSI joins. The syntax would be...

UPDATE player
   SET money = money + #wages.salary
  FROM #wages 
       Inner Join Player
         On #wages.playerID = player.playerID

From a performance perspective, you may be better off using a table variable instead of a temp table. You may also want to index the temp table or table variable to get better performance.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom