Trace and Detect a Deadlock with SQL Server Profiler

In this article, we will see about deadlocks in SQL and how to trace and detect a deadlock with SQL Server Profiler.

Table of Contents

Let’s start with an introduction to Deadlock.

What is Deadlock?

Deadlock in SQL server is a scenario where two or more resources are trying to access the same resource which is mutually being locked by each other and now waiting for each other to complete. In this case, users may face application performance issues.

The best example in the SQL server is, 2 transactions are trying to update the same table at the same time. In this case, the Deadlock scenario may occur.

Below SQL Query will help us to generate a Deadlock situation.

First, we will create 2 Global Temp tables

CREATE TABLE ##Table1 (
    Id int, Name nvarchar(100)
)
GO
 
INSERT INTO ##Table1 (Id, Name)
VALUES (1, 'Ram'), (2, 'Kumar')
GO
 
CREATE TABLE ##Table2(
        Id int, City nvarchar(100)
)
GO
 
INSERT INTO ##Table2 (Id, City)
VALUES (1, 'Delhi'), (2, 'Bangalore')
GO

Run below query in new query window –

BEGIN TRANSACTION
  
UPDATE ##Table1
SET Name = 'Shyam' WHERE Id = 1

WAITFOR DELAY '00:00:05'
 
 
UPDATE ##Table2
SET City= 'Chennai' WHERE Id = 1

COMMIT

Next, open another query window and execute the below script.

BEGIN TRANSACTION
UPDATE ##Table2
SET City= 'Chennai' WHERE Id = 1


WAITFOR DELAY '00:00:05'
 
 
UPDATE ##Table1
SET Name = 'Shyam' WHERE Id = 1


COMMIT

Make sure to execute the second query window immediately after executing the first one as the WAITFOR DELAY is 5 seconds.

If you want to increase it, you can set it to10 or 15 seconds delay.

In the above scenario, the first query window will run successfully, however, the 2nd query window will be in a Deadlock situation and you will get the below output –

Later, in this article, we will explore How to trace and detect a Deadlock in SQL Server Profile and then we will see the way we can minimize or prevent the Deadlock situation.

Shared vs Exclusive Lock

A well-optimized SQL Query is written in a manner where one transaction is trying to read the data from a table and no other transactions can update the same data at the same time. To achieve this lock operation is used to isolate the transactions.

Shared Lock – It is used for reading data so it is also called a read lock. Shared Lock is used to preventing data from being updated.
Exclusive Lock – In case a resource is an exclusive lock, then it can be read as well as written. It is also called Write Lock.

How to prevent Deadlock in SQL Server?

There are multiple ways to avoid the Deadlock situations in SQL Server.

The very first thing is to optimize your stored procedures and make sure to use small transaction blocks.

Another way is to use a lock hint within your query. If we talk about the above script, simply we can use UPDLOCK to avoid SQL Server Deadlock. Also, try to impose a lock for a short span of time.

In order to resolve this, add the below line in the first query window.

SELECT Id FROM ##Table2 WITH (UPDLOCK) WHERE Id=1

So, the first query window script will look like this –

BEGIN TRANSACTION
SELECT Id FROM ##Table2 WITH (UPDLOCK) WHERE Id=1 -- This line Added

UPDATE ##Table1 SET Name = 'Shyam' WHERE Id = 1

WAITFOR DELAY '00:00:05' 
 
UPDATE ##Table2 SET City= 'Chennai' WHERE Id = 1

COMMIT

We will not make any changes in the second query window. Now, run both queries and you will not see any Deadlock error.

UPDLOCK VS ROWLOCK VS NOLOCK

The below table tells about UPDLOCK, ROWLOCK and NOLOCK uses in SQL Server.

UPDLOCK	ROWLOCK	NOLOCK
The UPDLOCK is used to apply a lock on a resource that possibly can be updated in the transaction. Use UPDLOCK in a select statement before updating the record in the same transaction, this will help to prevent a Deadlock situation. Syntax – SELECT Id FROM TableName WITH (UPDLOCK) WHERE Id=1	ROWLOCK helps to be applied only on a specific row. Syntax – select * from Products with(ROWLOCK) where Id = 1	NOLOCK is used to read data from the SQL Server table by ignoring any locks imposed on it. It stops read and write transactions from blocking the same resource. Syntax – select * from Products with(NOLOCK) where Id = 1

How to trace and detect a Deadlock in SQL Server?

Once you know that frequent Deadlock occurs in your application database and in result the application performance gets hampered.

So, the next task is to trace the Deadlock and resolve this.

Follow the below steps to trace and detect Deadlock.

Copy below queries in 3 different Query windows and execute in the order it is mentioned.

SQL-Query-Deadlock Download

Before executing the above query, first, open SQL Server Profile.

To open this, go to Tools -> SQL Server Profiler

You may see the below window. Click on the Events Selection tab.

Check Show all events, expand the Locks section to select Deadlock graph, Lock: Deadlock and Lock: Deadlock Chain. Then click Run.

Refer to the below screenshot with the highlighted area.

Now, execute the queries as mentioned above.

As soon as the Deadlock will occur you may notice Deadlock in event class.

Click on the Deadlock graph and it will present a diagram presenting a Deadlock situation.

In the above output window, move your cursor over Oval shapes and it will show you the statement which is causing the Deadlock situation.

What is SQL Server Profiler?

SQL Server Profiler is a tool given by Microsoft which is available with SQL Server instance.

This tool helps developers with tracing, debugging and troubleshooting in MS-SQL Server.

It gives you an interface where each and every event in MS-SQL is captured, based on that developers and DBA can identify the problems and give their resolution.

Summary

In MS-SQL Server, a Deadlock is a situation where 2 or more transactions are trying to access the same resource but they keep waiting for each other to complete the action.

In case your database is facing frequent Deadlock situations then it will impact the application performance.

You may prevent deadlock by using UPDLOCK, ROWLOCK, NOLOCK table hints. In order to trace and detect Deadlock, you may use SQL Server Profiler.

Bonus Tips – If your application is not so large and you have optimized your query but you still face Deadlock situations. In this case, the most possible reason is your application UI, make sure the Save or Update button is not clickable unless it completes the transaction.

Hope you like this article.

If this is helpful to you please share this within your tech group.