Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Finding and troubleshooting SQL Server deadlocks

MSSQLTips author Greg Robidoux By:   |   Read Comments (10)   |   Related Tips: More > Locking and Blocking

Problem
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.

For example 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 viticm.

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

Solution
The only solution for handling deadlocks is to find the problem in your code and then modify your processing to avoid deadlock situations.  The first thing you need to do is find the deadlock situations and then investigate the problem.  There are a couple of ways of doing this.

The first approach is to turn on the trace flag to find the deadlocks.  This can be done with the following statement run in Query Analyzer.

DBCC TRACEON (1204)

When a deadlock occurs the information like the following will be captured in the SQL Server Error Log.

From this output we can see that SPID 53: was updating object 1977058079 and SPID 52: was updating object 117575457.  But what do these numbers mean. These numbers are the objectIDs.  To determine what table is affected you will need to query the sysobjects table in the Northwind database or whatever user database the deadlock occurred in using the following command and the find the ID that matches the ID from the deadlock information.

SELECT id, name
FROM sysobjects
WHERE xtype = 'U'
ORDER BY id

Another option to find the tables is to use the object_name function:

SELECT object_name(1977058079) --(returns Employees)
SELECT object_name(117575457) --(returns Products)

Thanks goes out to Armando P. for pointing out the error as well as using the object_name function.

With this information it is possible to see what tables were part of the deadlock process, but trying to figure out what statements caused the problem is much more difficult.  To provide further information about the deadlock process you will need to run a Trace to capture all of the information and then try to decipher what is going on.  This can be done by either using Profiler or by using a Server Side Trace.  With the trace there are a couple of additional items that need to be captured to help figure out what is going on and with what objects. 

SQL Profiler

To do this using SQL Profiler, you will need to capture the Lock Events Lock:Deadlock and Lock:Deadlock Chain.

And also capture the ObjectId data column.

 

Server Side Trace

For a Server Side Trace the following additional information will need to collected to capture the deadlock information.

EventNumber Event Description
25 Lock:Deadlock Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.
59 Lock:Deadlock Chain Produced for each of the events leading up to the deadlock.

In addition, you will also need to capture this additional column to see what objects are part of the deadlock chain.

ColumnNumber Column Description
22 ObjectID System-assigned ID of the object.

 

The output from our trace would show the following information:

From here we can see what was occurring at the time of the deadlock. This is a very simple example, but you can see how this additional information from a trace can help solve the problem.

When you have a lot of information to go through it is easier to load the data into a SQL Server table and then query the data for the particular timeframe and SPIDs in question.  This was covered in a Server Side Trace tip.  Here is a sample query that can help you narrow down the timeframe when the deadlock occurred. By changing the date values and the SPIDs to look at you can narrow down what was occurring at the time or right around the time that the deadlock occurred.

DECLARE @lowDate AS datetime@highDate AS datetime

SET @lowDate '2006-08-01 13:47:17.000'
SET @highDate '2006-08-01 13:47:18.999'


SELECT 
       
TextData,
       
StartTime
       
EndTime,
       
SPID
       
Duration
       
Reads
       
Writes
       
EventClass      
FROM 
       
TraceFile
WHERE 
       
SPID IN (52,53,4) AND
       (
StartTime BETWEEN @lowDate AND @highDate 
        
OR EndTime BETWEEN @lowDate AND @highDate 
        
OR StartTime @lowDate AND EndTime @lowDate)
ORDER BY 
       
StartTime

Next Steps

  • Take the time to learn more about how to capture and troubleshoot deadlocks.
  • Use the sample script to understand how they occur and what you can do to solve deadlock issues in your environment
  • Learn how a Server Side Trace and loading the data to a SQL table can help troubleshoot this type of problem


Last Update: 8/9/2006


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, December 04, 2008 - 6:26:42 PM - Charan Read The Tip

Hi,

I followed the below steps to generate the dead lock scenario. All commands ran fine. But I did not get any Deadlock graph in the Error log?

Could you plz tell me where Iam going wrong? 

 

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

Solution
The only solution for handling deadlocks is to find the problem in your code and then modify your processing to avoid deadlock situations.  The first thing you need to do is find the deadlock situations and then investigate the problem.  There are a couple of ways of doing this.

The first approach is to turn on the trace flag to find the deadlocks.  This can be done with the following statement run in Query Analyzer.

DBCC TRACEON (1204)

 


Thursday, December 04, 2008 - 6:38:39 PM - aprato Read The Tip

 Try issuing

DBCC TRACEON(3605)

prior to

DBCC TRACEON(1204)

 

 


Thursday, October 11, 2012 - 5:34:33 AM - Rajasekhar Read The Tip

Hi

 

I have implemented fulltext search and Rebuild indexes for this and scheduled every 5 mins and its working fine but for every 5 mins of running this job its going to write the below message in SQLServer Error Log.

 

 Message

 

Changing the status to MERGE for full-text catalog "CatalogName" (7) in database "DbName" (5). This is an informational message only. No user action is required

 

How can I avoid this,I mean I do not want to write this into SqlServer Error Log.I have searched for trace flags but I did not find related to this.


Tuesday, November 13, 2012 - 11:12:09 PM - dinesh Read The Tip

Hi,

I want see deadlock by using Query anlzer..


Tuesday, December 18, 2012 - 12:18:53 PM - Umair Read The Tip

Nice article.

Thanks


Tuesday, December 18, 2012 - 12:37:04 PM - jayron Read The Tip

 

Interest article, thank you!


Wednesday, December 19, 2012 - 10:57:25 PM - Dinesh Read The Tip

Nice Article.

I want read last deadlock information and history then what to do ? Please suggest some solution.

 

Regards,

Dinesh


Thursday, December 20, 2012 - 11:02:57 AM - Greg Robidoux Read The Tip

@Dinesh - once you figure out the SQL statements that are causing the deadlock you need to figure out where these are being called and when they are called.  You would need to make sure you do things in the same order to eliminate deadlocks or once you make a data change commit the changes so it doesn't cause other commands to be deadlocked.


Sunday, December 23, 2012 - 11:18:28 PM - Dinesh Read The Tip

I am getting which statement getting deadlock.I want read presous statement History. so Elimation can be done.So Please let me know to tacle Presous deadlock information.


Friday, January 11, 2013 - 11:18:17 AM - Leslie Read The Tip

@Dinesh - Have you tried running a tool to figure out which SQL statements are causing the deadlocks?  There are a lot of free and low-cost ones out there.  Two that I use constantly for SQL Server performance tuning are SQL Heartbeat and SQL Deadlock Detector.  The second tool is a little clunky because it takes a little while to load up the locks, but otherwise it works just fine.  Make sure you go in and set the lock threshold properly, so you get the most useful data.  Enjoy!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.