Problem
Deadlocks can occur in any database system, and SQL Server is no exception. In this article, we look at the steps I used to detect and resolve deadlocks in stored procedures.
Solution
It can be a challenge to troubleshoot deadlocks in SQL Server stored procedures and in this article, I will walk through the steps I used to resolve deadlock issues in stored procedures.
What is SQL Server Deadlock?
A deadlock occurs when two or more transactions are waiting for each other to release a resource that they need to complete their operation. This situation leads to an issue where both transactions are stuck and cannot continue, resulting in a deadlock. Deadlocks can occur in any database system, including SQL Server.
The most common cause of deadlocks in a database is a lack of proper transaction management. When transactions are not managed correctly, they can hold resources for an extended period, causing other transactions to wait for a long time, leading to a deadlock. Below outlines how two different stored procedures may lock needed resources in a different order, therefore creating a deadlock.
I discovered that certain SQL stored procedures initially designed to facilitate integration between our ERP application and external systems were causing deadlocks in our production environment. These problematic procedures required 10-14 minutes to execute, delaying transmitting of accurate information to the integrated system. This, in turn, impacted the ERP application performance.
To provide more context, our Logistic module of the ERP application contained numerous SQL stored procedures with over 2000 lines of intricate business logic. These procedures were responsible for sending outbound data or receiving inbound data from various vendor systems. They were scheduled for execution every 30 seconds during integration. However, our database monitoring system observed an increase in deadlock reports and the business also noticed inconsistencies in data exchange.
Monitoring SQL Server Deadlocks
Resolving the issue was a mixed effort of developers and DBAs. The DBA set up monitoring for deadlocks and shared the daily logs with the application team. To monitor the deadlocks in these long-running stored procedures, we set up Extended Events to capture deadlocks.
Step 1
In SQL Server Management Studio with Admin access go to Object Explorer > Management > Extended Events > Sessions and right-click on Sessions and choose “New Session.”
Step 2
In the newly opened window, input a name for the event you want to create. As an illustration, let’s name it DeadlockcausingSPs. You can initiate the event during server startup and after session creation, and if needed, you can modify these settings later on.
Step 3
Navigate to the Events tab and utilize the event library search bar by typing “deadlock” to filter and display only the deadlock events. To better comprehend the problem, you may need to use the XML deadlock report, which visually analyzes the deadlock.
Step 4
Within the Data Storage tab, you can specify where to store the Extended Event data.
Step 5
To inspect the stored procedures that have been running for an extended period and have been gathered by the session, double-click on the event file package0.event_file listed below the session. A query window will emerge, displaying the record of monitored events. After selecting a particular event, the details will be visible in the details window.
Analysis of Deadlocks in SQL Server
Below are some of the findings when we analyzed the deadlocks.
- While analyzing the deadlock files it was observed that deadlocks are mainly a type of read/write (Select/Update). Primarily, objects like transactional tables are deadlock resources.
- We also found that indexes are missing in some non-transactional tables used in multiple stored procedures while acquiring the shared (S) lock and update lock (U). This is impacting the lock granularity and, in turn, leading to deadlocks.
- In the culprit stored procedures, we found that the Transaction (Read Committed) statements are not handled correctly. In a few cases, a Transaction is not required at all. Also in a few cases, the Transaction begins at the wrong place, and SELECT statements are inside the scope of the Transaction acquiring shared (S) locks and leading to deadlocks.
We will dig into this deeper to show what was found.
We have a sales department where the table Orders is the most used transactional table in most store procedures written for the Sales ERP software. Below is a simple example:
This table is used by multiple SQL stored procedures like creating orders, updating orders, and viewing orders simultaneously by multiple sources.
For example:
- ViewOrder only selects the data from the Table, so no Transaction statement is required.
- UpdateOrder can be a long running stored procedure where the data for the Order table can be validated using a SELECT statement. Finally, when all data is valid, the insert/update statement gets executed. Still, the transaction starts right from the beginning of the stored procedures enclosing the SELECT statement on the Order table, causing shared locks.
- CreateOrder SP can also be long running SP, including multiple SQL statements validating and inserting data in the Order table.
Transaction Handling
In a stored procedure transaction request, different kinds of locks on resources (such as rows, pages, or tables) are created. It maintains the ACID (Atomicity, Consistency, Isolation, Durability) property of the database. Locks prevent other transactions from altering resources in a way that would be problematic for the transaction asking for the lock unless the first transaction releases it. If a transaction holds a resource for a long time and another transaction waits for the same resource, a deadlock situation begins in the database. So, transactions should always start just before the “write” statement. Therefore, it is necessary to understand while writing stored procedures from where the transaction should begin.
We observed that some stored procedure transaction statements were created at the beginning of the code. So, multiple SELECT statements were inside the transaction, which were not required, and resources were locked for a long time. For example, in the Create Order/Update Order stored procedure, the transaction was created at the start of the code, while it was only needed on the last few lines where the create/update statement was written. So, the Transaction scope of the stored procedure was reduced appropriately so that most of the SELECT statements would be kept out of the transaction.
Also, transactions were applied on some stored procedures that only used SELECT statements, which are not required. This was unnecessarily locking the resources exclusively while shared locks should be used. For example, there is no need for a Transaction statement in the ViewOrder SP.
Indexing
Data from a database can be quickly retrieved using a SQL index. One of the best ways to increase the speed of queries and applications is to index a table or view. A SQL index is a rapid lookup table for discovering the records users usually need to search. A fast, compact index is designed for speedy lookups. It is beneficial for tying relational tables together and searching huge tables.
It was observed that a few tables were frequently used in stored procedures. Upon further analysis, some indexes were missing and could be created on some tables to improve the performance of database activity during stored procedure execution.
For example, all Order stored procedures must validate the Product table since P_ID is the primary key and already an index on the table. Another index can be created on the Product table’s P_CODE(Product_code). Some stored procedures frequently validate the Product code before inserting the Order in the Order table and updating the Order table.
sp_getapplock/ sp_realeseapplock Use
The system-stored procedure, sp_getapplock, can be a useful tool for developing SQL code that involves concurrency. It enables the locking of an application resource and can be used to prevent race conditions, which occur when two or more transactions attempt to access the same data simultaneously. By preventing concurrent execution, sp_getapplock can help ensure data integrity and consistency. Therefore, it effectively manages multiple users attempting to run the same SQL Server stored procedure simultaneously. The system stored procedure, sp_releaseapplock, is designed to release the lock on an application resource previously placed by sp_getapplock.
After analyzing the stored procedures, we observed that using sp_getapplock/sp_releaseapplock in specific procedures was causing exclusive locks on sessions, resulting in long wait times and deadlocks in the database. These system stored procedures are not required for procedures that only retrieve data from the database.
For instance, the frequently called ViewOrder stored procedure was causing table locks and holding the procedure itself until the sp_releaseapplock statement was reached. This led to longer locking durations and wait times for other transactions, leading to deadlock scenarios. Therefore, it is recommended that sp_getapplock/sp_releaseapplock statements be removed from such stored procedures when not needed.
Conclusion
As you can see finding the deadlock was just part of the process, but figuring out why the deadlock occurred required a lot of additional investigation and experience working with SQL Server. I hope that some of the findings we uncovered are useful for you to troubleshoot deadlocks that you may be experiencing.
Next Steps
Here are additional links that provide further information:
