Zmienna tabelowa jako bufor błędów przeżywa ROLLBACK, a trzy błędy w CATCH maskują oryginał

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

Zmienna tabelowa przeżywa ROLLBACK, więc procedura zapisu może zbierać w niej opis przebiegu transakcji i dopisać go do dziennika dopiero po wycofaniu. Wiersze tabeli tymczasowej i tabeli trwałej, wstawione w tej samej transakcji, takiego wycofania nie przetrwają.

W teście zmienna tabelowa miała po ROLLBACK nadal jeden wiersz, a tabela tymczasowa zero. Bufor działa także przy SET XACT_ABORT ON, gdy zapis do dziennika przed ROLLBACK kończy się błędem 3930. Trzy błędy w samym bloku CATCH, czyli 3930, 3903 i 6401, przesłaniają oryginalny błąd 8134, a wołający widzi numer błędu z obsługi.

To wzorzec z nazwami przykładowymi. Przykład uruchomiliśmy na SQL Server 2022 Express w jednorazowej bazie przy poziomach zgodności 130 i 160 i dał identyczne wyniki. Instrukcja THROW jest dostępna od SQL Server 2012 (według dokumentacji). Sprawdziliśmy wyłącznie wywołanie procedury z własnym blokiem TRY...CATCH po stronie wołającego.

Zapis do dziennika po ROLLBACK jako punkt wyjścia

Przykład ma dwie tabele: Dane z jedną kolumną wartości oraz Dziennik z trzema kolumnami opisu: procedura, numer błędu i komunikat. Procedury wstawiają w transakcji wiersz do Dane, a potem dzielą przez zero przez zmienną, co daje błąd 8134. W dwóch procedurach z dalszych sekcji dzielenie stoi przed BEGIN TRANSACTION.

CREATE TABLE dbo.Dane (Id int IDENTITY(1,1) PRIMARY KEY, Wartosc int NOT NULL);
CREATE TABLE dbo.Dziennik (
    Id        int IDENTITY(1,1) PRIMARY KEY,
    Czas      datetime2(0)   NOT NULL DEFAULT SYSDATETIME(),
    Procedura sysname        NOT NULL,
    Numer     int            NULL,
    Komunikat nvarchar(2048) NULL
);

Podstawy opisuje artykuł o TRY CATCH i THROW po ROLLBACK: blok CATCH, który zawiera tylko ROLLBACK i nie ma THROW, nie zgłasza błędu. W teście (ZapisSamRollback) wołający nie dostał błędu, w Dane było 0 wierszy, a @@TRANCOUNT wynosił 0, więc dla aplikacji awaria wyglądała jak sukces.

Punktem odniesienia jest zapis do dziennika po wycofaniu transakcji. Procedura ZapisProsty robi najpierw IF XACT_STATE() <> 0 ROLLBACK TRANSACTION, potem INSERT do Dziennik, a na końcu THROW;. Wołający dostał błąd 8134, w Dziennik jest jeden wpis z numerem 8134 i komunikatem „Divide by zero error encountered.”, a w Dane zero wierszy.

CREATE PROCEDURE dbo.ZapisProsty
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @zero int = 0;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.Dane (Wartosc) VALUES (1);
        SELECT 1 / @zero AS Wynik;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE());
        THROW;
    END CATCH;
END;

Funkcje ERROR_NUMBER() i ERROR_MESSAGE() zwracają po ROLLBACK te same wartości co przed nim, więc ZapisProsty mogła użyć ich wprost w INSERT po wycofaniu. THROW; bez parametrów rzuca dalej oryginalny błąd 8134. Wersja z INSERT przed ROLLBACK, czyli ZapisZly, dała u wołającego ten sam błąd, ale w dzienniku zero wpisów.

CREATE PROCEDURE dbo.ZapisZly
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @zero int = 0;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.Dane (Wartosc) VALUES (1);
        SELECT 1 / @zero AS Wynik;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE());
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

BEGIN TRY EXEC dbo.ZapisZly; END TRY
BEGIN CATCH SELECT ERROR_NUMBER() AS BladOdWolajacego, ERROR_MESSAGE() AS Komunikat; END CATCH;
SELECT (SELECT COUNT(*) FROM dbo.Dziennik WHERE Procedura = N'ZapisZly') AS WpisyDziennika,
       (SELECT COUNT(*) FROM dbo.Dane) AS WierszeDane, @@TRANCOUNT AS Trancount;

Oczekiwany wynik wywołania: błąd dociera do wołającego, ale dziennik jest pusty.

Zmienna tabelowa zachowuje wpisy po ROLLBACK, a tabela tymczasowa je traci

Procedura ZapisZBuforem wpisuje w transakcji po jednym wierszu do zmiennej tabelowej @kroki i do tabeli tymczasowej #kroki. Blok CATCH liczy wiersze obu przed ROLLBACK i po nim, a potem zapisuje do Dziennik wiersze z bufora i błąd 8134 z komunikatem, a na końcu wiersz podsumowania z licznikami.

CREATE PROCEDURE dbo.ZapisZBuforem
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @zero int = 0;
    DECLARE @kroki TABLE (Nr int IDENTITY(1,1), Opis nvarchar(200));
    CREATE TABLE #kroki (Nr int IDENTITY(1,1), Opis nvarchar(200));
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.Dane (Wartosc) VALUES (1);
        INSERT @kroki (Opis) VALUES (N'krok 1: wstawiono wartosc');
        INSERT #kroki (Opis) VALUES (N'krok 1: wstawiono wartosc');
        SELECT 1 / @zero AS Wynik;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @numer int = ERROR_NUMBER(), @komunikat nvarchar(2048) = ERROR_MESSAGE(),
                @wZmiennej int = (SELECT COUNT(*) FROM @kroki), @wTymczasowej int = (SELECT COUNT(*) FROM #kroki);
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        SELECT OBJECT_NAME(@@PROCID), NULL, Opis FROM @kroki;
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        VALUES (OBJECT_NAME(@@PROCID), @numer, @komunikat);
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        VALUES (OBJECT_NAME(@@PROCID), NULL,
                CONCAT(N'przed ROLLBACK zmienna=', @wZmiennej, N' tymczasowa=', @wTymczasowej,
                       N'; po ROLLBACK zmienna=', (SELECT COUNT(*) FROM @kroki), N' tymczasowa=', (SELECT COUNT(*) FROM #kroki)));
        THROW;
    END CATCH;
END;

Przed ROLLBACK w obu było po jednym wierszu. Po ROLLBACK zmienna miała nadal 1 wiersz, a tabela tymczasowa 0. Do Dziennik trafiły razem 3 wpisy, z czego 1 z numerem błędu, a wiersz w Dane został cofnięty (0 wierszy, @@TRANCOUNT równe 0). Bufor chroni więc wyłącznie własną zawartość.

Wynik zapytania do dziennika po jednym wywołaniu procedury.

Zmienna tabelowa zachowuje zawartość po ROLLBACK, więc opis przebiegu transakcji zbieraj w niej i zapisuj do dziennika dopiero po wycofaniu.

Ta różnica dotyczy wierszy wstawionych w transakcji. Tabela tymczasowa wypełniona przed jej rozpoczęciem to inna sytuacja, której używa na przykład artykuł o zmianie zapytań w trybie próbnym z ROLLBACK. Samą zmienną opisuje dokumentacja typu table, a zmienną tabelową w klauzuli OUTPUT INTO pokazuje artykuł o znaczniku wysłano w jednym UPDATE.

Bufor działa także przy XACT_ABORT ON, gdy zapis do dziennika kończy się błędem 3930

Przy SET XACT_ABORT ON błąd w bloku TRY ustawia XACT_STATE() na -1. Zły wzorzec z INSERT przed ROLLBACK (procedura ZapisZlyXact to ZapisZly z dopisanym SET XACT_ABORT ON;) kończy się wtedy błędem 3930, który przesłania błąd 8134. Wpisu nie ma, a transakcja zostaje otwarta: po powrocie @@TRANCOUNT wynosił 1, XACT_STATE() wołającego -1, a w Dane widać wiersz do czasu wycofania.

W procedurze ZapisBuforXact ten sam moment wygląda inaczej. INSERT do zmiennej tabelowej w bloku CATCH przed ROLLBACK kończy się powodzeniem, choć zapis do Dziennik w tym samym miejscu daje błąd 3930. Po ROLLBACK procedura przepisuje bufor do dziennika, razem trzy wpisy: wiersz kroku, wiersz z wartością stanu transakcji i błąd 8134.

CREATE PROCEDURE dbo.ZapisBuforXact
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @zero int = 0;
    DECLARE @kroki TABLE (Nr int IDENTITY(1,1), Opis nvarchar(200));
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.Dane (Wartosc) VALUES (1);
        INSERT @kroki (Opis) VALUES (N'krok 1: wstawiono wartosc');
        SELECT 1 / @zero AS Wynik;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @numer int = ERROR_NUMBER(), @komunikat nvarchar(2048) = ERROR_MESSAGE(), @stan int = XACT_STATE();
        INSERT @kroki (Opis) VALUES (CONCAT(N'CATCH: XACT_STATE = ', @stan));
        IF @stan <> 0 ROLLBACK TRANSACTION;
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        SELECT OBJECT_NAME(@@PROCID), NULL, Opis FROM @kroki;
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        VALUES (OBJECT_NAME(@@PROCID), @numer, @komunikat);
        THROW;
    END CATCH;
END;

Wynik jest taki sam na poziomach zgodności 130 i 160. Wołający dostał błąd 8134, w Dane było 0 wierszy, a @@TRANCOUNT wynosił 0.

Szczegóły błędu 3930, czyli zapisu do dziennika w stanie -1, omawia artykuł o procedurze zapisu z THROW po ROLLBACK, razem z wywołaniem z cudzej transakcji. Tutaj nie sprawdzaliśmy zachowania bufora w cudzej transakcji ani w innej sesji.

Błąd 3903 przesłania oryginał, gdy ROLLBACK stoi przed BEGIN TRANSACTION

W procedurze ZapisPrzedTransakcjaBezSprawdzenia dzielenie przez zero stoi przed BEGIN TRANSACTION, a blok CATCH zaczyna się od bezwarunkowego ROLLBACK. Transakcji jeszcze nie ma, więc ROLLBACK kończy się błędem 3903 (brak odpowiadającego BEGIN TRANSACTION), który przesłania błąd 8134. Wołający dostał 3903, a wpisu w dzienniku nie ma, bo INSERT stoi za ROLLBACK.

CREATE PROCEDURE dbo.ZapisPrzedTransakcjaBezSprawdzenia
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @zero int = 0;
    BEGIN TRY
        SELECT 1 / @zero AS Wynik;
        BEGIN TRANSACTION;
        INSERT dbo.Dane (Wartosc) VALUES (1);
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        INSERT dbo.Dziennik (Procedura, Numer, Komunikat)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE());
        THROW;
    END CATCH;
END;

Ta sama procedura z warunkiem IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; zamiast samego ROLLBACK (ZapisPrzedTransakcjaZeSprawdzeniem) zachowuje się poprawnie. Wołający dostał oryginalny błąd 8134, a w Dziennik powstał 1 wpis z numerem 8134. Funkcję opisuje dokumentacja XACT_STATE.

Błąd 6401 pojawia się, gdy przed THROW brakuje średnika

Według dokumentacji instrukcja THROW wymaga średnika w poprzedniej instrukcji. W procedurze ZapisBezSrednika polecenie ROLLBACK TRANSACTION bez średnika przed THROW; zostało odczytane jako wycofanie transakcji o nazwie THROW. Wynik to błąd 6401 (Cannot roll back THROW. No transaction or savepoint of that name was found.), a transakcja pozostała otwarta: @@TRANCOUNT wynosił 1, a w Dane widoczny był wiersz.

CREATE PROCEDURE dbo.ZapisBezSrednika
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @zero int = 0;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.Dane (Wartosc) VALUES (1);
        SELECT 1 / @zero AS Wynik;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        THROW;
    END CATCH;
END;

W trzech opisanych przypadkach wołający dostał numer błędu z samej obsługi, a nie oryginalny 8134: 3930 w jednym teście, 3903 w drugim i 6401 w trzecim. Przyczyna, czyli dzielenie przez zero, nie dociera do wołającego w żadnym z nich. Numery i komunikaty sprawdziliśmy na SQL Server 2022 przy poziomach zgodności 130 i 160 i nie twierdzimy, że są takie same w każdej wersji.

Zapytanie kontrolne pokazuje, która procedura zostawia wpis

Tabela zbiera wszystkie warianty. Kolumna z błędem u wołającego to numer, który dostała aplikacja, a ostatnia kolumna to wpisy w dzienniku po jednym wywołaniu procedury.

Przykład ilustracyjny. Wyniki z SQL Server 2022 przy poziomach zgodności 130 i 160.

Zapytanie kontrolne obejmuje siedem procedur (bez ZapisBezSrednika, która nie zapisuje do dziennika) i liczy w Dziennik wszystkie wpisy oraz wpisy z numerem błędu. Trzy procedury zostawiają zero wpisów: ZapisPrzedTransakcjaBezSprawdzenia, ZapisZly i ZapisZlyXact.

SELECT p.Nazwa AS Procedura, COUNT(d.Id) AS WpisyDziennika, COUNT(d.Numer) AS WpisyZNumeremBledu
FROM (VALUES (N'ZapisZly'), (N'ZapisProsty'), (N'ZapisZBuforem'), (N'ZapisZlyXact'), (N'ZapisBuforXact'),
             (N'ZapisPrzedTransakcjaBezSprawdzenia'), (N'ZapisPrzedTransakcjaZeSprawdzeniem')) AS p(Nazwa)
LEFT JOIN dbo.Dziennik d ON d.Procedura = p.Nazwa
GROUP BY p.Nazwa ORDER BY p.Nazwa;

Wiersze z zerem wpisów to warianty, w których błąd obsługi przesłonił oryginał albo zapis stał przed ROLLBACK.

Przykład: procedura zapisuje dokument 26-3001 w transakcji i po każdym kroku dopisuje wiersz do zmiennej tabelowej. Przy trzeciej pozycji pojawia się błąd. Blok CATCH sprawdza stan transakcji, wycofuje ją i dopiero wtedy przepisuje bufor do dziennika, więc administrator widzi, które kroki zdążyły się wykonać przed błędem. W innej procedurze blok CATCH ma bezwarunkowy ROLLBACK, a błąd wystąpił przed otwarciem transakcji, więc wołający dostaje błąd o braku BEGIN TRANSACTION zamiast oryginalnego.

Procedurę z blokadą i blokiem CATCH, który zwalnia zasób, pokazuje artykuł o idempotencji wysyłki w T-SQL, a kolejny szablon przykładu z danymi próbnymi artykuł o bilansie pozycji zamówienia. Błędy 2601 i 2627 rozpoznawane w CATCH omawia artykuł o indeksie filtrowanym przy miękkim usuwaniu, a wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.

Cztery pytania przed budową własnej obsługi błędów

Zanim przeniesiesz wzorzec do własnej bazy, odpowiedz sobie na cztery pytania.

• Dziennik: czy dziennik błędów zawiera tylko numer i komunikat, czy także opis tego, co transakcja zdążyła zrobić przed błędem.
• XACT_STATE: czy ROLLBACK w bloku CATCH jest poprzedzony sprawdzeniem XACT_STATE() także w procedurach, w których błąd może wystąpić przed BEGIN TRANSACTION.
• Średnik: czy instrukcja przed każdym THROW kończy się średnikiem.
• Numer błędu: jaki numer widzi aplikacja po awarii, oryginalny czy numer błędu z samego bloku CATCH.