"Unrelated" INSERT and UPDATE blocking each other

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?

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.

StackExchange Database Administrators Q#187029, answer score: 4