In one of our databases we have a table that is intensively concurrently accessed by multiple threads. Threads do update or insert rows via MERGE. There are also threads that delete rows on occasion, so table data is very volatile. Threads doing upserts suffer from deadlocking sometimes. The issue looks similar to one described in this question. The difference, though, is that in our case each thread do update or insert exactly one row.

Simplified setup is following. The table is heap with two unique nonclustered indexes over

CREATE TABLE [Cache]
(
    [UID] uniqueidentifier NOT NULL CONSTRAINT DF_Cache_UID DEFAULT (newid()),
    [ItemKey] varchar(200) NOT NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Expires] datetime2(2) NOT NULL,
    CONSTRAINT [PK_Cache] PRIMARY KEY NONCLUSTERED ([UID])
)
GO
CREATE UNIQUE INDEX IX_Cache ON [Cache] ([ItemKey]);
GO

and the typical query is

DECLARE
    @itemKey varchar(200) = 'Item_0F3C43A6A6A14255B2EA977EA730EDF2',
    @fileName nvarchar(255) = 'File_0F3C43A6A6A14255B2EA977EA730EDF2.dat';

MERGE INTO [Cache] WITH (HOLDLOCK) T
USING (
    VALUES (@itemKey, @fileName, dateadd(minute, 10, sysdatetime()))
) S(ItemKey, FileName, Expires)
ON T.ItemKey = S.ItemKey
WHEN MATCHED THEN
    UPDATE
    SET
        T.FileName = S.FileName,
        T.Expires = S.Expires
WHEN NOT MATCHED THEN
    INSERT (ItemKey, FileName, Expires)
    VALUES (S.ItemKey, S.FileName, S.Expires)
OUTPUT deleted.FileName;

i.e., matching happens by unique index key. Hint HOLDLOCK is here, because of concurrency (as advised here).

I did small investigation and the following is what I found.

In most of the cases query execution plan is

with the following locking pattern

i.e. IX lock on the object followed by more granular locks.

Sometimes, however, query execution plan is different

(this plan shape can be forced by adding INDEX(0) hint) and its locking pattern is

notice X lock placed on object after IX is placed already.

Since two IX are compatible, but two X are not, the thing that happens under concurrency is

deadlock!

And here the first part of the question arises. Is placing X lock on object after IX eligible? Isn't it bug?

Documentation states:

Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

and also

IX means the intention to update only some of the rows rather than all of them

so, placing X lock on object after IX looks VERY suspicious to me.

First I attempted to prevent deadlocking by trying to add table locking hints

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCK) T

and

MERGE INTO [Cache] WITH (HOLDLOCK, TABLOCKX) T

with the TABLOCK in place locking pattern becomes

and with the TABLOCKX locking pattern is

since two SIX (as well as two X) are not compatible this prevents deadlock effectively, but, unfortunately, prevents concurrency as well (which is not desired).

My next attempts were adding PAGLOCK and ROWLOCK to make locks more granular and reduce contention. Both has no effect (X on object was still observed immediately after IX).

My final attempt was forcing "good" execution plan shape with good granular locking by adding FORCESEEK hint

MERGE INTO [Cache] WITH (HOLDLOCK, FORCESEEK(IX_Cache(ItemKey))) T

and it worked.

And here the second part of the question arises. Could it happen that FORCESEEK will be ignored and bad locking pattern will be used? (As I mentioned, PAGLOCK and ROWLOCK were ignored seemingly).

Update:

Adding UPDLOCK has no effect (X on object still observable after IX).

Making IX_Cache index clustered, as anticipated, worked. It led to plan with Clustered Index Seek and granular locking. Additionally I tried forcing Clustered Index Scan that shown granular locking also.

However. Additional observation. In the original setup even with FORCESEEK(IX_Cache(ItemKey))) in place, if one change @itemKey variable declaration from varchar(200) to nvarchar(200), execution plan becomes

see that seek is used, BUT locking pattern in this case again shows X lock placed on object after IX.

So, it seems that forcing seek may not guarantee. I'm not confident, that having clustered index guarantee something. Or does it?

My understanding (correct me if I'm wrong) is that locking is situational in a great degree, and certain execution plan shape does not implies certain locking pattern.

The question about eligibility of placing X lock on object after IX still open. If it is eligible. How can one avoid object locking?