Why do deadlocks happen in SQL Server?

Question

So as I understand it, SQL deadlocks happen when a SPID is busy processing another query and it can't be bothered to run another one because it's so busy right now. The SQL Server "randomly" picks one of the queries to deadlock out of the resources asked for and fails it out, throwing an exception.

I have an app running ~ 40 instances and a back-end Windows Service, all of which are hitting the same database. I'm looking to reduce deadlocks so I can increase the number of threads I can runs simultaneously.

1. Why can't SQL Server just enqueue the new query and run it when it has time and the resources are available? Most of what I'm doing can wait a few seconds on occasion.
2. Is there a way to set Transaction Isolation Level globally without having to specify it at the onset of each new connection/session?

Answer 1

Your understanding of deadlocks is not correct. What you've described is blocking. It's a common mistake to equate the two.

A deadlock occurs when two separate transactions each want different resources and neither will release the one that they have so that the other can run. It's probably easier to illustrate:

SPID #1 gets a lock on resource A SPID #2 gets a lock on resource B SPID #1 now needs a lock on resource B in order to complete SPID #2 now needs a lock on resource A in order to complete

SPID #1 can't complete (and therefor release resource A) because SPID #2 has it SPID #2 can't complete (and therefor release resource B) because SPID #1 has it

Since neither SPID can complete one has to give up (i.e. be chosen by the server as the deadlock victim) and will fail.

The best way to avoid them is to keep your transactions small (in number of resources needed) and quick.

Answer 2

Deadlock is where two threads of processing are both being held up by the other ( it can be more, but two is sufficiently complex ). So one thread locks a table, then requests a lock on another table. the other table is locked by the second thread, which cannot progress because it is waiting for a lock on the first table.

The reason that one of these has to be thrown out is that in a deadlock, they will never end - neither thread can progress at all. The only answer is for one to be stopped to allow the other to complete.

The solution to reducing deadlocks in the sort of situation you are talking about may be to redesign the solution. If you can make sure that less locking occurs, you will have less deadlocks.