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

blockinglockingsql servert-sql

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

Best Answer

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.

Related Solutions

Sql-server – Deadlock when update different rows with non-clustered index

...why with clustered index, the deadlock is still there (though hit rate seems to be dropped)

The question isn't precisely clear (e.g. how many updates and to which id values are in each transaction) but one obvious deadlock scenario arises with multiple single-row updates within a single transaction, where there is an overlap of [id] values, and the ids are updated in a different [id] order:

[T1]: Update id 2; Update id 1;
[T2]: Update id 1; Update id 2;

Deadlock sequence: T1 (u2), T2 (u1), T1 (u1) wait, T2 (u2) wait.

This deadlock sequence might be avoided by updating strictly in id order within each transaction (acquiring locks in the same order on the same path).

When use the clustered index, there is an exclusive lock on the key as well as an exclusive lock on RID when do update, which is expected; while there are two exclusive lock on two different RID if non-clustered index is used, which confuses me.

With a unique clustered index on id, an exclusive lock is taken on the clustering key to protect writes to the in-row data. A separate RID exclusive lock is required to protect the write to the LOB text column, which is stored on a separate data page by default.

When the table is a heap with only a nonclustered index on id, two things happen. First, one RID exclusive lock relates to the heap in-row data, and the other is the lock on the LOB data as before. The second effect is that a more complex execution plan is required.

With a clustered index and a simple single-value equality predicate update, the query processor can apply an optimization that performs the update (read and write) in a single operator, using a single path:

Single-operator update

The row is located and updated in a single seek operation, requiring only exclusive locks (no update locks are needed). An example locking sequence using your sample table:

acquiring IX lock on OBJECT: 6:992930809:0 -- TABLE
acquiring IX lock on PAGE: 6:1:59104 -- INROW
acquiring X lock on KEY: 6:72057594233618432 (61a06abd401c) -- INROW
acquiring IX lock on PAGE: 6:1:59091 -- LOB
acquiring X lock on RID: 6:1:59091:1 -- LOB

releasing lock reference on PAGE: 6:1:59091 -- LOB
releasing lock reference on RID: 6:1:59091:1 -- LOB
releasing lock reference on KEY: 6:72057594233618432 (61a06abd401c) -- INROW
releasing lock reference on PAGE: 6:1:59104 -- INROW

With only a nonclustered index, the same optimization cannot be applied because we need to read from one b-tree structure and write another. The multi-path plan has separate read and write phases:

Multi-iterator update

This acquires update locks when reading, converting to exclusive locks if the row qualifies. Example lock sequence with the schema given:

acquiring IX lock on OBJECT: 6:992930809:0 -- TABLE
acquiring IU lock on PAGE: 6:1:59105 -- NC INDEX
acquiring U lock on KEY: 6:72057594233749504 (61a06abd401c) -- NC INDEX
acquiring IU lock on PAGE: 6:1:59104 -- HEAP
acquiring U lock on RID: 6:1:59104:1 -- HEAP
acquiring IX lock on PAGE: 6:1:59104 -- HEAP convert to X
acquiring X lock on RID: 6:1:59104:1 -- HEAP convert to X
acquiring IU lock on PAGE: 6:1:59091 -- LOB
acquiring U lock on RID: 6:1:59091:1 -- LOB

releasing lock reference on PAGE: 6:1:59091 
releasing lock reference on RID: 6:1:59091:1
releasing lock reference on RID: 6:1:59104:1
releasing lock reference on PAGE: 6:1:59104 
releasing lock on KEY: 6:72057594233749504 (61a06abd401c)
releasing lock on PAGE: 6:1:59105

Note the LOB data is read and written at the Table Update iterator. The more complex plan and multiple read and write paths increase the chances of a deadlock.

Finally, I can't help but notice the data types used in the table definition. You should not use the deprecated text data type for new work; the alternative, if you really need the ability to store up to 2GB of data in this column, is varchar(max). One important difference between text and varchar(max) is that text data is stored off-row by default, while varchar(max) stores in-row by default.

Use Unicode types only if you need that flexibility (e.g. it is hard to see why an IP address would need Unicode). Also, choose appropriate length limits for your attributes - 255 everywhere seems unlikely to be correct.

Additional reading:
Deadlock and livelock common patterns
Bart Duncan's deadlock troubleshooting series

Tracing locks can be done in a variety of ways. SQL Server Express with Advanced Services (2014 & 2012 SP1 onward only) contains the Profiler tool, which is a supported way to view the details of lock acquisition and release.

Sql-server – Why does UPDLOCK cause SELECTs to hang (lock)

As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.

Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.

The first transaction holds an update lock on the row where name = 1. The second transaction is blocked when it attempts to acquire an update lock on the same row (to test if name = 2 for that row).

With an index, SQL Server can quickly locate and lock only those rows that qualify, so there is no conflict.

You should review the code with a qualified database professional to validate the reason for the locking hint, and to ensure appropriate indexes are present.

Best Answer

Related Solutions

Sql-server – Deadlock when update different rows with non-clustered index

Sql-server – Why does UPDLOCK cause SELECTs to hang (lock)

Related Question