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.
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 ?