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.

Deadlock graph: Deadlock Graph

Deadlock statements: Input Buffer

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..

closed as too broad by Erik Darling, hot2use, mustaccio, RDFozz, Mr.Brownstone Nov 13 '17 at 1:05

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Why do you show your deadlock graph as a picture and not as xml? It clearly shows all the processes involved with their code, and in resource list every owner is listed – sepupic Nov 10 '17 at 6:15
  • @sepupic, for privacy reasons. let me know if you need any further info – d-_-b Nov 10 '17 at 17:20

Question 1: But which statements are the OWNERS ?

Statements don't own locks. Transactions own locks.

And when did they start holding the X lock on the Index Key?

There's nothing in the deadlock graph or DMVs that will tell you that. That information is only available in a trace event or an XEvent, at the moment the lock is acquired.

Is a Key Lock held only on the rows which satisfy the UPDATE's WHERE clause, or is it held on All values that match the value in the Index

UPDATE uses a U lock to read, then converts to an X lock to update.

Would it go away by adding (TABLOCK) to the UPDATEs

Probably. One key strategy to deadlock resolution is to lock earlier and bigger. Deadlocks occur only when two sessions acquire compatible locks, and then later attempt to acquire incompatible locks. If the first locks in the transaction are incompatible, then the second session will wait until the first commits to acquire its first lock.

One easy way to get a lock at the right granularity is to use an application lock, with sp_getapplock. If you decide that transaction A and B can't run concurrently, then just make each acquire the same application lock at the beginning, and they will run sequentially.

  • Yes, a transaction Owns it, but a transaction is made up of statements, which actually need locks. The deadlock graph shows a X-Owner & U-requestor right ? Why does the Update statement show up in Frame1.. my understanding is That particular Update statement is the U-requestor. Similarly who is the X-Owner ? Which XEvent has the info abt the lock that was part of a deadlock? I looked at the system health one which has deadlock reports, but i'm, not sure where exactly to look for this info. – d-_-b Nov 10 '17 at 16:02
  • The deadlock graph only shows the query that is blocked. It kind of makes it look like that query acquired the blocking lock too, but that's not always the case. It may have been an earlier query in the transaction. To see the locks being acquired you would need to turn on trace or start a new XEvent session for the Lock:Acquired event or lock_acquired XEvent. This trace/session would be too verbose to run in production. – David Browne - Microsoft Nov 10 '17 at 16:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.