Risposta.

Storicamente, la gestione degli errori in SQL era limitata al rollback delle transazioni dopo un errore fatale. Tuttavia, con l'evolversi della logica di business, è sempre più richiesta una registrazione precisa degli errori, il rollback solo delle modifiche effettuate e la possibilità di annidare le chiamate alle procedure (nested transactions).

Il problema è che non tutti i DBMS supportano completamente le transazioni annidate (ad esempio, in MS SQL Server sono più che altro punti di salvataggio SAVEPOINT, piuttosto che vere e proprie transazioni), e le eccezioni in caso di errori possono interrompere l'esecuzione prima della necessaria pulizia se i meccanismi TRY/CATCH o analoghi non sono utilizzati.

Soluzione: utilizzare le strutture TRY/CATCH (o analoghe), SAVEPOINT per l'annidamento e configurare il comportamento delle procedure in caso di errori (ad esempio, SET XACT_ABORT in SQL Server).

Esempio di codice (MS SQL Server):

BEGIN TRY
    BEGIN TRANSACTION
        -- operazioni
        EXEC dbo.InnerProcedure
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    -- registrazione dell'errore
    INSERT INTO error_log VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH

Caratteristiche chiave:

Utilizzo di TRY/CATCH per intercettare gli errori.
Controllo di @@TRANCOUNT per l'annidamento.
SAVEPOINT (o equivalenti personalizzati per il controllo manuale del ritorno a un punto).

Domande insidiose.

Quando si verifica un errore in una transazione annidata, c'è un vero rollback di tutte le modifiche?

Nella maggior parte dei DBMS, le transazioni annidate non sono transazioni separate, ma punti di ripristino (SAVEPOINT). Durante un rollback generale, vengono annullate tutte le modifiche fino alla transazione iniziale (outer).

Cosa succede se si dimentica di controllare @@TRANCOUNT ed eseguire un ROLLBACK al di fuori di una transazione attiva?

Verrà sollevato un errore: non c'è alcuna transazione attiva da annullare. Controlla sempre @@TRANCOUNT > 0 prima di eseguire ROLLBACK.

Funzionano i trigger e i cursori in TRY/CATCH?

Sì, ma gli errori nei trigger possono causare il passaggio a CATCH e il rollback dell'intera transazione. È necessario prevedere la gestione degli errori sia nel corpo della procedura, sia negli oggetti chiamati (procedure, trigger).

Errori comuni e anti-pattern

Mancanza di controllo su @@TRANCOUNT: tentativo di rollback di una transazione inattiva.
COMMIT o ROLLBACK superflui a livelli annidati.
Registrazione dell'errore senza salvare tutti i dettagli (numero, testo, parametri).

Esempio della vita reale

Caso negativo

Diverse procedure annidate, ognuna delle quali utilizza BEGIN TRANSACTION / COMMIT senza TRY/CATCH, in caso di errore, parte dei dati modificata, parte no.

Pro:

Codice semplice

Contro:

Disallineamento dello stato
Errori poco chiari dopo un guasto

Caso positivo

Tutta la logica è avvolta in TRY/CATCH, le procedure annidate utilizzano SAVEPOINT o operano all'interno di una singola transazione esterna, tutti gli errori vengono registrati.