Procédures stockées imbriquées contenant le modèle TRY CATCH ROLLBACK ?

Question

Je suis intéressé par les effets secondaires et les problèmes potentiels du schéma suivant :

CREATE PROCEDURE [Name]
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        [...Perform work, call nested procedures...]
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    END CATCH
END

D'après ce que j'ai compris, ce modèle est valable lorsqu'il est utilisé avec une seule procédure - la procédure achèvera toutes ses déclarations sans erreur, ou bien elle annulera toutes les actions et signalera l'erreur.

Cependant, lorsqu'une procédure stockée appelle une autre procédure stockée pour effectuer une sous-unité de travail (étant entendu que la plus petite procédure est parfois appelée seule), je vois apparaître un problème lié aux rollbacks - un message d'information (niveau 16) est émis, indiquant que The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. . Je suppose que c'est parce que le retour en arrière dans la sous-procédure revient toujours sur la transaction la plus externe, et pas seulement sur la transaction lancée dans la sous-procédure.

Je veux que l'ensemble de la transaction soit annulée et interrompue en cas d'erreur (et que l'erreur soit signalée au client comme une erreur SQL), mais je ne suis pas sûr de tous les effets secondaires qui découlent du fait que les couches externes essaient d'annuler une transaction qui a déjà été annulée. Peut-être qu'une vérification de @@TRANCOUNT avant de faire un retour en arrière à chaque couche TRY CATCH ?

Enfin, il y a le côté client (Linq2SQL), qui possède sa propre couche de transaction :

try
{
    var context = new MyDataContext();
    using (var transaction = new TransactionScope())
    {       
            // Some Linq stuff
        context.SubmitChanges();
        context.MyStoredProcedure();
        transactionComplete();
    }
}
catch
{
    // An error occured!
}

Dans le cas où une procédure stockée, "MySubProcedure", appelée à l'intérieur de MyStoredProcedure lève une erreur, puis-je être sûr que tout ce qui a été fait précédemment dans MyStoredProcedure sera annulé, que toutes les opérations Linq effectuées par SubmitChanges seront annulées, et enfin que l'erreur sera enregistrée ? Ou bien, que dois-je changer dans mon modèle pour garantir que l'ensemble de l'opération est atomique, tout en permettant aux parties enfants d'être utilisées individuellement (c'est-à-dire que les sous-procédures devraient toujours avoir la même protection atomique) ?

Answer 1

Voici notre modèle (suppression de l'enregistrement des erreurs)

Il est conçu pour gérer

• L'article de Paul Randal "Les transactions imbriquées n'existent pas dans SQL Server".
• Erreur 266
• Déclencher des retours en arrière

Explications :

• tous les débuts de TXN et les commit/rollbacks doivent être appariés de manière à ce que @@TRANCOUNT est le même à l'entrée et à la sortie

• les déséquilibres de @@TRANCOUNT provoquer l'erreur 266 car

  • BEGIN TRAN incréments @@TRANCOUNT

  • COMMIT diminue @@TRANCOUNT

  • ROLLBACK renvoie à @@TRANCOUNT à zéro

• Vous ne pouvez pas décrémenter @@TRANCOUNT pour la portée actuelle
  C'est ce que vous pensez être la "transaction interne".

• SET XACT_ABORT ON supprime l'erreur 266 causée par une erreur de correspondance. @@TRANCOUNT
  Et traite également de questions comme celle-ci "Délai de transaction du serveur SQL" sur dba.se

• Cela permet d'utiliser des TXN côté client (comme LINQ). Une seule procédure stockée peut faire partie d'une transaction distribuée ou XA, ou simplement d'une transaction initiée dans le code client (par exemple, TransactionScope de .net).

UTILISATION :

• Chaque proc stockée doit se conformer au même modèle

RÉSUMÉ

• Donc, ne créez pas plus de TXNs que nécessaire.

Cependant,

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Notes :

• La vérification du retour en arrière est en fait redondante car SET XACT_ABORT ON . Cependant, il me permet de me sentir mieux, il a l'air bizarre sans lui et il permet de faire face à des situations où vous ne voulez pas le porter.

• Remus Rusanu a un coquille similaire qui utilise des points de sauvegarde. Je préfère un appel atomique à la base de données et je n'utilise pas de mises à jour partielles comme dans leur article.

Answer 2

Je ne suis pas un spécialiste de Linq (et Erland non plus), mais il a écrit les bibles absolues sur la gestion des erreurs. En dehors des complications que Linq pourrait ajouter à votre problème, toutes vos autres questions devraient trouver une réponse ici :

http://www.sommarskog.se/error_handling_2005.html

Answer 3

Pour résoudre le problème du renvoi du numéro d'erreur et du numéro de ligne mentionné par @AlexKuznetsov, on peut soulever l'erreur comme tel :

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorLine INT
DECLARE @ErrorNumber INT

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)

Answer 4

-- La méthode @Amanda ci-dessus ne renvoie pas le bon numéro d'erreur.

DECLARE  
  @ErrorMessage   nvarchar(4000),  
  @ErrorSeverity   int,  
  @ErrorState int,  
  @ErrorLine  int,  
  @ErrorNumber   int  

BEGIN TRY  
 SELECT 1/0; -- CATCH me  
END TRY  

BEGIN CATCH  

  DECLARE @err int = @@ERROR  

  PRINT @err           -- 8134, divide by zero  
  PRINT ERROR_NUMBER() -- 8134  

  SELECT  
    @ErrorMessage  = ERROR_MESSAGE(),  
    @ErrorSeverity = ERROR_SEVERITY(),  
    @ErrorState    = ERROR_STATE(),  
    @ErrorNumber   = ERROR_NUMBER(),  
    @ErrorLine     = ERROR_LINE()  

  -- error number = 50000 :(  
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)  

END CATCH  

-- error number = 8134  
SELECT 1/0