Deadlocks are a common occurrence in SQL Server, caused by a cyclic dependency between multiple processes, threads, or sessions for a set of resources. While there are different types of deadlocks, the methods used to resolve them are fundamentally the same.

When a deadlock is encountered, the deadlock monitor in SQL Server analyzes the processes involved and determines which process will be the least expensive to rollback. This process is then terminated, and a 1205 error is returned to the client.

It’s important to note that a deadlock in itself is not necessarily a problem. A well-designed and coded application should have exception handling built around SQL Server connections to intercept the 1205 error and resubmit the deadlock victim request. In most cases, the locking scenario that caused the deadlock will not exist during the resubmission, and the transaction will complete successfully the second time around.

However, simply preventing the deadlock from being problematic does not solve the underlying issue. To properly resolve deadlocks in SQL Server, the first step is to obtain the deadlock graph. In SQL Server 2005, there are two ways to obtain the deadlock graph.

The first method is to enable trace flag 1222, which outputs deadlock graph information. This trace flag is an updated version of trace flag 1204, which was used in SQL Server 2000.

The second method is to use SQL Trace and capture the Deadlock Graph event, saving the Deadlock XML Events separately from the trace file. This can be done in real-time using SQL Profiler or with a scripted server-side trace.

Once you have the deadlock graph, you can analyze the execution plans of the contributing processes. This will help identify the specific operations or queries that are causing the deadlock.

For example, let’s consider a common deadlock scenario where an UPDATE and SELECT statement deadlock against a single table. By examining the execution plan of the SELECT statement, we can identify the key point of interest, such as a Key Lookup operation.

In this scenario, the SELECT statement takes a shared lock on a non-clustered index to maintain concurrency while reading the data. However, the index does not contain all the necessary columns to satisfy the SELECT statement, resulting in a Key Lookup operation to retrieve the missing columns from the clustered index.

The deadlock occurs when the SELECT statement requires a shared lock on the clustered index, which is incompatible with the exclusive lock held by the UPDATE statement. Similarly, the UPDATE statement requires an exclusive lock on the non-clustered indexes, which conflicts with the shared lock held by the SELECT statement.

To resolve this type of deadlock, two options are available. First, you can remove the non-clustered index, resulting in a clustered index scan to satisfy the SELECT statement. However, this may negatively impact performance.

The second option is to create a covering index for the SELECT query, including all the necessary columns. In SQL Server 2005, this can be achieved by using the INCLUDED column list in the index definition.

It’s important to note that deadlocks can occur due to various factors, such as underlying table or index structure issues, accessing tables in reverse order in code, or different isolation levels between conflicting processes. Even heap allocations without indexes can be deadlocked against.

Properly handling deadlocks is crucial for applications. In the event of a deadlock, a well-built application should log the occurrence and resubmit the deadlocked transaction as part of error handling.

By understanding the causes of deadlocks and implementing appropriate measures, developers and database administrators can effectively manage and resolve deadlocks in SQL Server.