I'm trying to troubleshoot a complex deadlock issue.
There are 2 separate processes (service and agent job) which often concurrently execute the same SP but with different @BatchID parameters. This SP is called within an explicit BEGIN TRAN. There are 30 different Insert/Update statements within the SP. The 2 processes deadlock 5 times a day on the same table/index, around the same time each day, and the service process is always the victim. The table has many unnecessary/redundant indexes and a couple of Insert/Update Triggers. Lock Escalation is Disabled.
We know that the survivor/victim are SQL sessions (SPID), NOT SQL statements.
We also know that the UPDATE statements shown in Frame1 of both sessions are involved in the Deadlock. And How are they involved ? They are both REQUESTORS of the U lock on the Index Key. Am I correct so far..
But which statements are the OWNERS ? And when did they start holding the X lock on the Index Key?
One of the standard recommendations for resolving/reducing deadlocks is to Shorten Transactions.
But if there are a hundred similar statements, without knowing WHICH statement started holding the lock and WHEN, it's not easy to go about shortening a transaction..