Odpowiedź.

Historycznie, przetwarzanie błędów w SQL ograniczało się tylko do rollback transakcji po krytycznym błędzie. Jednak wraz z rozwojem logiki biznesowej, coraz częściej wymagane jest dokładne logowanie błędu, rollback tylko tych zmian, które zostały dokonane, a także możliwość zagnieżdżania wywołań procedur (nested transactions).

Problem polega na tym, że nie wszystkie DBMS w pełni obsługują zagnieżdżone transakcje (na przykład w MS SQL Server jest to raczej poziom punktów zapisu SAVEPOINT, a nie prawdziwe transakcje), a wyjątki w przypadku błędów mogą przerywać wykonanie przed koniecznym czyszczeniem, jeśli mechanizmy TRY/CATCH lub analogi nie są używane.

Rozwiązanie: używać konstrukcji TRY/CATCH (lub analogicznych), SAVEPOINT dla zagnieżdżenia oraz konfigurować zachowanie procedur w przypadku błędów (na przykład SET XACT_ABORT w SQL Server).

Przykład kodu (MS SQL Server):

BEGIN TRY
    BEGIN TRANSACTION
        -- operacje
        EXEC dbo.InnerProcedure
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    -- logowanie błędu
    INSERT INTO error_log VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE())
END CATCH

Kluczowe cechy:

Użycie TRY/CATCH do przechwytywania błędów.
Kontrola @@TRANCOUNT dla zagnieżdżenia.
SAVEPOINT (lub własne analogi do ręcznego kontrolowania powrotu do punktu).

Pytania z pułapką.

Czy podczas wystąpienia błędu w zagnieżdżonej transakcji następuje rzeczywisty rollback wszystkich zmian?

W większości DBMS zagnieżdżone transakcje to nie oddzielne transakcje, a punkty powrotu (SAVEPOINT). Przy ogólnym rollbacku cofa się wszystkie zmiany aż do początkowej (outer) transakcji.

Co się stanie, jeśli zapomnisz sprawdzić @@TRANCOUNT i wykonasz ROLLBACK poza aktywną transakcją?

Zostanie wywołany błąd — brak aktywnej transakcji do cofnięcia. Zawsze sprawdzaj @@TRANCOUNT > 0 przed ROLLBACK.

Czy triggery i kursory działają w TRY/CATCH?

Tak, ale błędy w triggerze mogą spowodować przejście do CATCH i rollback całej transakcji. Należy przewidzieć obsługę błędów zarówno w ciele procedury, jak i w wywoływanych obiektach (procedurach, triggerach).

Typowe błędy i antywzorce

Brak kontroli @@TRANCOUNT — próba rollbacku nieaktywnej transakcji.
Zbędne COMMIT lub ROLLBACK na zagnieżdżonych poziomach.
Logowanie błędu bez zachowywania wszystkich szczegółów (numer, tekst, parametry).

Przykład z życia

Negatywny przypadek

Kilka zagnieżdżonych procedur, każda używa BEGIN TRANSACTION / COMMIT bez TRY/CATCH, przy błędzie część danych zmieniona, część nie.

Plusy:

Prosty kod

Minusy:

Niespójność stanu
Nieoczywiste błędy po awarii

Pozytywny przypadek

Cała logika opakowana w TRY/CATCH, zagnieżdżone procedury korzystają z SAVEPOINT lub operują w ramach jednej zewnętrznej transakcji, wszystkie błędy są logowane.