Sobre a transação

Há muitas situações em que os usuários exigem alterações nos dados em muitas tabelas do banco de dados. Em muitas situações, os dados perderão consistência quando executados separadamente.

Suponha que a primeira instrução seja executada corretamente, mas a próxima instrução falhe porque os dados estão incorretos.

Por exemplo, uma situação particular são as transferências de dinheiro no sistema bancário. A transferência exigirá uma instrução INSERT e duas instruções UPDATE:

• O usuário precisa reduzir o saldo na conta de origem.
• Em seguida, é necessário aumentar o saldo da conta no sistema bancário no registro da conta de destino.

O usuário precisará verificar se essa transação foi confirmada e se as mesmas alterações foram feitas nas contas de origem e destino.

Definição de transação

Uma unidade de trabalho razoável deve exibir quatro propriedades, conhecidas como propriedades de Atomicidade, Consistência, Isolamento e Persistência (ACID), para se qualificar como uma transação:

Atomicidade : Se a transação tiver muitas operações, todas devem ser confirmadas. Se alguma operação no grupo falhar, ela será revertida.

Consistência: A sequência de operações deve ser apropriada

Isolamento: as operações executadas devem ser permanentemente isoladas de outras operações no mesmo banco de dados do servidor

Durabilidade: As operações realizadas no banco de dados devem ser salvas e armazenadas permanentemente no banco de dados.

Implementação de transação

O SQL Server oferece suporte a transações com vários modos da seguinte forma:

• Transações de Autocommit: (auto-commit) Cada linha de comando é automaticamente confirmada quando é bem-sucedida. Nesse modo, não há necessidade de escrever nenhuma instrução específica para iniciar e encerrar a transação. Este é o modo padrão do SQL Server
• Transações explícitas: (explícitas) Cada transação explícita começa com uma instrução BEGIN TRANSACTION e termina com uma transação ROLLBACK ou COMMIT.
• Transações implícitas: (implicitamente) uma transação é capturada automaticamente quando uma transação anterior é concluída e cada transação é concluída usando a sintaxe ROLLBACK ou COMMIT.
• Transações com escopo em lote: (com escopo em lote) essas transações estão relacionadas ao conceito de conjunto de resultados ativos múltiplos (MARS). E cada transação implícita ou explícita que começa com um pellet MARS é chamada de transação com escopo de lote.
• Transações distribuídas: (transações distribuídas) estão espalhadas por 2 ou mais servidores conhecidos como gerenciadores de recursos. O gerenciamento de transações deve estar localizado entre o gerenciador de recursos por um componente de servidor chamado gerenciador de transações. Cada instância no SQL Server pode atuar como um gerenciador de recursos em transações distribuídas, localizadas por um gerenciador de transações, como o Microsoft Distributed Transaction Coordinator (MS DTC).

Lotes de extensão de transação

As instruções de transação determinam o sucesso ou a falha do bloco e fornecem um banco de dados que pode reverter as operações.

Erros detectados durante a execução de um lote simples provavelmente serão parcialmente bem-sucedidos, o que não é o resultado desejado ao usar transações.

Esse problema levará a conflitos lógicos entre as tabelas no banco de dados.

Os usuários podem adicionar código de controle de erro para reverter a transação para o estado antigo em caso de erro.

O código de tratamento de erros desfaz todas as alterações antes que o erro seja encontrado.

Controle de transações

As transações podem ser controladas por meio do aplicativo, definindo o início e o fim de uma transação.

As transações são gerenciadas por camadas de conexão por padrão.

Quando uma transação inicia uma conexão, todas as instruções T-SQL são executadas na mesma conexão e fazem parte da conexão até que a transação termine.

INICIAR TRANSAÇÃO

A instrução BEGIN TRANSACTION marca o início de uma transação explícita.

Por exemplo:

USE AdventureWorks2019; GO DECLARE @TranName VARCHAR(30); SELECT @TranName = 'FirstTransaction'; BEGIN TRANSACTION @TranName; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13;

COMEÇAR TRANSAÇÃO

A instrução COMMIT TRANSACTION marca o endpoint, que é um commit que sinaliza o fim de uma transação implícita ou explícita.

COMMIT [TRAN | TRANSACTION] [transaction_name | @tran_name_variable]] [;]

Por exemplo:

BEGIN TRANSACTION; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION; GO

COMPROMETER TRABALHO

A instrução COMMIT WORK marca o terminal da transação.

Sintaxe:

COMMIT [WORK] [;]

COMMIT TRANSACTION e COMMIT WORK são idênticos, exceto que COMMIT TRANSACTION aceita um nome de transação definido pelo usuário.

Criar transação com commit:

BEGIN TRANSACTION DeleteCandidate WITH MARK N'Deleting a Job Candidate'; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION DeleteCandidate;

TRANSAÇÃO DE RECUPERAÇÃO

As transações podem ser abortadas e revertidas para o ponto original ou ponto de salvamento na transação.

Ele é usado para excluir todos os dados modificados gerados desde o início da transação ou até o ponto de salvamento. Ele também libera os recursos mantidos pela transação.

SALVAR TRANSAÇÃO

A instrução SAVE TRANSACTION definirá o ponto de salvamento dentro da transação.

Sintaxe:

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable} [;]

Por exemplo:

CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION ProcedureSave; ELSE BEGIN TRANSACTION; DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; IF @TranCounter = 0 COMMIT TRANSACTION; IF @tranCounter = 1 ROLLBACK TRANSACTION ProcedureSave; GO

No código acima, a transação do ponto de salvamento é criada dentro do procedimento. Ele será usado para reverter apenas se as alterações de dados forem geradas pelo procedimento armazenado se uma transação válida for iniciada antes da execução do procedimento.

@@TRANCOUNT na transação

@@TRANCOUNT é uma função do sistema que retorna o valor numérico da instrução de transação, ocorrendo na conexão atual.

Por exemplo:

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT

Resultado:

Exemplo usando @@TRANCOUNT com ROLLBACK

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT ROLLBACK PRINT @@TRANCOUNT

Resultado:

Marcar transação

A marcação de transação é útil apenas quando o usuário está disposto a perder transações recentemente confirmadas ou está verificando o banco de dados relacionado.

Marcar transações em um agendamento em cada banco de dados relacionado cria uma cadeia comum de pontos de restauração no banco de dados.

Preocupações ao usar a transação marcada:

Uma marca de transação consumirá espaço físico, use-a apenas para transações importantes na estratégia de recuperação do banco de dados.

Quando a transação marcada for confirmada, a linha será adicionada à tabela logmarkhistory na tabela msdb.

Se a transação marcada abrange vários bancos de dados em servidores diferentes ou no mesmo servidor, as marcas precisam ser registradas nos registros de todos os bancos de dados afetados.

Criar transação marcada

Para criar uma transação marcada, o usuário pode usar a sintaxe da instrução BEGIN TRANSACTION com a cláusula WITH MARK [DESCRIPTION].

A transação registra a descrição da marca, o nome, o usuário, o banco de dados, as informações de data e hora e o Log Sequence Number (LSN).

Etapas para criar uma transação marcada em um conjunto de bancos de dados:

• O nome da transação na instrução BEGIN TRAN e usando a cláusula WITH MARK.
• Execute uma atualização em todos os bancos de dados do conjunto.

Por exemplo:

USE AdventureWorks2019; GO BEGIN TRANSACTION ListPriceUpdate WITH MARK 'UPDATE Product List prices'; GO UPDATE Production.Product SET ListPrice = ListPrice * 1.20 WHERE ProductNumber LIKE 'BK-%'; GO COMMIT TRANSACTION ListPriceUpdate; GO

Diferença entre Transação Implícita e Explícita

Implícito	Explícito
A transação é mantida pelo SQL Server para cada instrução DDL e DML	Transação definida pelo compilador
Instruções DML e DDL são executadas sob transação implícita	Consiste em uma instrução DML e é executada como uma unidade de consulta.
O servidor SQL reverterá toda a instrução	Não inclua instruções SELECT porque elas não modificam os dados.

