1

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

1 Answer 1

2

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.

5
  • Thank you for your answer. Yes I think I confused the lock on the RID and the reason why a second query can not scan the table. In a case where I only have a clustered index and I lock a RID. Then the second query can not read the table. Is that correct ?
    – Gamleur84
    Mar 3, 2017 at 17:48
  • Well I am sorry, but I have to ask again. When I have proper indexing, I do not see RID lock on the table. I see only KEY locks on indexes. I still do not understand why I have RID locks.
    – Gamleur84
    Mar 3, 2017 at 22:47
  • Because this is on a heap (= no clustered index), which uses RID's to identify individual rows. It's equivalent to a Key lock on a clustered index, which is why you only see key locks when a clustered index is present, and a RID lock only when using a heap. Mar 7, 2017 at 13:48
  • Hi Gareth, thanks for your help. I have a clustered index on that table. I don't understand why it does a lock on a RID resource.
    – Gamleur84
    Mar 8, 2017 at 17:29
  • Np, just saw your follow up Q, will help there if I can Mar 8, 2017 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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