Controlar a durabilidade da transação

Artigo
03/16/2023

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

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

As confirmações de transações totalmente duráveis são síncronas e relatam uma confirmação como bem-sucedida, devolvendo o controle ao cliente somente após a gravação dos registros de log da transação 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 artigo detalha as transações duráveis atrasadas.

Durabilidade de transação completa versus Durabilidade de transação atrasada

A durabilidade de transações atrasadas e completas tem vantagens e desvantagens. Um aplicativo pode ter uma mistura de transações duráveis completas e atrasadas. Você deve considerar cuidadosamente as necessidades do seu negócio e como cada uma se adapta a essas necessidades.

Durabilidade de transação completa

As transações completamente duráveis gravam o log de transações no disco antes de devolver o controle para o cliente. Você deve usar transações completamente duráveis sempre que:

O sistema não puder tolerar perda de dados. Consulte a seção Onde posso perder os dados? para obter mais informações sobre quando você pode perder alguns dos seus dados.
O gargalo não seja devido à latência de gravação do log de transações.

A durabilidade da transação atrasada reduz a latência devido à E/S do log mantendo os registros do log de transações na memória e gravando no log de transações em lotes, exigindo assim menos operações de E/S. A durabilidade da transação atrasada potencialmente reduz a contenção de E/S de log, reduzindo a espera no sistema.

Garantias de durabilidade de transação completa

Assim que a confirmação de transação tiver êxito, as alterações feitas pela transação serão visíveis para as outras transações no sistema. Para obter mais informações sobre níveis de isolamento de transação, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL) ou Transações com tabelas com otimização de memória.
A durabilidade é garantida após a confirmação. Os registros de log correspondentes são persistidos no disco antes que a confirmação de transação tenha êxito e retornam o controle para o cliente.

Durabilidade de transação atrasada

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

O processamento da confirmação de transação não aguarda a E/S do log ser concluída e devolver o controle para o cliente.
As transações simultâneas são menos prováveis de conter para a E/S do log; em vez disso, o buffer de log pode ser liberado para disco em partes maiores, reduzindo a contenção e aumentando a taxa de transferência.

Observação

Você ainda poderá ter a contenção de E/S de log se houver um nível alto de simultaneidade, especialmente se você preencher o buffer do log mais rápido do que o liberar.

Quando usar a durabilidade de transação atrasada

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

Você pode tolerar alguma perda de dados.
Se você puder tolerar alguma perda de dados, por exemplo, nos casos em que os registros individuais não sejam críticos desde que você tenha a maioria dos dados, poderá ser válido considerar a durabilidade atrasada. Se você não puder tolerar perda de dados, não use a durabilidade de transação atrasada.

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

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

Garantias de durabilidade de transação atrasada

Assim que a confirmação de transação tiver êxito, as alterações feitas pela transação serão visíveis para as outras transações no sistema.
A durabilidade da transação é garantida apenas após uma liberação do log de transações de memória para o disco. O log de transações de memória é liberado para disco quando:
- Uma transação completamente durável no mesmo banco de dados faz uma alteração no banco de dados e é confirmada com sucesso.
- O usuário executa um procedimento armazenado do sistema sp_flush_log com sucesso.
  
  Se uma transação completamente durável ou um sp_flush_log for confirmado com êxito, todas as transações de durabilidade atrasadas confirmadas têm a garantia de se tornarem duráveis.
- O SQL Server tenta liberar o log em disco com base na geração de log e no tempo, mesmo se todas as transações forem duráveis atrasadas. Em geral, isso terá êxito se o dispositivo de E/S estiver acompanhando. Contudo, o SQL Server não fornece nenhuma garantia de durabilidade rígida além das 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 a durabilidade da transação atrasada em um banco de dados com a instrução a seguir. Você deve definir a configuração de durabilidade atrasada com ALTER DATABASE.

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

DESABILITADO
[padrão] Com essa configuração, todas as transações confirmadas no banco de dados são completamente duráveis, independentemente da configuração do nível de confirmação (DELAYED_DURABILITY=[ON | OFF]). Não há nenhuma necessidade de modificação e recompilação do procedimento armazenado. Isso permite garantir que a durabilidade atrasada nunca coloque os dados em risco.

PERMITIDO
Com essa configuração, a durabilidade de cada transação é determinada no nível da transação – DELAYED_DURABILITY = { OFF | ON }. Confira Controle no nível do bloco atômico – procedimentos armazenados e compilados nativamente e Nível de controle COMMIT para obter mais informações.

FORÇADO
Com essa configuração, cada transação que é confirmada no banco de dados é durável atrasada. Independentemente de a transação especificar completamente durável (DELAYED_DURABILITY = OFF) ou não fizer nenhuma especificação, a transação será durável atrasada. Essa configuração é útil quando a durabilidade da transação atrasada é útil para um banco de dados e você não quer alterar o código do aplicativo.

Controle no nível do bloco atômico – procedimentos armazenados e compilados nativamente

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

DELAYED_DURABILITY = { OFF | ON }

OFF
[padrão] A transação será completamente durável, a menos que a opção DELAYED_DURABLITY = FORCED esteja em vigor; nesse caso, a confirmação será assíncrona e, portanto, durável atrasada. Para obter mais informações, confira Controle de nível de banco de dados.

