I can see a X lock on a RID although the query uses a non-clustered index

Question

My initial issue was two update queries, ran in the same transaction, trying to acquire a X lock on a RID on the same table. The second query wants to update a different record in the table, but it would never complete because the first query blocks the table.

In order to fix that issue, I created a non-clustered index.

My question is: After creating that new non-clustered index, I checked the execution plan and I can see that the query uses the new non-clustered index. But, when I look at my locks, I see that the first query still acquires a X lock on a resource_type RID. Why ? I expected to see a X lock on a KEY resource_type.

Non-CLustered Index

    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170302-092518] ON [dbo].[USERS]
(
    [PARTITION] ASC,
    [DATAAREAID] ASC,
    [WORKBENCHID] ASC,
    [VALID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

Query (acquires a X lock on the RID resource_type, updates 54 rows)

BEGIN TRANSACTION
UPDATE WORKBENCHOPENINVOICES SET VALID=1,RECVERSION=894734738
WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'1010')) AND ((WORKBENCHID='WB10000887') AND (VALID=1))) 
    WAITFOR DELAY '00:30:00' -- 5 minutes
ROLLBACK TRANSACTION

By the way, the second query will complete now because I have that non-clustered Index, but I still don't understand why the first query would acquire a lock on a RID instead of KEY when using the non-clustered index Thoughts ?

Thank you

Answer 1

The update still has to lock the rows being updated regardless of any nonclustered index, so RID locks are required. The nonclustered index helps limit the number of RID locks obtained (and/or avoids table escalation) so the second query is no longer blocked.