Monitoring Deadlocks

In order to monitor and analyze deadlocks in your server you will first need to understand what does deadlock mean.  The Books Online definition is:

“A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.”

In other words, this is a situation when two or more processes holds a lock on a different resource try to get a lock on the other process resource which is already locked. This will eventually create an endless loop. SQL Server knows how to deal with those kind of situations with the “Deadlock detection” mechanism.

The mechanism continuously (every five seconds) checks the lock monitor thread and searches for deadlocks.

If a deadlock occurs the mechanism choses a process “victim” and rolls back his transaction. The victim is the process that its rollback will need the less resources.

Note – If we want we can predefine the process importance level and then the victim will be chosen by our definition.

Rollback the victim process will enable the other process to finish its transaction and that will end the loop.

It will be wise to create a “try and catch” mechanism in the processes transactions that will re-execute the victim process after a few random seconds.

Here is a script as an example of a simple and classical deadlock, which contains two transactions for each query window.

first query:

Transact-SQL

--Transaction A-- USE AdventureWorks GO BEGIN TRANSACTION --Statement 1-- UPDATE Sales.SalesOrderDetail SET OrderQty = OrderQty * 2 WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1 --Hold for 10 seconds-- WAITFOR DELAY '00:00:10' --Statement 2-- SELECT * FROM HumanResources.Department WHERE DepartmentID = 1 COMMIT GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

--Transaction A--

USE AdventureWorks

GO

BEGIN TRANSACTION

--Statement 1--

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty * 2

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

--Hold for 10 seconds--

WAITFOR DELAY '00:00:10'

--Statement 2--

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1

COMMIT

GO

second query: