DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
The Latest "Software Integration: The Intersection of APIs, Microservices, and Cloud-Based Systems" Trend Report
Get the report
  1. DZone
  2. Data Engineering
  3. Databases
  4. A very quick guide to deadlock diagnosis in SQL Server

A very quick guide to deadlock diagnosis in SQL Server

Rob Sanders user avatar by
Rob Sanders
·
Jan. 27, 15 · Interview
Like (0)
Save
Tweet
Share
163.18K Views

Join the DZone community and get the full member experience.

Join For Free

Recently I was asked about diagnosing deadlocks in SQL Server – I’ve done a lot of work in this area way back in 2008, so I figure it’s time for a refresher.  If there’s a lot of interest in exploring SQL Server and deadlocks further, I’m happy to write an extended article going into far more detail.  Just let me know.

Before we get into diagnosis and investigation, it’s a good time to pose the question: “what is a deadlock?”:

From TechNet:

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. The following graph presents a high level view of a deadlock state where:

  • Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).

  • Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

  • Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

Diagram showing tasks in a deadlock state

The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

Basically, it’s a resource contention issue which blocks one process or transaction from performing actions on resources within SQL Server.  This can be a serious condition, not just for SQL Server as processes become suspended, but for the applications which rely on SQL Server as well.

image

The T-SQL Approach

A fast way to respond is to execute a bit of T-SQL on SQL Server, making use of System Views.  The following T-SQL will show you the “victim” processes, much like activity monitor does:

select * from sys.sysprocesses where blocked > 0

Which is not particularly useful (but good to know, so you can see the blocked count).  To get to the heart of the deadlock, this is what you want (courtesy of this SO question/answer):

SELECT Blocker.text –, Blocker.*, *
FROM sys.dm_exec_connections AS Conns
INNER JOIN sys.dm_exec_requests AS BlockedReqs
    ON Conns.session_id = BlockedReqs.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS w
    ON BlockedReqs.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(Conns.most_recent_sql_handle) AS Blocker

This will show you line and verse (the actual statement causing the resource block) – see the attached screenshot for an example.

deadlock

However, the generally accepted way to determine and diagnose deadlocks is through the use of SQL Server trace flags. 

SQL Trace Flags

They are (usually) set temporarily, and they cause deadlocking information to be dumped to the SQL management logs.  The flags that are useful are flags 1204 and 1222.  From TechNet: https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx

Trace flags are set on or off by using either of the following methods:

· Using the DBCC TRACEON and DBCC TRACEOFF commands.

For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON (2528, -1). To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.

· Using the -T startup option to specify that the trace flag be set on during startup.

The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.  So to enable or disable deadlock trace flags globally, you’d use the following T-SQL:

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

DBCC TRACEOFF (1204, -1)
DBCC TRACEOFF (1222, -1)

Due to the overhead, it’s best to enable the flag at runtime rather than on start up.  Note that the scope of a non-startup trace flag can be global or session-level.

Basic Deadlock Simulation

By way of a very simple scenario, you can make use of SQL Management Studio (and breakpoints) to roughly simulate a deadlock scenario.

Given the following basic table schema:

CREATE TABLE [dbo].[UploadedFile](
    [Id] [int] NOT NULL,
    [Filename] [nvarchar](50) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateModified] [datetime] NULL,
CONSTRAINT [PK_UploadedFile] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

With some basic test data in it:

image

If you create two separate queries in SQL Management Studio, use the following transaction (Query #1) to lock rows in the table:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT [Id],[Filename],[DateCreated],[DateModified]
FROM [dbo].[UploadedFile]
WHERE DateCreated > ‘2015-01-01′
ROLLBACK TRANSACTION

Now add a “victim” script (Query #2) in a separate query session:

UPDATE [dbo].[UploadedFile]
SET [DateModified] = ‘2014-12-31′
WHERE DateCreated > ‘2015-01-01′

As long as you set a breakpoint on the ROLLBACK TRANSACTION statement, you’ll block the second query due to the isolation level of the transaction which wraps query #1.

image

Now you can use the diagnostic T-SQL to examine the victim and the blocking transaction. Enjoy!

sql Threading Database engine

Published at DZone with permission of Rob Sanders, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Building Microservice in Golang
  • What Are the Benefits of Java Module With Example
  • Introduction to Spring Cloud Kubernetes
  • The Power of Docker Images: A Comprehensive Guide to Building From Scratch

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: