Utilizar transações num conjunto de SQL no Azure Synapse
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.