Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place.

WEBINAR:

On-Demand

eadlocking can be a difficult problem in a multi-user SQL Server application. Deadlocks are caused when transactions mutually block each other, and each is waiting for the other to finish. SQL Server will detect deadlocks involving locked database resources and cancel one of the queries, and roll back the transaction. In this article, you'll learn how to use SQL Server Profiler and the trace flag 1204 to detect and analyze deadlocks. You'll also learn some strategies to refactor the conditions leading to deadlocking.

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place. In this article, you'll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.

Deadlocking is not a problem that is unique to SQL Server. Any database system that relies on locking to ensure that user transactions do not interfere with each other is subject to deadlock conditions. In order to understand and resolve SQL Server deadlocks, it's important to understand the basic concepts underlying deadlocking in SQL Server.

Deadlocking Concepts
The key concept behind deadlocking is the transaction. To give your users a consistent view of the database, where either all changes in a transactional unit of work succeed or all fail, the database system must lock some resources while the work is being done. A SQL Server deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other's resource in order to finish a transaction. The result is mutual blocking: each waits on each other to acquire some resource that the other process already has.

A SQL Server deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other's resource in order to finish a transaction. The result is mutual blocking: each waits on the other to acquire some resource that the other process already has.

The result is a situation where neither process can finish. SQL Server's lock manager will detect a deadlock cycle and end one of the transactions. Table 1 shows how, in general, a deadlock occurs.

Read the table from top to bottom, imagining time to progress from instance T1 through T7. By time T3, Transaction1 and Transaction2 have both been granted locks on some resource. At time T4, Transaction1 requests an incompatible lock on the resource already locked by Transaction2, and is blocked. At that point, Transaction1 goes into a wait state, waiting for the lock to be released.

At time T5, Transaction2 requests an incompatible lock on the resource that Transaction1 already has locked. At this point, Transaction2 also goes into a wait state, and each process is blocking the other. This is a deadlock cycle, and here is where SQL Server will detect the deadlock cycle and end one of the transactions.