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 era esperado, o pool de SQL oferece 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 serão limitados em comparação com o SQL Server. Este artigo destaca as diferenças entre eles.

Níveis de isolamento da transação

O pool de SQL implementa transações ACID. O nível de isolamento do suporte transacional usa como padrão 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.

Após a habilitação, todas as transações nesse banco de dados serão executadas em READ COMMITTED SNAPSHOT ISOLATION, e a configuração READ UNCOMMITTED no nível da sessão não será respeitada. Confira Opções ALTER DATABASE SET (Transact-SQL) para 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 chegar a uma aproximação do número máximo de linhas na transação, divida o limite de distribuição pelo tamanho total de cada linha. Para colunas de tamanho variável, considere o uso de um tamanho médio de coluna em vez do tamanho máximo.

Na tabela abaixo, foram feitas as seguintes suposições:

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

Gen2

Gen1

O limite de tamanho de transação é aplicado por transação ou operação. Ele não é aplicado 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 Transactions best practices(Melhores práticas 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 para reversão somente.

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.

Portanto, é importante verificar o código do aplicativo para ver se ele usa XACT_STATE(), pois você pode precisar modificar o código.

Por exemplo, no SQL Server, você verá uma transação semelhante ao 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 oferece a seguinte mensagem de erro:

Msg 111233, Nível 16, Estado 1, Linha 1 111233; a transação atual foi anulada, e 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 receberá a saída das funções ERROR_*.

No pool de SQL, 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 é observado agora. 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 aceita a função ERROR_LINE(). Se você tiver isso em seu código, será necessário removê-lo para que ele fique em conformidade com o pool de SQL.

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

Uso de THROW e RAISERROR

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

• Os números das 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 do 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.

Elas são as seguintes:

• Sem transações distribuídas
• Não há transações aninhadas permitidas
• Não são permitidos pontos de salvamento
• Nenhuma transação nomeada
• Nenhuma transação marcada
• Não há suporte para DDL, como CREATE TABLE em uma transação definida pelo usuário

Próximas etapas

Para saber mais sobre a otimização das transações, consulte Transactions best practices (Melhores práticas de transações). Para saber mais sobre outras práticas recomendadas, consulte Práticas recomendadas do pool de SQL.