August 16, 2021 at 5:46 am
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?
August 16, 2021 at 6:07 am
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.
August 17, 2021 at 12:23 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply