Controle da Durabilidade da Transação

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

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 ao cliente somente depois que os registros de log da transação são gravados em 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. É necessário gravar as entradas de log das transações em disco para que uma transação seja durável. As transações duráveis atrasadas tornam-se duráveis quando as entradas de log de transações são liberadas para o disco.

Este tópico detalha transações duráveis atrasadas.

Durabilidade da transação completa versus postergada

A durabilidade da transação, tanto total quanto atrasada, tem suas vantagens e desvantagens. Um aplicativo pode ter uma combinação de transações totalmente duráveis e transações duráveis 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. Você deve usar transações totalmente duráveis sempre que:

• Seu sistema não pode tolerar nenhuma perda de dados.
  Veja 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.

A durabilidade de transação atrasada reduz a latência causada pelas operações de E/S de log mantendo os registros de log de transações na memória. Ao escrevê-los no log de transações em lotes, são necessárias menos operações de E/S. A durabilidade atrasada da transação potencialmente reduz a contenção de E/S do log, o que, por sua vez, reduz as esperas no sistema.

Garantias completas de durabilidade da transação

• Depois que a confirmação de transação for bem-sucedida, as alterações feitas pela transação ficarão visíveis para as outras transações no sistema. Consulte o tópico Níveis de Isolamento de Transação para obter mais informações.

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

Durabilidade de transações atrasadas

A durabilidade de transações atrasadas é alcançada usando escritas assíncronas em log para o disco. Os registros de log de transações são mantidos em um buffer e gravados em disco quando o buffer é preenchido ou ocorre um evento de liberação de buffer. A durabilidade de transação atrasada reduz tanto a latência quanto a contenção dentro do sistema porque:

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

• Transações simultâneas são menos propensas a 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 o desempenho.

  Observação

  Talvez você ainda tenha contenção de E/S de log se houver um alto grau de simultaneidade, especialmente se você preencher o buffer de log mais rápido do que liberá-lo.

Quando usar a durabilidade de transação retardada

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

Você pode tolerar alguma perda de dados.
Se você puder tolerar alguma perda de dados, por exemplo, em que os registros individuais não são críticos, contanto que você possua a maior parte dos dados, a durabilidade atrasada pode valer a pena ser considerada. Se você não puder tolerar nenhuma perda de dados, não use durabilidade de transação atrasada.

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

Suas cargas de trabalho têm uma alta taxa de contenção.
Se o sistema tiver cargas de trabalho com um alto nível de contenção, muito tempo será perdido aguardando a liberação dos bloqueios. A durabilidade atrasada da transação reduz o tempo de confirmação e, assim, libera os bloqueios mais rapidamente, o que resulta em um aumento da taxa de transferência.

Garantias de durabilidade de transação atrasadas

• Depois que a confirmação de transação for bem-sucedida, as alterações feitas pela transação ficarão visíveis para as outras transações no sistema.

• A durabilidade da transação só é garantida 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 para o disco quando:

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

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

  • O buffer de log de transações na memória é preenchido e liberado automaticamente para o disco.

  Se uma transação totalmente durável ou sp_flush_log se confirmar com sucesso, as transações previamente confirmadas de durabilidade atrasada foram tornadas duráveis.

O log pode ser gravado no disco periodicamente. No entanto, o SQL Server não fornece garantias de durabilidade além de transações duráveis e sp_flush_log.

Como controlar a durabilidade da transação

Controle de nível de banco de dados

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

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

DISABLED
[padrão] Com essa configuraçã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 de procedimento armazenado. Isso permite que você garanta que nenhum dado seja colocado em risco pela durabilidade atrasada.

ALLOWED
Com essa configuração, a durabilidade de cada transação é determinada no nível da transação - DELAYED_DURABILITY = { OFF | ON }. Consulte o controle de nível de bloco atômico – Procedimentos armazenados compilados nativamente e controle de nível COMMIT – Transact-SQL para obter mais informações.

FORCED
Com essa configuração, toda transação que é confirmada no banco de dados tem sua durabilidade atrasada. Se a transação especifica durabilidade total (DELAYED_DURABILITY = OFF) ou não faz nenhuma especificação, a transação possui 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.

