SQL Server 2022 - Why does a DELETE using a clustered index scan block on an uncommitted INSERT’s key lock when its predicate excludes that row?

Question

SQL Server 2022 - Why does a DELETE using a clustered index scan block on an uncommitted INSERT’s key lock when its predicate excludes that row?

Seth 0

I have a single-column table (Number, bigint, PK, clustered, unique) that is usually small (always under 5,000 rows, typically 1,000 or fewer).

Service A frequently INSERTs rows inside long-running transactions. A row is inserted but not committed until about a minute later.
Service B runs every 30s and DELETEs up to 500 rows at once. The rows it deletes are never the uncommitted ones. This is because it polls the oldest 500 (or less) records under READ COMMITTED.

Service B occasionally hits its 30s command timeout. Monitoring shows the DELETE is blocked by Service A with wait_type = LCK_M_U and wait_resource = KEY. So the DELETE is waiting for a U lock on a key while the INSERT holds an X lock on it. That doesn’t make sense to me, because the DELETE never targets uncommitted rows.

Monitoring also shows that when the DELETE times out, it is using a Clustered Index Scan rather than a Seek. My guess is that the optimizer chooses a scan because the DELETE targets a large percentage of a very small table, so scanning is cheaper than seeking each row.

DELETE query:

DELETE FROM QueueTable
WHERE Number in <list of Numbers>

SQL Server version: 2022 16.0.4275.2 Isolation: RCSI is enabled for all services. I know row versioning mostly applies to reads and that data modification statements generally ignore it.

My question: when the DELETE scans during its locate phase, does it only wait when it reaches the specific uncommitted key, or does it see something at the table or page level (the IX locks held by the inserting transaction) and wait immediately? Does the scan have to take a U lock on every key it passes, including keys outside my predicate, before it can evaluate the predicate? Is this intentional behavior, and why does SQL Server do it?

I’m not necessarily looking for a fix. I know I could add READPAST or FORCESEEK to the DELETE, shorten the INSERT transaction, or use a larger table. I’d be interested in other approaches, but mainly I want to understand how the optimizer and the locate phase work and what a query can “see” while locating rows.

0 comments

2 answers

Your answer

Answer 1

Bruce (SqlWork.com) 85,696

Service A is taking page locks to insert. Service B definitely needs a page lock to perform a delete. Service B will be blocked if the scan or delete hits Service A locked page.

Seth 0 Reputation points

2026-10-23T14:23:54.4366667+00:00

If both services are requesting page locks, why does wait_resource = KEY (row) ?
Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-23T14:41:00.6366667+00:00

Because Bruce is wrong. :--) Both processes need intent locks on page, but that does cause any blocking. You get the blocking, because, as Deepesh explained, the DELETE statement needs to lock the row to see if it qualifies for the delete.

Answer 2

Deepesh Dhake 1,325

The DELETE doesn't wait at the table or page level. It takes IX on the table and IU/IX on pages, and those are compatible with Service A's IX locks. The wait is on the specific KEY, which is what your monitoring shows.

The locate phase uses locking reads, not row versions, even under RCSI. Versioning only affects the reads of plain SELECTs.

A scan takes a U lock on every row it reads before testing your predicate. Rows that don't qualify are released right away under READ COMMITTED. Rows that qualify are converted to X. So when the Clustered Index Scan reaches Service A's uncommitted key, it waits on the X lock even though that key isn’t in your IN list.

A seek only touches the keys in your list, so it never meets the uncommitted row. That's why FORCESEEK or a seek plan avoids the blocking.

This is intentional. The engine only evaluates predicates on rows it has locked, so a row can't change between being tested and being modified. SQL Server 2022 has no way to test the last committed version first and lock only matching rows.

SQL Server 2025 adds that capability as Lock After Qualification, part of optimized locking. It's off by default and enabled per database. It requires ADR, and RCSI is needed for its benefit. With it on, the scan tests rows against the last committed version and locks only the rows it will delete, so the uncommitted insert doesn’t block it.

Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-23T07:17:26.7833333+00:00

SQL Server 2022 has no way to test the last committed version first and lock only matching rows.

Actually it has, if you are running with snapshot isolation. With snapshot isolation, DELETE statements uses the version store to find rows to delete. The new feature in SQL Server 2025, Optimized Locking, extends this to Read Committed Snapshot.

The DELETE doesn't wait at the table or page level.

