Deadlocking

Deadlocking occurs when one user session (let’s call it Session 1) has locks on a resource that another user session (let’s call it Session 2) wants to modify, and Session 2 has locks on resources that Session 1 needs to modify. Neither Session 1 nor Session 2 can continue until the other releases the locks, so SQL Server chooses one of the sessions in the deadlock as the “deadlock victim”:


Some reasons why deadlocks can happen:

• The application accesses tables in different orders. For example, Session 1 updates Customers and then      Orders, whereas Session 2 updates Orders and then Customers. This increases the chance of two processes deadlocking, rather than them accessing and updating a table in a serialized (in order) fashion.
• The application uses long-running transactions, updating many rows or many tables within one transaction. This increases the surface area of rows that can cause deadlock conflicts.
•  In some situations, SQL Server issues several row locks, which it later decides must be escalated to a table lock. If these rows exist on the same data pages, and two sessions are bothtrying to escalate the lock granularity on the same page, a deadlock can occur.