Spesso abbiamo bisogno di effettuare più operazioni di aggiornamento di un Database in una transazione. Ad esempio l’aggiunta di un movimento di conto corrente e il relativo aggiornamento del saldo devono essere eseguiti come un unico blocco: se qualcosa dovesse andare storto in una delle due operazioni, allora anche l’altra deve essere annullata. 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 su SQL Server producono effetti diversi

Partiamo da una semplice tabella Clienti con tre righe

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

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.

Istruzioni 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 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 io personalmente non ho mai usato. Comunque con XACT_ABORT ON (quasi) tutti gli errori  renderanno impossibile eseguire il COMMIT della transazione nel blocco.

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.

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 ad un errore di Timeout quando la query è lanciata su SQL Server da un’applicazione client.

Continua a imparare

• Ripassiamo l’utilizzo delle Window Function
• Studiamo Cross Apply e Outer Apply
• Scriviamo insieme una query che calcoli dinamicamente l’elenco dei valori da usare nella PIVOT