YOUR's SQL SERVER: Dead Locks in SQL Server

Dead Locks in SQL Server

One thing that will you most certainly face at some time as a DBA is dealing with deadlocks. A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL Server selects one of the processes as a deadlock victim and rolls back the statements.

You have two sessions that are updating the same data, session 1 starts a transaction updates table A and then session 2 starts a transaction and updates table B and then updates the same records in table A.

Session 1 then tries to update the same records in table B. At this point it is impossible for the transactions to be committed, because the data was updated in a different order and SQL Server selects one of the processes as a deadlock victim.

To further illustrate how deadlocks work you can run the following code in the Northwind database.

To create a deadlock you can issue commands similar to the commands below.
Step	Commands
1	--open a query window (1) and run these commands begin tran update products set supplierid = 2
2	-- open another query window (2) and run these commands begin tran update employees set firstname = 'Bob' update products set supplierid = 1
3	-- go back to query window (1) and run these commands update employees set firstname = 'Greg' At this point SQL Server will select one of the process as a deadlock victim and roll back the statement
4	--issue this command in query window (1) to undo all of the changes Rollback
5	--go back to query window (2) and run these commands to undo changes Rollback

Steps to capture dead lock information into error log file

1. Enable trace flag 1204

DBCC TRACEON (1204)

2. Create the event alert for the error number 1205 so that it should send response to required operator.

Capturing deadlocks with Profiler

a. Start à Run à Profiler

b. Go to File menu à New Trace

c. Select Server Name

d. Click on Options

e. Connect to a database = Browse Server

f. Yes

g. Select Northwind (required database)

h. OK

i. Connect

j. Enter Trace Name: Northwind_DeadLocks_Trace

k. Use the Template : Tuning

l. Select checkbox – Save to File à Save

m. Select “Events Selection” tab

n. Select checkbox – Show all events

o. Under Locks node select DeadLock graph and DeadLock chain

p. Run

q. Go to SSMS à Run the above queries

--open a query window (1) and run these commands

begin tran
update products set supplierid = 2

-- open another query window (2) and run these commands

begin tran
update employees set firstname = 'Bob'
update products set supplierid = 1

-- go back to query window (1) and run these commands

update employees set firstname = 'Greg'

At this point SQL Server will select one of the process as a deadlock victim and roll back the statement

r. Stop trace in Profiler

s. Under Event Class click on Dead Lock graph

Resources That Can Deadlock

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.

Locks. Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.
Worker threads. A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result. For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. This results in a deadlock.
Memory. When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.
Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.
Multiple Active Result Sets (MARS) resources. These resources are used to control interleaving of multiple active requests under MARS

User resource. When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.
Session mutex. The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Before the task can run, it must have exclusive access to the session mutex.
Transaction mutex. All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Before the task can run, it must have exclusive access to the transaction mutex.

Deadlock Detection

All of the resources listed in the section above participate in the Database Engine deadlock detection scheme. Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process:

The default interval is 5 seconds.
If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.
If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.