Como reverter usando transações explícitas do SQL Server

Como reverter usando transações explícitas do SQL Server

Como reverter usando transações explícitas do SQL Server 1
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Neste artigo, exploraremos o processo de reversão de uma transação explícita do SQL Server. Também vamos explorar
a diferença entre transações explícitas e implícitas.

Introdução

Uma transação no SQL Server é uma única unidade de trabalho em um banco de dados. Realizamos muitas transações diariamente. No real
mundo, considere uma transação bancária. Suponha que você retire dinheiro da sua conta bancária e espera que
obtenha sucesso assim que receber dinheiro em sua conta. No caso, você tenta sacar dinheiro, mas depois de terminar com
todas as formalidades, devido a um erro técnico, o valor foi deduzido da sua conta, mas não chegou a
você. Nesse caso, a transação deve ser revertida e o valor reaparecer na sua conta.

Semelhante ao cenário acima, considere que você está executando um script no banco de dados de produção que atualiza os dados no
a tabela existente. Seu desenvolvedor deixou de colocar uma cláusula WHERE e pode atrapalhar seus dados. Neste artigo, nós
tente encontrar respostas para as seguintes perguntas.

  • Temos uma opção para reverter alterações no SQL Server?

  • Que precauções podemos tomar antes de executar consultas em um ambiente de produção?

Antes de avançarmos, você pode consultar o SQL
Artigo Visão geral da transação do servidor e consulte as propriedades do ACID e os estados da transação.

Propriedades ACID e estados de transação.

Transação implícita e explícita do SQL Server

Transação implícita do SQL Server:

O comportamento padrão do SQL Server é transação implícita. Ele fornece a funcionalidade de confirmação automática, para que você não precise
para emitir uma instrução COMMIT TRAN. É uma solução conveniente e podemos evitar problemas de transações abertas, como
sessão mantendo recursos, mas não foi confirmado.

Para exibir a propriedade padrão do SQL Server, conecte-se a uma instância do SQL no SSMS. Vá em Ferramentas-> Opções -> procure
palavra chave Inquerir.

No menu filtrado, clique em ANSI e você verá a opção SET IMPLICIT_TRANSACTION o modo está desativado.

SET IMPLICIT_TRANSACTION

Por exemplo, executamos a instrução de atualização abaixo e ela confirma automaticamente os dados sem solicitar COMMIT ou ROLLBACK
declaração.

Depois que o SQL Server confirma uma transação, você não pode executar a instrução ROLLBACK. Cada instrução de reversão deve ter um
associação com a instrução BEGIN Transaction.

Erro devido a nenhuma BEGIN TRANSACTION

Vamos voltar para a opção SSMS e, em uma nova janela de consulta, use a opção SET IMPLICIT_TRANSACTION ON antes
iniciando uma transação.

Ele confirma uma única linha e você obtém a saída – 1 linha afetada. Para minha demonstração, esta consulta usa o SPID 55.

TRANSAÇÃO IMPLÍCITO

Agora, verifique os bloqueios mantidos pela sessão 55 usando o sp_whoisactive armazenado
procedimento.

sp_whoisactive

Na saída acima, vemos uma transação aberta para o SPID 55. Clique no hiperlink para obter bloqueios e você obtém
bloqueios atualmente retidos em um formato XML.

Verifique os detalhes XML do bloqueio

Agora, abra uma nova janela de consulta e tente selecionar os mesmos registros que atualizamos.

Ainda não confirmamos transações de atualização, portanto, a instrução SELECT enfrenta o bloqueio.

Bloqueando o SPID

Volte para a sessão de atualização e confirme os registros para limpar o bloqueio. Se especificarmos SET IMPLICIT_TRANSACTIONS ON
em uma transação, o SQL Server inicia automaticamente uma transação para você e aguarda suas instruções para confirmar ou confirmar
dados de reversão.

Você pode verificar o status de uma transação implícita para uma sessão de consulta, usando a consulta abaixo.

Transação implícita

Transação explícita do SQL Server

Nesse modo, cada bloco de código começa com uma instrução BEGIN TRANSACTION e requer um COMMIT ou
Instrução ROLLBACK. Ele oferece a flexibilidade de decidir se você deseja salvar as alterações executadas por consulta ou não.

  • COMEÇAR A TRANSAÇÃO – Indica o ponto inicial de uma transação
  • TRANSAÇÃO ROLLBACK -Inicia o processo de reversão e reverte todas as alterações realizadas pela transação. Pode demorar muito, dependendo das alterações realizadas anteriormente
  • COMPRAR TRANSAÇÃO -Comete as alterações no banco de dados. Depois que emitimos uma transação de confirmação, ela não pode ser revertida

Podemos obter o controle de transação usando transações explícitas semelhantes à especificação de SET
IMPLICIT_TRANSACTIONS ON.

Na consulta abaixo, especificamos BEGIN TRAN, no início, para especificar uma transação explícita e reverter o
atualizações.

Podemos definir um nome para a transação, bem como usar transações explícitas. Isso nos ajuda a confirmar ou reverter um
transação específica quando temos várias transações em uma consulta.

Na consulta abaixo, temos duas transações Demotran1 e Demotran2. Revertemos a primeira transação, mas confirmamos
a segunda transação.

Transação explícita do SQL Server

Vamos explorar alguns exemplos úteis para transações explícitas.

Comportamento das colunas IDENTITY para reversão explícita

Suponha que você tenha iniciado uma transação explícita que insere um registro na tabela de demonstração. Mais tarde, queremos reverter.
Esta tabela também possui uma coluna de identidade.

