主页 / dba / 问题 / 198860
Accepted
PicoDeGallo
Asked: 2018-02-27 15:20:07 +0800 CST

利用触发器动态改变分区函数

  • 772

我想利用基于 a 的分区[TenantId]（稍后结合日期范围）。PARTITION FUNCTION我不需要TRIGGER AFTER INSERT在. [TenantId]_ ALTER PARTITION FUNCTION_ SPLIT RANGE但是，我遇到了一个意外错误：

无法在/使用表“租户”上执行 ALTER PARTITION FUNCTION，因为该表是目标表或当前正在执行的触发器的级联操作的一部分。

首先，我正在创建PARTITION FUNCTION [PF_Tenant_Isolation]andPARTITION SCHEME [PS_Tenant_Isolation]用于在[TenantId].

CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    AS RANGE LEFT FOR VALUES (1);
GO

CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
GO

在此之后，我将[Tenant]针对新创建的分区方案创建表。

IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[Tenant] (
        [TenantId] [int] IDENTITY(1,1)
        ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1
        ,[TenantName] [varchar](256) NOT NULL
        ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
    ) ON [PS_Tenant_Isolation]([TenantId]);
END

我在创建触发器之前播种了第一个值。

INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test A');

我针对 [Tenant] 表创建触发器。

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
AFTER INSERT
AS
BEGIN
    DECLARE @MaxInsertedId int
    SET @MaxInsertedId = (SELECT MAX([TenantId]) FROM inserted)

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@MaxInsertedId);
END

我继续尝试插入第二个[Tenant]值。

INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test B');

这是弹出上述错误的时候。基于错误本身以及阅读Technet 论点，我理解使用中的问题AFTER INSERT。由于事务的分区动作依赖于利用分区函数内的范围值，ALTER PARTITION SCHEME因此失败，因此整个事务也是如此。

AFTER 指定仅当触发 SQL 语句中指定的所有操作都已成功执行时才触发 DML 触发器。在触发此触发器之前，所有引用级联操作和约束检查也必须成功。

我已经研究过INSTEAD OF INSERT但没有任何成功。触发器触发一次并SPLIT RANGE使用值 0 更新 （从 NULL 隐式转换）。我相信这是由于IDENTITY没有在交易范围内正确捕获。

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @MaxInsertedId int
    SET @MaxInsertedId =  (SELECT [TenantId] FROM inserted)

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@MaxInsertedId);

    INSERT INTO [Auth].[Tenant] ([TenantActive], [TenantName])
    SELECT [TenantActive], [TenantName]
    FROM inserted;
END

[Tenant]由于尝试输入 0 (NULL)，随后的行插入会产生额外的错误。

分区函数边界值列表中不允许有重复的范围边界值。添加的边界值已经存在于边界值列表的序号 1 中。

我该如何解决这个问题？我需要明确设置 的IDENTITY值[TenantId]吗INSTEAD OF INSERT？新插入[Tenant]将是相当零星的和最小的，但[TenantId]将成为其他表的约束键。这就是为什么我决定研究这种实现方法以便动态改变分区函数的原因。

sql-server database-design

1 个回答

  1. Best Answer

    触发器中的神秘错误AFTER是由于对触发器目标表执行 DDL。使用INSTEAD OF触发器，您需要执行INSERT以获取分配的IDENTITY值，然后拆分分区函数。但是，您可能无论如何都不想在这里使用 IDENTITY，因为它们可能存在有时很大的间隙并导致分区边界列表不整洁。

    下面是一个放弃 IDENTITY 并使用 RANGE RIGHT 函数的示例，我认为这对于增量分区边界更自然。此版本验证是否插入了一行，但如果需要，可以扩展为处理多行插入。据我了解，您的用例仅建议罕见的单例插入。

    --start with no partition boundaries
CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    AS RANGE RIGHT FOR VALUES ();
GO

CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
GO

CREATE TABLE [Auth].[Tenant] (
     [TenantId] [int] NOT NULL
    ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1
    ,[TenantName] [varchar](256) NOT NULL
    ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
) ON [PS_Tenant_Isolation]([TenantId]);
GO

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
DECLARE @TenantId int;
BEGIN TRY

    --Get next TenantId and exclusively lock table to prevent deadlocking during DDL.
    --If other tables are partitoned via this function, add code to get exclusive locks on those too.
    SELECT TOP(1) @TenantId = COALESCE(MAX(TenantId),0) + 1 FROM [Auth].[Tenant] WITH(TABLOCKX);

    INSERT INTO [Auth].[Tenant] ([TenantId], [TenantActive], [TenantName])
        SELECT @TenantId, [TenantActive], [TenantName]
        FROM inserted;

    IF @@ROWCOUNT <> 1
    BEGIN
        RAISERROR('Exactly one row must be inserted into Auth.Tenant at a time',16,1);
    END;

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@TenantId);

END TRY
BEGIN CATCH;
    THROW;
END CATCH;
GO

INSERT INTO [Auth].[Tenant]([TenantActive], [TenantName])
VALUES (1,'Partition Trigger Test A');
GO

    编辑：

    我看到了您的符号，但是鉴于查询将从 [Tenant] 读取，在实际上会导致死锁的情况下不会发生相反的情况吗？

    租户表上的粗粒度 X 锁将等待（被阻止）针对该表的其他并发活动完成，并且一旦授予，就阻止针对该表的其他活动。此阻塞将防止触发器事务中的 DDL 操作期间租户表上的死锁。SPLIT 本身的持续时间会很快，因为行不会在分区之间移动。在授予初始块 X 锁之前的阻塞持续时间将取决于其他查询运行多长时间。

    在多个表的情况下（即基于相同功能的方案划分的相关表），如果触发器中的锁定顺序与其他活动的锁定顺序不同，仍然会发生死锁。在触发器中也对这些表进行排他锁只能减轻在这种情况下发生死锁的可能性。例如，如果您有一个连接 Tenant 和 TenantDetails 的 SELECT 查询，两者的分区相似，如果查询以相反的顺序作为触发器获取锁这些表，则可能会发生死锁。

    此外，我了解分区方案通常希望将分区留在“空”的左右边界上以进行正确切换。

    空分区是SPLITandMERGE但不是的考虑因素SWITCH。在SPLIT触发器中，拆分分区始终为空，因此无需进行昂贵的数据移动即可符合新的边界规范。

    一般的最佳实践是MERGE在两个相邻分区都为空时进行边界。也就是说，MERGE只要包含边界的分区（右侧有一个RANGE RIGHT函数）是空的，您就可以在不移动行的情况下进行窗台。

    • 3

相关问题