Cosa sono e come gestire le transazioni su SQL Server

Gestire correttamente gli errori durante l'esecuzione delle transazioni su SQL Server è vitale per mantenere l'integrità dei dati e assicurare il funzionamento corretto delle operazioni.

Facciamo prima però un passo indietro e capiamo perché in alcuni casi è fondamentale usare le transazioni.

Le transazioni

Per spiegare le transazioni prendiamo ad esempio il database di una banca in cui ogni giorno dobbiamo eseguire per migliaia di volte queste due operazioni: aggiungere una riga nella tabella dei movimenti dei conti correnti e aggiornare il valore della colonna saldo corrispondente.

Cosa succederebbe se l'aggiunta del movimento andasse a buon fine, mentre l'aggiornamento del saldo per un qualsiasi motivo (anche per un banale timeout o errore di rete) genera un errore?

Sicuramente avremo molti clienti scontenti! In altri termini il nostro database si troverebbe in uno stato inconsistente e con importanti problemi di Data Quality.

Un altro esempio: dobbiamo cancellare le righe da una tabella con una certa data di riferimento per inserirne subito dopo delle nuove con la medesima data.

Cosa succede se, una volta confermata la cancellazione, l'inserimento dovesse fallire? Anche in questo caso avremmo una perdita dei dati, o comunque una situazione sicuramente meno consistente rispetto a quella di partenza.

Questi sono solo alcuni esempi per cui risulta fondamentale avere sui Database un meccanismo per gestire le transazioni.

L'obiettivo è eseguire alcuni specifici insiemi di operazioni di modifica dei dati in modo atomico, in modo da confermare tutti gli aggiornamenti contemporaneamente oppure annullarli se anche uno sola di essi non va a buon fine.

Su un Database SQL Server, se lavoriamo con le impostazioni di Default, dobbiamo esplicitare l'operazione di apertura di una transazione con l'istruzione

BEGIN TRAN

per poi decidere di confermare o meno le modifiche tramite una Commit o una Rollback. Tuttavia il percorso non è sempre così lineare perché, come vedremo nel prossimo paragrafo, alcuni errori potrebbero chiudere automaticamente la transazione. Inoltre il nostro obiettivo è salvare il codice all'interno di procedure automatiche, che riescano a gestire in autonomia il rollback in caso di errori, senza che qualcuno stia a lanciare l'istruzione manualmente.

Impatto degli errori sulle transazioni di SQL Server

Su SQL Server un errore di divisione per zero ha un impatto diverso sulla transazione rispetto a un errore di conversione. Dopo aver avviato esplicitamente la transazione con BEGIN TRAN, un errore di divisione per zero lascia la transazione aperta, mentre un errore di conversione causa il rollback automatico della transazione.

Questo è solo un esempio, altri errori potrebbero provocare comportamenti diversi. Invece di enumerare tutti i casi possibili, studiamo come arricchire il nostro codice con il costrutto TRY-CATCH, in modo da gestire in automatico la maggior parte dei possibili errori.

Try Catch in SQL

Il costrutto Try - Catch può essere usato in generale su SQL Server per gestire gli errori. Ad esempio l'istruzione

BEGIN TRY
   SELECT 1/0;
 END TRY
 BEGIN CATCH
   SELECT 2; 
 END CATCH

seleziona il valore 2. L'utilizzo di Try-Catch diventa davvero interessante se utilizzato per gestire in automatico gli errori durante una transazione. Analizziamo ad esempio questo codice:

BEGIN TRY

 /* calcoli che non effettuano aggiornamenti dei dati */

 BEGIN TRAN

  DELETE FROM Tabella 
  WHERE DataRiferimento = @Datarif

  INSERT INTO Tabella (colonna1, colonna2)
  SELEC colonna1, colonna2 
  FROM  Staging_tabella

 COMMIT;
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0 ROLLBACK;

 /* gestione errore */

 THROW;
END CATCH

Prima di entrare nei dettagli del try-catch vi ricordo che specificare l'elenco delle colonne dopo una Insert come fatto in questo esempio rientra nelle buone pratiche di uno sviluppatore SQL.

Analizziamo il codice precedente: la presenza del blocco BEGIN TRY sposta immediatamente il flusso al blocco CATCH in caso di errori. In questo modo la Commit, essendo l'ultima istruzione del TRY, verrà eseguita solo quando sia la Delete e la Insert andranno a buon fine. Vi ricordo che è importante fare in modo che il codice dentro il blocco BEGIN TRAN sia eseguito nel modo più veloce possibile per evitare l'insorgere di Deadlock. A tal proposito ho dedicato un video sul mio canale Youtube

Tornando al nostro codice, nel blocco Catch viene eseguito subito un rollback nel caso ci sia qualche transazione aperta (l'IF serve perché l'errore si potrebbe generare anche nella parte di calcoli tra l'apertura del blocco Try e l'apertura del blocco Tran). Poi viene gestito l'errore, ad esempio andando a scrivere in una tabella di Log o inviando una mail, e infine possiamo (in base alla situazione) sollevare o meno nuovamente l'errore che ha portato il codice a passare nel blocco Catch.

Concludiamo l'articolo menzionando che non tutti gli errori possono essere gestiti dal costrutto TRY-CATCH (come ad esempio alcuni errori di sintassi o timeout delle query quando lanciate da un client). In alcuni di questi casi potremmo prendere in considerazione l'utilizzo dell'istruzione SET XACT_ABORT