Controle de nível de bloco atômico – Procedimentos armazenados compilados nativamente

O código a seguir entra no bloco atômico.

DELAYED_DURABILITY = { OFF | ON }  

OFF
[padrão] A transação é totalmente durável, a menos que a opção de banco de dados DELAYED_DURABLITY = FORCED esteja em vigor, nesse caso, a confirmação é assíncrona e, portanto, durável atrasada. Consulte o controle de nível de banco de dados para obter mais informações.

ON
A transação é atrasada durável, a menos que a opção de banco de dados DELAYED_DURABILITY = DISABLED esteja ativada, caso em que a confirmação é síncrona e totalmente durável. Consulte o controle de nível de banco de dados para obter mais informações.

Código de exemplo:

CREATE PROCEDURE <procedureName> ...  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  
    DELAYED_DURABILITY = ON,  
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
    LANGUAGE = N'English'  
    ...  
)  
END  

Tabela 1: Durabilidade em blocos atômicos

Controle de nível COMMIT – (T-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), a opção COMMIT será ignorada.

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

OFF
[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, nesse caso o COMMIT é assíncrono e, portanto, com durabilidade atrasada. Consulte o controle de nível de banco de dados para obter mais informações.

ON
A transação COMMIT é feita com durabilidade atrasada, a menos que a opção de banco de dados DELAYED_DURABILITY = DISABLED esteja em vigor, caso em que o COMMIT é síncrono e, portanto, totalmente durável. Consulte o controle de nível de banco de dados para obter mais informações.

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

Esta tabela resume as interações entre as configurações de durabilidade atrasada no nível de banco de dados e as configurações no 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 liberação de log de transações

Existem dois meios de forçar a gravação do log de transações no disco.

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

• Execute o procedimento sp_flush_logarmazenado do sistema. Este procedimento força a escrita dos registros de log no disco de todas as transações duráveis atrasadas que foram previamente confirmadas. Para obter mais informações , consulte sys.sp_flush_log (Transact-SQL).

Durabilidade atrasada e outros recursos do SQL Server

Controle de alterações e captura de dados de alteração
Todas as transações com controle de alterações são totalmente duráveis. Uma transação terá a propriedade de controle de alterações se fizer operações de gravação em tabelas habilitadas para controle de alterações. Não há suporte para o uso de durabilidade atrasada em bancos de dados que utilizam captura de dados alterados (CDC).

Recuperação de falha
A consistência é garantida, mas algumas alterações de transações duráveis que sofreram atrasos e foram confirmadas podem ser perdidas.

Banco de dados cruzado 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 transação ou banco de dados.

Grupos de Disponibilidade AlwaysOn e Espelhamento
Transações duráveis atrasadas não garantem qualquer durabilidade no primário ou em qualquer um dos secundários. Além disso, eles não garantem nenhum conhecimento sobre a transação no mercado secundário. Após a confirmação da transação, o controle é devolvido ao cliente antes que qualquer confirmação seja recebida de qualquer secundário síncrono.

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

Replicação de Transações
Não há suporte para transações duráveis atrasadas com Replicação Transacional.

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

Log Backup
Somente as transações cuja durabilidade foi assegurada são incluídas no backup.

Quando posso perder dados?

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

Eventos catastróficos

No caso de um evento catastrófico, como uma falha de 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 (otimizada para a memória durável ou baseada em disco) no banco de dados, ou quando sp_flush_log é chamada. Se você estiver usando transações duráveis atrasadas, talvez queira criar uma tabela pequena no banco de dados que possa atualizar periodicamente ou chamar sp_flush_log periodicamente para salvar todas as transações confirmadas pendentes. O log de transações também é esvaziado quando fica cheio, mas isso é difícil de prever e impossível de controlar.

Desligamento e reinicialização do SQL Server

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

Consulte Também

Níveis de isolamento de transação
Diretrizes para níveis de isolamento de transação com tabelas de Memory-Optimized