Transação autônoma em Oracle PL/SQL

O que são instruções TCL em PL/SQL?

TCL significa Instruções de Controle de Transação. Essas instruções salvam ou revertem as transações pendentes. Elas desempenham um papel vital, pois, a menos que uma transação seja salva, as alterações feitas por meio dela não serão revertidas. Declarações DML não serão armazenados permanentemente no banco de dados. Abaixo estão as diferentes instruções TCL em PL/SQL.

A transação será concluída nos seguintes cenários:

• Quando qualquer uma das declarações acima for emitida (exceto SAVEPOINT).
• Quando instruções DDL são emitidas (DDL são instruções de confirmação automática).
• Quando instruções DCL são emitidas (DCL são instruções de confirmação automática).

Usando SAVEPOINT e ROLLBACK TO

A tabela acima apresenta os conceitos de SAVEPOINT e ROLLBACK TO, que juntos oferecem controle parcial sobre uma transação. Um SAVEPOINT marca um ponto específico dentro da transação atual. Um ROLLBACK TO posterior a esse ponto de salvamento desfaz todas as alterações feitas após ele, enquanto mantém as alterações existentes.ping o trabalho realizado anteriormente permaneceu intacto.

Isso é útil quando uma transação longa executa várias etapas. SQL etapas e apenas a última etapa falha. Em vez de descartar toda a transação, você pode reverter para o último ponto de salvamento válido e continuar.

Sintaxe:

SAVEPOINT <savepoint_name>;
   -- one or more DML statements
ROLLBACK TO <savepoint_name>;

Pontos importantes a lembrar sobre os pontos de salvamento:

• Um ponto de salvamento existe apenas dentro da transação atual; um COMMIT ou um ROLLBACK completo apaga todos os pontos de salvamento.
• Ao retornar a um ponto de salvamento, quaisquer pontos de salvamento criados posteriormente serão apagados, mas o ponto de salvamento para o qual você retornou será mantido.
• ROLLBACK TO não finaliza a transação; as alterações feitas antes do ponto de salvamento permanecem pendentes até que você execute COMMIT ou ROLLBACK.
• Se você reutilizar o nome de um ponto de salvamento, o novo SAVEPOINT moverá o marcador para a posição posterior.

Como o ROLLBACK TO mantém a transação aberta, você ainda decide no final se deseja CONFIRMAR as alterações restantes ou descartá-las com um ROLLBACK completo.

O que é transação autônoma

Em PL/SQL, todas as modificações feitas nos dados são chamadas de transação. Uma transação é considerada completa quando um comando de salvar ou descartar é aplicado a ela. Se nenhum comando de salvar ou descartar for executado, a transação não é considerada completa e as modificações feitas nos dados não serão tornadas permanentes no servidor.

Por padrão, o PL/SQL trata todas as modificações durante uma sessão como uma única transação, e salvar ou descartar essa transação afeta todas as alterações pendentes na sessão. Uma transação autônoma permite ao desenvolvedor fazer alterações em uma transação separada e salvar ou descartar essa transação específica sem afetar a transação principal da sessão.

• Uma transação autônoma pode ser especificada no nível do subprograma.
• Para fazer qualquer subprograma Para trabalhar em uma transação diferente, a palavra-chave PRAGMA AUTONOMOUS_TRANSACTION deve ser fornecida na seção declarativa desse bloco.
• Isso instrui o compilador a tratar isso como uma transação separada, e salvar ou descartar dentro deste bloco não terá reflexo na transação principal.
• É obrigatório enviar um COMMIT ou ROLLBACK antes de sair desta transação autônoma e retornar à transação principal, pois apenas uma transação pode estar ativa por vez.
• Assim, uma vez iniciada uma transação autônoma, ela deve ser salva e concluída antes que o controle possa retornar à transação principal.

Sintaxe:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
.
BEGIN
<execution_part>
[COMMIT|ROLLBACK]
END;
/

Na sintaxe acima, o bloco foi transformado em uma transação autônoma.

