Controle a durabilidade da transação

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

As confirmações de transações do SQL Server podem ser totalmente duráveis, padrão do SQL Server, ou duráveis atrasadas (também conhecidas como confirmação preguiçosa).

As confirmações de transação totalmente duráveis são síncronas e relatam uma confirmação como bem-sucedida e retornam o controle para o cliente somente depois que os registros de log da transação são gravados no disco. As confirmações de transações duráveis atrasadas são assíncronas e relatam uma confirmação como bem-sucedida antes que os registros de log da transação sejam gravados no disco. Gravar as entradas do log de transações no disco é necessário para que uma transação seja durável. Transações duráveis atrasadas tornam-se duráveis quando as entradas do log de transações são gravadas no disco.

Este artigo detalha transações duráveis atrasadas.

Durabilidade completa vs. durabilidade da transação adiada

Tanto a durabilidade total como a durabilidade diferida das transações têm vantagens e desvantagens. Uma aplicação pode ter uma combinação de transações totalmente duráveis e atrasadas. Você deve considerar cuidadosamente suas necessidades de negócios e como cada uma se encaixa nessas necessidades.

Durabilidade total da transação

Transações totalmente duráveis gravam o log de transações no disco antes de retornar o controle ao cliente. Deve utilizar transações totalmente duradouras sempre que:

• O seu sistema não pode tolerar qualquer perda de dados. Consulte a seção Quando posso perder dados para obter informações sobre quando você pode perder alguns de seus dados.

• O gargalo não se deve à latência de gravação do log de transações.

O atraso na durabilidade das transações reduz a latência devido à E/S de log ao manter os registos do log de transações na memória e gravando-os em lotes no log de transações, exigindo assim menos operações de E/S. A durabilidade atrasada da transação reduz potencialmente a contenção de E/S de log, reduzindo assim as esperas no sistema.

Garantias de Durabilidade Total da Transação

• Quando a confirmação da transação for bem-sucedida, as alterações feitas pela transação serão visíveis para as outras transações no sistema. Para obter mais informações sobre os níveis de isolamento de transações, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL) ou Transactions with Memory-Optimized Tables.

• A durabilidade é garantida no compromisso. Os registros de log correspondentes são mantidos no disco antes que a confirmação da transação seja bem-sucedida e retorne o controle ao cliente.

Atraso na durabilidade da transação

A durabilidade de transações com atraso é alcançada através de gravações assíncronas de logs no disco. Os registros de log de transações são mantidos em um buffer e gravados no disco quando o buffer é preenchido ou ocorre um evento de liberação de buffer. A durabilidade da transação atrasada reduz a latência e a contenção dentro do sistema porque:

• O processamento de confirmação de transação não aguarda a conclusão da E/S de log e retorna o controle ao cliente.

• As transações simultâneas têm menos probabilidade de disputar a E/S de log; em vez disso, o buffer de log pode ser liberado para o disco em partes maiores, reduzindo a contenção e aumentando a taxa de transferência.

  Observação

  Você ainda pode ter contenção de entrada/saída de log se houver um alto grau de simultaneidade, especialmente se encheres o buffer de log mais rápido do que esvaziá-lo.

Quando usar a durabilidade da transação atrasada

Alguns dos casos em que você poderia se beneficiar do uso da durabilidade da transação atrasada são:

Você pode tolerar alguma perda de dados.
Se você pode tolerar alguma perda de dados, por exemplo, onde os registros individuais não são críticos, desde que você tenha a maioria dos dados, então a durabilidade atrasada pode valer a pena considerar. Se não consegue tolerar qualquer perda de dados, não utilize a durabilidade atrasada das transações.

Você está enfrentando um gargalo nas gravações do log de transações.
Se os seus problemas de desempenho forem devido à latência nas gravações do log de transações, a sua aplicação provavelmente se beneficiará do uso da durabilidade de transação adiada.

Suas cargas de trabalho têm uma alta taxa de contenção.
Se o seu sistema tem cargas de trabalho com um alto nível de contenção, muito tempo é perdido esperando que os bloqueios sejam liberados. A durabilidade de transações atrasadas reduz o tempo de confirmação e, portanto, libera bloqueios mais rapidamente, o que resulta em maior desempenho.

Garantias de durabilidade para transações temporariamente adiadas

• Quando a confirmação da transação for bem-sucedida, as alterações feitas pela transação serão visíveis para as outras transações no sistema.

• A durabilidade da transação é garantida somente após uma liberação do log de transações na memória para o disco. O log de transações na memória é liberado no disco quando:

  • Uma transação totalmente durável no mesmo banco de dados faz uma alteração no banco de dados e é confirmada com êxito.

  • O usuário executa o procedimento armazenado do sistema sp_flush_log com êxito.

    Se uma transação totalmente durável ou sp_flush_log for confirmada com sucesso, é garantido que todas as transações de durabilidade atrasada anteriormente confirmadas se tornaram duráveis.

  • O SQL Server tenta liberar o log para o disco com base na geração de log e no tempo, mesmo que todas as transações sejam atrasadas durável. Isso geralmente é bem-sucedido se o dispositivo de E/S estiver a funcionar adequadamente. No entanto, o SQL Server não oferece nenhuma garantia de durabilidade além das transações duráveis e do sp_flush_log.

Como controlar a durabilidade das transações

Controle no nível do banco de dados

Você, o DBA, pode controlar se os usuários podem usar a durabilidade da transação adiada em um banco de dados com a instrução seguinte. Você deve definir a configuração de durabilidade atrasada com ALTER DATABASE.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

DESATIVADO
[padrão] Com esta definição, todas as transações confirmadas no banco de dados são totalmente duráveis, independentemente da configuração de nível de confirmação (DELAYED_DURABILITY=[ON | OFF]). Não há necessidade de alteração e recompilação do procedimento armazenado. Isso permite garantir que nenhum dado seja colocado em risco por atraso na durabilidade.

PERMITIDO
Com esta configuração, a durabilidade de cada transação é determinada ao nível da transação - DELAYED_DURABILITY = { OFF | ON }. Consulte controle a nível de bloco atómico - Procedimentos Armazenados Nativamente Compilados e controle a nível de COMMIT para obter mais informações.

FORÇADA
Com esta configuração, todas as transações confirmadas no banco de dados são retardadas de forma durável. Se a transação especificar totalmente durável (DELAYED_DURABILITY = OFF) ou não fizer nenhuma especificação, a transação terá durabilidade atrasada. Essa configuração é útil quando a durabilidade da transação atrasada é útil para um banco de dados e você não deseja alterar nenhum código do aplicativo.

Controlo ao nível do bloco atómico - Procedimentos armazenados, compilados de forma nativa

O código a seguir vai dentro do bloco atômico.

DELAYED_DURABILITY = { OFF | ON }

DESLIGADO
[padrão] A transação é totalmente durável, a menos que a opção de banco de dados DELAYED_DURABILITY = FORCED esteja em vigor, caso em que a confirmação é assíncrona e, portanto, retardada durável. Para obter mais informações, consulte Controle de nível de banco de dados.

SOBRE
A transação é atrasada durável, a menos que a opção de banco de dados DELAYED_DURABILITY = DISABLED esteja em vigor, caso em que a confirmação é síncrona e, portanto, totalmente durável. Para obter mais informações, consulte Controle de nível de banco de dados.

Código de exemplo:

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

Tabela 1: Durabilidade em blocos atômicos

Controle de nível COMMIT -Transact-SQL

A sintaxe COMMIT é estendida para que você possa forçar a durabilidade da transação atrasada. Se DELAYED_DURABILITY estiver DESABILITADO ou FORÇADO no nível do banco de dados (veja acima), essa opção COMMIT será ignorada.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

DESLIGADO
[padrão] A transação COMMIT é totalmente durável, a menos que a opção de banco de dados DELAYED_DURABILITY = FORCED esteja em vigor, caso em que o COMMIT é assíncrono e, portanto, durável retardado. Para obter mais informações, consulte Controle de nível de banco de dados.

SOBRE
A transação COMMIT é atrasada mas ainda durável, a menos que a opção de banco de dados DELAYED_DURABILITY = DISABLED esteja ativa, caso em que o COMMIT é síncrono e, portanto, totalmente durável. Para obter mais informações, consulte Controle de nível de banco de dados.

Resumo das opções e suas interações

Esta tabela resume as interações entre as configurações de durabilidade atrasada ao nível do banco de dados e as configurações ao nível de confirmação. As configurações de nível de banco de dados sempre têm precedência sobre as configurações de nível de confirmação.

