Usando transações no SQL Data WarehouseUsing transactions in SQL Data Warehouse

Dicas para implementar transações no Azure SQL Data Warehouse para o desenvolvimento de soluções.Tips for implementing transactions in Azure SQL Data Warehouse for developing solutions.

O que esperarWhat to expect

Como era esperado, o SQL Data Warehouse oferece suporte a transações como parte da carga de trabalho do data warehouse.As you would expect, SQL Data Warehouse supports transactions as part of the data warehouse workload. No entanto, para garantir que o desempenho do SQL Data Warehouse seja mantido em grande escala, alguns recursos serão limitados em comparação com o SQL Server.However, to ensure the performance of SQL Data Warehouse is maintained at scale some features are limited when compared to SQL Server. Este artigo realça as diferenças e lista as outras.This article highlights the differences and lists the others.

Níveis de isolamento da transaçãoTransaction isolation levels

O SQL Data Warehouse implementa transações ACID.SQL Data Warehouse implements ACID transactions. No entanto, o nível de isolamento do suporte transacional é limitado a READ UNCOMMITTED; esse nível não pode ser alterado.However, the isolation level of the transactional support is limited to READ UNCOMMITTED; this level cannot be changed. Se READ UNCOMMITTED for uma preocupação, será possível implementar inúmeros métodos de codificação para impedir leituras sujas de dados.If READ UNCOMMITTED is a concern, you can implement a number of coding methods to prevent dirty reads of data. Os métodos mais populares usam CTAS e a comutação de partição de tabela (normalmente conhecida como padrão de janela deslizante) para impedir que os usuários consultem dados que ainda estejam sendo preparados.The most popular methods use both CTAS and table partition switching (often known as the sliding window pattern) to prevent users from querying data that is still being prepared. Os modos de exibição que filtram previamente os dados também são uma abordagem popular.Views that pre-filter the data are also a popular approach.

Tamanho da transaçãoTransaction size

Uma única transação de modificação de dados é limitada em tamanho.A single data modification transaction is limited in size. O limite é aplicado por distribuição.The limit is applied per distribution. Portanto, a alocação total pode ser calculada multiplicando o limite pela contagem de distribuição.Therefore, the total allocation can be calculated by multiplying the limit by the distribution count. 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.To approximate the maximum number of rows in the transaction divide the distribution cap by the total size of each row. Para colunas de tamanho variável, considere o uso de um tamanho médio de coluna em vez do tamanho máximo.For variable length columns, consider taking an average column length rather than using the maximum size.

Na tabela abaixo, foram feitas as seguintes suposições:In the table below the following assumptions have been made:

  • Ocorreu uma distribuição uniforme dos dadosAn even distribution of data has occurred
  • O tamanho médio da linha é de 250 bytesThe average row length is 250 bytes

Gen2Gen2

DWUDWU Limite por distribuição (GB)Cap per distribution (GB) Número de distribuiçõesNumber of Distributions Tamanho máximo da transação (GB)MAX transaction size (GB) Nº de linhas por distribuição# Rows per distribution Máximo de linhas por transaçãoMax Rows per transaction
DW100cDW100c 11 6060 6060 4.000.0004,000,000 240.000.000240,000,000
DW200cDW200c 1.51.5 6060 9090 6.000.0006,000,000 360.000.000360,000,000
DW300cDW300c 2.252.25 6060 135135 9.000.0009,000,000 540.000.000540,000,000
DW400cDW400c 33 6060 180180 12.000.00012,000,000 720.000.000720,000,000
DW500cDW500c 3,753.75 6060 225225 15.000.00015,000,000 900.000.000900,000,000
DW1000cDW1000c 7.57.5 6060 450450 30.000.00030,000,000 1.800.000.0001,800,000,000
DW1500cDW1500c 11,2511.25 6060 675675 45.000.00045,000,000 2.700.000.0002,700,000,000
DW2000cDW2000c 1515 6060 900900 60.000.00060,000,000 3.600.000.0003,600,000,000
DW2500cDW2500c 18,7518.75 6060 11251125 75 milhões75,000,000 4.500.000.0004,500,000,000
DW3000cDW3000c 22,522.5 6060 1.3501,350 90.000.00090,000,000 5.400.000.0005,400,000,000
DW5000cDW5000c 37,537.5 6060 2.2502,250 150 milhões150,000,000 9.000.000.0009,000,000,000
DW6000cDW6000c 4545 6060 2.7002,700 180.000.000180,000,000 10.800.000.00010,800,000,000
DW7500cDW7500c 56,2556.25 6060 3.3753,375 225 milhões225,000,000 13.500.000.00013,500,000,000
DW10000cDW10000c 7575 6060 4.5004,500 300.000.000300,000,000 18.000.000.00018,000,000,000
DW15000cDW15000c 112,5112.5 6060 6.7506,750 450 milhões450,000,000 27.000.000.00027,000,000,000
DW30000cDW30000c 225225 6060 13.50013,500 900.000.000900,000,000 54.000.000.00054,000,000,000

