Partilhar via

Utilizar transações num conjunto de SQL no Azure Synapse

  • Artigo

Este artigo inclui sugestões para implementar transações e desenvolver soluções num conjunto de SQL.

O que esperar

Como seria de esperar, o conjunto de SQL suporta transações como parte da carga de trabalho do armazém de dados. No entanto, para garantir que o conjunto de SQL é mantido em escala, algumas funcionalidades são limitadas quando comparadas com SQL Server. Este artigo realça as diferenças.

Níveis de isolamento de transações

O conjunto de SQL implementa transações ACID. O nível de isolamento do suporte transacional é predefinido para READ UNCOMMITTED. Pode alterá-lo para ISOLAMENTO DE INSTANTÂNEO CONSOLIDADO DE LEITURA ao ativar a opção de base de dados READ_COMMITTED_SNAPSHOT para um conjunto de SQL do utilizador quando estiver ligado à base de dados mestra.

Depois de ativada, todas as transações nesta base de dados são executadas em ISOLAMENTO DE INSTANTÂNEOS CONSOLIDADO DE LEITURA e a definição LER NÃO CONSOLIDADA ao nível da sessão não será cumprida. 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. Por conseguinte, 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 ter um comprimento médio de coluna em vez de utilizar o tamanho máximo.

Na tabela seguinte, foram efetuadas duas suposições:

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

Gen2

DWU Limite por distribuição (GB) Número de Distribuições Tamanho máximo da transação (GB) # Linhas por distribuição Linhas Máximas por transação
DW100c 1 60 60 4,000,000 240,000,000
DW200c 1.5 60 90 6,000,000 360,000,000
DW300c 2.25 60 135 9,000,000 540,000,000
DW400c 3 60 180 12,000,000 720,000,000
DW500c 3,75 60 225 15,000,000 900,000,000
DW1000c 7,5 60 450 30,000,000 1,800,000,000
DW1500c 11.25 60 675 45,000,000 2,700,000,000
DW2000c 15 60 900 60,000,000 3,600,000,000
DW2500c 18.75 60 1125 75,000,000 4,500,000,000
DW3000c 22,5 60 1,350 90,000,000 5,400,000,000
DW5000c 37,5 60 2,250 150,000,000 9,000,000,000
DW6000c 45 60 2,700 180,000,000 10,800,000,000
DW7500c 56.25 60 3,375 225,000,000 13,500,000,000
DW10000c 75 60 4500 300,000,000 18,000,000,000
DW15000c 112,5 60 6,750 450,000,000 27,000,000,000
DW30000c 225 60 13,500 900,000,000 54,000,000,000

Gen1

DWU Limite por distribuição (GB) Número de Distribuições Tamanho máximo da transação (GB) # Linhas por distribuição Linhas Máximas por transação
DW100 1 60 60 4,000,000 240,000,000
DW200 1.5 60 90 6,000,000 360,000,000
DW300 2.25 60 135 9,000,000 540,000,000
DW400 3 60 180 12,000,000 720,000,000
DW500 3,75 60 225 15,000,000 900,000,000
DW600 4,5 60 270 18,000,000 1,080,000,000
DW1000 7,5 60 450 30,000,000 1,800,000,000
DW1200 9 60 540 36,000,000 2,160,000,000
DW1500 11.25 60 675 45,000,000 2,700,000,000
DW2000 15 60 900 60,000,000 3,600,000,000
DW3000 22,5 60 1,350 90,000,000 5,400,000,000
DW6000 45 60 2,700 180,000,000 10,800,000,000

O limite de tamanho da transação é aplicado por transação ou operação. Não é aplicado em todas as transações simultâneas. Por conseguinte, cada transação tem permissão para escrever esta quantidade de dados no registo.

Para otimizar e minimizar a quantidade de dados escritos no registo, veja o artigo Melhores práticas de transações .

Aviso

O tamanho máximo da transação só pode ser alcançado para HASH ou ROUND_ROBIN tabelas distribuídas em que a propagação dos dados é uniforme. Se a transação estiver a escrever dados de forma distorcida para as distribuições, é provável que o limite seja atingido antes do tamanho máximo da transação.

Estado da transação

O conjunto de SQL utiliza a função XACT_STATE() para comunicar uma transação falhada com o valor -2. Este valor significa que a transação falhou e está marcada apenas para reversão.

Nota

A utilização de -2 pela função XACT_STATE para denotar uma transação falhada representa um comportamento diferente para SQL Server. SQL Server utiliza o valor -1 para representar uma transação não comprometedora. SQL Server pode tolerar alguns erros dentro de uma transação sem ter de ser marcada como incomodável. Por exemplo, causaria um erro, SELECT 1/0 mas não forçaria uma transação para um estado não comprometedor.

SQL Server também permite leituras na transação não comprometedora. No entanto, o conjunto de SQL não lhe permite fazê-lo. Se ocorrer um erro dentro de uma transação do conjunto de SQL, este irá introduzir automaticamente o estado -2 e não poderá efetuar mais instruções selecionadas até que a instrução seja revertida.

Como tal, é importante verificar se o código da aplicação utiliza XACT_STATE(), uma vez que poderá ter de efetuar modificações de código.

Por exemplo, no SQL Server, poderá 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 abortada e quaisquer alterações pendentes foram revertidas. A causa deste problema é que uma transação num estado apenas de reversão não está a ser explicitamente revertida antes de uma instrução DDL, DML ou SELECT.

Não obterá o resultado das funções ERROR_*.

No conjunto de SQL, o código tem de 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 é gerido e as funções ERROR_* fornecem valores conforme esperado.

Tudo o que mudou foi que a REVERSÃO da transação tinha de ocorrer antes da leitura das informações de erro no bloco CATCH.

função Error_Line()

Também vale a pena notar que o conjunto de SQL não implementa nem suporta a função ERROR_LINE(). Se tiver isto no seu código, terá de removê-lo para estar em conformidade com o conjunto de SQL.

Utilize etiquetas de consulta no seu código para implementar funcionalidades equivalentes. Para obter mais detalhes, consulte o artigo ETIQUETA .

Utilizar THROW e RAISERROR

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

  • Os números de mensagens de erro definidas pelo utilizador não podem estar no intervalo de 100 000 a 150 000 para THROW
  • As mensagens de erro RAISERROR são corrigidas em 50 000
  • A utilização de sys.messages não é suportada

Limitações

O conjunto de SQL tem algumas outras restrições relacionadas com transações.

São os seguintes:

  • Sem transações distribuídas
  • Não são permitidas transações aninhadas
  • Não são permitidos pontos de poupança
  • Sem transações nomeadas
  • Sem transações marcadas
  • Não existe suporte para DDL, como CRIAR TABELA dentro de uma transação definida pelo utilizador

Passos seguintes

Para saber mais sobre a otimização de transações, veja Melhores práticas de transações. Para saber mais sobre outras melhores práticas do conjunto de SQL, veja Melhores práticas do conjunto de SQL.