1

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.

5

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.

  • Thanks Dan, but I'm not trying to improve this query performance, as I wrote, I just tried to generate a deadlock for testing purposes. I've changed lock escalation property because of this note, but it still don't explain why sql server decides to escalate this lock since it needs to find only a small amount of rows. – Michael Cherevko Dec 13 '16 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.