Gen1Gen1

DWUDWU Limite por distribuição (GB)Cap per distribution (GB) Número de distribuiçõesNumber of Distributions Tamanho máximo da transação (GB)MAX transaction size (GB) Nº de linhas por distribuição# Rows per distribution Máximo de linhas por transaçãoMax Rows per transaction
DW100DW100 11 6060 6060 4.000.0004,000,000 240.000.000240,000,000
DW200DW200 1.51.5 6060 9090 6.000.0006,000,000 360.000.000360,000,000
DW300DW300 2.252.25 6060 135135 9.000.0009,000,000 540.000.000540,000,000
DW400DW400 33 6060 180180 12.000.00012,000,000 720.000.000720,000,000
DW500DW500 3,753.75 6060 225225 15.000.00015,000,000 900.000.000900,000,000
DW600DW600 4.54.5 6060 270270 18.000.00018,000,000 1.080.000.0001,080,000,000
DW1000DW1000 7.57.5 6060 450450 30.000.00030,000,000 1.800.000.0001,800,000,000
DW1200DW1200 99 6060 540540 36.000.00036,000,000 2.160.000.0002,160,000,000
DW1500DW1500 11,2511.25 6060 675675 45.000.00045,000,000 2.700.000.0002,700,000,000
DW2000DW2000 1515 6060 900900 60.000.00060,000,000 3.600.000.0003,600,000,000
DW3000DW3000 22,522.5 6060 1.3501,350 90.000.00090,000,000 5.400.000.0005,400,000,000
DW6000DW6000 4545 6060 2.7002,700 180.000.000180,000,000 10.800.000.00010,800,000,000

O limite de tamanho de transação é aplicado por transação ou operação.The transaction size limit is applied per transaction or operation. Ele não é aplicado em todas as transações simultâneas.It is not applied across all concurrent transactions. Portanto, cada transação tem permissão para gravar essa quantidade de dados no log.Therefore each transaction is permitted to write this amount of data to the log.

Para otimizar e minimizar a quantidade de dados gravados no log, consulte o artigo Transactions best practices(Melhores práticas de transações).To optimize and minimize the amount of data written to the log, please refer to the Transactions best practices article.

Aviso

O tamanho máximo de transações só pode ser obtido para tabelas distribuídas HASH ou ROUND_ROBIN nas quais o espalhamento de dados é uniforme.The maximum transaction size can only be achieved for HASH or ROUND_ROBIN distributed tables where the spread of the data is even. Se a transação estiver gravando dados de maneira distorcida nas distribuições, provavelmente, o limite será alcançado antes do tamanho máximo de transações.If the transaction is writing data in a skewed fashion to the distributions then the limit is likely to be reached prior to the maximum transaction size.

Estado da transaçãoTransaction state

O SQL Data Warehouse usa a função XACT_STATE() para relatar uma transação com falha usando o valor -2.SQL Data Warehouse uses the XACT_STATE() function to report a failed transaction using the value -2. Esse valor significa que a transação falhou e está marcada para reversão somente.This value means the transaction has failed and is marked for rollback only.

Observação

O uso de -2 pela função XACT_STATE para denotar uma transação com falha representa um comportamento diferente para o SQL Server.The use of -2 by the XACT_STATE function to denote a failed transaction represents different behavior to SQL Server. O SQL Server usa o valor -1 para representar uma transação não confirmável.SQL Server uses the value -1 to represent an uncommittable transaction. O SQL Server consegue tolerar alguns erros dentro de uma transação sem precisar ser marcado como não confirmável.SQL Server can tolerate some errors inside a transaction without it having to be marked as uncommittable. Por exemplo, SELECT 1/0 causaria um erro, mas não forçaria uma transação em um estado não confirmável.For example SELECT 1/0 would cause an error but not force a transaction into an uncommittable state. O SQL Server também permite leituras na transação não confirmável.SQL Server also permits reads in the uncommittable transaction. No entanto, o SQL Data Warehouse não permite que você faça isso.However, SQL Data Warehouse does not let you do this. Se um erro ocorrer dentro de uma transação do SQL Data Warehouse, ele entrará automaticamente no estado -2 e você não poderá mais dar instruções do tipo select até que a instrução seja revertida.If an error occurs inside a SQL Data Warehouse transaction it will automatically enter the -2 state and you will not be able to make any further select statements until the statement has been rolled back. Portanto, é importante verificar o código do aplicativo para ver se ele usa XACT_STATE(), pois você poderá precisar modificar o código.It is therefore important to check that your application code to see if it uses XACT_STATE() as you may need to make code modifications.

