try catch et transaction

**KyoshiroKensei** · 30/01/2025, 15h14

Bonjour

J'hérite de code SQL legacy capricieux et je suis face à un problème que je parviens pas à comprendre. Je sollicite donc vos lumières. J'ai une procédure stockée appelée depuis une application cliente de cette forme :

Maps1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Try
 
EXEC maps2  -- dans  cette ps en plein milieu du code , sans try catch, il y a un begin tran + commit tran et aucun rollback.
 
Catch
IF XACT_STATE() <> 0   ROLLBACK TRANSACTION  
--code pour tracer

Mon problème est le suivant : de temps en temps, mon catch capture les logs suivantes :
Echec du traitement : un Rollback a été effectué.
Echec du traitement. Erreur n° 3930, La transaction actuelle ne peut pas être validée et ne prend pas en charge les opérations qui écrivent dans le fichier journal. Restaurez la transaction.

En résulte que l'application ne peut plus appeler uniquement la procedure "Maps1" qui tombe toujours en erreur 3930. Le rollback ayant été déclenché, pourquoi les ressources ne sont pas libérées ? Je n'ai pas non plus la cause de l'erreur de maps2.
Pour résoudre le problème je ferme l'application cliente.

Merci d'avance.

**SQLpro** · 30/01/2025, 15h47

Sans le code complet des procédure impossible de t'aider....

A +

**KyoshiroKensei** · 30/01/2025, 16h02

Le code est trop étendu, j'ai donc mis le "principe" du code. Il y a beaucoup de d'appel de procédure stockée imbriquée dans la ps2, sans aucun try catch ni transaction explicite. L'ensemble du code s'exécute bien jusqu' à la rencontre du begin/commit dans la ps2. Je vais essayer d'en sortir une partie.

J'ai l'impression de que le rollback echoue en fait, car les ps appelées avant le commit ont bien mis à jour les données et cela n'a pas été annulé. Est il possible qu'un rollback échoue?

**SQLpro** · 31/01/2025, 09h01

Un rollback échoue s'il est appelé deux fois. En effet si la transaction a déjà été annulée auparavant, alors le rollback ne peut pas annuler ce qui est déjà annulé et se trouve donc en dehors d'une transaction explicite.

Pour information, dans les cours Transact SQL que je délivre aux stagiaires à Orsys, voici le template de code que je donne pour s'assurer de la cohérence transactionnelle au sein des différentes procédures stockées appelées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE PROCEDURE name
AS
 
-- code hors transaction explicite
 
BEGIN TRANSACTION;
 
-- code transactionné non sécurisé
 
BEGIN TRY
 
-- code transactionné sécurisé
 
COMMIT
 
-- code non transactionné, sécurisé
 
END TRY
-- gestion des exceptions
BEGIN CATCH
 
-- code avant annulation
 
   IF XACT_STATE <> 0
      ROLLBACK;
   THROW; --> renvoyer l'erreur aux appelant !!!
 
-- code après annulation
 
END CATCH
 
-- code après traitement
 
GO

Si chaque procédure ne remonte pas l'erreur (THROW), alors les ROLLBACK peuvent se superposer....

A +

**KyoshiroKensei** · 03/02/2025, 07h49

Oui, ayant suivi votre formation, j'applique ce template dans les nouveaux developpement depuis quelques années

.

Je vais voir si il n'y a pas un rollback qui traine sans xstate et revint vers vous.

**Michel.Priori** · 03/02/2025, 15h49

Envoyé par KyoshiroKensei

L'ensemble du code s'exécute bien jusqu' à la rencontre du begin/commit dans la ps2.

Bon, j'imagine que lorsque vous écrivez begin/commit, c'est un résumé de begin transaction/commit.
On est d'accord ?

Si c'est le cas, on en est où avec le paramètre IMPLICIT_TRANSACTIONS ?

**KyoshiroKensei** · 03/02/2025, 16h28

Envoyé par Michel.Priori

Bon, j'imagine que lorsque vous écrivez begin/commit, c'est un résumé de begin transaction/commit.
On est d'accord ?

Si c'est le cas, on en est où avec le paramètre IMPLICIT_TRANSACTIONS ?

oui on est d'accord. IMPLICIT_TRANSACTIONS est à OFF, nous sommes donc en auto commit.

**Michel.Priori** · 04/02/2025, 08h49

re,

