Est-ce conseillé de faire des transactions imbriquées ?

Bonjour,

Normalement oui c'est possible.

Je crois que le rollback sera globale mais vous devez pouvoir asser
facilement le vérifier.

Phil.
________________________________________________________
Philippe TROTIN
Microsoft Services France http://www.microsoft.com/france
"Gilles TOURREAU" <gilles.tourreau@pos.fr> wrote in message
news:mn.842b7d6767a21b19.52180@pos.fr...

Bonjour tout le monde !

Voilà, au niveau application j'ai 2 fonctions développé par 2 personnes
différentes qui mettent à jour 2 tables différentes :

Fonction MAJTable1()
{
BeginTransaction("Trans1")
UpdateTable("Table1")
EndTransaction("Trans1")
}

Et

Fonction MAJTable2()
{
BeginTransaction("Trans2")
UpdateTable("Table2")
EndTransaction("Trans2")
}

On m'a demandé de modifier la fonction "MAJTable1" afin de modifier la
table1 et la table2...

C'est à dire :

Fonction MAJTable1()
{
BeginTransaction("Trans")
UpdateTable("Table1")
UpdateTable("Table2")
EndTransaction("Trans")
}


Je voudrais savoir si je peux faire plustôt (Ce qui m'arrangerais) :

Fonction MAJTable1()
{
BeginTransaction("Trans1")
UpdateTable("Table1")
MAJTable2()
EndTransaction("Trans1")
}

Si oui, que ce passe-t-il si on RollBack la transaction "Trans2" ? La
transaction "Trans1" sera-t-elle "RollBacké" ?

En vous remerciant par avance de vos lumières !

Cordialement

--
Gilles TOURREAU
Responsable informatique
gilles.tourreau@pos.fr

Société P.O.S
Spécialiste en motoculture depuis + de 30 ans !
http://www.pos.fr

Bonjour,

les transactions imbriquées ne sont pas symétriques dans leur "finition".

En effet le PREMIER ROLLBACK entraîne l'annulation et c'est le DERNIER
COMMIT qui entraîne la validation.

Dans ce cas il faut prévoir un code tel que celui-ci afin de rendre
générique le traitement d'annulation ou de validation :

******************* DEMO ******************


-- création d'une table test pour notre transaction
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T_TRN')
DROP TABLE T_TRN
GO

-- table avec une contrainte de validité
CREATE TABLE T_TRN
(N INT CHECK (N >= 0))
GO


-- création d'une procédure stockée de test de transaction imbriquée
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME = 'P_TRN_INTERNE')
DROP PROCEDURE P_TRN_INTERNE
GO

CREATE PROCEDURE P_TRN_INTERNE
AS

DECLARE @ERROR INT, @ROWCOUNT INT

BEGIN TRANSACTION

-- insertion invalide : elle doit déclencher le ROLLBACK
INSERT INTO T_TRN VALUES (-4)
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT
IF @ERROR <> 0 OR @ROWCOUNT = 0
BEGIN
RAISERROR('Procédure P_TRN_INTERNE : Erreur à l''insertion', 16, 1)
GOTO LBL_ERROR
END

COMMIT TRANSACTION

RETURN (0)


LBL_ERROR:

IF @@TRANCOUNT > 1
COMMIT TRANSACTION
IF @@ROWCOUNT = 1
ROLLBACK TRANSACTION
RETURN (-1)

GO


-- création d'une procédure stockée de test de transaction imbriquée
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME = 'P_TRN_EXTERNE')
DROP PROCEDURE P_TRN_EXTERNE
GO


CREATE PROCEDURE P_TRN_EXTERNE
AS

DECLARE @ERROR INT, @ROWCOUNT INT, @RETVAL INT

BEGIN TRANSACTION

-- insertion valide
INSERT INTO T_TRN VALUES (33)
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT
IF @ERROR <> 0 OR @ROWCOUNT = 0
BEGIN
RAISERROR('Procédure P_TRN_EXTERNE : Erreur à l''insertion', 16, 1)
GOTO LBL_ERROR
END

EXEC @RETVAL = P_TRN_INTERNE
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT

IF @RETVAL = -1 -- la transaction a été pseudo validée mais elle doit être
annulée
BEGIN
RAISERROR('Procédure P_TRN_EXTERNE : Erreur à l''appel de la procédure
P_TRN_INTERNE', 16, 1)
GOTO LBL_ERROR
END

IF @ERROR <> 0 OR @@ROWCOUNT = 0
GOTO LBL_ERROR

COMMIT TRANSACTION

RETURN (0)


LBL_ERROR:

IF @@TRANCOUNT > 1
COMMIT TRANSACTION
IF @@ROWCOUNT = 1
ROLLBACK TRANSACTION
RETURN (-1)

GO
-- exécution teste
EXEC P_TRN_EXTERNE
GO

-- a l'issu de cet exécution aucune ligne ne doit avoir été inséré :
SELECT * FROM T_TRAN
GO

**************** FIN *****************

Vous constaterez que ça marche pas bien !!!!

************** LA SOLUTION ************

-- partie à rajouter à TOUTES les procédures (finalisation) :

-- succès
COMMIT TRANSACTION
RETURN (0)

-- échec
LBL_ERROR:
IF @@TRANCOUNT > 1
COMMIT TRANSACTION
IF @@ROWCOUNT = 1
ROLLBACK TRANSACTION
RETURN (-1)


-- Si vous avez besoin de rétablir le niveau d'isolation par défaut :

...
DECLARE @RETVAL INT

...

-- succès
COMMIT TRANSACTION
SET @RETVAL = 0
GOTO RESUME

-- échec
LBL_ERROR:
IF @@TRANCOUNT > 1
COMMIT TRANSACTION
IF @@ROWCOUNT = 1
ROLLBACK TRANSACTION
SET @RETVAL = -1

LBL_RESUME:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RETURN @RETVAL


A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************



"Philippe T [MS]" a écrit

"Gilles TOURREAU" a écrit :

Bonjour tout le monde !

Voilà, au niveau application j'ai 2 fonctions développé par 2 personnes
différentes qui mettent à jour 2 tables différentes :

Fonction MAJTable1()
{
BeginTransaction("Trans1")
UpdateTable("Table1")
EndTransaction("Trans1")
}

Et

Fonction MAJTable2()
{
BeginTransaction("Trans2")
UpdateTable("Table2")
EndTransaction("Trans2")
}

On m'a demandé de modifier la fonction "MAJTable1" afin de modifier la
table1 et la table2...

C'est à dire :

Fonction MAJTable1()
{
BeginTransaction("Trans")
UpdateTable("Table1")
UpdateTable("Table2")
EndTransaction("Trans")
}


Je voudrais savoir si je peux faire plustôt (Ce qui m'arrangerais) :

Fonction MAJTable1()
{
BeginTransaction("Trans1")
UpdateTable("Table1")
MAJTable2()
EndTransaction("Trans1")
}

Si oui, que ce passe-t-il si on RollBack la transaction "Trans2" ? La
transaction "Trans1" sera-t-elle "RollBacké" ?

En vous remerciant par avance de vos lumières !

Cordialement

--
Gilles TOURREAU
Responsable informatique
gilles.tourreau@pos.fr

Société P.O.S
Spécialiste en motoculture depuis + de 30 ans !
http://www.pos.fr