sql server 2005 - What is a deadlock in a database?

Question

What is a deadlock in SQL Server and when it arises?

What are the issues with deadlock and how to resolve it?

Answer 1

In general, deadlock means that two or more entities are blocking some sources, and none of them is able to finish, because they are blocking sources in a cyclic way.

One example: Let's say I have table A and table B, I need to do some update in A and then B and I decide to lock both of them at the moment of usage (this is really stupid behaviour, but it serves it's purpose now). At the same moment, someone else does the same thing in opposite order - locks B first, then locks A.

Chronologically, this happens:

proc1: Lock A

proc2: Lock B

proc1: Lock B - starts waiting until proc2 releases B

proc2: Lock A - starts waiting until proc1 releases A

Neither of them will ever finish. That's a deadlock. In practice this usually results in timeout errors since it is not desired to have any query hanging forever, and the underlying system (e.g. the database) will kill queries that don't finish in time.

One real world example of a deadlock is when you lock your house keys in your car, and your car keys in your house.