what is deadlock in a database?

Question

What is deadlock in sql server and when it arises? What are the issues with deadlock and how to resolve it?

Answer 1

Deadlock is what happens when two people need multiple resources to execute, and where some of the resources are locked by each of the people. This leads to the fact that A can't execute without something B has and vice versa.

Lets say I have Person A and Person B. They both need to get two rows to run (Row1 and Row2).

• Person A locks Row1 and tries to get Row2.
• Person B locks Row2 and tries to get Row1.

Person A can't run because it needs Row2, Person B can't run because it needs Row1. Neither person will ever be able to execute because they're locking what the other needs and vice versa.

---

One reasonably simple way to reduce deadlock is in all your complex transactions, you should do operations in the same order. In other words, access Table1 then Table2 in the same order. This will help reduce the number of deadlocks that occur.

Answer 2

Well this is something really googlable :-)

But in general, deadlock means that two or more entities are blocking some sources, and none of them is able to finish, because their 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 nd I decide to lock both 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 oposite order - locks B firstly, 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

It's obvious none of them will finish. That's deadlock.

The hole goes much deeper, but this is just entry and if you need to know more, invest your time. In our university there are whole lectures about that - so don't think that reading few articles makes you an expert ;-)