As they say, it depends. When running a DELETE with an index scan, SQL Server may decide to take page locks rather than row locks to preserve memory. But that is less likely to happen with a 5000-row table.
Seth 0 Reputation points

2026-10-23T15:20:50.4066667+00:00

Thanks for the replies. In my case, not using Snapshot isolation, just Read Committed Snapshot.

Can you speak to my guess that SQL optimizer is choosing to do a scan over a seek when the DELETE targets a large portion of the table? From what I understand, 'the tipping point' usually happens after a certain percentage of the table is being targeted, as opposed to lock escalation, which happens after a hard set (~5,000) amount of lower-level locks are taken.

Also, does the optimizer see the uncommitted rows and include those when considering how much of the table is being targeted?
Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-23T15:56:09.31+00:00
The optimizer does not see any rows at all. It sees statistics. From the statistics, it sees the cardinality, the density and the distribution.

If you say:

DELETE tbl WHERE id (@val1, @val2, ..., @val500)

This is in practice the same as if you had inserted those 500 values into a temp table and said

DELETE tbl WHERE id IN (SELECT val FROM #temp)

This is because if there more than 63 values in the IN list, it creates a temp table internally. Here is a script to demonstrate:

DROP TABLE IF EXISTS #t CREATE TABLE #t(b bigint NOT NULL PRIMARY KEY) INSERT #t (b) SELECT value FROM generate_series(1, 5000) DECLARE @sql nvarchar(MAX) SELECT @sql = 'DELETE FROM #t WHERE b IN(' + string_agg(value, ',') + ')' FROM generate_series(1, 500) PRINT @sql EXEC (@sql)

Run this with display execution plan enabled. I get this plan for the DELETE:

That Constant Scan results in an internal temp table which is then sorted.

As you can see #t is scanned. This not surprising, since the DELETE qualifies ten per cent of the rows, which is well over the tipping point where looking up each row is more expensive than scanning the table once. Note, though, that the scan does not read the entire table. In this example it can stop at b = 501, since the join is a Merge Join, and there is no value > 500 in the source.

It is not unlikely that you get this sort of plan in your system as well. And that plan will work most of the time, if these integers are added in a monotonically increasing fashion. If all numbers to delete are smaller the least uncommitted value, there will not be any blocking. But if a value in the list is > an uncommitted value, the CI scan on #t will block. It will also block if the lowest uncommitted value is the next one after the last value to delete. (Since it reads on row extra.)

I know I could ..., or use a larger table

Beware, the large the table, the more likely that the scan results in page locks, which increases the risk for blocking.

Lock escalation, on the other hand, is less of a concern here. Even if you would exceed the limit where SQL Server attempts lock escalation, this will fail because of the uncommitted rows, and SQL Server will jog along with the row locks.
Seth 0 Reputation points

2026-10-26T14:12:06.0433333+00:00

@Erland Sommarskog Thank you, this is very helpful.

It is somewhat often that the DELETE query has less than 63 values. In that case, would it scan the entire table?

The integers are added in a monotonically increasing fashion, however it is possible that, say, (1, 4, 5, 6) are the numbers to delete where the records with 2 and 3 are still uncommitted, due to multiple concurrent processes inserting to the queue table from Service A where some of them have longer transactions than others. It's also common for the lowest uncommitted value to be the next to delete. Is there a specific reason that SQL reads one row extra?
Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-26T14:21:00.4566667+00:00
It is somewhat often that the DELETE query has less than 63 values. In that case, would it scan the entire table?

That depends on the query plan. But it does have to. Run this in one window:

DROP TABLE IF EXISTS T go CREATE TABLE T(b bigint NOT NULL PRIMARY KEY) INSERT T (b) SELECT value FROM generate_series(1, 5000) WHERE value <> 43 BEGIN TRANSACTION INSERT T(b) VALUES (43)

Then in a second window run

DECLARE @sql nvarchar(MAX) SELECT @sql = 'DELETE FROM T WHERE b IN(' + string_agg(value, ',') + ')' FROM generate_series(1, 50) WHERE value <> 43 PRINT @sql EXEC (@sql)

When I try it, the DELETE complets. The plan is a Clustered Index Seek.

Is there a specific reason that SQL reads one row extra?

That is how the Merge Join operator works. It can stop reading the table first when there are no more rows in the outer input. Note that if SQL Server would settle for a Hash Join, it would always read the entire table.