Blocking between two MERGE queries inserting into the same table

Question

Scenario

I have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other.

Sample code to recreate the scenario:

1. Preparation. Create tables and some dummy data

SET NOCOUNT ON
GO
--
--  Create parition function and partition scheme
--
    DROP FUNCTION IF EXISTS PF_Site_ID 
    GO
    CREATE PARTITION FUNCTION PF_Site_ID (INT)  
        AS RANGE RIGHT FOR VALUES   (
                                        0,
                                        1, 2, 3, 4, 5, 6, 7, 8, 9, 10
                                    )
    GO  
    DROP PARTITION SCHEME PS_Site_ID
    GO
    CREATE PARTITION SCHEME PS_Site_ID
        AS PARTITION PF_Site_ID
        ALL TO ('PRIMARY')
    GO

--
-- Large table partitioned on Site_ID. Two STG tables. And some dummy data
--
    DROP TABLE IF EXISTS dbo.PartitionedLargeTable
    GO
    CREATE TABLE dbo.PartitionedLargeTable
    (
          ID       INT          NOT NULL IDENTITY(1,1)
        , Site_ID  INT          NOT NULL
        , Name     VARCHAR(50)
    ) ON PS_Site_ID (Site_ID)
    GO
    ALTER TABLE dbo.PartitionedLargeTable SET (LOCK_ESCALATION = AUTO)
    GO

--
--  STG tables
--
    DROP TABLE IF EXISTS dbo.STG_Test1
    GO
    CREATE TABLE dbo.STG_Test1
    (
          ID       INT          NOT NULL IDENTITY(1,1)
        , Site_ID  INT          NOT NULL
        , Name     VARCHAR(50)
    ) ON [PRIMARY]
    GO
    DROP TABLE IF EXISTS dbo.STG_Test2
    GO
    CREATE TABLE dbo.STG_Test2
    (
          ID       INT          NOT NULL IDENTITY(1,1)
        , Site_ID  INT          NOT NULL
        , Name     VARCHAR(50)
    ) ON [PRIMARY]
    GO

--
--  Dummy data
--
    INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name) SELECT 1, NEWID()
    INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name) SELECT 2, NEWID()
    GO 10000

    INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name)
    SELECT Site_ID, Name FROM dbo.PartitionedLargeTable
    GO 5

    INSERT INTO dbo.STG_Test1(Site_ID, Name) SELECT 1, NEWID()
    GO 10000
    INSERT INTO dbo.STG_Test2(Site_ID, Name) SELECT 2, NEWID()
    GO 10000

    INSERT INTO dbo.STG_Test1 (Site_ID, Name)
    SELECT Site_ID, Name FROM dbo.STG_Test1
    GO 7

    INSERT INTO dbo.STG_Test2 (Site_ID, Name)
    SELECT Site_ID, Name FROM dbo.STG_Test2
    GO 7

2. MERGE 1

In one SSMS window, run this MERGE statement:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ID, Site_ID, Name FROM dbo.STG_Test1) AS SRC
    ON  SRC.Site_ID = TGT.Site_ID
    AND SRC.ID      = TGT.ID

WHEN MATCHED THEN
    UPDATE 
        SET TGT.Name = SRC.Name

WHEN NOT MATCHED THEN
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name);

3. MERGE 2

In a second SSMS window, run this MERGE statement:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ID, Site_ID, Name FROM dbo.STG_Test2) AS SRC
    ON  SRC.Site_ID = TGT.Site_ID
    AND SRC.ID      = TGT.ID

WHEN MATCHED THEN
    UPDATE 
        SET TGT.Name = SRC.Name

WHEN NOT MATCHED THEN
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name);

---

The two MERGE statements run on different Site_IDs (so two different partitions).

One of the performance benefits of partitioned tables is that we could manipulate partitions independently of each other (within reason). So, something like INSERT or UPDATE on one partition will not block similar operations on other partitions.

Compare this to when the table is NOT partitioned, if we perform two large INSERT operations (or two large UPDATE operations), then one blocks the other once the number of rows manipulated goes over a certain number (something like 3k or 5k rows), then the PAGE lock is escalated to TABLOCK. Hence INSERT blocks INSERT (or UPDATE blocks UPDATE)

To avoid such lock escalation to TABLOCK, this table was partitioned with LOCK_ESCALATION = AUTO, which limits locks up to HOBT level (and not table). But with MERGE, the blocking still happens.

Any ideas on how to prevent this blocking? We have 10 parallel MERGE statements running, on 10 different partitions of this large table (and they are blocking each other).

The image below shows the nature of blocking. When a table is partitioned, the lock escalation is supposed to only go up to the partition (not to the whole table). When these MERGE statements are running, I see the HOBT id's that each MERGE is querying (locking). And in some cases, the HOBT ID does not match the partition IDs of this table.

The actual table I work with has a COLUMNSTORE CLUSTERED index on the partitioning scheme.

Answer 1

The execution plan I see with your example data isn't getting any partition elimination and just scans the whole table.

This means it ends up reading pages belonging to other site ids and getting blocked on page locks for pages that it doesn't need to be reading (your screen shot doesn't necessarily show that the lead blocker is holding an object lock)

You can use

--Implicitly asserts that the staging table does not contain more than 
--one distinct value as error will be thrown. 
DECLARE @Site_ID INT = (SELECT DISTINCT Site_ID FROM dbo.STG_Test1);

WITH TGT AS (SELECT * FROM dbo.PartitionedLargeTable WHERE Site_ID = @Site_ID)
MERGE TGT
USING dbo.STG_Test1 AS SRC
    ON  SRC.ID      = TGT.ID
WHEN MATCHED AND TGT.Name <> SRC.Name OR (TGT.Name IS NULL AND SRC.Name IS NOT NULL) OR (SRC.Name IS NULL AND TGT.Name IS NOT NULL) THEN
    UPDATE 
        SET TGT.Name = SRC.Name
WHEN NOT MATCHED THEN
    INSERT (Site_ID, Name)
    VALUES (@Site_ID, SRC.Name);

Hopefully in reality you also have useful partition aligned indexes so it doesn't just have to scan the entire partition and hash join it to the staging table.

Answer 2

'PartitionedLargeTable' in your sample has not index, when execute merge it cause table scan. When transaction isolation level of you database is READ COMMITTED, merge will locks large rows exists in target table with long time, it might block write operation of UPDATE. I tried to build a NonClusteredIndex on 'PartitionedLargeTable', which includes columns 'ID' and 'Site_ID', when excute merge, it's using index scan. By this way, read operation takes less time, and read I/O moves to index page separated from table heap page. It will help reduce lock conflict.