RID and Key lock on same object by a Select and Delete query

Posted on

Question :

To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row?

Also, how did Delete query (SPID 121, on the right) is able to get X (exclusive) lock on object but unable to get a X lock on Index?

Deadlock XML:

<deadlock>
 <victim-list>
  <victimProcess id="processa4c4804e8" />
 </victim-list>
 <process-list>
  <process id="processa4c4804e8" taskpriority="0" logused="0" waitresource="RID: 19:1:6635214:0" waittime="7365" ownerId="2047519827" transactionname="SELECT" lasttranstarted="2018-07-31T06:16:10.353" XDES="0x17db223920" lockMode="S" schedulerid="27" kpid="9788" status="suspended" spid="128" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-07-31T06:16:10.353" lastbatchcompleted="2018-07-31T06:16:10.303" lastattention="1900-01-01T00:00:00.303" clientapp="jTDS" hostname="abc_Server" hostpid="123" loginname="XYZSVC_ABC" isolationlevel="read committed (2)" xactid="2047519827" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="7018" stmtend="47282" sqlhandle="0x0200000045a5451b6c3d47c0d921b0f2f2ca56ca9a4b26fa0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 bigint,@P3 bigint,@P4 nvarchar(4000),@P5 bigint,@P6 bigint,@P7 nvarchar(4000),@P8 bigint,@P9 bigint,@P10 nvarchar(4000),@P11 bigint,@P12 bigint,@P13 nvarchar(4000),@P14 bigint,@P15 bigint,@P16 int,@P17 int,@P18 nvarchar(4000),@P19 bigint,@P20 bigint,@P21 nvarchar(4000),@P22 bigint,@P23 bigint,@P24 nvarchar(4000),@P25 bigint,@P26 bigint,@P27 nvarchar(4000),@P28 bigint,@P29 bigint,@P30 decimal(38,0),@P31 decimal(38,1),@P32 int,@P33 int,@P34 int,@P35 int,@P36 decimal(38,1),@P37 int,@P38 int,@P39 decimal(38,0),@P40 int,@P41 int,@P42 nvarchar(4000),@P43 bigint,@P44 bigint,@P45 nvarchar(4000),@P46 bigint,@P47 bigint,@P48 nvarchar(4000),@P49 bigint,@P50 bigint,@P51 nvarchar(4000),@P52 bigint,@P53 bigint,@P54 nvarchar(4000),@P55 bigint,@P56 bigint,@P57 nvarchar(4000),@P58 bigint,@P59 bigint,@P60 nvarchar(4000),@P61 bit,@P62 int,@P63 decimal(38,0),@P64 decimal(38,0),@P65 decimal(38,0),@P66 decimal(38,0),@P67 decimal(38,0),@P68 decimal(38,0),@P69 decimal(38,0),@P70 nvarchar(4   </inputbuf>
  </process>
  <process id="processa4d92f848" taskpriority="0" logused="2176492" waitresource="KEY: 19:72057594649706496 (e37a97d3049b)" waittime="7773" ownerId="2047625413" transactionname="DELETE" lasttranstarted="2018-07-31T06:30:05.207" XDES="0x11bd931bf0" lockMode="X" schedulerid="7" kpid="13108" status="suspended" spid="121" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-31T06:30:05.207" lastbatchcompleted="2018-07-31T06:30:05.207" lastattention="2018-07-31T04:28:01.577" clientapp="jTDS" hostname="abc_Server" hostpid="123" loginname="XYZSVC_ABC" isolationlevel="read committed (2)" xactid="2047625413" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="84" stmtend="412" sqlhandle="0x020000006f180e1fffe8b18ae2942c8e9498c66bdd56e2600000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 nvarchar(4000),@P1 bigint,@P2 bigint)DELETE TOP(100000) FROM DIL2_OBJECT_NAME_88888888888888 WHERE rowStatus =  @P0  OR (expirationDate &gt;  @P1  AND expirationDate &lt;  @P2 )   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <ridlock fileid="1" pageid="6635214" dbid="19" objectname="Cache.dbo.DIL2_OBJECT_NAME_88888888888888" id="lock21441c4400" mode="X" associatedObjectId="72057594616020992">
   <owner-list>
    <owner id="processa4d92f848" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="processa4c4804e8" mode="S" requestType="wait" />
   </waiter-list>
  </ridlock>
  <keylock hobtid="72057594649706496" dbid="19" objectname="Cache6.dbo.DIL2_OBJECT_NAME_88888888888888" indexname="idx_" id="lock156e38f280" mode="S" associatedObjectId="72057594649706496">
   <owner-list>
    <owner id="processa4c4804e8" mode="S" />
   </owner-list>
   <waiter-list>
    <waiter id="processa4d92f848" mode="X" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

Answer :

how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row?

An nonclustered index is a separate object from the table. So the reader using the index first gets an S lock on the index key, and then retrieves the row locator (here a RID) from the leaf level of the index. To read the row referred to by the row locator will require an additional lock on the RID or the clustered index key.

how did Delete query (SPID 121, on the right) is able to get X (exclusive) lock on object but unable to get a X lock on Index?

Similarly a query operating on the table (here a heap) gets an X lock on the RID to delete the row, and then needs to get an X lock on the corresponding index keys in the non-clustered indexes. But here the index key is locked by the reader. If you look at the DELETE query plan it’s probably performing a table scan of the heap to find the rows to delete.

So this is a garden-variety reader/writer deadlock that would be avoided by having the readers use Row Versioning (SNAPSHOT isolation, or setting the database in READ_COMMITTED_SNAPSHOT mode), instead of using Shared Locks (S) to ensure a consistent view of the database.

As this appears to be a Denodo cache table, and deadlocks are application bugs, you may need to open a case with the vendor about a fix, and ask whether they support using Row Versioning for the cache tables. I’d be disappointed in them if they don’t.

Leave a Reply

Your email address will not be published. Required fields are marked *