Sql-server – Seeing 2 exclusive locks on the same index – is this possible

deadlocksql serversql-server-2012

Please help me understand how it is possible that I am seeing 2 exclusive locks on the same index. This is on SQL Server 2012 SP3. It is from within a vendor application and don't know how transaction settings are working. Oh, and this is on SELECTs!

Here is a picture from SQL Sentry's monitoring:
enter image description here

The deadlock graph details are here:

<deadlock>
  <victim-list>
    <victimProcess id="process3dd4558" />
  </victim-list>
  <process-list>
    <process id="process3dd4558" taskpriority="0" logused="968" waitresource="KEY: 10:72057594054049792 (34375086fdb7)" waittime="3723" ownerId="1155141462" transactionname="implicit_transaction" lasttranstarted="2016-10-18T18:49:28.763" XDES="0x1c5b81b6a8" lockMode="S" schedulerid="5" kpid="2968" status="suspended" spid="257" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-10-18T18:49:28.770" lastbatchcompleted="2016-10-18T18:49:28.770" lastattention="1900-01-01T00:00:00.770" hostname="host4" hostpid="0" loginname="host4user" isolationlevel="read committed (2)" xactid="1155141462" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="144" sqlhandle="0x02000000a9eef71a951d683a59623271b0518177293509aa0000000000000000000000000000000000000000">
        SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil &gt;= @P3))    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
        unknown    
    </frame>
      </executionStack>
      <inputbuf>
    (@P0 varbinary(8000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime)SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil &gt;= @P3))                                   </inputbuf>
    </process>
    <process id="process4158558" taskpriority="0" logused="4652" waitresource="KEY: 10:72057594054049792 (9c5f356ba0f3)" waittime="3723" ownerId="1155141461" transactionname="implicit_transaction" lasttranstarted="2016-10-18T18:49:28.763" XDES="0x2fa2ce8d28" lockMode="S" schedulerid="2" kpid="7032" status="suspended" spid="162" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-10-18T18:49:28.770" lastbatchcompleted="2016-10-18T18:49:28.770" lastattention="1900-01-01T00:00:00.770" hostname="host4" hostpid="0" loginname="host4user" isolationlevel="read committed (2)" xactid="1155141461" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="144" sqlhandle="0x02000000a9eef71a951d683a59623271b0518177293509aa0000000000000000000000000000000000000000">
        SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil &gt;= @P3))    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
        unknown    
    </frame>
      </executionStack>
      <inputbuf>
    (@P0 varbinary(8000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime)SELECT fulfillmentid, distid, transid, transtime, signref, loanuntil, userid, confirmed, returnable, returned FROM fulfillment WHERE ((fulfillment.userid = @P0) AND (fulfillment.returnable = @P1) AND (fulfillment.returned = @P2)) AND ((fulfillment.loanuntil IS NULL) OR (fulfillment.loanuntil &gt;= @P3))                                   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594054049792" dbid="10" objectname="xxxx.dbo.fulfillment" indexname="IX_fullfilment_userid_loanuntil_returnable_returned" id="lock3118a8ae80" mode="X" associatedObjectId="72057594054049792">
      <owner-list>
        <owner id="process4158558" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process3dd4558" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594054049792" dbid="10" objectname="xxxx.dbo.fulfillment" indexname="IX_fullfilment_userid_loanuntil_returnable_returned" id="lockcffbaed80" mode="X" associatedObjectId="72057594054049792">
      <owner-list>
        <owner id="process3dd4558" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process4158558" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Also, if there is anything else I need to post, please let me know. Thank you.

The server is part of an AG set, we failed over today and the
deadlocks disappeared. Another mystery unsolved.

Best Answer

Yes, you might have an exclusive rowlock or page lock on different pages or rows. In this case you have a key lock on two different parts of the index. See lock granuality https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx