两个 MERGE 查询插入同一张表时发生阻塞

设想

我有一个按列分区的大表INT。当我MERGE在该表的两个不同分区上运行两个不同的语句时，它们似乎互相阻塞。

重现该场景的示例代码：

1. 准备工作。创建表和一些虚拟数据

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. 合并 1

在一个 SSMS 窗口中，运行此MERGE语句：

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. 合并 2

在第二个 SSMS 窗口中，运行此MERGE语句：

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

这两个语句在不同的Site_IDMERGE上运行（因此是两个不同的分区）。

分区表的一个性能优势是我们可以独立地操作分区（在合理范围内）。因此，在一个分区上执行类似INSERT或 的UPDATE操作不会阻止在其他分区上执行类似操作。

与未分区表相比，如果我们执行两个大型INSERT操作（或两个大型UPDATE操作），一旦操作的行数超过一定数量（例如 3k 或 5k 行），一个操作就会阻塞另一个操作，然后锁PAGE将升级为TABLOCK。因此INSERT阻塞INSERT（或UPDATE阻塞UPDATE）

为了避免将锁升级到TABLOCK，此表使用 LOCK_ESCALATION = AUTO 进行分区，这将锁限制在 HOBT 级别（而不是表）。但是使用MERGE，阻塞仍然会发生。

关于如何防止这种阻塞，您有什么想法吗？我们MERGE在这个大表的 10 个不同分区上运行了 10 个并行语句（它们相互阻塞）。

下图显示了阻塞的本质。当表被分区时，锁升级应该只升级到分区（而不是整个表）。当这些MERGE语句运行时，我看到每个语句MERGE正在查询（锁定）的 HOBT ID。在某些情况下，HOBT ID 与此表的分区 ID 不匹配。

我实际使用的表COLUMNSTORE CLUSTERED在分区方案上有一个索引。