Deadlock scenerio, but where did that S lock come from?

SQLHammer 2015-03-05 16:47:17

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.

deadlock graph

Any explanation would be helpful, thank you in advance.

spupdatetaxpreparer.sqlplan

spupdateuseraccount.sqlplan

object-definitions.txt

SQLHammer 2015-03-06 13:35:15
Answered via dba.stackexchange.com.

Full Answer