1

I am encountering the below deadlock scenario. Please see the attached object definitions and execution plans. The plans were pulled out of cache from a system where this deadlock does occur.

I understand what all of this data is telling me but what I am confused about is why the spUpdateUserAccount stored procedure is taking an S lock on IX_Person_TaxPreparer_rowCreated. This lock appears unnecessary and there is no reference to IX_Person_TaxPreparer_rowCreated in the execution plan for spUpdateUserAccount.

...

Full question, with attachments, posted on SQLPerformance

Improve this question
1
  • It seems like there is an issue with the SSL cert on the link you posted. Why don't you post all of the details on here instead of a link?
    – BateTech
    Commented Mar 6, 2015 at 2:44

1 Answer 1

Reset to default
3

I'll repost my response on twitter here:

Based on the deadlock XML you posted, which lists a trancount of 3 in the session that invokes spUpdateUserAccount, a prior statement or batch in that transaction likely put the lock on IX_Person_TaxPreparer_rowCreated.

I'm presuming this lock is held because of the foreign key constraint in place.

The locks are held for the duration of the transaction, so your second statement can't complete til the first commits the open transaction.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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