Usar transações em um pool de SQL no Azure Synapse

Este artigo inclui dicas para implementar transações e desenvolver soluções em um pool de SQL.

O que esperar

Como seria de esperar, o pool de SQL dá suporte a transações como parte da carga de trabalho do data warehouse. No entanto, para garantir que o pool de SQL seja mantido em escala, alguns recursos são limitados quando comparados ao SQL Server. Este artigo destaca as diferenças.

Níveis de isolamento de transação

O pool de SQL implementa transações ACID. O nível de isolamento do suporte transacional é padrão para READ UNCOMMITTED. Você pode alterá-lo para READ COMMITTED SNAPSHOT ISOLATION selecionando ON na opção de banco de dados READ_COMMITTED_SNAPSHOT para um pool de SQL do usuário ao conectar-se com o banco de dados mestre.

Uma vez habilitada, todas as transações nesse banco de dados são executadas em READ COMMITTED SNAPSHOT ISOLATION e a configuração READ UNCOMMITTED no nível da sessão não será respeitada. Verifique as opções ALTER DATABASE SET (Transact-SQL) para obter detalhes.

Tamanho da transação

Uma única transação de modificação de dados é limitada em tamanho. O limite é aplicado por distribuição. Portanto, a alocação total pode ser calculada multiplicando o limite pela contagem de distribuição.

Para aproximar o número máximo de linhas na transação, divida o limite de distribuição pelo tamanho total de cada linha. Para colunas de comprimento variável, considere usar um comprimento médio da coluna em vez de usar o tamanho máximo.

Na tabela a seguir, duas suposições foram feitas:

• Ocorreu uma distribuição uniforme de dados
• O comprimento médio da linha é de 250 bytes

Gen2

Primeira Geração

O limite de tamanho da transação é aplicado por transação ou operação. Ela não é aplicada em todas as transações simultâneas. Portanto, cada transação tem permissão para gravar essa quantidade de dados no log.

Para otimizar e minimizar a quantidade de dados gravados no log, consulte o artigo de práticas recomendadas de transações .

Estado da transação

O pool de SQL usa a função XACT_STATE() para relatar uma transação com falha usando o valor -2. Esse valor significa que a transação falhou e está marcada apenas para reversão.

O SQL Server também permite leituras na transação não confirmável. No entanto, o pool de SQL não permite que você faça isso. Se um erro ocorrer dentro de uma transação do pool de SQL, ela entrará automaticamente no estado -2 e não será mais possível dar instruções do tipo select até que a instrução seja revertida.

Dessa forma, é importante verificar se o código do aplicativo para ver se ele usa XACT_STATE(), pois talvez seja necessário fazer modificações de código.

Por exemplo, no SQL Server, você pode ver uma transação semelhante à seguinte:

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

O código anterior fornece a seguinte mensagem de erro:

Msg 111233, Nível 16, Estado 1, Linha 1 111233; A transação atual foi anulada e todas as alterações pendentes foram revertidas. Esse problema acontece quando uma transação em um estado somente reversão não é revertida explicitamente antes de uma instrução DDL, DML ou SELECT.

Você não obterá a saída das funções ERROR_*.

No SQL pool, o código precisa ser ligeiramente alterado:

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;
    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

O comportamento esperado agora é observado. O erro na transação é gerenciado e as funções ERROR_* fornecem valores conforme o esperado.

Tudo o que mudou é que o ROLLBACK da transação deve ocorrer antes da leitura das informações de erro no bloco CATCH.

função Error_Line()

Também vale a pena observar que o pool de SQL não implementa nem dá suporte à função ERROR_LINE(). Se você tiver isso em seu código, precisará removê-lo para estar em conformidade com o pool de SQL.

Use rótulos de consulta em seu código para implementar a funcionalidade equivalente. Para obter mais detalhes, consulte o artigo LABEL .

Usando THROW e RAISERROR

THROW é a implementação mais moderna para gerar exceções no pool de SQL, mas o RAISERROR também tem suporte. No entanto, há algumas diferenças que vale a pena prestar atenção.

• Os números de mensagens de erro definidas pelo usuário não podem estar no intervalo de 100.000 a 150.000 para THROW
• As mensagens de erro RAISERROR são fixadas em 50.000
• Não há suporte para o uso de sys.messages

Limitações

O pool de SQL tem algumas outras restrições relacionadas a transações.

Eles são os seguintes:

• Nenhuma transação distribuída
• Nenhuma transação aninhada permitida
• Pontos de salvamento não permitidos
• Nenhuma transação nomeada
• Nenhuma transação marcada
• Não há suporte para DDL, como CREATE TABLE dentro de uma transação definida pelo usuário

Próximas etapas

Para saber mais sobre como otimizar transações, consulte as práticas recomendadas de transações. Para saber mais sobre outras práticas recomendadas do pool de SQL, consulte as práticas recomendadas do pool de SQL.