Jak wycofać, gdy 3 procedury przechowywane są uruchamiane z jednej procedury przechowywanej

Mam procedurę składowaną, która wykonuje w niej tylko 3 procedury składowane. Używam tylko 1 parametru do przechowywania, jeśli główny SP jest udany.

Jeśli pierwsza procedura przechowywana działa poprawnie w głównej procedurze przechowywanej, ale druga procedura przechowywana nie powiedzie się, to czy automatycznie przywróci wszystkie SP w głównej SP, czy muszę wykonać jakieś polecenie?

Oto moja procedura:

CREATE PROCEDURE [dbo].[spSavesomename] 
    -- Add the parameters for the stored procedure here

    @successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
    begin Try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   BEGIN 

   EXEC [dbo].[spNewBilling1]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling2]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling3]

   END 

   set @successful  = 1

   end Try

    begin Catch
        rollback transaction createSavesomename
        insert into dbo.tblErrorMessage(spName, errorMessage, systemDate) 
             values ('spSavesomename', ERROR_MESSAGE(), getdate())

        return
    end Catch
commit transaction createSavesomename
return
END

GO

Biorąc pod uwagę tylko kod pokazany w pytaniu, a przy założeniu, że żaden z trzech sub-proca nie lada obsługa transakcja jawne, to tak, błąd w którymkolwiek z trzech sub-proca zostanie złapany, a ROLLBACKw CATCHbloku przywróci wszystko z pracy.

ALE oto kilka rzeczy na temat transakcji (przynajmniej w SQL Server):

• Jest tylko jedna prawdziwa transakcja (pierwsza), bez względu na to, ile razy dzwoniszBEGIN TRAN

  • Można wymienić transakcji (jak to zrobić tutaj) i że nazwa pojawi się w dziennikach, ale nazewnictwa tylko ma znaczenie dla pierwszego / zewnętrznej skrajnej transakcji (bo znowu pierwsza jest transakcji).
  • Za każdym razem, gdy dzwonisz BEGIN TRAN, niezależnie od tego, czy jest nazwany, licznik transakcji jest zwiększany o 1.
  • Możesz zobaczyć aktualny poziom wykonując SELECT @@TRANCOUNT;
  • Wszelkie COMMITpolecenia wydane, gdy @@TRANCOUNTjest na poziomie 2 lub wyższym, nie robią nic więcej niż zmniejszają, pojedynczo, licznik transakcji.
  • Nic nie jest nigdy popełnione, dopóki nie COMMITzostanie wydane, gdy @@TRANCOUNTjest w1
  • Na wypadek gdyby powyższe informacje nie wskazywały jednoznacznie: niezależnie od poziomu transakcji, faktyczne zagnieżdżenie transakcji nie występuje.

• Punkty zapisu pozwalają na utworzenie podzbioru pracy w ramach transakcji, który można cofnąć.

  • Punkty zapisu są tworzone / zaznaczane za pomocą SAVE TRAN {save_point_name}polecenia
  • Punkty zapisu oznaczają początek podzbioru pracy, który można cofnąć bez cofania całej transakcji.
  • Nazwy punktów zapisu nie muszą być unikalne, ale używanie tej samej nazwy więcej niż jeden raz tworzy odrębne punkty zapisu.
  • Punkty zapisu można zagnieżdżać.
  • Punkty zapisu nie mogą zostać zatwierdzone.
  • Zapisane punkty można cofnąć za pomocą ROLLBACK {save_point_name}. (więcej na ten temat poniżej)
  • Cofnięcie punktu zapisu spowoduje cofnięcie wszelkich prac, które miały miejsce po ostatnim wywołaniu SAVE TRAN {save_point_name}, w tym wszystkich punktów zapisu utworzonych po utworzeniu wycofanego punktu (stąd „zagnieżdżanie”).
  • Cofnięcie punktu zapisu nie ma wpływu na liczbę / poziom transakcji
  • Wszelkie prace wykonane przed rozpoczęciem SAVE TRANnie mogą zostać cofnięte, chyba że wydane zostaną pełne ROLLBACKtransakcje.
  • Żeby było jasne: wydanie COMMITkiedy @@TRANCOUNTjest na poziomie 2 lub wyższym, nie ma wpływu na punkty zapisu (ponieważ ponownie poziomy transakcji powyżej 1 nie istnieją poza tym licznikiem).

• Nie można zatwierdzać określonych nazwanych transakcji. „Nazwa” transakcji, jeśli jest podana wraz z COMMIT, jest ignorowana i istnieje tylko dla czytelności.

• ROLLBACKWydawane bez nazwy zawsze będzie wycofać wszystkie transakcje.

