Come gestire le transazioni e gli errori su SQL Server

L’utilizzo delle transazioni su SQL Server nasce dalla necessità di effettuare più operazioni di aggiornamento del database come un unico blocco: se qualcosa dovesse andare storto in una delle operazioni della transazione, allora dovranno essere annullate anche tutte le altre. Nel gergo tecnico si dice che dobbiamo eseguire una ROLLBACK.

In questo articolo vediamo come scrivere del codice su SQL Server che permetta di gestire correttamente le transazioni in caso di errori. Vi anticipo che non sarà semplice perché SQL Server reagisce in modo diverso in base alla tipologia di errore che si verifica. Ma il tutto ci sarà più chiaro con una serie di esempi.

Errori diversi producono effetti differenti sulle transazioni

Partiamo da una semplice tabella Clienti con tre righe

IdClienteNome
1Nicola
2Francesco
3Alberto

e lanciamo una alla volta le seguente istruzioni. Partiamo aprendo una transazione

BEGIN TRAN

e aggiorniamo la riga del cliente 1.

UPDATE dbo.Clienti
SET    Nome = 'Raffaele'
WHERE  IdCliente = 1;

Ora aggiorniamo la riga del cliente 2, ma quest’istruzione genererà un errore in quanto è presente una divisione per zero.

UPDATE dbo.Clienti
SET    Nome = 'Raffaele'
WHERE  IdCliente = 2/0;

Aggiorniamo la riga del cliente 3

UPDATE dbo.Clienti
SET    Nome = 'Raffaele'
WHERE  IdCliente = 3;

ed eseguiamo il ROLLBACK della transazione per annullare tutte le modifiche

ROLLBACK;

Se interroghiamo la tabella vediamo che è rimasta completamente invariata. Fino a qui sembra tutto lineare, ma proviamo a eseguire del codice leggermente diverso. Riapriamo la transazione

BEGIN TRAN;

aggiorniamo la riga del cliente 1

UPDATE dbo.Clienti
SET    Nome = 'Raffaele'
WHERE  IdCliente = 1;

e anche quella del cliente 2. Questa volta generiamo un errore di conversione, una tipologia diversa dalla precedente (divisione per zero).

UPDATE dbo.Clienti
SET    Nome = 'Raffaele'
WHERE  IdCliente = 2/0;

Aggiorniamo infine la riga del cliente 3

UPDATE dbo.Clienti
SET    Nome = 'Raffaele'
WHERE  IdCliente = 3;

eseguiamo il rollback della transazione ma questa volta andrà in errore dicendoci che non ci sono transazioni aperte!

ROLLBACK;

Ma come è possibile? La “colpa” è dell’errore di conversione nella seconda query, che produce un rollback automatico della transazione. Di conseguenza la terza update sarà stata eseguita separatamente in auto-commit e mi ritroverò così la tabella modificata permanentemente:

IdClienteNome
1Nicola
2Francesco
3Raffaele

Ne deduciamo quindi che con le impostazioni di default alcuni errori effettuano in automatico il rollback della transazione, altri no. Errori diversi possono provocare comportamenti ancora diversi. Di conseguenza abbiamo bisogno di arricchire il nostro codice con altri costrutti per la gestione degli errori.

TRY CATCH su T-SQL

Facciamo un primo passo in avanti nella gestione degli errori riscrivendo il codice utilizzando i blocchi TRY – CATCH. Nel caso si verifichi un errore nelle query del blocco TRY, il flusso di esecuzione si interromperà. L’esecuzione riprenderà dalla prima istruzione presente nel blocco CATCH. Nel caso invece non si verifichino errori, le istruzioni del blocco CATCH non verranno eseguite.