Dans un premier temps je commencerais par déterminer la valeur de XACT_STATE.
Si 1, il ne devrait pas y avoir de problème pour clôturer la transaction par un rollback ...
Si 0, la transaction est déjà close => remonter le code pour trouver le cheminement qui cause le doublon.
Si -1, faut expliciter l'erreur qui a été catchée.

**SQLpro** · 04/02/2025, 09h50

Envoyé par Michel.Priori

re,

Dans un premier temps je commencerais par déterminer la valeur de XACT_STATE.
Si 1, il ne devrait pas y avoir de problème pour clôturer la transaction par un rollback ...
Si 0, la transaction est déjà close => remonter le code pour trouver le cheminement qui cause le doublon.
Si -1, faut expliciter l'erreur qui a été catchée.

D’où le template de code donné !!!!

A +

**Michel.Priori** · 04/02/2025, 10h18

Envoyé par SQLpro

D’où le template de code donné

Nous sommes d'accord qu'en toute logique le respect du template devrait éviter la situation décrite.
Ok avec ça ?

Donc, afin de déboguer la situation je propose simplement de faire une variante du modèle en traitant les différentes valeurs possibles.
Ainsi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE PROCEDURE name AS
 -- code hors transaction explicite
 BEGIN TRANSACTION;
 -- code transactionné non sécurisé 
 BEGIN TRY
  -- code transactionné sécurisé 
 COMMIT
  -- code non transactionné, sécurisé
 END TRY
 BEGIN CATCH
 -- code avant annulation
    IF XACT_STATE =-1
     BEGIN
       --> gestion des exceptions fortes
      ROLLBACK;
     END
    IF XACT_STATE =1
     BEGIN
      --> gestion des erreurs applicatives
      ROLLBACK;
    END
   THROW; --> renvoyer l'erreur aux appelant !!!
    -- code après annulation
END CATCH
-- code après traitement
GO

**Michel.Priori** · 04/02/2025, 10h25

re,

Dans la gestion des exceptions, je conseille d'utiliser la variable @@procid afin de bien identifier le code qui traite l'erreur.

**KyoshiroKensei** · 04/02/2025, 13h06

merci je vais mettre tout cela en place et reviens vers vous avec également une extraction du code plus précise dans la semaine.

**KyoshiroKensei** · 07/02/2025, 15h42

bonjour, ci-dessous une extraction du code de mon problème. J'ai également tracé la valeur du xstate et @@procid.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
ALTER PROCEDURE [laf].[PROCabineLigneLiberer]
 
AS
BEGIN
	-------------
	-- Options --
	-------------
	SET NOCOUNT ON		 
	-----------------
	-- Déclaration --
	-----------------
	DECLARE @Procedure			AS VARCHAR(100) SET @Procedure = 'PROCabineLigneLiberer'
	DECLARE @Message			AS VARCHAR(500)	SET @Message = ''   
 
	----------------
	-- Traitement --
	---------------- 
 	BEGIN TRY
 
		--------------------------------
		-- Initialisation des données --
		-------------------------------- 
		SET @Message = 'Demande de libération.'		
 		EXEC PROEcrJdb @Procedure, @Message, 2
 
		--------------------------------
		-- Traitement --
		-------------------------------- 
		EXEC [api].[PROMessageEtatLRYEcrire] 0,'Deblocage',0,-1 
 
               -- jusque là pas de problème
                EXEC dbo.ProZoneForcerMouvement  
 
		SET @Message = 'Fin de la demande de libération.'		
 		EXEC PROEcrJdb @Procedure, @Message, 2
 
		RETURN 0
 
	END TRY 
	BEGIN CATCH
 
		IF XACT_STATE() <> 0 
		BEGIN 		 
			ROLLBACK TRANSACTION 
 			SET @Message = 'Echec du traitement: un Rollback a été effectué.'	
 			EXEC PROEcrJdb @Procedure, @Message, 3 -- <= 3 : Message, >3 : Erreur	
		END
 
 		DECLARE @ErreurMessage		AS NVARCHAR(4000);  
		DECLARE @ErreurSeverite		AS INT;  
		DECLARE @ErreurEtat			AS INT;  
		DECLARE @ErreurNumero		AS INT;   
		DECLARE @ErreurProcedure	AS NVARCHAR(50);   
		DECLARE @ErreurLigne		AS INT;  
 
		SELECT   @ErreurNumero = ERROR_NUMBER() 
				,@ErreurMessage = ERROR_MESSAGE()  
				,@ErreurSeverite = ERROR_SEVERITY()  
				,@ErreurEtat = ERROR_STATE()
				,@ErreurProcedure = ERROR_PROCEDURE()  
				,@ErreurLigne = ERROR_LINE() ;  
 
 
		SET @Message = 'Echec du traitement. Erreur n° ' + CAST(@ErreurNumero AS VARCHAR) + ', ' + @ErreurMessage	
		EXEC PROEcrJdb @Procedure, @Message, 3 -- <= 3 : Message, >3 : Erreur	
 
		--renvoi de l'erreur au client  
		RAISERROR (@ErreurMessage, -- Message text.  
				   @ErreurSeverite, -- Severity.  
				   @ErreurEtat -- State.  
				   );  
 
 
		RETURN -1
 
	END CATCH

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
-- code de EXEC dbo.ProZoneForcerMouvement 
ALTER PROCEDURE [dbo].[PROZoneForcerMouvement] 
	  @NumZoneDepart	INT 
	, @NumZoneArrivee	INT 
	, @Mode				INT = 1
	, @NomApplication	VARCHAR(50)=''
	, @NomUtl			VARCHAR(50)=''
	, @NomPst			VARCHAR(50)=''
 
