Detecting deadlocks with SQL Server Profiler

Let's suppose you are a database administrator. One of your colleagues reports to you about frequent deadlocks occurring in the database due to inefficient application code and asks you to investigate such deadlocks and to analyze when they occur. As a DBA you are required to detect such situations and find out the queries that are the culprits behind these deadlocks.

Deadlock is a state of blockage that occurs when two or more transactions are blocked by one another in such a way that, in order to complete its transaction, each transaction waits to acquire a lock on the resource that the other one has locked. In this state, each transaction waits for the other one to be finished, in order to be able to complete its own transaction. This results in endless blocking and neither transaction can be completed. The following sample diagram represents the deadlock in action:

In this recipe, we will learn how to detect such deadlocks occurring in the...