Sql-server – Trouble understanding locking logic

lockingsql serversql server 2014

While trying to create deadlock I've faced locking issue that I'm having trouble to understand:
I'm updating large table partitioned by timekey column:
enter image description here

I am trying to run two update statements:

BEGIN TRAN
    UPDATE [Transform].[AllCommunications_Arch] 
        SET Callid = 10300503454
    WHERE Callid = 10348103154
    AND TimeKey = 20161205

and 

BEGIN TRAN
    UPDATE [Transform].[AllCommunications_Arch] 
        SET Callid = 1234576704
    WHERE Callid = 4321276791
        AND TimeKey = 20160720

Each one should update one row, each one runs on different partition, but for some reason one statement still blocks another. DB has default isolation lever(read committed).
enter image description here

Execution plan:
enter image description here

CREATE TABLE [Transform].[AllCommunications_Arch](
    [SessionId] [varchar](100) NULL,
    [TimeKey] [int] NULL,
    [Callid] [bigint] NULL,
    [MemberId] [int] NULL,
    [Duration] [int] NULL,
    [CalledAt] [datetime] NULL,
    [EndTime] [datetime] NULL....
) ON [Transform_Timekey_Daily_Arch_PS]([TimeKey])
WITH
(
DATA_COMPRESSION = PAGE
)

GO

CREATE CLUSTERED INDEX [CL_Transform_AllCommunications_Arch] ON [Transform].[AllCommunications_Arch]
(
    [TimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [Transform_Timekey_Daily_Arch_PS]([TimeKey])
GO

I've changed LOCK_ESCALATION from default table to auto and now they do not block each other, but still I don't understand why server escalates lock in this case.

Best Answer

I've changed LOCK_ESCALATION from default table to auto and now they do not block each other, but still I don't understand why server escalates lock in this case.

This is specifically called out in the SQL Server Books Online CREATE TABLE topic. The note states:

After you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO. This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table.

The default table will escalate to the table level, which includes all partitions.

You could also improve both concurrency and performance of this query by adding Callid to the clustered index key and specify unique. This would touch only the single row needed by the query instead of scanning all rows in the partition with an exclusive lock.

Related Question