AS
BEGIN
 
	-- Variables pour les messages
	DECLARE @Procedure VARCHAR(50)   -- Nom de la procedure
	DECLARE @Message   VARCHAR(250)  -- Message
 
	-- Infos zone depart
	DECLARE @LigneDepart     INT
	DECLARE @LigneArrivee     INT
	DECLARE @ColonneDepart   INT
	DECLARE @ColonneArrivee  INT
	DECLARE @NbrBarres     INT
	Declare @Position varchar(50)
 
	DECLARE @IdtBar       INT
	DECLARE @Retour          INT
	DECLARE @Err          INT
	DECLARE @TranName VARCHAR(20)
	DECLARE @NumSeq int
	SELECT @TranName = 'Transaction'
 
	-- Initialise le nom de la procédure
	SET @Procedure = 'PROZoneForcerMouvement'
 
	SET @Message = 'Forçage déplacement mode(' + ISNULL( CAST(@Mode AS VARCHAR), 'NULLE')  + ')' 
	             + ' de la zone n°' + ISNULL( CAST(@NumZoneDepart AS VARCHAR), 'NULLE') 
	             + ' vers la zone n°' + ISNULL( CAST(@NumZoneArrivee AS VARCHAR), 'NULLE') 
				 + '.' 
 
	EXEC BDRAPP.dbo.PROEcrJdb @Procedure, @Message, 2 -- <= 3 : Message, >3 : Erreur 
 
 
 
----ICI: Lorsque le problème survient, meme après rollback, les traces demeurent et se sont arrêtées ici.
 
 
 
   	SET @ColonneArrivee=-1
	IF (@NumZoneArrivee=9)
	BEGIN
		SET @ColonneArrivee=0
	END
 
	-------------------------------------------------------------------
	-- On recupere la ligne de la zone de départ et le nombre de barres
	-------------------------------------------------------------------
	EXEC PROZoneLigneProchaine @NumZoneDepart, @LigneDepart output, @NbrBarres output
 
	----------------------------------------------
	-- Une seule barre
	----------------------------------------------
	IF (@NbrBarres = 1)
	BEGIN
 
		if (@NumZoneArrivee=3) or (@NumZoneArrivee=4) or (@NumZoneArrivee=6) or (@NumZoneArrivee=7) or (@NumZoneArrivee=8) 
		BEGIN
			SET @ColonneArrivee=0
		END
 
		BEGIN TRANSACTION @TranName  -- On sécurise la transaction
			--------------------------------------------------------------
			SELECT @IdtBar = IdtBarFil  FROM TabPosBar WHERE NumZon = @NumZoneDepart AND LigBar = @LigneDepart
			-- On supprime la barre de la zone d'origine
			exec PROBarreZoneSupprimer @NumZoneDepart , @IdtBar  
			-- On insere la barre dans la zone destination en automatique
			exec probarrezoneAjouter @IdtBar,@NumZoneArrivee,-1,@ColonneArrivee,@Err output 
			-- Si la zone destination est , on affecte le numéro de séquence.
			if (@NumZoneArrivee=8) 
			BEGIN
				Exec PRONUMSEQ 1, @NumSeq output
				-- On remet à jour les n° de sequence des barres concernées.
				Update TabBarFil 
					set NumSeq=@NumSeq
					where IdtBarFil=@IdtBar
			END
		COMMIT TRANSACTION @TranName 
		--
 
	END 
	---------------------------------------------------------
	-- on retourne 0 pour indiquer que le deplacement est OK.
	---------------------------------------------------------
	RETURN 0
 