Como forçar uma descarga do log de transações

Há dois meios de forçar o esvaziamento do log de transações em disco.

• Execute qualquer transação totalmente durável que altere o mesmo banco de dados. Isso força uma liberação dos registros de log de todas as transações de durabilidade atrasada confirmadas anteriores para o disco.

• Execute o procedimento armazenado do sistema sp_flush_log. Este procedimento força uma descarga dos registos de log de todas as transações duráveis atrasadas e confirmadas anteriores para o disco. Para obter mais informações, consulte sys.sp_flush_log (Transact-SQL).

Durabilidade atrasada e outros recursos do SQL Server

replicação transacional, controle de alterações e captura de dados de alterações

• Para bancos de dados habilitados para Replicação Transacional ou Captura de Dados de Alteração (CDC), não há suporte para o uso de durabilidade atrasada.

• O Rastreamento de Alterações com Durabilidade Atrasada é suportado. Todas as transações com o Change Tracking são totalmente duráveis. Uma transação tem a propriedade de controle de alterações se fizer operações de gravação em tabelas que habilitaram o controle de alterações.

A partir do SQL Server 2022 2 e do SQL Server 2019 20, você pode ver:

• Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set se tentar ativar a Replicação Transacional ou a Captura de Dados de Alteração num banco de dados que tenha ativado a durabilidade adiada.

• Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled se tentares habilitar a durabilidade atrasada num banco de dados configurado com replicação de transações ou captura de alterações de dados.

Recuperação de falhas
A coerência é garantida, mas algumas alterações em transações duradouras que foram confirmadas, mas estavam atrasadas, podem ser perdidas.

Integração entre bases de dados e DTC
Se uma transação for entre bancos de dados ou distribuída, ela será totalmente durável, independentemente de qualquer configuração de confirmação de banco de dados ou transação.

Grupos de disponibilidade Always On e espelhamento
Transações duráveis adiadas não garantem qualquer durabilidade nem no primário nem nos secundários. Além disso, não garantem qualquer conhecimento sobre a transação no mercado secundário. Após a confirmação, o controle é retornado ao cliente antes que qualquer confirmação seja recebida de qualquer secundário síncrono. A replicação para réplicas secundárias continua a acontecer à medida que ocorre a liberação em disco no primário.

Agrupamento de alta disponibilidade
Algumas gravações de transações duráveis atrasadas podem ser perdidas.

Azure Synapse Link para SQL
Não há suporte para transações duráveis atrasadas com o Azure Synapse Link for SQL.

de envio de logs
Somente as transações que foram tornadas permanentes são incluídas no log que é enviado.

Cópia de segurança do registo de transações
Apenas as transações que foram feitas duráveis são incluídas no backup.

Quando posso perder dados

Se você implementar durabilidade atrasada em qualquer uma de suas tabelas, você deve entender que certas circunstâncias podem levar à perda de dados. Se você não pode tolerar qualquer perda de dados, você não deve usar durabilidade atrasada em suas tabelas.

Acontecimentos catastróficos

No caso de um evento catastrófico, como uma falha do servidor, você perderá os dados de todas as transações confirmadas que não foram salvas no disco. Transações duráveis atrasadas são salvas em disco sempre que uma transação totalmente durável é executada em qualquer tabela (com otimização de memória durável ou baseada em disco) no banco de dados ou sp_flush_log é invocada. Se você estiver usando transações duráveis atrasadas, convém criar uma pequena tabela no banco de dados que você pode atualizar periodicamente ou chamar periodicamente sp_flush_log para salvar todas as transações confirmadas pendentes. O log de transações também se esvazia sempre que fica cheio, mas isso é difícil de prever e impossível de controlar.

Desligamento e reinicialização do SQL Server

No caso de durabilidade atrasada, não há diferença entre desligamento inesperado e o desligamento/o reinicialização esperada do SQL Server. Como eventos catastróficos, você deve planejar a perda de dados. Em um desligamento/reinício planejado, algumas transações que não foram gravadas em disco podem ser salvas no disco antes do desligamento, mas você não deve planejar isso. Planeje como se um desligamento/reinício, planejado ou não, perdesse os dados da mesma forma que um evento catastrófico.

Próximos passos

• Transações com Memory-Optimized Tabelas