Por exemplo, no SQL Server, você verá uma transação com esta aparência:For example, in SQL Server you might see a transaction that looks like the following:

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:The preceding code gives the following error message:

Msg 111233, Nível 16, Estado 1, Linha 1 111233; a transação atual foi anulada, e as alterações pendentes foram revertidas.Msg 111233, Level 16, State 1, Line 1 111233; The current transaction has aborted, and any pending changes have been rolled back. Causa: uma transação em um estado somente de reversão não foi revertida explicitamente antes de uma instrução DDL, DML ou SELECT.Cause: A transaction in a rollback-only state was not explicitly rolled back before a DDL, DML, or SELECT statement.

Você não receberá a saída das funções ERROR_*.You won't get the output of the ERROR_* functions.

No SQL Data Warehouse, o código precisa ser ligeiramente alterado:In SQL Data Warehouse the code needs to be slightly altered:

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.The expected behavior is now observed. O erro na transação é gerenciado e as funções ERROR_* fornecem valores conforme o esperado.The error in the transaction is managed and the ERROR_* functions provide values as expected.

Tudo o que mudou é que o ROLLBACK da transação deve ocorrer antes da leitura das informações de erro no bloco CATCH.All that has changed is that the ROLLBACK of the transaction had to happen before the read of the error information in the CATCH block.

Função Error_line()Error_Line() function

Também vale a pena observar que o SQL Data Warehouse não implementa ou aceita a função ERROR_LINE().It is also worth noting that SQL Data Warehouse does not implement or support the ERROR_LINE() function. Se você tiver isso em seu código, será necessário removê-lo para que ele esteja em conformidade com o SQL Data Warehouse.If you have this in your code, you need to remove it to be compliant with SQL Data Warehouse. Em vez disso, use rótulos de consulta em seu código para implementar a funcionalidade equivalente.Use query labels in your code instead to implement equivalent functionality. Para obter mais detalhes, consulte o artigo LABEL.For more details, see the LABEL article.

Uso de THROW e RAISERRORUsing THROW and RAISERROR

THROW é a implementação mais moderna para lançar exceções no SQL Data Warehouse, mas também há suporte para RAISERROR.THROW is the more modern implementation for raising exceptions in SQL Data Warehouse but RAISERROR is also supported. No entanto, existem algumas diferenças que valem a pena prestar atenção.There are a few differences that are worth paying attention to however.

  • Os números das mensagens de erro definidas pelo usuário não podem estar no intervalo de 100.000 a 150.000 para THROWUser-defined error messages numbers cannot be in the 100,000 - 150,000 range for THROW
  • As mensagens de erro do RAISERROR são fixadas em 50.000RAISERROR error messages are fixed at 50,000
  • Não há suporte para o uso de sys.messagesUse of sys.messages is not supported

LimitaçõesLimitations

O SQL Data Warehouse tem algumas outras restrições relacionadas a transações.SQL Data Warehouse does have a few other restrictions that relate to transactions.

Elas são as seguintes:They are as follows:

  • Sem transações distribuídasNo distributed transactions
  • Não há transações aninhadas permitidasNo nested transactions permitted
  • Não são permitidos pontos de salvamentoNo save points allowed
  • Nenhuma transação nomeadaNo named transactions
  • Nenhuma transação marcadaNo marked transactions
  • Não há suporte para DDL, como CREATE TABLE em uma transação definida pelo usuárioNo support for DDL such as CREATE TABLE inside a user-defined transaction

Próximas etapasNext steps

Para saber mais sobre a otimização das transações, confira Práticas recomendadas das transações.To learn more about optimizing transactions, see Transactions best practices. Para saber mais sobre outras práticas recomendadas do SQL Data Warehouse, confira Práticas recomendadas do SQL Data Warehouse.To learn about other SQL Data Warehouse best practices, see SQL Data Warehouse best practices.