Uma transação SQL é uma sequência de uma ou mais operações SQL (por exemploINSERT UPDATE DELETE) executado como uma única unidade de trabalho. As transações garantem que todas as operações sejam bem-sucedidas ou que nenhuma seja aplicada, mantendo a integridade dos dados.

Principais propriedades de transações SQL: ACID

A integridade das transações SQL é governada pelas propriedades ACID que garantem transações confiáveis ​​do banco de dados. Estas quatro propriedades trabalham juntas para garantir que o banco de dados permaneça consistente e confiável.

• Atomicidade: O resultado de uma transação pode ser totalmente bem-sucedido ou totalmente malsucedido. Toda a transação deverá ser revertida se uma parte dela falhar.
  
• Consistência: As transações mantêm restrições de integridade movendo o banco de dados de um estado válido para outro.
  
• Isolamento: As transações simultâneas são isoladas umas das outras garantindo a precisão dos dados.
  
• Durabilidade: Depois que uma transação é confirmada, suas modificações permanecem em vigor mesmo no caso de falha do sistema.

Comandos de controle de transação SQL

Nos comandos de controle de transação SQL gerenciam o execução de operações SQL garantindo a integridade e confiabilidade das transações do banco de dados. Esses comandos ajudam a gerenciar o início da confirmação e a reversão das alterações feitas no banco de dados. Abaixo estão os principais comandos de controle de transação em SQL explicados com sintaxe e exemplos para cada um.

1. Comando BEGIN TRANSACTION

OBEGIN TRANSACTIONcomando marca o início de uma nova transação. Todas as instruções SQL que seguem este comando farão parte da mesma transação até que umCOMMIT ou ROLLBACK é encontrado. Este comando não faz nenhuma alteração no banco de dados, apenas inicia a transação.

Sintaxe:

BEGIN TRANSACTION transaction_name ;

Exemplo de transação SQL com cenário de transferência bancária

Vejamos um exemplo de transferência bancária entre duas contas. Este exemplo demonstra o uso de múltiplas consultas em uma única transação.

derivada parcial de látex

BEGIN TRANSACTION;  
  
-- Deduct 0 from Account A  
UPDATE Accounts  
SET Balance = Balance - 150  
WHERE AccountID = 'A';  
  
-- Add 0 to Account B  
UPDATE Accounts  
SET Balance = Balance + 150  
WHERE AccountID = 'B';  
  
-- Commit the transaction if both operations succeed  
COMMIT;

Se ocorrer algum erro, como um problema com oUPDATEconsulta que você pode usarROLLBACKpara desfazer todas as alterações feitas durante a transação:

ROLLBACK;

Isso garante que o sistema não acabe em um estado inconsistente, como deduzir dinheiro de uma conta sem adicioná-lo a outra.

BEGIN TRANSACTION TransferFunds;

2. Comando COMMIT

OCOMMITO comando é usado para salvar todas as alterações feitas durante a transação atual no banco de dados. Depois que uma transação é confirmada, as alterações são permanentes. 

Sintaxe:

COMMIT;

Exemplo

Aqui está a amostraStudenttabela que será usada para realizar as operações neste exemplo. Esta tabela contém detalhes básicos do aluno, como nome de identificação, idade e outras informações relevantes que serão manipuladas usando vários comandos de controle de transação.

A seguir está um exemplo que excluiria da tabela os registros com idade = 20 e então COMMIT as alterações no banco de dados. 

DELETE FROM Student WHERE AGE = 20;  
COMMIT;

Saída

3. Comando ROLLBACK

OROLLBACKO comando é usado para desfazer todas as alterações feitas na transação atual. É usado quando ocorre um erro ou quando as alterações desejadas não podem ser concluídas. O banco de dados retornará ao estado em que estava antes do BEGIN TRANSACTION foi executado.

Sintaxe:

ROLLBACK;

Exemplo

Exclua da tabela os registros que têm idade = 20 e depois ROLLBACK as alterações no banco de dados. Neste caso o DELETE a operação é desfeita e as alterações no banco de dados não são salvas.

DELETE FROM Student WHERE AGE = 20;  
ROLLBACK;

Saída:

4. Comando SAVEPOINT

UMSAVEPOINTé usado para criar um posto de controle dentro de uma transação. Podemos reverter para um específicoSAVEPOINTem vez de reverter toda a transação. Isso nos permite desfazer parte da transação em vez de toda a transação.

Sintaxe:

SAVEPOINT SAVEPOINT_NAME;

Exemplo

SAVEPOINT SP1;  
//Savepoint created.  
DELETE FROM Student WHERE AGE = 20;  
//deleted  
SAVEPOINT SP2;  
//Savepoint created.

Saída:

Explicação:

Do exemplo acima, tabela de exemplo1 Exclua os registros da tabela que têm idade = 20 e, em seguida, ROLLBACK as alterações no banco de dados, mantendo Savepoints. Aqui o SP1 é o primeiro SAVEPOINT criado antes da exclusão. Neste exemplo, ocorreu uma exclusão. Após a exclusão, o SAVEPOINT SP2 é criado novamente. 

5. ROLLBACK PARA SAVEPOINT

OROLLBACK TO SAVEPOINTO comando nos permite reverter a transação para um ponto de salvamento específico, desfazendo efetivamente as alterações feitas após esse ponto.

Sintaxe:

