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](https://dba.stackexchange.com/q/23467/99746) 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](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx)).

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

In most of the cases query execution plan is

[![index seek execution plan][1]][1]

with the following locking pattern

[![index seek locking pattern][2]][2]

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

Sometimes, however, query execution plan is different

[![table scan execution plan][3]][3]

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

[![table scan locking pattern][4]][4]

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][5]][5]

[![deadlock graph][6]][6]

**deadlock**!

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

[Documentation](https://technet.microsoft.com/en-us/library/ms175519.aspx) 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](https://technet.microsoft.com/en-us/library/ms186396.aspx)

> 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

[![merge holdlock tablock locking pattern][7]][7]

and with the `TABLOCKX` locking pattern is

[![merge holdlock tablockx locking pattern][8]][8]

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).

---

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

[![index seek execution plan with nvarchar][9]][9]

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 not necessarily guarantee granular locks (and deadlocks absence hence). I'm not confident, that having clustered index guarantee granular locking. 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. And if it is eligible, is there something that one can do to prevent object locking?


  [1]: https://i.sstatic.net/MC0HA.png
  [2]: https://i.sstatic.net/HFrA1.png
  [3]: https://i.sstatic.net/Kzhuz.png
  [4]: https://i.sstatic.net/rXk11.png
  [5]: https://i.sstatic.net/4lEVs.png
  [6]: https://i.sstatic.net/ilA8a.png
  [7]: https://i.sstatic.net/s9stN.png
  [8]: https://i.sstatic.net/1SBw9.png
  [9]: https://i.sstatic.net/j3j3z.png