ON
A transação será durável atrasada, a menos que a opção DELAYED_DURABLITY = DISABLED esteja em vigor; nesse caso, a confirmação será síncrona e completamente durável. Para obter mais informações, confira 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

Opção da durabilidade do bloco atômico	Não há transação existente	Transação em processo (completamente durável ou atrasada)
DELAYED_DURABILITY = OFF	O bloco atômico inicia uma nova transação completamente durável.	O bloco atômico cria um ponto de salvamento na transação existente e, em seguida, inicia a nova transação.
DELAYED_DURABILITY = ON	O bloco atômico inicia uma nova transação durável atrasada.	O bloco atômico cria um ponto de salvamento na transação existente e, em seguida, inicia a nova transação.

Controle de nível COMMIT – Transact-SQL

A sintaxe de COMMIT é estendida para que você possa forçar a durabilidade da transação atrasada. Se DELAYED_DURABILITY for DISABLED ou FORCED no nível de banco de dados (veja acima) essa 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 será completamente durável, a menos que a opção de banco de dados DELAYED_DURABLITY = FORCED esteja em vigor; nesse caso, a COMMIT é assíncrona e, portanto, durável atrasada. Para obter mais informações, confira Controle de nível de banco de dados.

ON
A transação COMMIT será durável atrasada, a menos que a opção de banco de dados DELAYED_DURABLITY = DISABLED esteja em vigor; nesse caso, a COMMIT é síncrona e, portanto, completamente durável. Para obter mais informações, confira Controle de nível de banco de dados.

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

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

Configuração de COMMIT/Configuração de banco de dados	DELAYED_DURABILITY = DISABLED	DELAYED_DURABILITY = ALLOWED	DELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF Transações de nível de banco de dados.	A transação é completamente durável.	A transação é completamente durável.	A transação é durável atrasada.
DELAYED_DURABILITY = ON Transações de nível de banco de dados.	A transação é completamente durável.	A transação é durável atrasada.	A transação é durável atrasada.
DELAYED_DURABILITY = OFF Transações entre bancos de dados ou distribuídos.	A transação é completamente durável.	A transação é completamente durável.	A transação é completamente durável.
DELAYED_DURABILITY = ON Transações entre bancos de dados ou distribuídos.	A transação é completamente durável.	A transação é completamente durável.	A transação é completamente durável.

Como forçar uma liberação de log de transações

Há dois meios de forçar a liberação do log de transações para o disco.

Executar qualquer transação completamente durável que altera o mesmo banco de dados. Isso força uma liberação para o disco dos registros de log de todas as transações de durabilidade confirmadas anteriormente.
Executar o procedimento armazenado do sistema sp_flush_log. Esse procedimento força uma liberação para o disco dos registros de log de todas as transações duráveis confirmadas anteriormente. Para obter mais informações, confira 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ção

Em bancos de dados habilitados para replicação transacional ou CDC (captura de dados de alterações), não há suporte para o uso de durabilidade atrasada.
Há suporte para o controle de alterações com durabilidade atrasada. Todas as transações com controle de alterações são completamente duráveis. Uma transação terá a propriedade de controle de alterações se fizer alguma operação de gravação nas tabelas que habilitaram o controle de alterações.

A partir do SQL Server 2022 CU 2 e do SQL Server 2019 CU 20, você poderá ver:

Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set se você tentar habilitar a Replicação Transacional ou a Captura de Dados de Alterações em um banco de dados que habilitou a durabilidade atrasada.
Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled se você tentar habilitar a durabilidade atrasada em um banco de dados configurado com Replicação Transacional ou Captura de Dados de Alterações.

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

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

Grupos de Disponibilidade AlwaysOn e espelhamento
As transações duráveis atrasadas não garantem nenhuma durabilidade no primário nem em nenhum dos secundários. Além disso, elas não garantem conhecimento sobre a transação no secundário. Após a confirmação, o controle é retornado para o cliente antes de qualquer reconhecimento ser recebido de algum secundário síncrono. Replicação para réplicas secundárias continua a ocorrer conforme acontece a liberação em disco no primário.

Clustering de failover
Algumas gravações de transações duráveis atrasadas podem ser perdidas.

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

Envio de logs
Somente as transações que se tornaram duráveis são incluídas no log enviado.

Backup de log de transações
Somente as transações que se tornaram duráveis são incluídas no backup.

Quando posso perder os dados?

Se você implementar durabilidade atrasada em qualquer de suas tabelas, você deve compreender que certas circunstâncias podem levar à perda de dados. Se você não puder tolerar perda de dados, não use a durabilidade atrasada em suas tabelas.

Eventos 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 em disco. As transações duráveis atrasadas são salvas no disco sempre que uma transação totalmente durável é executada em qualquer tabela (duráveis com otimização de memória ou baseada em disco) no banco de dados ou quando o sp_flush_log é chamado. Se estiver usando transações duráveis atrasadas, você pode querer criar uma pequena tabela no banco de dados que você pode atualizar periodicamente ou chamar periodicamente o sp_flush_log para salvar todas as transações confirmadas pendentes. O log de transações também é liberado sempre que fica cheio, mas é difícil de prever e impossível de controlar.

Desligamento e reinicialização do SQL Server

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

Próximas etapas

Transações com tabelas com otimização de memória

Compartilhar via