Gestion de @@trancount dans les procédures stockées

**jejestanko** · 11/10/2022, 11h02

Bonjour à tous
Dans mon code, j'utilise beaucoup les procédures stockées imbriquées. Les traitements de mes procédures stockées effectuent tous plusieurs opérations dans la base de données, ils sont donc encapsulés dans une transaction. Mes procédures stockées enfants peuvent être également éxécutées en tant que parent. Je démarre donc toutes mes procédures stockées enfant par un BEGIN TRAN après le BEGIN TRY et en cas d'erreur, je teste la condition suivante avant d'effectuer un rollback : IF @@trancount = 1 and xact_state()<>0. Ma question est la suivante, lorsque j'effectue un EXECUTE d'une procédure stockée, est ce que la valeur de @@trancount peut être modifiée par un autre EXECUTE d'une procédure stockée, hors appels de procédure stockée interne ou externe.
Exemple :
la procédure stockée A appelle la procédure stockée B
la procédure stockée C ne fait appel à aucune autre procédure stockée
Le traitement de chacune des procédures stockées est encapsulé dans une transaction

Est ce que, dans la même session user, le cas présent peut se produire :
J'éxécute la procédure stockée A, la valeur de @@trancount passe à 1 puis à 2 après le BEGIN TRAN de la procédure stockée B
Maintenant, imaginons que ma procédure ne soit pas terminée mais que, dans le même temps, le moteur SQL Server éxecute la procédure stockée C sur la même session. Est ce que la valeur de @@trancount après le BEGIN TRAN de la procédure stockée est C est 1 ou 3 ?

**SQLpro** · 12/10/2022, 15h05

L'utilisation de @@TRANCOUNT n'est pas conseillé car complexe et piègeuse.

Le bon squelette de code à adopter pour TOUTES vos procédures transactionnée est le suivant :

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
CREATE PROCEDURE ...
AS
 
...
 
BEGIN TRY
 
   ...
 
   BEGIN TRANSACTION;
 
   ...
 
   COMMIT;
 
   ...
 
END TRY
BEGIN CATCH
   IF XACT_STATE() <> 0
      ROLLBACK;
   THROW;
END TRY
 
...
 
GO

**jejestanko** · 12/10/2022, 17h05

Merci beaucoup pour votre réponse @sqlpro, c'est exactement ce que j'avais écrit.
Sauf que : j'ai décidé d'ajouter un insert de l'erreur rencontrée après le rollback pour pouvoir journaliser les erreurs. Et pour aérer au maximum le code, je me suis dit que je vais gérer tout ça dans une autre procédure stockée, ce qui me donne

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
 
 
--procédure handle error
PROCEDURE sp_i_ts_erreur_err
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
 
	BEGIN TRY		
                IF XACT_STATE()<>0
                    ROLLBACK
 
		DECLARE @v_erreur_numero int = ERROR_NUMBER()
		,@v_erreur_severite int = ERROR_SEVERITY()
		,@v_erreur_etat int = ERROR_STATE()
		,@v_erreur_procedure nvarchar(126) = ERROR_PROCEDURE()
		,@v_erreur_ligne int = ERROR_LINE()
		,@v_erreur_message nvarchar(2048) = ERROR_MESSAGE()
		,@v_date datetime2(0) = getdate()
		,@v_user nvarchar(128) = system_user;
 
 
		EXEC sp_executesql N'INSERT INTO ts_erreur_err(err_user,err_date,err_numero,err_etat,err_severite,err_ligne,err_procedure,err_message) VALUES (@user,@date,@numero,@etat,@severite,@ligne,@procedure,@message) 
		',N'@user nvarchar(128),@date datetime2(0),@numero int,@etat int,@severite int,@ligne int,@procedure nvarchar(128),@message 
                 nvarchar(2048)',@v_user,@v_date,@v_erreur_numero,@v_erreur_etat,@v_erreur_severite,@v_erreur_ligne,@v_erreur_procedure,@v_erreur_message
 
	END TRY
 
	BEGIN CATCH
		;THROW
    END CATCH
END
 
-- procédure modèle
CREATE PROCEDURE ...
AS
 
...
 
BEGIN TRY
 
   ...
 
   BEGIN TRANSACTION;
 
   ...
 
   COMMIT;
 
   ...
 
END TRY
BEGIN CATCH
   execute sp_i_ts_erreur_err
   ;THROW
END TRY

