April 12, 2010

Troubleshooting Deadlocks in SQL Server

Deadlocks are a side effect of blocking and not a SQL Server Bug. Poorly written queries in SQL Server can trigger deadlock in the system. We all knows cyclic dependency causes dead lock.
A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, P1 holds a lock on table A and requests a lock on table B; P2 holds a lock on table B and requests a lock on table A.
The lock manager(Lock Monitor)/ thread checks for deadlocks. When a lock manager/ deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID.

When SQL Server find a deadlock, it kill one process (deadlock victim) rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Database engine choose deadlock victim according to the least cost to rollback.

what can cause deadlocks?
• Locks
• Worker threads
• Memory
• Parallel query execution-related resources
• Multiple Active Result Sets (MARS) resources.


They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur.

Trace Flags can be used to output the details of deadlocks as well, and generally provide you the maximum amount of information to help facilitate identifying the root of the problem. In SQL 2000, flags 1204 (basic) and 1205 (detailed) provide information regarding deadlocks.

An Example statement to enable tracing to the ErrorLog for all connections:
DBCC TRACEON (1204, 3605, -1)
DBCC TRACEON (1205, 3605, -1)
DBCC TRACEON (1222, 3605, -1)



SQL Profiler

The SQL Profiler can also be used to help in identifying deadlocking. In order to identify the deadlocking statements in SQL Profiler, you need to be tracing the following events.

Locks - Lock:Deadlock
Locks - Lock:Deadlock Chain
Stored Procedure - SP:StmtStarting
TSQL - SQL:StmtStarting


How to create profiler trace to find deadlock?
1.Start SQL profiler
2.On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
3.Click “Show all events”
4.Click the Events tab, above specified events
(i) Add the Deadlock graph event to the trace from the Locks category. You will get an additional tab appears on the Trace Properties window, called Event Extraction Settings.
(ii) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file.

When a deadlock is encountered, the Deadlock chain event will output the SPID's associated with the deadlock. From there, you can look at the StmtStarting event for the SPID's immediately following the Deadlock Event to isolate the problem statements.

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention.
To translate the database id and object id into names, you can do

SELECT database_name(DatabaseID)
SELECT object_name(ObjectID)



You can also run following query to check which process is being blocked.

SELECT * FROM sys.sysprocesses WHERE blocked <> 0
Get the SPID from blocked column
DBCC inputbuffer (SPID)
sp_who2 'active'
sp_lock2

6 comments:

Sanjoy Singh said...

Well Written !!!

dhiraj salui said...

dear avishkar,
good post.keep on writing.
would also like to see few
articles on troubleshooting replication and mirroring issues

lp said...

Well written post, Avishkar. I like the section where you step through how to use SQL Server Profiler to run a trace. It was very concise and easy to read. I went through the steps and successfully ran a trace. The major question I had after I finished was how to interpret the trace file in such a way that I could identify what changes should be made to increase performance. I asked around and it seems like a lot of people use a visual tool to interpret traces, like SQL Trace Analyzer. Do you have any experience with such tools?

Joe said...

Avishkar, this is a great post, you should really keep it up.

Also a nice description of deadlocks here:

http://www.programmerinterview.com/index.php/database-sql/database-deadlock/

Kathiresan Muthu said...

Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

Thanks,
kathiresan

Kathiresan Muthu said...

Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

Thanks,
kathiresan