• ROLLBACKWydane z nazwa musi odpowiadać albo:

  • Pierwsza transakcja, zakładając, że została nazwana:
    Zakładając, że nie SAVE TRANzostała wywołana żadna z tą samą nazwą transakcji, spowoduje to wycofanie WSZYSTKICH transakcji.
  • „Punkt zapisu” (opisany powyżej):
    To zachowanie spowoduje „cofnięcie” wszystkich zmian wprowadzonych od czasu wywołania ostatniego SAVE TRAN {save_point_name} .
  • Jeśli pierwsza transakcja była a) nazwana ib) SAVE TRANwydano komendy z jej nazwą, to każde ROLLBACK tej nazwy transakcji cofnie każdy punkt zapisu, dopóki nie pozostanie żadna z tej nazwy. Następnie wydany ROLLBACK o tej nazwie przywróci WSZYSTKIE transakcje.

  • Załóżmy na przykład, że następujące polecenia zostały uruchomione w pokazanej kolejności:

    BEGIN TRAN A -- @@TRANCOUNT is now 1
    -- DML Query 1
    SAVE TRAN A
    -- DML Query 2
    SAVE TRAN A
    -- DML Query 3
    
    BEGIN TRAN B -- @@TRANCOUNT is now 2
    SAVE TRAN B
    -- DML Query 4

    Teraz, jeśli wydajesz (każdy z poniższych scenariuszy jest od siebie niezależny):

    • ROLLBACK TRAN Bjeden raz: Cofa „DML Query 4”. @@TRANCOUNTjest nadal 2.
    • ROLLBACK TRAN Bdwa razy: Cofa „DML Query 4”, a następnie błąd, ponieważ nie ma odpowiedniego punktu zapisu dla „B”. @@TRANCOUNTjest nadal 2.
    • ROLLBACK TRAN Ajeden raz: Cofnie „DML Query 4” i „DML Query 3”. @@TRANCOUNTjest nadal 2.
    • ROLLBACK TRAN Adwa razy: Cofa „DML Query 4”, „DML Query 3” i „DML Query 2”. @@TRANCOUNTjest nadal 2.
    • ROLLBACK TRAN Atrzy razy: Cofnie „DML Query 4”, „DML Query 3” i „DML Query 2”. Następnie cofnie całą transakcję (pozostało tylko „DML Query 1”). @@TRANCOUNTjest teraz 0.
    • COMMITjeden raz: @@TRANCOUNTspada do 1.
    • COMMITraz, a potem ROLLBACK TRAN Braz: @@TRANCOUNTspada do 1. Następnie cofnie „DML Query 4” (udowadniając, że COMMIT nic nie zrobił). @@TRANCOUNTjest nadal 1.

• Nazwy transakcji i nazwy punktów zapisu:

  • może mieć do 32 znaków
  • są traktowane jako binarne sortowanie (bez rozróżniania wielkości liter, jak obecnie dokumentuje), niezależnie od sortowania na poziomie instancji lub bazy danych.
  • Aby uzyskać szczegółowe informacje, zobacz sekcję Nazwy transakcji w następującym poście: Co jest w nazwie ?: Wewnątrz zwariowanego świata identyfikatorów T-SQL

• Procedura przechowywana sama w sobie nie jest transakcją niejawną. Każde zapytanie, jeśli żadna jawna transakcja nie została rozpoczęta, jest transakcją niejawną. Z tego powodu jawne transakcje wokół pojedynczych zapytań nie są konieczne, chyba że istnieje powód programowy ROLLBACK, w przeciwnym razie każdy błąd w zapytaniu jest automatycznym wycofywaniem tego zapytania.

• Podczas wywoływania procedury składowanej musi wyjść z wartością @@TRANCOUNTbycia taką samą, jak w momencie jej wywołania. Oznacza to, że nie możesz:

  • Rozpocznij procedurę BEGIN TRANw proc, nie popełniając jej, oczekując zatwierdzenia w procesie wywołującym / nadrzędnym.
  • Nie można wystawić, ROLLBACKjeśli jawna transakcja została uruchomiona przed wywołaniem proc, ponieważ powróci ona @@TRANCOUNTdo zera.

  Jeśli wyjdziesz z procedury składowanej z liczbą transakcji, która jest albo wyższa, albo niższa niż wtedy, gdy się zapatrzyła, pojawi się błąd podobny do:

  Msg 266, poziom 16, stan 2, procedura YourProcName, wiersz 0
  Liczba transakcji po EXECUTE wskazuje na niezgodną liczbę instrukcji BEGIN i COMMIT. Poprzednia liczba = X, bieżąca liczba = Y.