Qual seria o impacto na coluna identidade se revertermos uma transação do SQL Server? Vamos explorar usando um
exemplo. Primeiro, crie a tabela de demonstração com duas colunas [id] e [EmpName]. ID é uma coluna de identidade para esta tabela.

Na consulta abaixo, realizamos as seguintes tarefas.

  • Verifique o valor atual da identidade usando a função IDENT_CURRENT ()

  • Ele inicia uma transação explícita usando a instrução BEGIN TRANSACTION

  • Insere alguns registros

  • Verifique a identidade após inserções

Podemos ver que o primeiro registro na tabela de demonstração obtém o valor de identidade 1 e, após as inserções, o valor de identidade é 4.

Colunas IDENTITY

Vamos reverter esta transação e verificar o valor da identidade. Depois que a reversão é concluída, o SQL Server remove o
linhas de dados, mas ainda assim, o valor da identidade é definido como 4. Se inserirmos uma nova linha na tabela de demonstração, ela obterá a próxima
valor da identidade 5.

Transação de reversão

Esta demonstração mostra que uma transação explícita reverte uma transação, mas não pode reverter a identidade
valor. É a razão pela qual vemos lacunas no valor da identidade em uma tabela SQL. Seu valor depende da última identidade
valor para essa tabela. Você pode usar a função RESEED para redefinir os valores de identidade em uma tabela.

Variáveis ​​da tabela SQL e transação explícita do SQL Server

Usamos variáveis ​​de tabela como um tipo de dados de tabela específico que você pode usar para executar tabelas intermediárias semelhantes a
tabelas temporárias. Você pode consultar um
visão geral do artigo da variável de tabela SQL, para saber mais sobre ele.

No exemplo abaixo, realizamos as seguintes tarefas.

  • Declarar uma variável de tabela @Demo

  • Inserir um registro nele

  • Inicia uma transação explícita usando BEGIN TRANSACTION

  • Atualize o registro na variável de tabela

  • Transação de reversão

  • Verifique o valor do registro na variável da tabela.

Variáveis ​​de tabela

Na captura de tela acima, podemos observar que o SQL Server não reverte o valor mantido em uma variável de tabela usando o
transação explícita. Você precisa considerar isso antes de usar a variável de tabela e reverter usando uma variável explícita.
transação.

@@ Trancount para acompanhar transações abertas do SQL Server

Podemos usar a variável global @@ trancount para rastrear as transações em aberto.

  • Uma BEGIN TRANSACTION incrementa o valor para @@ Trancount em 1

  • Uma transação de confirmação diminui o valor para @@ Trancount em 1

  • @@ O valor zero da conta não mostra transações pendentes na sessão atual

  • Um valor diferente de zero para @@ Trancount mostra que os dados da primeira BEGIN TRANSACTION ainda não foram confirmados

  • Um comando ROLLBACK TRANSACTION define o valor para @@ Trancount como zero

Na consulta abaixo, você pode ver como o valor é alterado para a conta @@ conforme BEGIN TRANSACTION e COMMIT
Instruções TRANSACTION para transações explícitas do SQL Server.

@@ Varcount variável global

Vamos ver outro exemplo. Execute a seguinte consulta e observe o valor de @@ trancount.

Na captura de tela abaixo, observe os seguintes pontos.

  1. Inicialmente, não temos uma transação, então @@ trancount retorna o valor 0

  2. A primeira instrução BEGIN TRANSACTION incrementa o valor de @@ trancount em 1, então @@ trancount retorna 1 na saída

  3. O segundo BEGIN TRANSACTION incrementa o valor de @@ trancount y 1, retornando 2 na saída

  4. A primeira instrução commit diminui o valor de @@ trancount y 1, portanto, retorna 1 na saída

  5. No final, temos um ROLLBACK TRANSACTION que redefine o valor de @@ trancount para zero, portanto, ele reverte toda a transação, independentemente da transação de confirmação anterior

Verifique o status de @@ Trancount

Podemos usar a conta @@ trancount no procedimento armazenado para verificar as transações abertas existentes e confirmar as transações
no valor de @@ trancount é maior que 1. No procedimento armazenado abaixo, cometemos erros ao usar o comando TRY
Bloco de captura. Ele executa COMMIT ou ROLLBACK, dependendo do valor de @@ trancount.

Teste de desempenho de transações implícitas e explícitas

Vamos fazer uma rápida comparação de desempenho das transações implícitas e explícitas do SQL Server. Para esta demonstração, nós
insira registros na tabela de demonstração com transações implícitas e explícitas. Ambas as consultas inserem o mesmo número de
linhas na tabela de demonstração.

Antes de executar a consulta, ative as estatísticas do cliente na opção Consulta SSMS-> Ativar estatísticas do cliente.

Primeiro, eu executo a transação implícita e, mais tarde, na mesma janela de consulta, inicio a transação explícita.

Na captura de tela abaixo, podemos ver a transação explícita sendo executada mais rapidamente em comparação com a transação implícita.
Você vê uma diferença no tempo de processamento do cliente, no tempo total de execução e no tempo de espera nas respostas do servidor.

Comparação de transações implícitas e explícitas

Na transação explícita, o SQL Server libera apenas quando os blocos de log na memória estão cheios. Na transação implícita, ele registra a liberação com mais frequência, portanto, requer mais tempo para concluir.

Conclusão

Neste artigo, discutimos transações implícitas e explícitas do SQL Server. Podemos decidir confirmar ou reverter um
transação no modo explícito. Você deve usar transações explícitas, especialmente no ambiente de produção em que um pequeno erro pode levar a um problema significativo. Você sempre deve ter uma estratégia adequada de backup e recuperação de dados antes de fazer alterações nos dados de produção.

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
Leia Também  Registre-se agora para assistir às consultas Brent Tune hoje à noite (grátis)