1 exemplo: Neste exemplo, vamos entender como funciona uma transação autônoma.

A captura de tela abaixo mostra este exemplo de transação autônoma e sua saída em Oracle.

DECLARE
   l_salary   NUMBER;
   PROCEDURE nested_block IS
   PRAGMA autonomous_transaction;
    BEGIN
     UPDATE emp
       SET salary = salary + 15000
       WHERE emp_no = 1002;
   COMMIT;
   END;
BEGIN
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;
   dbms_output.put_line('Before Salary of 1001 is'|| l_salary);
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
   dbms_output.put_line('Before Salary of 1002 is'|| l_salary);    
   UPDATE emp 
   SET salary = salary + 5000 
   WHERE emp_no = 1001;

nested_block;
ROLLBACK;

 SELECT salary INTO  l_salary FROM emp WHERE emp_no = 1001;
 dbms_output.put_line('After Salary of 1001 is'|| l_salary);
 SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
 dbms_output.put_line('After Salary of 1002 is '|| l_salary);
end;

saída

Before:Salary of 1001 is 15000 
Before:Salary of 1002 is 10000 
After:Salary of 1001 is 15000 
After:Salary of 1002 is 25000

Code Explicação:

• Code linha 2: Declarando l_salary como NÚMERO.
• Code linha 3: Declarando o procedimento nested_block.
• Code linha 4: Transformando o procedimento nested_block em uma AUTONOMOUS_TRANSACTION.
• Code linhas 7-9: Aumentar o salário do funcionário número 1002 em 15000.
• Code linha 10: Confirmando a transação autônoma.
• Code linhas 13-16: Impressão dos detalhes salariais dos funcionários 1001 e 1002 antes das alterações.
• Code linhas 17-19: Aumentar o salário do funcionário número 1001 em 5000.
• Code linha 20: Chamando o procedimento nested_block.
• Code linha 21: Descartando a transação principal.
• Code linhas 22-25: Impressão dos detalhes salariais dos funcionários 1001 e 1002 após as alterações.

O aumento salarial do funcionário número 1001 não foi refletido porque a transação principal foi descartada. O aumento salarial do funcionário número 1002 foi refletido porque esse bloco foi transformado em uma transação separada e salvo ao final.

Assim, independentemente de salvar ou descartar na transação principal, as alterações na transação autônoma são salvas sem afetar a transação principal.

Quando usar transações autônomas

Transações autônomas são poderosas, por isso é importante saber quando usá-las. Reserve-as para tarefas que precisam ser concluídas com sucesso ou falhar independentemente da transação principal, e não para a lógica de negócios essencial. Casos de uso comuns incluem:

• Registro de auditoria: Registre quem alterou os dados confidenciais, quando e os valores antigos e novos, para que o registro permaneça válido mesmo se a transação principal for revertida.
• Erro ao registrar: Escreva um registro de erro dentro de um exceção manipulador e COMMITe-o, de forma que os detalhes de diagnóstico sejam mantidos enquanto a transação com falha é descartada.
• Contadores e estatísticas: Avançar um contador de uso ou de acessos que deve persistir independentemente do resultado da chamada.
• COMMIT dentro de um gatilho: Um gatilho não pode emitir um COMMIT diretamente; uma transação autônoma é a única maneira suportada de fazê-lo.

Evite transações autônomas para atualizações comuns que devem compartilhar o destino da transação principal. O uso excessivo delas pode ocultar dados por trás de commits independentes e dificultar a depuração. Como regra geral, todo bloco autônomo deve terminar com um COMMIT ou ROLLBACK explícito.

Transações autônomas versus transações regulares

A diferença entre uma transação regular (principal) e uma transação autônoma reside no escopo e na independência. A tabela abaixo compara-as.

Ao contrário de um normal bloco aninhadoUm bloco independente, cujas alterações sempre compartilham o resultado da transação que o contém, é um bloco autônomo. Compreender essa diferença ajuda a decidir quando um bloco deve ser independente e quando deve compartilhar o resultado da transação principal.

Perguntas Frequentes