Deadlock in SQL

  • I am getting below Deadlock Error:

     

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

    Is there any method to find what is causing deadlock in SQL?

     

  • SQL2008?  Well I have to say it that has been out of any support now for over 2 years so you really need to get that upgraded to 2019 and like yesterday.

     

    Back to the question at hand, if you have 2008 then you need to write your own methods of capturing the deadlock or enable trace flag 1222 where the deadlock details then get written to the error log and you can see what processes where involved in the deadlock.

    Your access methods to the data need to be the same in all processes.

    Essentially you have two or more sessions involved taking locks in an opposite way to each other on a resource they both need access to.

    session 1 takes a lock on “orders” then “order items”

    session 2 takes a lock on “order items” then “orders”

    You need to find the processes involved what they tried to run and go fix your code base to grab the locks in a uniform way.

  • Since you're in 2008, you can take advantage of the system_health Extended Events session. system_health contains deadlock information that you can query as outlined here (yeah, yeah, RDS, but the query will work anywhere). I don't recommend generally using Extended Events on any system less than 2012. However, the system_health info is there, so you may as well take advantage.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply