TRY...CATCH w procedurze zapisu: po ROLLBACK trzeba jeszcze zgłosić błąd

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Blok TRY CATCH z ROLLBACK i THROW w procedurze składowanej decyduje, co zobaczy wywołujący po błędzie zapisu: wyjątek albo pozorny sukces. Formularz, który woła procedurę zapisu, pokaże przy samym ROLLBACK komunikat o zapisie, choć tabela została pusta. Pokazujemy wzorzec, który tego unika.

Po ROLLBACK w bloku CATCH procedura musi zgłosić błąd dalej instrukcją THROW bez parametrów. Bez niej wywołujący nie dostaje wyjątku, tabela zostaje pusta, a dla aplikacji brak wyjątku oznacza sukces. Poprawna kolejność zaczyna się od ROLLBACK własnej transakcji i kończy na THROW, a między nimi stoi zapis błędu do tabeli.

Procedura z samym ROLLBACK w CATCH wygląda jak sukces

Zacznijmy od wariantu, którego nie należy stosować. Procedura otwiera transakcję, zapisuje wiersz, a w bloku CATCH wykonuje wyłącznie ROLLBACK. Wygląda to na staranną obsługę błędu, bo transakcja jest wycofywana.

CREATE PROCEDURE dbo.rejestr_zapisz_cicho
    @email varchar(100), @opis varchar(50)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.rejestr (email, opis) VALUES (@email, @opis);
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
    END CATCH
END
GO

Przykład ilustracyjny, dane wymyślone. Wariant, którego nie należy stosować: CATCH wycofuje transakcję i nie zgłasza błędu.

W teście wywołujący nie złapał wyjątku, tabela została pusta, a sqlcmd -b zakończył skrypt kodem 0. Dla aplikacji brak wyjątku oznacza sukces, więc użytkownik nie dowie się, że zapis nie powstał. Wersja z THROW; kończy ten sam skrypt kodem 1 i komunikatem, który wskazuje numer błędu oraz procedurę wraz z linią.

Blok CATCH, który wycofuje transakcję i nie zgłasza błędu, zamienia awarię w pozorny sukces.

Przełącznik -b programu sqlcmd odróżnia obie wersje w teście, a przełącznik -I opisuje artykuł o QUOTED_IDENTIFIER OFF w procedurze. Przykład sprawdziliśmy na SQL Server 2022 Express w jednorazowej bazie, przy poziomach zgodności od 130 do 160. Składnia nie używa elementów nowszych niż SQL Server 2016 SP1.

XACT_ABORT i flaga własnej transakcji porządkują początek procedury

Procedura zapisuje wiersz do rejestru i wiersz do historii, a tabela błędów zbiera wpisy z bloku CATCH. Kolumna adresu e-mail jest krótsza niż parametr procedury, żeby dało się wymusić błąd obcięcia.

CREATE TABLE dbo.rejestr (id int IDENTITY PRIMARY KEY, email varchar(30) NOT NULL, opis varchar(50) NOT NULL);
CREATE TABLE dbo.rejestr_historia (id int IDENTITY PRIMARY KEY, id_rejestr int NOT NULL, zdarzenie varchar(10) NOT NULL);
CREATE TABLE dbo.log_bledow (id int IDENTITY PRIMARY KEY, kiedy datetime2 NOT NULL DEFAULT SYSDATETIME(),
    procedura sysname NULL, numer int NULL, linia int NULL, komunikat nvarchar(2048) NULL);
GO

Przykład ilustracyjny, dane wymyślone. Trzy tabele pomocnicze z przykładu.

Początek procedury robi dwie rzeczy. SET XACT_ABORT ON sprawia, że błąd wykonania w bloku TRY ustawia XACT_STATE() na -1, czyli transakcja jest nieodwołalna. Zmienna @wlasna przyjmuje 1 tylko wtedy, gdy @@TRANCOUNT w chwili wejścia wynosi 0. Procedura otwiera wtedy własną transakcję i tylko w tym przypadku ją zatwierdza albo wycofuje.

CREATE PROCEDURE dbo.rejestr_zapisz
    @email     varchar(100),
    @opis      varchar(50),
    @zdarzenie varchar(30) = 'przyjecie'
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @wlasna bit = CASE WHEN @@TRANCOUNT = 0 THEN 1 ELSE 0 END;
    BEGIN TRY
        IF @wlasna = 1 BEGIN TRANSACTION;
        INSERT dbo.rejestr (email, opis) VALUES (@email, @opis);
        DECLARE @id int = SCOPE_IDENTITY();
        INSERT dbo.rejestr_historia (id_rejestr, zdarzenie) VALUES (@id, @zdarzenie);
        IF @wlasna = 1 COMMIT TRANSACTION;
        SELECT @id AS id;
    END TRY
    BEGIN CATCH
        IF @wlasna = 1 AND XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        IF XACT_STATE() <> -1
            INSERT dbo.log_bledow (procedura, numer, linia, komunikat)
            VALUES (ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_LINE(), ERROR_MESSAGE());
        THROW;
    END CATCH
END
GO

Przykład ilustracyjny, dane wymyślone. Procedura zapisu z flagą własnej transakcji i THROW w bloku CATCH.

Identyfikator pierwszego wiersza procedura odczytuje funkcją SCOPE_IDENTITY i przekazuje do zapisu w historii. Po zatwierdzeniu zwraca go zapytaniem SELECT @id AS id.

Blok CATCH wycofuje transakcję, a potem zgłasza błąd dalej

W bloku CATCH pierwsza instrukcja wycofuje własną transakcję, gdy @wlasna wynosi 1, a stan transakcji jest różny od zera. Ostatnia instrukcja to THROW bez parametrów. Według dokumentacji działa on tylko w bloku CATCH, a instrukcja przed nim musi kończyć się średnikiem, więc w przykładzie wszystkie poprzednie instrukcje mają średnik.

Wywołujący dostaje oryginalny błąd z tą samą procedurą i tą samą linią. Błąd obcięcia danych ma numer 2628 w bazie z poziomem zgodności 150 lub wyższym (SQL Server 2019 i nowszy). W bazie z poziomem zgodności 130 ten sam błąd ma numer 8152, także na SQL Server 2022. Wersji starszych niż 2019 nie sprawdzaliśmy. Poziom zgodności ustawia się instrukcją ALTER DATABASE. Po błędzie @@TRANCOUNT wywołującego wynosi 0.

Błąd w drugim INSERT cofa także pierwszy. W osobnym teście liczba wierszy w tabeli głównej po takim wywołaniu była taka sama jak przed nim. Samo THROW niczego jednak nie cofa. Transakcję wycofuje ROLLBACK, a przy XACT_ABORT ON jest ona cofnięta albo nieodwołalna już z powodu samego błędu.

Składnię opisują strony dokumentacji o TRY...CATCH i o ROLLBACK TRANSACTION. Procedura API, która zamiast THROW zwraca błąd jako wynik w formacie JSON, wygląda inaczej: tak robi procedura dopisująca kartotekę, a błąd jako zwykły wynik opisuje także artykuł o API na procedurach SQL i JSON.

Wpis do tabeli błędów musi powstać po ROLLBACK

Wpis do tabeli błędów wykonany przed ROLLBACK znika razem z transakcją. W teście bez XACT_ABORT nie przybył żaden wpis, bo zapis został cofnięty razem z resztą. Przy XACT_ABORT ON taki zapis kończy się błędem 3930 i przesłania oryginalny błąd. Kolejność w CATCH jest więc stała: najpierw ROLLBACK, potem zapis do logu, na końcu THROW.

Drugi warunek dotyczy stanu transakcji. Przy XACT_ABORT ON błąd w bloku TRY ustawia XACT_STATE() na -1. W tym stanie zapis do logu kończy się błędem 3930 i przesłoniłby oryginalny błąd, dlatego procedura zapisuje do logu tylko wtedy, gdy stan jest różny od -1.

Komunikat błędu obcięcia na SQL Server 2022 zawiera nazwę tabeli i kolumny oraz początek wartości (w teście 30 znaków adresu e-mail). Zapis ERROR_MESSAGE() do logu zapisuje więc także fragment danych użytkownika. Przy poziomie zgodności 130 komunikat błędu 8152 nie zawiera ani nazwy tabeli i kolumny, ani wartości. Kto czyta tabelę błędów, powinien o tym wiedzieć przed ustaleniem, co wolno do niej zapisywać.

IDENTITY nie cofa się przy ROLLBACK. Po nieudanych zapisach kolejny udany zapis dostaje numer wyższy niż liczba wierszy w tabeli (w teście numer 6 przy jednym wierszu po czterech nieudanych próbach).

Wywołanie z cudzej transakcji nie może jej cofać za plecami wywołującego

Wersja naiwna, która zawsze wykonuje BEGIN TRANSACTION i ROLLBACK, wywołana z cudzej transakcji cofa transakcję wywołującego. Licznik @@TRANCOUNT spada do 0, a późniejszy COMMIT wywołującego kończy się błędem 3902. Wersja bez THROW kończy się błędem 266, czyli niezgodną liczbą instrukcji BEGIN i COMMIT.

Wersja z flagą zachowuje się inaczej. Nie wykonuje ROLLBACK, bo transakcja nie jest jej własna, a wywołujący w swoim CATCH widzi XACT_STATE() = -1 i sam wycofuje transakcję. Cały zapis, także pierwszego udanego wywołania, zostaje cofnięty. Przy XACT_ABORT ON wywołujący musi więc wycofać transakcję, a dane z niej przepadają.

Zapisu do logu w stanie -1 nie wymuszamy. Procedura go pomija, a wywołujący może zapisać błąd po własnym ROLLBACK. Ostrożność wobec transakcji w skrypcie wdrożeniowym pokazuje artykuł o zmianie zapytań z trybem próbnym i ROLLBACK.

Test z za długą wartością pokazuje różnicę po stronie wywołującego

Test wywołuje procedurę dwa razy. Pierwsze wywołanie ma adres e-mail mieszczący się w kolumnie. Drugie ma adres dłuższy niż 30 znaków, więc zapis kończy się błędem obcięcia, który blok CATCH w teście przechwytuje po stronie wywołującego. Na końcu zapytanie kontrolne liczy wiersze w trzech tabelach i pokazuje @@TRANCOUNT.

EXEC dbo.rejestr_zapisz @email = 'klient.testowy@example.test', @opis = 'przyklad';
BEGIN TRY
    EXEC dbo.rejestr_zapisz @email = 'bardzo.dlugi.adres.klienta.testowego@example.test', @opis = 'x';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS numer, ERROR_PROCEDURE() AS procedura, ERROR_LINE() AS linia;
END CATCH
SELECT (SELECT COUNT(*) FROM dbo.rejestr) AS rejestr,
       (SELECT COUNT(*) FROM dbo.rejestr_historia) AS historia,
       (SELECT COUNT(*) FROM dbo.log_bledow) AS log_bledow,
       @@TRANCOUNT AS trancount;

Przykład ilustracyjny, dane wymyślone. Wywołanie testowe z wymuszonym błędem i zapytanie kontrolne.

Na SQL Server 2019 lub nowszym, w bazie z poziomem zgodności 150 lub wyższym, pierwsze wywołanie zwraca id = 1. Blok CATCH zwraca 2628 | dbo.rejestr_zapisz | 12, a linia 12 to pierwszy INSERT w procedurze. Zapytanie kontrolne zwraca 1 | 1 | 1 | 0, czyli po jednym wierszu w każdej z trzech tabel i brak otwartej transakcji. Przy poziomie zgodności 130 numer błędu to 8152.

Numery błędu dotyczą bazy z poziomem zgodności 150 lub wyższym. Przy poziomie 130 w miejscu 2628 stoi 8152.

Przykład: formularz zgłoszeń woła procedurę zapisu dla klienta Klient Testowy i numeru RMA 7C3A-1E94-B20F, ale adres e-mail jest dłuższy niż kolumna w tabeli. W wersji, w której CATCH robi tylko ROLLBACK, formularz pokazuje komunikat o zapisie, choć tabela jest pusta, a zgłoszenie 2026-10-0007 nigdy nie powstaje. Po dodaniu THROW; formularz dostaje błąd 2628 (przy poziomie zgodności 150 lub wyższym) i może poprosić o poprawienie adresu.

W tabeli błędów zostaje wpis, który wskazuje błąd i miejsce jego wystąpienia, więc programista widzi przyczynę bez przeszukiwania logów aplikacji. Gdy procedura zostanie później wywołana z większej transakcji innej procedury, cały zapis cofa się razem z nią, a wywołujący sam decyduje, co dalej. Od strony użytkownika kolejkę zgłoszeń z Internetu i pierwszy zapis pracownika opisuje strona o zgłoszeniu z Internetu, które nie jest jeszcze dokumentem.

Wzorzec nie był testowany pod kątem zakleszczeń, ponowień prób ani transakcji rozproszonych. Pokrewne wzorce to podstawianie pól w szablonie wiadomości, gdzie błędy tokenu zgłasza THROW, oraz wąska lista z zapasowym zbiorem.

Cztery pytania przed budową własnej procedury zapisu

Przed napisaniem własnej procedury zapisu odpowiedz na cztery pytania.

• Aplikacja: czy wywołująca procedurę aplikacja sprawdza błąd, czy zakłada, że brak wyjątku oznacza sukces.
• Transakcja wywołującego: czy procedura bywa wywoływana z transakcji innej procedury lub aplikacji.
• Zawartość logu: co wolno zapisywać do tabeli błędów, skoro komunikat może zawierać fragment danych użytkownika.
• Czytelnik logu: kto czyta tabelę błędów i po jakim czasie dowiaduje się o nowym wpisie.