BEGIN TRY
 BEGIN TRAN
  UPDATE dbo.clienti
  SET    Nome = 'Raffaele'
  WHERE  IdCliente = 1;
 
  UPDATE dbo.clienti
  SET    Nome = 'Raffaele'
  WHERE  IdCliente = 'a';
  UPDATE dbo.clienti
  SET    Nome = 'Raffaele'
  WHERE  IdCliente = 3;
 COMMIT TRAN;
END TRY
BEGIN CATCH
 IF @@trancount>0 ROLLBACK;
 THROW;
END CATCH

Osserviamo che il COMMIT è l’ultima istruzione del blocco TRY, di conseguenza verrà eseguito solo quando non si sono verificati errori in precedenza. Invece, se qualcosa va storto, il flusso passerà nel blocco CATCH. Analizziamo il codice al suo interno: tramite l’istruzione

IF @@trancount>0 ROLLBACK;

effettueremo la ROLLBACK nel caso ci siano transazioni aperte e infine con

THROW;

genereremo lo stesso errore che ha portato il codice a passare nel blocco CATCH.

Casi non gestiti dal TRY CATCH

Consideriamo ora questo codice con la stessa struttura del paragrafo precedente. Questa volta però l’errore è causato dal fatto che interroghiamo una tabella che non esiste.

BEGIN TRY
 BEGIN TRAN
  DELETE 
  FROM dbo.Clienti 
  WHERE IdCliente = 1;
  
  DELETE 
  FROM dbo.TabellaCheNonEsiste;
 COMMIT;
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0 ROLLBACK;
 THROW;
END CATCH

Il TRY-CATCH non riesce a gestire errori di questo genere e il codice si interromperà senza che siano eseguite le operazioni nel blocco CATCH. Di conseguenza non avverrà il rollback della transazione che rimarrà aperta. Se infatti dopo il codice precedente lanciamo l’istruzione

SELECT @@TRANCOUNT

vedremo 1 come risultato.

Per gestire anche questo caso possiamo usare l’opzione

SET XACT_ABORT ON

che varrà anche per le query successive eseguite nella stessa sessione.  Con questa opzione quasi sempre (ahimè anche in questo caso c’è qualche eccezione) verrà effettuato il ROLLBACK di una transazione quando viene sollevato un errore, sia tramite un THROW nel blocco CATCH e sia se il flusso si interrompe “bruscamente” per un errore causato da una tabella che non esiste.

L’opzione SET XACT_ABORT ON ha anche un altro effetto. Quando è OFF (come di default) alcuni errori nel blocco TRY potrebbero permetterci teoricamente di eseguire comunque il COMMIT della transazione nel blocco CATCH. Si tratta comunque di un’implementazione a mio parere molto rara, che personalmente non ho mai usato. Comunque con XACT_ABORT ON (quasi) tutti gli errori  renderanno impossibile eseguire il COMMIT della transazione nel blocco.

Il tuo percorso completo di Analisi dei Dati

Altro caso di utilizzo di SET XACT_ABORT ON

Ripristiniamo l’opzione

SET XACT_ABORT OFF

o usiamo un’altra sessione. Eseguiamo il codice

BEGIN TRY
 BEGIN TRAN
  DELETE 
  FROM  dbo.Clienti 
  WHERE IdCliente = 1;
  WAITFOR DELAY '00:01'; 
 COMMIT;
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0 ROLLBACK;
 THROW;
END CATCH

L’istruzione

WAITFOR DELAY '00:01'

genera un’attesa di 1 minuto. Per eseguire l’esperimento, lanciamo la query e interrompiamola dopo qualche secondo tramite il tasto “Cancel Executing Query” di SQL Server Management Studio.

Gestione errori sql server

La query si interromperà senza che l’istruzione passi nel blocco CATCH e quindi senza che avvenga il ROLLBACK della transazione. Anche in questo caso possiamo gestire l’errore con l’opzione SET XACT_ABORT ON. Questa casistica è molto simile a un errore di Timeout quando la query è lanciata su SQL Server da un’applicazione client.

Continua a imparare

Scroll to Top
Torna su