Trouble understanding locking logic

Question

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:

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

Execution plan:

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.

Answer 1

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.