"Unrelated" INSERT and UPDATE blocking each other

Question

Repro scenario:

CREATE TABLE test (
  ID int IDENTITY(1,1),
  mykey nvarchar(255) NOT NULL,
  exp_date datetime,
PRIMARY KEY (ID));
GO

CREATE INDEX not_expired_keys ON test (exp_date, mykey);

INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);

I start transaction 1:

-- add key B
BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);
...

and then execute transaction 2 in parallel:

-- expire key A
BEGIN TRANSACTION;
UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking
ROLLBACK;

As it turns out, transaction 1's uncommitted INSERT blocks transaction 2's UPDATE, even though they affect disjoint sets of rows (mykey = 'B' vs. mykey = 'A').

Observations:

• The blocking also occurs on the lowest transaction isolation level READ UNCOMMITTED.
• The blocking goes away if I put a unique index on mykey. Unfortunately, I cannot do that, since key names can be reused once a key has expired.

My questions:

• (Out of curiosity:) Why do these statements block each other even on the READ UNCOMMITTED level?

• Is there an easy and reliable way to make them not block each other?

Answer 1

Lets take a look on the execution plans.

1st query - Insert

BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);

And its execution plan

We see that sql server is doing Clustered Index Insert operation.

Now lets take a look on update

BEGIN TRANSACTION;
UPDATE test 
SET exp_date = GETDATE() 
WHERE exp_date IS NULL AND mykey = 'A' -- <-- Blocking

And its execution plan

SQL Server scans the Clustered Index of the table, and put U lock on it, even if it can choose another index to find the matching rows. The reason is, because we have only 1 row in the table, and SQL Server Optimizer find easier to scan the Clustered Index instead of searching data in the non-clustered index.

But what if we force the sql server to use non-clustered index?

BEGIN TRANSACTION;
UPDATE test 
SET exp_date = GETDATE() 
FROM test WITH(INDEX = not_expired_keys)
WHERE exp_date IS NULL AND mykey = 'A' -- <-- No Blocking!!!

And its execution plan

I think if we put more rows in the table SQL Server will choose the non-clustered index for finding the rows that must be updated, and there will be no blocking.

Answer 2

This is essentially a bug in SQL server. Rather than implement predicate locks, MS took the cheap way out of blocking a range on one of the indexes used to select the rows from the table. If it didn't use an index (or if there's no index) it will block a range on the table instead.

Yeah forcing the index will cause it to lock on the index instead; however it always takes page locks. Page locks are bad for lock hierarchies; unrelated rows that happen to be on the same index page or table page will be blocked. There is no way around this with two writing statements. If one of your statements is reading, WITH(ROWLOCK) on the reader will bypass.

I opened a case with MS that lasted two months before they outright refused to fix it.