END

**Michel.Priori** · 11/02/2025, 11h47

Bonjour,

Je me suis focalisé sur la proc [dbo].[PROZoneForcerMouvement]

Pour démarrer plusieurs remarques qui tiennent plus de mes habitudes de développement que d'autre chose :
* Minimiser le nombre de mot clés DECLARE (à remplacer par une virgule)
* S'assurer que les variables sont bien utilisées (c'est pas le cas de @NomApplication, @NomUtl, @NomPst, @LigneArrivee, @ColonneDepart, @Position, @Retour) -- Est-ce que le code a été tronqué ?
* Initialiser les variables lors de la déclaration (DECLARE @ColonneArrivee INT=-1, @TranName VARCHAR(20)= 'Transaction')
* N'utiliser BEGIN ... END à la suite de IF que s'il y a plusieurs instructions
* Utiliser les commentaires de bloc (/* */) dans le code pérenne (ne pas garder les commentaires de lignes -- dans du code qu'on doit déboguer, relire, conserver)

Ensuite des remarques qui ont un peu plus de poids :
* Ne pas faire une procédure stockée pour "seulement" lire des données => PROZoneLigneProchaine
* Justifier la présence de chaque instruction incluse dans la transaction ( utilité d'avoir SELECT @IdtBar = IdtBarFil ; Est-ce par rapport au niveau d'isolation ? lequel ?)

Et enfin, la relecture du code doit veiller à rendre la lecture aisée : la variable @ColonneArrivee est modifiée plusieurs fois.
Ecriture simplifiée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
	if @NumZoneArrivee in (3,4,6,7,8,9)
		SET @ColonneArrivee=0;

Et pour finir, pouvez vous expliciter :

ICI: Lorsque le problème survient, meme après rollback, les traces demeurent et se sont arrêtées ici.

"Trace" = profiler ?
"Les traces demeurent et se sont arrêtées ici" = ??
Avez vous tenté de placer des marqueurs avant/après chaque instruction ?

**KyoshiroKensei** · 12/02/2025, 08h59

Bonjour,

un grand merci pour le temps passé. Concernant vos remarques:

Minimiser le nombre de mot clés DECLARE (à remplacer par une virgule)
* S'assurer que les variables sont bien utilisées (c'est pas le cas de @NomApplication, @NomUtl, @NomPst, @LigneArrivee, @ColonneDepart, @Position, @Retour) -- Est-ce que le code a été tronqué ?
* Initialiser les variables lors de la déclaration (DECLARE @ColonneArrivee INT=-1, @TranName VARCHAR(20)= 'Transaction')
* N'utiliser BEGIN ... END à la suite de IF que s'il y a plusieurs instructions
* Utiliser les commentaires de bloc (/* */) dans le code pérenne (ne pas garder les commentaires de lignes -- dans du code qu'on doit déboguer, relire, conserver)

il s'agit de code legacy, issu de sql 2000/2005, donc l'écriture laisse clairement à désirer et je plussoie vos remarques a l'exception du if sans begin/end , par expérience. J'ai retiré le code qui n'est pas joué lors du problème (un if inhibe certains traitements).

Ne pas faire une procédure stockée pour "seulement" lire des données => PROZoneLigneProchaine

Je ne comprends pas cette remarque. Le but ici est d'avoir un select réutilisable dans plusieurs procédure, donc faire une procédure permet de factoriser le select. J'ai ouvert cette ps, il ne s'agit pas d'un "simple" select.

Concernant les traces, nos traitements tracent des logs en base de données (via la ps PROEcrJdb présente dans l'extrait de code). Normalement, lorsque le rollback declenche, je perd logiquement les traces. Je ne devrais donc avoir que les traces de mon rollback fait et la cause d'erreur. Lorsque le problème survient, j'ai toute les traces des ps + la trace du rollback et d'erreur de PROCabineLigneLiberer.

D'ou ma question sur l'echec possible d'un rollback.

try catch et transaction

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager