Transazione autonoma in Oracle PL / SQL

Cosa sono le istruzioni TCL in PL/SQL?

TCL sta per Transaction Control Statements (Istruzioni di controllo delle transazioni). Queste istruzioni salvano o annullano le transazioni in sospeso. Svolgono un ruolo fondamentale, perché a meno che una transazione non venga salvata, le modifiche apportate tramite Dichiarazioni DML non verrà memorizzato in modo permanente nel database. Di seguito sono riportate le diverse istruzioni TCL in PL / SQL.

La transazione sarà completata nei seguenti casi:

• Quando viene emessa una qualsiasi delle dichiarazioni di cui sopra (ad eccezione di SAVEPOINT).
• Quando vengono emesse le istruzioni DDL (le DDL sono istruzioni di auto-commit).
• Quando vengono emesse le istruzioni DCL (le DCL sono istruzioni di auto-commit).

Utilizzo di SAVEPOINT e ROLLBACK TO

La tabella sopra introduce SAVEPOINT e ROLLBACK TO, che insieme ti danno un controllo parziale su una transazione. Un SAVEPOINT contrassegna un punto denominato all'interno della transazione corrente. Un successivo ROLLBACK TO a quel savepoint annulla tutte le modifiche apportate dopo di esso, mentre mantieneping il lavoro svolto in precedenza è rimasto intatto.

Questo è utile quando una transazione lunga esegue diverse operazioni SQL passaggi e solo l'ultimo passaggio fallisce. Invece di scartare l'intera transazione, puoi tornare all'ultimo punto di salvataggio valido e continuare.

Sintassi:

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

Punti chiave da ricordare sui punti di salvataggio:

• Un SAVEPOINT esiste solo all'interno della transazione corrente; un COMMIT o un ROLLBACK completo cancellano tutti i savepoint.
• Quando si ripristina un punto di salvataggio, tutti i punti di salvataggio creati successivamente vengono cancellati, ma il punto di salvataggio a cui si è effettuato il ripristino viene conservato.
• ROLLBACK TO non termina la transazione; le modifiche apportate prima del punto di salvataggio rimangono in sospeso finché non si esegue COMMIT o ROLLBACK.
• Se si riutilizza il nome di un punto di salvataggio, il nuovo punto di salvataggio sposta l'indicatore nella posizione successiva.

Poiché ROLLBACK TO lascia la transazione aperta, alla fine spetta comunque decidere se COMMITARE le modifiche rimanenti o annullarle con un ROLLBACK completo.

Cos'è la transazione autonoma

In PL/SQL, tutte le modifiche apportate ai dati sono definite transazioni. Una transazione si considera completata quando viene eseguito un comando di salvataggio (save) o di annullamento (discard). Se non viene eseguito alcun comando di salvataggio o annullamento, la transazione non si considera completata e le modifiche apportate ai dati non saranno permanenti sul server.

Per impostazione predefinita, PL/SQL considera tutte le modifiche apportate durante una sessione come un'unica transazione, e il salvataggio o l'annullamento di tale transazione influisce su tutte le modifiche in sospeso nella sessione. Una transazione autonoma offre allo sviluppatore la possibilità di apportare modifiche in una transazione separata e di salvare o annullare tale transazione specifica senza influire sulla transazione principale della sessione.

• A livello di sottoprogramma è possibile specificare una transazione autonoma.
• Per farne qualcuno sottoprogramma Se si lavora in una transazione diversa, la parola chiave PRAGMA AUTONOMOUS_TRANSACTION deve essere specificata nella sezione dichiarativa di tale blocco.
• Questo comando indica al compilatore di trattare questa operazione come una transazione separata, e il salvataggio o l'eliminazione all'interno di questo blocco non si rifletteranno nella transazione principale.
• L'esecuzione di COMMIT o ROLLBACK è obbligatoria prima di uscire da questa transazione autonoma e tornare alla transazione principale, poiché in un dato momento può essere attiva una sola transazione.
• Pertanto, una volta avviata una transazione autonoma, questa deve essere salvata e completata prima che il controllo possa tornare alla transazione principale.

Sintassi:

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

Nella sintassi sopra riportata, il blocco è stato reso una transazione autonoma.

Esempio 1: In questo esempio, capiremo come funziona una transazione autonoma.

Lo screenshot qui sotto mostra questo esempio di transazione autonoma e il suo output in 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;

Uscita

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 Spiegazione:

• Code riga 2: Dichiarazione di l_salary come NUMERO.
• Code riga 3: Dichiarazione della procedura nested_block.
• Code riga 4: Trasformare la procedura nested_block in una AUTONOMOUS_TRANSACTION.
• Code righe 7-9: Aumento dello stipendio del dipendente numero 1002 di 15000.
• Code riga 10: Esecuzione della transazione autonoma.
• Code righe 13-16: Stampa dei dettagli salariali dei dipendenti 1001 e 1002 prima delle modifiche.
• Code righe 17-19: Aumento dello stipendio del dipendente numero 1001 di 5000.
• Code riga 20: Chiamata alla procedura nested_block.
• Code riga 21: Scartare la transazione principale.
• Code righe 22-25: Stampa dei dettagli salariali dei dipendenti 1001 e 1002 dopo le modifiche.

L'aumento salariale per il dipendente numero 1001 non viene visualizzato perché la transazione principale è stata scartata. L'aumento salariale per il dipendente numero 1002 viene invece visualizzato perché tale blocco di dati è stato reso una transazione separata e salvato alla fine.

Pertanto, indipendentemente dal salvataggio o dall'annullamento nella transazione principale, le modifiche nella transazione autonoma vengono salvate senza influire sulla transazione principale.

Quando utilizzare le transazioni autonome

Le transazioni autonome sono potenti, quindi è utile sapere quando utilizzarle. Riservatele alle operazioni che devono avere successo o fallire indipendentemente dalla transazione principale, non alla logica di business fondamentale. Esempi di utilizzo comuni includono:

• Registrazione di controllo: Registra chi ha modificato i dati sensibili, quando e i valori vecchi e nuovi, in modo che il registro rimanga disponibile anche se la transazione principale viene annullata.
• Registrazione degli errori: Scrivi un record di errore all'interno di un eccezione gestore e COMMIT it, in modo che i dettagli diagnostici vengano conservati mentre la transazione fallita viene scartata.
• Contatori e statistiche: Aumenta un contatore di utilizzo o di accessi che deve persistere indipendentemente dall'esito della chiamata.
• COMMIT all'interno di un trigger: Un trigger non può emettere COMMIT direttamente; una transazione autonoma è l'unico modo supportato per farlo.

Evitate le transazioni autonome per gli aggiornamenti ordinari che dovrebbero condividere il destino della transazione principale. Un uso eccessivo può nascondere i dati dietro commit indipendenti e rendere più difficile il debug. Come regola generale, ogni blocco autonomo deve terminare con un COMMIT o un ROLLBACK esplicito.

Transazioni autonome vs. transazioni regolari

La differenza tra una transazione ordinaria (principale) e una transazione autonoma risiede nella portata e nell'indipendenza. La tabella seguente le confronta.

A differenza di un normale blocco annidatoMentre un blocco principale, le cui modifiche condividono sempre l'esito della transazione che lo contiene, è un blocco autonomo indipendente. Comprendere questa differenza aiuta a decidere quando un blocco deve essere indipendente e quando deve condividere il risultato della transazione principale.

DOMANDE FREQUENTI