Nível de isolamento (nível de isolamento)

As transações definem uma camada de isolamento que define o grau em que uma transação deve ser isolada de dados ou modificações de recursos feitas por outras transações.

Os níveis de isolamento são definidos em condições que permitem efeitos simultâneos, como leituras sujas.

O nível de isolamento da transação controla o seguinte:

• Quando os dados são lidos, existem bloqueios e que tipo de bloqueios são necessários?
• Por quanto tempo os bloqueios de leitura são mantidos?
• Se uma operação de leitura que faz referência a uma linha for modificada por outra transação, ocorrerá uma das seguintes situações:
  • Bloqueie até que o mecanismo de travamento exclusivo da fileira seja desbloqueado.
  • Recupere a versão confirmada da linha que existia no início da transação ou instrução.
  • Ler dados não confirmados

As transações exigem uma chave exclusiva o tempo todo em cada dado que ela controla. Em seguida, ele mantém esse bloqueio até que a transação seja concluída, independentemente do nível de isolamento definido para essa transação.

Níveis de isolamento:

Nível de isolamento	Leitura suja	Leitura Não Repetível
Leitura comprometida	Não	Sim
Ler não confirmado	Sim	Não
Instantâneo	Não	Não
Leitura repetível	Não	Não
Serializável	Não	Não

Gama e tipos de fechaduras

Lista de tipos de bloqueio comuns no SQL Ser

Modo de bloqueio	Descrição
Atualizar	Use em recursos de preparação de atualização
Compartilhado	Use para ler a operação sem alterar dados como a instrução SELECT .
Pretende	Usado para estabelecer um bloqueio em cascata
Exclusivo	Usado para operações de manipulação de dados como INSERT, UPDATE, DELETE.
ATUALIZAÇÃO EM MASSA	Usado ao copiar grandes quantidades de dados em uma tabela.
Esquema	Usado quando a operação depende do esquema da tabela

Atualizar bloqueios

Esses bloqueios evitam situações de impasse. Ele serializa as transações, a transação lê os dados, adquire um bloqueio compartilhado na linha ou página e a modificação dos dados requer a conversão do bloqueio em um bloqueio exclusivo.

Bloqueios compartilhados

Esses bloqueios permitem que transações paralelas leiam um recurso sob controle de simultaneidade.

Os bloqueios compartilhados liberam recursos quando uma operação de leitura é concluída, exceto que a camada de isolamento é atribuída a uma ação de leitura repetida ou superior.

Fechaduras Exclusivas

Esses bloqueios impedem o acesso simultâneo a recursos dentro da transação.

Ao usar o bloqueio exclusivo, nenhuma transação pode alterar os dados e a operação de leitura será agendada apenas por meio da camada de isolamento não confirmada ou do modo NOLOCK.

Instruções DML como INSERT,UPDATE, DELETE são usadas para modificar dados.

Bloqueios de intenção

O papel do bloqueio de intenção:

• Impede que outras transações alterem dados em recursos de nível superior, de uma forma que invalidaria bloqueios de nível inferior.
• para melhorar a eficiência do mecanismo de banco de dados na identificação de conflitos de chave em uma granularidade mais alta.

Lista de descrições de bloqueio de intent:

Modo de bloqueio	Descrição
Intenção compartilhada (IS)	A proteção de bloqueio compartilhado é necessária em alguns recursos de camada inferior.
Intenção exclusiva (IX)	A proteção de bloqueio exclusiva é necessária em alguns recursos de camadas inferiores. IX é um superconjunto (um conjunto de conjuntos que consiste em outro conjunto) de IS, que protege os bloqueios compartilhados necessários na camada de recursos inferior.
Compartilhado com intenção exclusiva (SIX)	A proteção de bloqueios compartilhados é necessária em recursos inferiores na hierarquia e bloqueios exclusivos de intenção em alguns recursos de nível inferior. Bloqueios IS simultâneos (bloqueios IS simultâneos) são habilitados em recursos de nível superior.
Atualização de intenção (IU)	Para proteger os bloqueios necessários em todos os recursos da camada inferior. Os bloqueios de IU são usados apenas em recursos de página. Os bloqueios IU são convertidos em bloqueios IX se uma operação de atualização for executada.
Atualização de intenção compartilhada (SIU)	Fornece uma combinação de fechaduras S e IU, como resultado de obter fechaduras separadas e manter ambas as fechaduras simultaneamente.
Atualizar intenção exclusiva (UIX)	Fornece uma combinação de travas U e IX, como resultado de obter travas separadas e manter ambas as travas simultaneamente.

Bloqueios de atualização em massa

Os bloqueios de atualização em massa são usados quando uma grande quantidade de dados é copiada para a tabela. Esses bloqueios permitirão que vários threads sejam executados simultaneamente para carregar dados em massa sequencialmente em uma tabela.

Bloqueios de esquema

Os bloqueios de modificação de esquema são usados no Mecanismo de Banco de Dados durante a execução de operações DDL, como exclusão de tabelas ou colunas.

As chaves de estabilidade de esquema são usadas pelo mecanismo de banco de dados durante a compilação e execução de consultas.

Fechaduras de intervalo de chaves

Este tipo de bloqueios protegerá a lista de registros representados no RRset.

Os bloqueios de intervalo de chaves evitam leituras fantasmas. new corresponde à condição pesquisada para a transação A. Se A executar a mesma condição novamente, obterá um conjunto de dados que não é uniforme. )

Gerenciar transações

O SQL Server implementa transações com diferentes escopos que garantem as propriedades ACID dessas transações.

Na prática, isso significa usar bloqueios como base para transações para consultar recursos de banco de dados compartilhados e evitar interferências entre transações.

Log de transações

O log de transações é um componente importante do banco de dados, se o sistema travar, o log de transações garantirá a recuperação dos dados para o estado adequado.

O log de transações não deve ser excluído ou movido até que o usuário entenda suas consequências.

Operações suportadas pelo log de transações:

• Recuperação de transações individuais.
• Reversão de transação inacabada quando o SQL Server é iniciado.
• Suporte para replicação de transações
• Solução de recuperação de desastres, suportando sistemas com requisitos de alto desempenho.
• Restaure arquivos, bancos de dados, grupos de arquivos ou encaminhe páginas até o ponto de falha.

Truncar log de transações

O truncamento do log de resgate liberará a memória ocupada pelo arquivo de log para continuar o log. Os logs serão truncados automaticamente ao encontrar os seguintes eventos:

• Em um modelo de recuperação simples após (ponto de verificação) o ponto de verificação.
• Um modelo de recuperação em massa e recuperação completa, se for encontrado checkpoing desde o último backup.

Quando os logs estiverem ativos por muito tempo, o log de transações será atrasado e poderá encher a memória do sistema. Os truncamentos de log podem ser lentos por vários motivos, os usuários podem descobrir se algo está impedindo o truncamento do log de trnassaction consultando as colunas log_reuse_wait_desc e log_reuse_wait na exibição de catálogo sys.databases.

Descrição do valor de 2 colunas:

Log_reuse_wait	Log_reuse_wait_desc	Descrever
0	NADA	Especifica que ele representa mais de um arquivo de log virtual reutilizável
primeiro	PONTO DE VERIFICAÇÃO	Determina que nenhum ponto de verificação apareceu desde o último truncamento do log ou que o título do log não foi movido para fora do arquivo de log virtual
2	LOG_BACKUP	Especifique o backup de log necessário antes de executar o truncamento de log.
3	ACTIVE_BACKUP_OR_RESTORE	Especifica que um backup ou restauração está em andamento.
4	ACTIVE_TRANSACTION	Determina a transação ativa.
5	DATABASE_MIRRORING	Especifica que o espelhamento de banco de dados está pausado ou em modo de alto desempenho, o banco de dados espelhado está atrás do banco de dados principal