ROLLBACK TO SAVEPOINT SAVEPOINT_NAME;

Exemplo

A exclusão ocorreu, vamos supor que mudamos de ideia e decidimos ROLLBACK para o SAVEPOINT que identificamos como SP1, que está antes da exclusão. Então neste caso oDELETEoperação é desfeita e a transação retorna ao estado em que estava no momentoSP1ponto de salvamento.

ROLLBACK TO SP1;  
//Rollback completed

Saída:

6. Comando RELEASE SAVEPOINT

Este comando é usado para remover um SAVEPOINT que criamos. Depois que um SAVEPOINT for liberado não poderemos mais usar o ROLLBACK comando para desfazer transações realizadas desde o último SAVEPOINT. É usado para iniciar uma transação de banco de dados e para especificar as características da transação que se segue. 

Sintaxe:  

RELEASE SAVEPOINT SAVEPOINT_NAME;

Exemplo

Uma vez que o ponto de salvamentoSP2for liberado, não podemos mais reverter para ele.

RELEASE SAVEPOINT SP2; -- Release the second savepoint.

Por que usar transações bancárias?

Neste caso, sem uma transação, você corre o risco de cenários em que o dinheiro é deduzido de uma conta, mas não adicionado à outra, deixando o sistema num estado inconsistente. As transações garantem que tais problemas sejam evitados, garantindo que ambas as operações sejam bem-sucedidas ou falhem juntas.

Tipos de transações SQL

Existem diferentes tipos de transações com base na sua natureza e nas operações específicas que realizam:

• Ler transações : Usado apenas para ler os dados normalmente com SELECT consultas.
  
• Escrever transações : envolvem a modificação dos dados no banco de dados com INSERT UPDATEouDELETEoperações.
  
• Transações Distribuídas : essas transações abrangem vários bancos de dados e garantem consistência entre eles.
  
• Transações implícitas : iniciado automaticamente pelo SQL Server para determinadas operações.
  
• Transações explícitas : Transações controladas manualmente onde o usuário inicia e termina a transação usandoBEGIN TRANSACTION COMMITeROLLBACK.

Monitorando e otimizando transações SQL

Para manter o desempenho e evitar problemas, considere as seguintes técnicas:

1. Bloqueios de monitor : rastreie o comportamento de bloqueio e ajuste as consultas para minimizar conflitos de bloqueio.

2. Limitar o escopo da transação : limite o número de linhas ou registros afetados por uma transação para acelerar o processamento.

3. Use processamento em lote : se você estiver lidando com grandes quantidades de dados, divida as operações em transações ou lotes menores para evitar sobrecarregar o sistema.

Qual dos seguintes cenários descreve melhor uma violação da propriedade 'Isolamento' no ACID?

• UM

  Uma transação deixa o banco de dados em um estado que viola uma restrição de chave primária.

• B

  Duas transações em execução leem e gravam simultaneamente nos mesmos dados, levando a resultados inconsistentes.

• C

  Um usuário atualiza um registro com êxito, mas uma falha no sistema apaga a alteração.

• D

  Uma transação falha no meio do caminho e todas as suas alterações são revertidas.

Este é um exemplo clássico de violação de isolamento em que o estado intermediário de uma transação é visível para outra.

Numa aplicação bancária, uma transferência de fundos envolve o débito de uma conta e o crédito de outra. Qual propriedade ACID garante que ambas as operações sejam concluídas ou nenhuma delas?

• UM

  Isolamento

• B

  Atomicidade

• C

  Durabilidade

• D

  Consistência

A atomicidade garante que todas as operações dentro de uma transação sejam concluídas com sucesso; caso contrário, toda a transação será revertida.

Uma transação é executada e um 'COMMIT' é emitido. Imediatamente após ocorrer uma falha de energia. Qual propriedade ACID garante que as alterações feitas pela transação ainda estarão presentes após a reinicialização do sistema?

• UM

  Consistência

• B

  Atomicidade

• C

  Durabilidade

• D

  Isolamento

Durabilidade é a propriedade que garante que, uma vez confirmada uma transação, ela permanecerá assim mesmo no caso de perda de energia ou falha do sistema.

Qual é o objetivo principal do comando 'SAVEPOINT' em uma transação?

• UM

  Para confirmar uma parte da transação.

• B

  Para marcar um ponto em uma transação para o qual você poderá reverter posteriormente.

• C

  Para salvar permanentemente o estado da transação.

• D

  Para encerrar a transação e tornar todas as alterações permanentes.

'SAVEPOINT' permite reversões parciais dentro de uma transação.

Considere a seguinte transação: 'START TRANSACTION; INSERIR...; PONTO DE SALVAÇÃO A; ATUALIZAR ...; PONTO DE SALVAÇÃO B; EXCLUIR...; ROLLBACK PARA SAVEPOINT A;' Qual é o estado da transação após o comando 'ROLLBACK'?

normalização de rdbms

• UM

  Toda a transação é revertida.

• B

  Apenas o 'DELETE' é desfeito.

• C

  Ocorre um erro porque você não pode reverter para um ponto de salvamento que não seja o mais recente.

• D

  O 'INSERT' é salvo mas o 'UPDATE' e 'DELETE' são desfeitos.

Reverter para 'SAVEPOINT A' desfaz todas as alterações feitas após esse savepoint que são 'UPDATE' e 'DELETE'.