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

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.

Answer 1

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.

Answer 2

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.