Erreurs de transaction SQL Server: annuler et transmettre

Une commande qui réduit le stock mais ne crée pas la réservation n'est pas partiellement réussie: elle a rompu une règle métier. SQL Server peut rendre les écritures atomiques, à condition de gérer correctement erreurs et frontières transactionnelles. Un CATCH qui affiche une erreur puis retourne normalement peut produire un succès trompeur.

Définissez le résultat attendu avant le gestionnaire. Réduction de stock et réservation doivent être validées ensemble ou disparaître ensemble. L'exemple possède sa transaction et refuse une transaction extérieure déjà ouverte. Une procédure réutilisable appelée dans un traitement plus large nécessite un contrat de responsabilité différent.

Provoquer une erreur après la première écriture

Créez ces tables temporaires dans une connexion de pratique.

CREATE TABLE #Stock (ProductId int PRIMARY KEY, Quantity int NOT NULL);
CREATE TABLE #Reservations (
    ReservationId int PRIMARY KEY, ProductId int NOT NULL, Quantity int NOT NULL
);
INSERT #Stock VALUES (1, 10);
INSERT #Reservations VALUES (1, 1, 1);

L'identifiant de réservation existe déjà. Le batch suivant réduit le stock puis rencontre volontairement cette clé dupliquée.

SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'Run without an existing transaction.', 1;

DECLARE @ReservationId int = 1; -- Deliberate duplicate for the failure test.
DECLARE @Quantity int = 3;
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE #Stock
    SET Quantity = Quantity - @Quantity
    WHERE ProductId = 1 AND Quantity >= @Quantity;
    IF @@ROWCOUNT <> 1
        THROW 50002, 'Insufficient stock or missing product.', 1;

    INSERT #Reservations(ReservationId, ProductId, Quantity)
    VALUES (@ReservationId, 1, @Quantity);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Le UPDATE conditionnel vérifie et modifie la disponibilité en une instruction. Une lecture préalable non protégée permettrait à des sessions concurrentes de travailler avec une disponibilité périmée. En production, validez aussi que la quantité est strictement positive; la constante de l'exemple garde ce point simple.

L'échec de INSERT doit annuler la réduction précédente. Après l'erreur attendue, exécutez séparément cette inspection dans la même connexion.

-- Run separately after the expected error, in the same connection.
SELECT ProductId, Quantity FROM #Stock;
SELECT @@TRANCOUNT AS OpenTransactions, XACT_STATE() AS TransactionState;
-- Expected: Quantity = 10, OpenTransactions = 0, TransactionState = 0.

La quantité doit rester à 10 et aucune transaction ne doit être ouverte. Pour tester le succès, repartez de données neuves avec un nouvel identifiant, par exemple 2; le stock restant doit être 7. Vérifier seulement le message d'erreur ne dit pas si la première écriture a survécu.

Comprendre les trois mécanismes

TRY CATCH intercepte de nombreuses erreurs d'exécution, mais pas toutes les défaillances. Certaines erreurs de compilation au même niveau, la fin de connexion et l'annulation côté client demandent aussi une gestion par l'appelant. Une procédure ne peut pas assurer un retour normal sur une connexion disparue.

SET XACT_ABORT ON fait abandonner la transaction pour de nombreuses erreurs d'exécution, plutôt que la seule instruction. C'est utile pour cette écriture atomique, mais cela ne remplace ni le nettoyage ni la propagation. THROW respecte XACT_ABORT; RAISERROR a un comportement différent et n'est pas une simple autre orthographe.

XACT_STATE distingue absence de transaction, transaction validable et transaction non validable. @@TRANCOUNT indique la profondeur et ne répond pas à cette dernière question. Dans ce modèle propriétaire, toute transaction restante est annulée parce que l'opération métier a échoué, même si elle pourrait techniquement encore être validée.

THROW sans arguments dans CATCH préserve l'erreur initiale. La remplacer par un retour réussi ou un numéro générique complique le diagnostic et la classification des reprises. Pour la télémétrie, capturez ERROR_NUMBER, ERROR_PROCEDURE et ERROR_LINE avec un identifiant de demande.

Respecter le contrat de l'appelant

Un ROLLBACK simple annule toute la transaction, y compris les écritures antérieures de l'appelant. Ne copiez donc pas ce modèle dans une procédure auxiliaire imbriquée sans adaptation. Des BEGIN TRANSACTION et COMMIT imbriqués ne créent pas des transactions internes durablement indépendantes.

Une procédure composable peut mémoriser le nombre de transactions à l'entrée et utiliser un point de sauvegarde lorsque cela convient. Mais une transaction non validable ne se répare pas par retour à ce point; son propriétaire doit tout annuler. Les transactions distribuées imposent aussi des restrictions. Une responsabilité explicite est souvent préférable à un gestionnaire prétendument universel.

Écrivez une télémétrie durable après l'annulation ou via un canal indépendant. Une insertion de journal dans une transaction non validable peut échouer; dans la transaction annulée, elle disparaît. Évitez d'enregistrer inutilement des paramètres sensibles.

Enfin, un délai réseau dépassé autour de COMMIT signifie résultat inconnu, pas preuve d'annulation. Utilisez des identifiants et une vérification d'état faisant autorité pour les opérations non répétables sans risque. Testez l'échec après chaque écriture, le chemin réussi et la réponse de l'appelant: une annulation ne doit jamais devenir un succès annoncé.

Références techniques: Microsoft Learn: SET XACT_ABORT · Microsoft Learn: TRY CATCH · Microsoft Learn: XACT_STATE.