• Zmienne tabelowe, podobnie jak zmienne zwykłe, nie są powiązane transakcjami.

Odnośnie obsługi transakcji w procesach, które mogą być wywoływane niezależnie (a zatem wymagają obsługi transakcji) lub połączenia z innych procedur (stąd nie wymagają obsługi transakcji): można to zrobić na kilka różnych sposobów.

Sposób, w jaki sobie z tym radzę od kilku lat, który wydaje się działać dobrze, to tylko BEGIN/ COMMIT/ ROLLBACKw najbardziej zewnętrznej warstwie. Wywołania sub-proc po prostu pomijają polecenia transakcji. Poniżej nakreśliłem, co wkładam w każdy proces (cóż, każdy, który wymaga obsługi transakcji).

• U góry każdego proca DECLARE @InNestedTransaction BIT;

• Zamiast prostego BEGIN TRANwykonaj:

  IF (@@TRANCOUNT = 0)
  BEGIN
     SET @InNestedTransaction = 0;
     BEGIN TRAN; -- only start a transaction if not already in one
  END;
  ELSE
  BEGIN
     SET @InNestedTransaction = 1;
  END;

• Zamiast prostego COMMITwykonaj:

  IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
  BEGIN
     COMMIT;
  END;

• Zamiast prostego ROLLBACKwykonaj:

  IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
  BEGIN
     ROLLBACK;
  END;

Ta metoda powinna działać tak samo, niezależnie od tego, czy transakcja została uruchomiona w programie SQL Server, czy w warstwie aplikacji.

Pełny szablon obsługi transakcji w ramach TRY...CATCHkonstruktu znajduje się w mojej odpowiedzi na następujące pytanie DBA.SE: Czy jesteśmy zobowiązani do obsługi transakcji w kodzie C #, jak również w procedurze przechowywanej .

Wychodząc poza „podstawy”, istnieją pewne dodatkowe niuanse transakcji, o których należy pamiętać:

• Domyślnie transakcje przez większość czasu nie są automatycznie wycofywane / anulowane po wystąpieniu błędu. Zwykle nie stanowi to problemu, o ile masz odpowiednią obsługę błędów i zadzwonisz do ROLLBACKsiebie. Czasami jednak sprawy się komplikują, na przykład w przypadku błędów przerywających wsad lub podczas korzystania OPENQUERY(lub ogólnie z połączonych serwerów) i błąd występuje w systemie zdalnym. Chociaż większość błędów można złapać w pułapkę za pomocą TRY...CATCH, są dwa, których nie można złapać w ten sposób (chociaż nie pamiętam, które z nich w tej chwili - badanie). W takich przypadkach musisz użyć, SET XACT_ABORT ONaby poprawnie wycofać transakcję.

  Ustawienie XACT_ABORT ON powoduje, że SQL Server natychmiast wycofuje dowolną transakcję (jeśli jest aktywna) i przerywa partię, jeśli wystąpi jakikolwiek błąd. To ustawienie istniało przed SQL Server 2005, który wprowadził TRY...CATCHkonstrukcję. W przeważającej części TRY...CATCHobsługuje większość sytuacji, a więc w większości przypadków przestaje istnieć taka potrzeba XACT_ABORT ON. Jednak podczas korzystania OPENQUERY(i prawdopodobnie jednego innego scenariusza, którego obecnie nie pamiętam), nadal będziesz musiał go użyć SET XACT_ABORT ON;.

• Wewnątrz wyzwalacza XACT_ABORTjest domyślnie ustawiony na ON. Powoduje to, że każdy błąd w Triggerze anuluje całą instrukcję DML, która uruchomiła Trigger.

• Zawsze powinieneś mieć odpowiednią obsługę błędów, szczególnie podczas korzystania z Transakcji. TRY...CATCHKonstrukt, wprowadzony w SQL Server 2005, zapewnia środki do obsługi niemal wszystkich sytuacjach poprawę powitalny nad testowania dla @@ERRORpo każdej instrukcji, co nie pomogło z błędami wsadowych-przerywanie.

  TRY...CATCHwprowadził jednak nowy „stan”. Jeśli nie używasz TRY...CATCHkonstrukcji, jeśli masz aktywną transakcję i wystąpi błąd, istnieje kilka ścieżek, które można podjąć:

  • XACT_ABORT OFFi błąd przerywania instrukcji: Transakcja jest nadal aktywna i przetwarzanie jest kontynuowane z następną instrukcją , jeśli taka istnieje.
  • XACT_ABORT OFFi większość błędów przerywania partii: Transakcja jest nadal aktywna i przetwarzanie jest kontynuowane z następną partią , jeśli taka istnieje.
  • XACT_ABORT OFForaz niektóre błędy przerywania partii: Transakcja jest wycofywana, a przetwarzanie jest kontynuowane z następną partią , jeśli taka istnieje.
  • XACT_ABORT ONi każdy błąd: transakcja jest wycofywana, a przetwarzanie jest kontynuowane z następną partią , jeśli taka istnieje.

  
  JEDNAK podczas używania TRY...CATCHbłędy przerywania partii nie przerywają partii, lecz przekazują kontrolę do CATCHbloku. Kiedy XACT_ABORTto nastąpi OFF, transakcja będzie nadal aktywna przez większość czasu i będziesz musiał to zrobić COMMIT, lub najprawdopodobniej ROLLBACK. Ale w przypadku napotkania pewnych błędów przerywania partii (takich jak z OPENQUERY) lub kiedy XACT_ABORTjest ON, transakcja będzie w nowym stanie, „niekomfortowym”. W tym stanie nie możeszCOMMIT ani nie możesz wykonywać żadnych operacji DML. Wszystko, co można zrobić, to ROLLBACKi SELECToświadczenia. Jednak w tym „nieprzyzwoitym” stanie transakcja została wycofana po wystąpieniu błędu, a wydanie ROLLBACKjest tylko formalnością, ale należy ją wykonać.

  Funkcja XACT_STATE może być użyta do ustalenia, czy transakcja jest aktywna, niekomfortowa lub nie istnieje. Zaleca się (przynajmniej przez niektórych), aby sprawdzić tę funkcję w CATCHbloku, aby ustalić, czy wynik jest -1(tj. Niekomfortowy) zamiast testować, czy @@TRANCOUNT > 0. Ale z XACT_ABORT ON, powinien to być jedyny możliwy stan, więc wydaje się, że testowanie @@TRANCOUNT > 0i XACT_STATE() <> 0są równoważne. Z drugiej strony, kiedy XACT_ABORTjest OFFi jest aktywna Transakcja, wówczas możliwe jest posiadanie stanu jednego z nich 1lub jeśli Transakcja jest możliwa do zatwierdzenia). Więcej informacji i badań na temat używania w bloku z można znaleźć w mojej odpowiedzi na następujące pytanie DBA.SE:-1 w CATCHbloku, co pozwala na możliwość wydania COMMITzamiast ROLLBACK(chociaż nie mogę wymyślić przypadku, w którym ktoś chciałbymCOMMITXACT_STATE()CATCHXACT_ABORT ONW jakich przypadkach transakcja może zostać zatwierdzona z bloku CATCH, gdy XACT_ABORT jest ustawiony na ON? . Należy pamiętać, że istnieje niewielki błąd, XACT_STATE()który powoduje, że 1w niektórych scenariuszach zwraca on błąd : XACT_STATE () zwraca 1, gdy jest używany w SELECT z niektórymi zmiennymi systemowymi, ale bez klauzuli FROM

Uwagi na temat oryginalnego kodu:

• Możesz usunąć nazwę nadaną transakcji, ponieważ to nie pomaga.
• Nie potrzebujesz BEGINi ENDwokół każdego EXECpołączenia

Tak, jeśli z powodu jakiegokolwiek błędu przywracania kodu w instrukcji catch głównej procedury przechowywanej zostanie wykonane, spowoduje to wycofanie wszystkich operacji wykonanych przez dowolną instrukcję bezpośrednią lub dowolną z zagnieżdżonych w niej procedur przechowywanych.

Nawet jeśli nie zastosowałeś żadnej jawnej transakcji w zagnieżdżonych procedurach przechowywanych, nadal ta procedura przechowywana będzie korzystać z transakcji niejawnych i zostanie zatwierdzona po zakończeniu, ALE albo dokonałeś transakcji jawnej lub niejawnej w zagnieżdżonych procedurach przechowywanych Silnik SQL Server zignoruje to i będzie wycofaj wszystkie działania tych zagnieżdżonych procedur przechowywanych, jeśli główna procedura przechowywana nie powiedzie się, a transakcja zostanie wycofana.

Za każdym razem, gdy transakcja jest zatwierdzana lub wycofywana w oparciu o działania podjęte na końcu najbardziej zewnętrznej transakcji. Jeśli transakcja zewnętrzna zostanie zatwierdzona, transakcje wewnętrzne zagnieżdżone również zostaną zatwierdzone. Jeśli transakcja zewnętrzna zostanie wycofana, wszystkie transakcje wewnętrzne również zostaną wycofane, niezależnie od tego, czy transakcje wewnętrzne zostały indywidualnie zatwierdzone.

W celach informacyjnych http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx