Tracking down Deadlocks in SQL Database

Tracking down Deadlocks in SQL Database

Chris Skorlinski
Microsoft SQL Server Escalation Services

I want to share some tips for handling deadlocks in your SQL Database applications. You'll see the solutions for SQL Database are similar to on-premises SQL Server for which you'll well documented on web.  I'll focus on parts unique to SQL Database.

To me deadlocks are easier to understand if you create your own simple step-by-step deadlock, then see how the deadlock is shown in both SQL Server DMVs and via the SQL Database Management Portal.  Let's get started.

Generate a Deadlock

I'm using the sample database AdventureWorks.  I have 2 scripts, both doing update into the PersonPhone and Person table.  Thread 1 update PersonPhone, then Person while Thread 2 updates Person, then PersonPhone.  If these start at the same time, a classic deadlock.  You have what I need to complete my work and I have what you need to complete your work.  To make the queries less time-sensitive, I've added a WAITFOR DEALY.

Open a connection to SQL Database AdventureWorks and run script DeadLock Thread 1, then in a 2nd query window, execute Deadlock Thread 2.

 --Deadlock Thread 1

BEGIN TRANSACTION

  UPDATE [Person].[PersonPhone]

      SET PhoneNumber = '999-555-1212'

  WHERE [BusinessEntityID] = 1

   WAITFOR DELAY '00:00:10'

 UPDATE [Person].[Person]

       SET [FirstName] = 'Chris', [LastName] = 'Skorlinski'

      WHERE [BusinessEntityID] = 1

ROLLBACK TRANSACTION

 --Deadlock Thread 2

BEGIN TRANSACTION

   UPDATE [Person].[Person]

       SET [FirstName] = 'Chris', [LastName] = 'Skorlinski'

      WHERE [BusinessEntityID] = 1

   UPDATE [Person].[PersonPhone]

      SET PhoneNumber = '999-555-1212'

  WHERE [BusinessEntityID] = 1

   WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION

/*

(1 row(s) affected)

Msg 1205, Level 13, State 51, Line 10

Transaction (Process ID 773) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

*/

Deadlock Graph

The application will receive Msg 1205 indicating that sessions\connections was "victim" and aborted while the other continued to execute until completion.  To investigate further, you can retrieve historical deadlock details by querying sys.event_log DMVs.

For this DMV, you'll need to make a connection to the MASTER database for your server.

The sys.event_log DMV retrieves various connectivity related events for SQL Database.  One of those are "deadlock" events.  Run the query below to display our recent deadlock.  The data display is retrieved from historical tables populated every few minutes.  You may need to wait a bit before the actual deadlock is displayed.

 --sys.event_log

--Deadlock data displayed as XML in additonal_data

SELECT * FROM sys.event_log

WHERE database_name like 'AdventureWorks2012'

AND event_type = 'deadlock' 

The deadlock is displayed as XML data under the additional_data column.  Clicking in this column automatically opens the readable XML deadlock report.

While a bit messy, looking at the output compared to our controlled example, we can better understand the structure of this report.  The key sections are the "victim-list", the "process-list" (winner) along with details on what resources were locked and what resources were being requested. 

Victim

Winner

SQL Database Portal

You can also find deadlocks in your SQL Database portal under Databases, Administration, then Events.

You show all events or as I've done in this report filter just for Deadlocks.

Deadlock Details from SQL Database Portal

I would recommend using SQL Server Management Studio to query sys.event_log table to retrieve the deadlock details.  It's a bit more user friendly then SQL Database Portal.  If you're using the portal, here are the steps.

In upper left corner, click your database, then select Master.

Now click New Query and paste in the same sys.event_log DMV.

Here is here it gets messy. Click the |> to select the ROW displaying the deadlock.  When selected, you'll see the |> highlighted as shown below.  Then CTRL-C to copy the row and paste into your favorite text editor like NotePad or TextPad.

This is one simple example to help your improve your comfort level with deadlocks.  Here we clearly see the application was submitting 2 conflicting workload locking resources in different order leading to a deadlock.  Your deadlocks can get more complicated as multiple connections with multiple queries.  BING searching on deadlocks will provide great wealth of blog postings by SQL MVPs and white papers to help you resolve your deadlocks.