2

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.

  • Usually this involves other statements in an outer transaction. Is it possible an application is using a transaction and has mixed other operations in? – Aaron Bertrand Oct 19 '16 at 15:53
  • It does have other operations going on but the code is not open to us. We'd have to decompile is what I was told. I am stumped as to how to troubleshoot the root cause and resolve it at this point. – R Alladi Oct 19 '16 at 16:18
  • Have you shown this deadlock to the vendor? – Aaron Bertrand Oct 19 '16 at 16:20
  • We are sadly out of date with the upgrades on this product and the determination was that that's probably not the way to go. The other thing is these deadlocks started happening quite recently - don't know if that adds any value in resolving this. – R Alladi Oct 19 '16 at 16:23
  • Hi, @RAlladi . I have very similar issue (deadlock on 2 selects) in my application. Could you tell how did you resolve it? – kolobok Feb 20 '18 at 12:44
3

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

| improve this answer | |
  • Thanks, I did notice that while trying to unravel this but I wasn't sure how it worked. I'll check out moreon the granularity. – R Alladi Oct 19 '16 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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