Slow order processing due to database transaction deadlocks

Overview

Everest users are experiencing slow order processing with the server error log indicating that some custom stored procedures and views are creating deadlocks on the database server with the following error present in the error logs (svrerr.log):

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

These transaction deadlock errors will appear in the Everest server error log files located at %ProgramData%\Everest\Temp\svrerr.log e.g:

Solution

A common issue in SQL Server environments is deadlocks which are often more prevalent in large OLTP deployments.  A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward.  When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of the processes as the deadlock victim and rollback that process, so the other process or processes can move forward.

Usually, SQL Server is able to internally manage and recover from these deadlock situations automatically and the errors can be safely ignored if users are subsequently able to complete processes and save desired data in Everest.

However, customizations through stored procedures and external integrations are known to significantly increase the risk of encountering deadlock events.

Follow these steps to mitigate and resolve persistent deadlocks resulting in Everest performance degradation:

1. Capture the SQL Server Deadlocks using Extended Events steps provided on Capturing SQL Server Deadlocks using Extended Events to confirm what is causing the deadlocks, paying close attention to any custom stored procedures (SP) and views.
2. Analyze the Everest svrerr.log to identify when deadlock error appears and what SP method caused the deadlock event. Defragment the indexes in the associated tables. See Gathering SQL Server indexes statistics and usage information.
   • To set defragmentation on some specific tables or whole database, refer to the external KB on How to automate and schedule SQL Server index defragmentation
   • The below tables should be defragged or indexed nightly or bi-nightly:

     ADDRESS
     BCH_HEAD
     BCH_Tran
     CUST
     INVENTOR
     ITEMS
     INVOICES
     ITEMHIST
     ITEMS
     PO
     SERHIST
     STK_AREA
     TRANHIST
     TRANSACT
     VENDORS
     X_ASSEMBLY
     X_INVOIC
     X_KIT
     X_PO
     X_STK_AREA
     

   • Alter the configuration in the reindex on 'Optimize index only if' Fragmentation > 10% and uncheck the Page Count as shown below:
     
     
     
3. For custom stored procedures and views, use tables "WITH (NOLOCK)" syntax in views and also use "BEGIN TRANSACTION" & "COMMIT TRANSACTION" in each Update statement. SQL Server Deadlock Analysis and Prevention - Dot Net Tutorials

   BEGIN
   BEGIN TRANSACTION
   UPDATE TableA SET Name = 'Anurag From Transaction 1' 
   WHERE Id = 101
   
   UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
   WHERE Id = 1001
   COMMITTRANSACTION
   END

4. Repeat the above steps for all occurrences of the deadlock errors in the svrerr.log file.

Testing

Capture the SQL Server Deadlocks using Extended Events steps provided on Capturing SQL Server Deadlocks using Extended Events and confirm that there are no more deadlock errors.

Back to top