Des Transactions SQL non validable tout en utilisant try..catch.. Pourquoi?
Bonjour les amis utiles,
Nous sommes en cours d'exécution dans un problème ici et je ne peux pas comprendre pourquoi il se comporte comment il se comporte. J'espère que vous pourrez m'aider.
Donné les deux (simplifié) des procédures stockées dans TSQL (SQL Server 2008R2)
create procedure [datetransaction1]
as
begin
begin try
begin transaction
declare @a datetime
exec datetransaction2 '2013-02-02 22:21', @a output
select @a
exec datetransaction2 '2013-020222:22', @a output
select @a
exec datetransaction2 '2013-02-02 22:23', @a output
select @a
commit transaction
end try
begin catch
print 'Catch'
end catch
end
et
create procedure [dbo].[datetransaction2] @text nvarchar(100), @res datetime OUTPUT
AS
BEGIN
BEGIN TRY
if (LEN(@text) = 16) SET @text = replace(@text, ' ', 'T') + ':00.000'
else if (LEN(@text) = 19) SET @text = replace(@text, ' ', 'T') + '.000'
else SET @text = replace(@text, ' ', 'T')
PRINT 'trydate:' + @text
SELECT @res =convert(datetime, @text, 126)
END TRY
BEGIN CATCH
PRINT ERROR_SEVERITY()
PRINT 'errordate:' + @text
END CATCH
END
Si vous exécutez alors exec datetransaction1
, nous voyons que tous les 3 appels à datetransaction2
sont exécutées, avec la première et la dernière (comme prévu) de fonctionner correctement, et la seconde pour entrer dans le CATCH
bloc dans datetransaction2
.
Donc bon.
Mais nous débarquons dans le bloc catch de datetransaction1
avec le message que la transaction est non validable:
Msg 266, Level 16, State 2, Procedure datetransaction1, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
Ce qui n'est pas censé arriver (je pense). Nous avons pris les erreurs dans les procédures sub, alors pourquoi la transaction est soudainement devenu non validable?
Quelqu'un peut-il m'expliquer?
Noter que l'on peut probablement trouver un moyen de contourner cela, mais je suis plus intrigué par l'idée derrière tout cela. Pourquoi cette opération est soudainement devenu non validable ici?
Vous devez vous connecter pour publier un commentaire.
La raison est la suivante: Sql Server voue à l'échec de la transaction LORSQU'une erreur se produit, quelle que soit l'erreur, si elle est dans un bloc TRY ou pas, si vous avez enregistré un état de la transaction ou pas, si l'erreur se produit dans une procédure, ou pas, quoi que vous fassiez.
Lorsque l'erreur se produit dans l'un des appels de procédure, l'opération est vouée à l'échec. Vous pouvez seulement de restauration complète (aucun point de sauvegarde ne va pas aider).
À la fin, puisque l'opération est vouée à l'échec, vous ne pouvez pas commettre...
Essayez ceci:
Depuis le deuxième appel à datetransaction2 fonction causé niveau de gravité 16 erreur SQL Server automatiquement annulée votre transaction. C'est la raison de l'erreur que vous voyez.
Ici est un vraiment sympa l'article pourquoi les transactions pénètre dans vouée état lorsque le niveau de gravité de 16 erreur se produit.
Pour vérifier que son être annulée automatiquement, j'ai ajouté la ligne suivante à votre datetransaction2 proc : imprimer XACT_STATE()
Ressemble 'commit de la transaction" n'est jamais atteint, car le code des sauts sur le bloc catch. Pour Éviter cela, vous pouvez ajouter un "rollback transaction" à votre bloc catch comme suit:
En bref: un
catch
déclaration provoque souvent une restauration (voir Un). Cela dépendXACT_ABORT
.Ensuite, les retours en arrière ne sont pas à la SP, où ils sont mis en œuvre (voir Deux).
La première référence (Un) donne une solution de contournement à l'aide de
@@trancount
, voir la accepté réponse.Je suis sûr que cette erreur peut se produire uniquement Try/Catch est utilisé.
En fin de compte cette erreur signifie que la transaction a été commencé et une erreur est survenue qui n'entraîne pas automatiquement un retour en arrière. Il existe de nombreuses raisons pour lesquelles la xact_abort paramètre (qui est désactivée par défaut) n'est qu'une. Vous avez attrapé une erreur qui n'a pas automatiquement annulée et vous n'avez pas de rouleau de l'opération vous-même.
Plutôt que de comprendre quelles erreurs exiger la restauration et quand, personnellement, si je commence une opération de moi ou pas, j'ai mis le code suivant au début de CHAQUE bloc catch.
Cela empêche définitivement le problème tout en assurant que vos données se comporte exactement de la manière que vous auriez normalement s'attendre. OIE toujours rollback en cas d'erreur. De façon uniforme assure qu'il n'a pas d'importance si certains appelant de votre procédure a commencé une transaction, vous démarrez l'un ou si une procédure d'appel commence l'un et le laisse pendre; il est toujours roulé en arrière quand vous attrapez une erreur.