CODEWITHSUNDEEP
t-sqlstored-proceduresdeadlocksql-server-2016
Claudio
Claudio

Reputation: 250

Deadlock between 2 queries from 2 stored procedures

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

Upvotes: 1

Views: 289

Answers (1)

Hogan
Hogan

Reputation: 70513

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.

Upvotes: 1

Related Questions