Início / dba / Perguntas / 198860
Accepted
PicoDeGallo
Asked: 2018-02-27 15:20:07 +0800 CST

Alterando funções de partição dinamicamente usando gatilhos

  • 772

Estou querendo utilizar o particionamento com base em um [TenantId](e posteriormente em conjunto com intervalos de datas). Em vez de precisar inserir manualmente o valor mais recente dentro do PARTITION FUNCTION, pensei em criar um TRIGGER AFTER INSERTpara puxar o [TenantId]valor e ALTER PARTITION FUNCTIONadicioná-lo ao SPLIT RANGE. No entanto, estou me deparando com um erro inesperado:

Não é possível executar ALTER PARTITION FUNCTION na/usando a tabela 'Tenant', pois a tabela é a tabela de destino ou parte das ações em cascata de um gatilho atualmente em execução.

Primeiro, estou criando o PARTITION FUNCTION [PF_Tenant_Isolation]and PARTITION SCHEME [PS_Tenant_Isolation]para particionamento no [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

Depois disso, estou criando a [Tenant]tabela no esquema de partição recém-criado.

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

Eu semeio o primeiro valor antes de criar o gatilho.

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

Eu crio o gatilho na tabela [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

Eu sigo isso com a tentativa de inserir o segundo [Tenant]valor.

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

É quando o erro descrito acima aparece. Com base no erro em si, lendo os argumentos do Technet , entendo que o problema está na utilização do AFTER INSERT. Como a ação de partição da transação depende da utilização do valor de intervalo dentro da função de partição, a ALTER PARTITION SCHEMEfalha e, portanto, toda a transação também.

AFTER especifica que o gatilho DML é acionado somente quando todas as operações especificadas na instrução SQL de gatilho forem executadas com êxito. Todas as ações referenciais em cascata e verificações de restrição também devem ser bem-sucedidas antes que esse gatilho seja acionado.

Eu olhei em INSTEAD OF INSERT , mas não tive nenhum sucesso. O gatilho é acionado uma vez e atualiza o SPLIT RANGEcom um valor de 0 (convertido implicitamente de NULL). Acredito que isso se deva ao fato de IDENTITYnão estar devidamente capturado no escopo da transação.

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

As inserções de linhas subsequentes [Tenant]produzem um erro adicional devido à tentativa de inserir 0 (NULL).

Valores de limite de intervalo duplicados não são permitidos na lista de valores de limite de função de partição. O valor de limite que está sendo adicionado já está presente no ordinal 1 da lista de valores de limite.

Como posso contornar isso? Preciso definir explicitamente o IDENTITYvalor de [TenantId]em conjunto com INSTEAD OF INSERT? Novas inserções em [Tenant]serão bastante esporádicas e mínimas, mas [TenantId]serão uma chave restritiva em outras tabelas. É por isso que decidi investigar esse método de implementação para alterar dinamicamente a função de partição.

sql-server database-design

1 respostas

  1. Best Answer

    O erro enigmático no AFTERgatilho é devido à execução de um DDL na tabela de destino do gatilho. Com o INSTEAD OFgatilho, você precisaria executar o INSERTpara obter o valor atribuído IDENTITYe, em seguida, dividir a função de partição. No entanto, você provavelmente não deseja usar IDENTITY aqui de qualquer maneira, pois eles podem ter lacunas que às vezes são grandes e resultam em uma lista de limites de partição desordenada.

    Abaixo está um exemplo que abandona a IDENTITY e usa uma função RANGE RIGHT, que acredito ser mais natural para limites de partição incrementais. Esta versão valida exatamente uma linha inserida, mas pode ser estendida para lidar com inserções de várias linhas, se necessário. Seu caso de uso, como eu o entendo, sugere apenas inserções singleton raras.

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

    EDITAR:

    Eu vejo sua notação, mas dado que as consultas serão lidas de [inquilino], o oposto não aconteceria onde isso realmente causaria impasses?

    O bloqueio X granular do curso na tabela Tenant aguardará (ser bloqueado por) outra atividade simultânea na tabela para ser concluído e, uma vez concedido, bloqueará outra atividade na tabela. Esse bloqueio evitará deadlocks na tabela Tenant durante a operação DDL na transação do acionador. A duração do SPLIT em si será rápida, pois as linhas não são movidas entre as partições. A duração do bloqueio antes que o bloqueio inicial do bloco X seja concedido dependerá de quanto tempo as outras consultas são executadas.

    No caso de várias tabelas (ou seja, tabelas relacionadas particionadas por esquemas baseados na mesma função), os deadlocks ainda podem ocorrer se a ordem de bloqueio no gatilho for diferente daquela de outra atividade. Um bloqueio exclusivo nessas tabelas também no gatilho pode apenas mitigar a probabilidade de deadlocks nesse caso. Por exemplo, se você tiver uma consulta SELECT que une Tenant e TenantDetails, ambos particionados de forma semelhante, pode ocorrer um deadlock se a consulta adquirir bloqueios dessas tabelas na ordem inversa do gatilho.

    Além disso, entendo que com esquemas de partição você normalmente deseja deixar partições nos limites esquerdo e direito que estão "vazios" para a comutação adequada.

    As partições vazias são uma consideração para SPLITe MERGEmas não para SWITCH. Com o SPLITno acionador, a partição dividida está sempre vazia, portanto, nenhuma movimentação de dados cara é necessária para estar em conformidade com a nova especificação de limite.

    A melhor prática geral é MERGEdelimitar quando ambas as partições adjacentes estiverem vazias. Dito isso, você pode sill MERGEsem movimento de linha, desde que a partição que contém o limite (uma à direita com uma RANGE RIGHTfunção) esteja vazia.

    • 3

relate perguntas