T-SQL Query To Find Deadlocks In MS SQL Server

Introduction

As a Backend Developer, I have frequently encountered performance issues, particularly those stemming from poorly written code or lack of indexes, which can lead to blocking conditions called “deadlocks.” These situations can directly impact user experience and become more severe with frequent occurrences.

This article addresses deadlock issues and reporting. Upon completion, readers will be equipped to explain how deadlocks occur and how to access extended information to diagnose and take appropriate action, ultimately resulting in decreased frequency or total elimination of deadlock conditions. We will examine the fundamentals of deadlocks and their occurrence through a practical T-SQL example

What Is Deadlock

MS SQL Server Deadlocks occur when two or more transactions are competing for the same resources, and each transaction is waiting for the other to release the resource it needs. This results in a deadlock, where none of the transactions can proceed and the server is stuck in a state of limbo.

To better understand deadlocks, let’s consider an example:

Suppose that transaction A needs resource 1 and resource 2 to complete its operation, while transaction B needs resource 2 and resource 1. If transaction A acquires resource 1 and transaction B acquires resource 2, both transactions will be waiting for the resource the other has acquired. This leads to a deadlock.

The SQL Server deadlock detection mechanism detects deadlocks and rolls back one of the transactions to resolve the deadlock. The transaction that is rolled back is the one that is the least expensive to roll back.

There are several ways to prevent and resolve deadlocks in MS SQL Server:

• Use the proper locking granularity: Lock only the minimum set of resources required for a transaction.
• Use the proper isolation level: Choose an appropriate isolation level that balances concurrency and consistency. This includes using snapshot isolation and read committed snapshot isolation.
• Use a deadlock graph: A deadlock graph provides detailed information about the deadlock, including the transactions involved and the resources they are competing for.
• Use a deadlock monitor: A deadlock monitor is a tool that can help you detect and resolve deadlocks by monitoring the server for deadlocks.
• Reduce transaction times: Minimize the time that transactions are holding locks to reduce the likelihood of deadlocks.
• Reduce transaction complexity: Simplify transactions to reduce the likelihood of deadlocks.

By following these guidelines, you can reduce the likelihood of deadlocks and resolve them quickly when they do occur

T-SQL Script To Find Deadlock Occured Sessions

By utilizing the T-SQL script provided below, we can generate a list of sessions that experienced deadlocks, along with the timestamp of each occurrence

The above script is also available on my GitHub – https://github.com/SundaramSubramanian/DeadlockScript

Conclusion

This brief article offers a few helpful tips and tricks for identifying deadlock occurrences by retrieving the associated session information. We welcome your feedback and invite you to share your thoughts in the comments section below