1

I have this odd deadlock issue which has been bugging me. The scenario is as follows:

Deadlock_Graph

As per the photo, the 2nd stored procedure cannot update data for that Tournament Number until it's been first inserted in the table. How is it possible for it to deadlock in this scenario and how can I possibly reproduce it and avoid it?

Thanks.

2nd screenshot:

Deadlock_Graph 2

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Claudio
  • 230
  • 1
  • 2
  • 10
  • Can we see the procs? – Aaron Dietz Aug 16 '17 at 19:55
  • No way for us to answer this question 100% with information given, but I'm guessing the diagram does not include linked tables and those table are causing the deadlock. Another possible reason is SP2 is updating multiple rows and makes one update but does not release table lock while it waits for the next update. – Hogan Aug 16 '17 at 20:07
  • @AaronDietz - Updated, I added a 2nd photo. – Claudio Aug 16 '17 at 20:20
  • @Hogan - Please see 2nd photo. No, SP2 only updates one Tournament per call. – Claudio Aug 16 '17 at 20:21
  • @Claudio -- so you have a tour table and a tour status table. Are you referencing both tables in both SPs? Are you referencing them in the same order? In the same TRANS? Are there any triggers on the tables? Are the triggers fired in the same order. The problem with these diagrams is they are way to simple. We need to see all the code and/or the log information from the deadlock to know why this is happening. ALSO, it could just be a slow server. Often SQL Server will flag some slow queries as deadlocks when they are just slow not locked. `WITH #NOLOCK` everywhere you can, might help – Hogan Aug 16 '17 at 20:25
  • @Hogan - Sorry, I didn't think it's relevant but it actually is. The Tournament table is being updated as well in the 2nd SP, but right after the Tournament Status one. Yes, the order is the other way around and I assume that by switching it to be the same order in both SPs it might do the trick, but I wasn't able to reproduce it. Also, it's not a slow server, it's very fast. These SPs execute in milliseconds. No triggers in either tables either. – Claudio Aug 16 '17 at 20:30
  • There is your problem... the order is different -- this is why a deadlock occurs -- I could draw you a diagram on the whiteboard over here... – Hogan Aug 16 '17 at 20:35
  • @Hogan - But it's very odd, cause until the transaction completes in SP1, we'll never get a Tournament Number to pass in to SP2, how could that be explained? Should the Tournament Numbers not be the same for the deadlock to occur? – Claudio Aug 16 '17 at 20:36
  • @Claudio -- Not odd. Explained in answer – Hogan Aug 16 '17 at 20:43
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/152099/discussion-between-claudio-and-hogan). – Claudio Aug 16 '17 at 21:11

1 Answers1

1

See comments on main question for pre-amble. The issue here is two tables (A and B) in a different order so it happens like this:

  1. SP1 finishes and releases id n1.
  2. SP2 takes id n1 and locks table B.
  3. SP1 starts with new number and locks table A. then trys to lock table B but SP2 has it.
  4. SP2 now tries to lock table A but SP1 has it.

deadlock

note: This is basically how every deadlock happens just some complicated fluff around it. It is all about the order you lock the tables and release them. If everything locks and releases in the same order you should never get a deadlock.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • It didn't work @Hogan :-( I tried it but the 1st SP just inserts the new tournament numbers without any issues, even if a lock is placed on the table and SP2 doesn't deadlock. – Claudio Aug 16 '17 at 21:42
  • @Claudio -- What didn't work? It sounds like changing the order on SP2 make it not deadlock -- wasn't that the goal? – Hogan Aug 17 '17 at 02:25
  • Yeah, but I'm trying to first reproduce it as on the Development environment it doesn't deadlock either way. Is it one of those which can't be reproduced (even when using WAITFOR DELAY '00:00:10' for example)? – Claudio Aug 17 '17 at 05:24
  • @Claudio -- it is hard to reproduce because a simple wait does not work in all cases -- you have to time it so both sides hit the "in between" part at the same time and then one gets to the 2nd one before the other. So you can force it but you have to be able to control step #1 and step #3 and the time between. -- can you do that? – Hogan Aug 17 '17 at 13:26