Lorsque j'exécute ce code, l'erreur n°266 m'est retourné. Elle m'indique que le nombre de transactions après EXECUTE indique un nombre différent d'instructions BEGIN et COMMIT.
Cette erreur n'est pas bloquante mais je ne comprends pas pourquoi elle m'est retourné dans ce cas de figure et pourquoi cela n'est pas le cas lorsque je modifie mon code comme ci-dessous

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
 
 
--procédure handle error
PROCEDURE sp_i_ts_erreur_err
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
 
	BEGIN TRY		
 
		DECLARE @v_erreur_numero int = ERROR_NUMBER()
		,@v_erreur_severite int = ERROR_SEVERITY()
		,@v_erreur_etat int = ERROR_STATE()
		,@v_erreur_procedure nvarchar(126) = ERROR_PROCEDURE()
		,@v_erreur_ligne int = ERROR_LINE()
		,@v_erreur_message nvarchar(2048) = ERROR_MESSAGE()
		,@v_date datetime2(0) = getdate()
		,@v_user nvarchar(128) = system_user;
 
 
		EXEC sp_executesql N'INSERT INTO ts_erreur_err(err_user,err_date,err_numero,err_etat,err_severite,err_ligne,err_procedure,err_message) VALUES (@user,@date,@numero,@etat,@severite,@ligne,@procedure,@message) 
		',N'@user nvarchar(128),@date datetime2(0),@numero int,@etat int,@severite int,@ligne int,@procedure nvarchar(128),@message 
                 nvarchar(2048)',@v_user,@v_date,@v_erreur_numero,@v_erreur_etat,@v_erreur_severite,@v_erreur_ligne,@v_erreur_procedure,@v_erreur_message
 
	END TRY
 
	BEGIN CATCH
		;THROW
    END CATCH
END
 
-- procédure modèle
CREATE PROCEDURE ...
AS
 
...
 
BEGIN TRY
 
   ...
 
   BEGIN TRANSACTION;
 
   ...
 
   COMMIT;
 
   ...
 
END TRY
BEGIN CATCH
        IF XACT_STATE()<>0
        BEGIN
            ROLLBACK
            execute sp_i_ts_erreur_err
        END
        ;THROW
END TRY

Pouvez vous éclairer mon ignorance ???

**SQLpro** · 12/10/2022, 17h18

Tout simplement parce que tu ouvre une nouvelle transaction pour gérer l'insertion de ton erreur dans une table...

Il ne faut pas gérer de transaction dans ce cas de figure, mais transmettre les informations à stocker via une variable table qui n'est pas concernée par les opérations de ROLLBACK ou COMMIT, puisque c'est une variable et non une table.

A +

**SQLpro** · 12/10/2022, 17h33

Tu peux aussi utiliser une variable XML pour synthétiser l'erreur :

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
DECLARE @XML_ERROR XML = CAST((
SELECT ERROR_NUMBER() AS ERROR_NUMBER,
       ERROR_SEVERITY() AS ERROR_SEVERITY,
	   ERROR_STATE() AS ERROR_STATE,
	   ERROR_PROCEDURE() AS ERROR_PROCEDURE,
	   ERROR_LINE() AS ERROR_LINE,
	   ERROR_MESSAGE() AS ERROR_MESSAGE,
	   SYSUTCDATETIME() AS UTC_DH,
	   SYSDATETIME() AS LOCAL_DH,
	   system_user AS "LOGIN",
	   USER AS "USER",
	   ORIGINAL_LOGIN() AS ORIGINAL_LOGIN,
	   ORIGINAL_DB_NAME() AS ORIGINAL_DB_NAME,
	   DB_NAME() AS CONTEXTUAL_DB_NAME,
	   PROGRAM_NAME() AS PROGRAM_NAME,
	   HOST_NAME() AS HOST_NAME,
	   (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID)
FOR XML AUTO, ELEMENTS, ROOT('error')) AS XML);

Puis transmettre à une proc hyper simple :

Code :

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

1
2
3
4
5
CREATE PROC P_STACK_ERROR @XML XML
AS
SE NOCOUNT ON;
INSERT INTO S_ERROR.T_TRACK_ERROR VALUES (@XML);
GO

**jejestanko** · 12/10/2022, 17h58

Merci pour votre réponse SQLPro
Néanmoins quelque chose m'échappe...
Ma procédure stockée qui capte l'erreur et fait le rollback ne déclenche pas de transaction explicite alors je suppose que vous parlez de transaction implicite déclenchée par l'insert... Cependant si je fais un test en supprimant la commande insert de ma procédure stockée et en conservant uniquement le rollback, le message d'erreur 266 se déclenche quand même... ???
J'ai lu que l'erreur 266 se déclenche lorsque vous quittez une procédure stockée et que @@trancount a une valeur différente de celle du début de l'exécution de la procédure. Or, dans le cas de procédures stockées imbriquées, en cas d'erreur dans une procédure stockée "enfant", je vais toujours retomber dans ce cas de figure si j'effectue le rollback dans la procédure stockée "enfant". Pour ne pas avoir cette erreur, il faudrait que j'effectue le rollback uniquement dans la procédure stockée parent même si l'erreur se produit dans l'enfant...
Je n'ai pas vraiment envie d'adopter ce dernier modèle. Etant donné que l'erreur 266 n'est pas une erreur bloquante, pensez-vous que je puisse l'ignorer ?

Gestion de @@trancount dans les procédures stockées

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager