How to find sql server deadlocks

Sql Server Deadlocks are a huge topic. Simply, they occur when data is updated by two different processes at the same time, prior to committing a transaction. It can also occur on a read whilst a write is occuring on the same data. This is usually created by inefficient code, holding open a transaction longer than […]

Sql Server Deadlocks are a huge topic. Simply, they occur when data is updated by two different processes at the same time, prior to committing a transaction. It can also occur on a read whilst a write is occuring on the same data.

This is usually created by inefficient code, holding open a transaction longer than is needed, while another tries to access the table or row in question. The other cause can be from

How to find sql server deadlocks by reporting as they happen

This has been done on SQL 2012, so you should be able to follow these steps:

Open SQL Server Profiler

  1. Create a new Trace
  2. In the template drop down select ‘Deadlock Grapher and Exporter’
  3. On the Events Selection tab, uncheck, then recheck the Deadlock Graph
  4. The Events Selection Export tab will appear, and you can optionally choose to export the deadlocks to XML files. This is recommended as it gives more information, and is a permanent record of the deadlocks (in case the server restarts, or someone closes the profiler).

Open SQL Server Profiler:

  1. Create a new trace, by selecting File->New Trace or hitting Ctrl+N. Connect to the database server you want to profile with.
  2. The Trace Properties window will appear. Under the General tab, choose ‘Blank’ for the ‘Use the template’ field.
  3. The Events Selection tab of the Trace Properties window offers a plethora of database events that you can monitor, including several lock and deadlock events. I only select the Deadlock graph event (in the Locks section) to trace, though, as it provides me plenty of data about the deadlock without overwhelming me with too much information.
  4. After checking the Deadlock graph event, a new tab will appear titled Events Extraction Settings. In this tab, you can optionally click the ‘Save Deadlock XML events separately’ checkbox.
  5. Press the Run button.

Simulate a deadlock, or wait for one to occur

To simulate this, is simple and the short way to get a deadlock is to access the tables data in a reverse order and hence introducing a cyclic deadlock between two connections: (taken from http://www.extremeexperts.com/SQL/Yukon/DeadLockDetection.aspx).

Now with the tables ready, we will create two connections (two SQL query windows) and update the columns in the reverse order:

Connection 1

Connection 2

When you trigger the second query you will see a deadlock appear in the profiler report. It will tell you what query and table the issue occurs in. When this happens live in your code you should then be able to optimise the code or table (i.e. by adding an index or similar) to speed up the query so that it is not held open for so long.

Useful Links

http://www.togsblom.com/2008/12/basic-sql-server-deadlock-debugging.html

Crazy indepth information on troubleshooting deadlocks http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

Image from http://www.shortsbrewing.com/

Duncan Isaksen-Loxton

Educated as a web developer, with over 20 years of internet based work and experience, Duncan is a Google Workspace Certified Collaboration Engineer and a WordPress expert.
Login
Log in below to access your courses.
Log In With Google
Forgot Password
Enter your email address or username and we’ll send you instructions to reset your password.