Problème de transaction

Version imprimable

Bonjour à tous,

J'ai un problème de compréhension au niveau de l’exécution d'une transaction.

Je vous montre les deux scripts que j'ai exécuté :

Code:

1
2
3
4
5
6
7
8
9
10
11
BEGIN TRANSACTION                           -- je commence ma transaction
 
CREATE TABLE TEST(ID int NOT NULL)     -- je créé une table
DROP FROM TOTO WHERE titi = 4            -- erreur de syntaxe
 
IF (@@error <> 0)
   ROLLBACK TRANSACTION
ELSE
   COMMIT TRANSACTION
 
-- ma table TEST n'a pas été créée.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN TRANSACTION                               -- je commence ma transaction
 
CREATE TABLE TEST(ID int NOT NULL)        -- je créé une table
DELETE FROM TOTO WHERE titi = 4            -- je veux supprimer les informations d'une table qui n'existe pas
 
IF (@@error <> 0)
   ROLLBACK TRANSACTION
ELSE
   COMMIT TRANSACTION
END
 
-- la table TEST a bien été créée.

Je ne comprends pas pourquoi dans l'exemple 1 la table n'a pas été créée alors que dans l’exemple 2 la table a bien été créée.

Merci

Ci-dessous les explications du fait observé :

Cas 1
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 BEGIN TRANSACTION -- je commence ma transaction CREATE TABLE TEST(ID int NOT NULL) -- je créé une table DROP FROM TOTO WHERE titi = 4 -- erreur de syntaxe IF (@@ERROR <> 0) BEGIN PRINT 'Rollback imminent ! ' ROLLBACK TRANSACTION END ELSE BEGIN PRINT 'Rollback imminent ! ' COMMIT TRANSACTION END GO
Code:

1 2 3 -- vérifier le nombre de transactions en cours SELECT @@TRANCOUNT -- Résultat : 0 GO
Explication du "Cas 1"
- Aucun des 2 messages, rollback ou commit ci-dessus, n'a été affiché à l'écran. En effet l'instruction @@error n'a pas intercepté l'erreur !
- Cependant, un Rollback implicite a été tout de même effectué suite à l'erreur générée par l'instruction DDL (DROP ...)
- Le rollback implicite a eu pour effet l'annulation de la création de la table (@@trancout = 0). Ce qui explique pourquoi la table n'a donc pas été créée.

Cas 2
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 BEGIN TRANSACTION -- je commence ma transaction CREATE TABLE TEST(ID int NOT NULL) -- je créé une table DELETE FROM TOTO WHERE titi = 4 -- je veux supprimer les informations d''une table qui n'existe pas IF (@@error <> 0) BEGIN PRINT 'ROLLBACK imminent !' ROLLBACK TRANSACTION end ELSE BEGIN PRINT 'Commit imminent ! ' COMMIT TRANSACTION END GO
Code:

1 2 3 -- vérifier le nombre de transactions en cours SELECT @@TRANCOUNT -- résultat 1 !!!!!! GO
Explication du "Cas 2"
- Aucun des 2 messages, rollback ou commit ci-dessus, n'a été affiché à l'écran. En effet l'instruction @@error n'a pas intercepté l'erreur !
- Mais contrairement au cas 1, la transaction n'a été ni validée ni annulée ! La transaction reste ouverte (@@TRANCOUNT = 1) ! (situation bien plus dangereuse que le cas 1 )
- La table a été créée, mais comme la transaction n'a pas été validé. la table n'est visible que pour la session en cours ! Si tu ouvre une deuxième fenêtre de l’éditeur et tu essais d’accéder à la table, tu auras le message suivant :
' Msg*208, Niveau*16, État*1, Ligne*1 Nom d'objet 'temp' non valide'

CONCLUSION
L'instruction @@error <> 0 n'intercepte pas toutes les erreurs !
Sous SQL server 2000 nous n'avions pas d'autres choix pour gérer les erreurs !
Mais à partir de SQL Server 2005 , 2008 etc.., il faut utiliser TRY ... CATCH pour apporter une solution “robuste” et élégante à ce genre de problème ayant trait à la gestion des erreurs :

Solution pour "Cas 1"
Code:

1 2 3 4 5 6 7 8 9 10 11 12 BEGIN TRANSACTION -- je commence ma transaction BEGIN TRY CREATE TABLE TEST(ID int NOT NULL) -- je créé une table DROP FROM TOTO WHERE titi = 4 -- erreur de syntaxe END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION GO
Code:

1 2 3 -- Vérifier le nombre de transactions en cours SELECT @@TRANCOUNT -- résultat 0 GO
Solution pour Cas 2
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 BEGIN TRANSACTION -- je commence ma transaction CREATE TABLE TEST(ID int NOT NULL) -- je créé une table DELETE FROM TOTO WHERE titi = 4 -- je veux supprimer les informations d''une table qui n'existe pas IF (@@error <> 0) BEGIN PRINT 'ROLLBACK imminent !' ROLLBACK TRANSACTION end ELSE BEGIN PRINT 'Commit imminent ! ' COMMIT TRANSACTION END GO
Code:

1 2 3 -- vérifier le nombre de transactions en cours SELECT @@TRANCOUNT -- résultat 0 GO
A+

19/05/2011, 11h26
SQLpro

Il est surtout totalement anormale de mélanger DDL et DML dans une même procédures et en principe le DDL devrait être hors transaction, sauf cas particulier.

A +

Bonjour et merci de cette réponse très détaillée.

Mais lorsque j'exécute le script suivant j'ai un soucis :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 BEGIN TRANSACTION -- je commence ma transaction BEGIN TRY CREATE TABLE TEST(ID int NOT NULL) -- je créé une table DELETE FROM TOTO WHERE titi = 4 -- suppression d'un élément qui n'existe pas END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION GO
La première fois que je l’exécute, l’exécution du code s’arrête au niveau de

Code:

DELETE FROM TOTO WHERE titi = 4

avec l'erreur suivante : Msg*208, Niveau*16, État*1, Ligne*5
Nom d'objet 'TOTO' non valide.

A ce niveau là, la transaction ne s'est pas fini car si je fais sur la même page un DROP TABLE TEST , la commande réussit, Et un SELECT @@TRANCOUNT me renvoie 1.

Par contre si j'execute le script deux fois d'affilés ou plus, les fois suivantes la transaction se fini jusqu'au bout, ma table n'est pas créée et j'ai comme message : Commande(s) réussie(s).

Question subsidiaire : Y a t'il des opérations non 'ROLLBACKABLE' ?

Merci

19/05/2011, 11h51
le5ejumeau

Bonjour SQL pro,

En fait dans l'entreprise pour laquelle je travaille pour chaque mise à jour de notre base de données, on crée un script qui contient toutes les nouveautés (CREATE VIEW, ALTER TABLE, INSERT, ...)

Entre deux releases, nous avons donc un ensemble de scripts à passer chez chaque client.

L'idée est d'embarquer cet ensemble de scripts dans une transaction afin de revenir à l'état initial s'il y a un problème.

Citation:

[ ..... ]
La première fois que je l’exécute, l’exécution du code s’arrête au niveau de

Code:

DELETE FROM TOTO WHERE titi = 4

avec l'erreur suivante : Msg*208, Niveau*16, État*1, Ligne*5
Nom d'objet 'TOTO' non valide.
A ce niveau là, la transaction ne s'est pas fini car si je fais sur la même page un DROP TABLE TEST , la commande réussit, Et un SELECT @@TRANCOUNT me renvoie 1.

Par contre si j'execute le script deux fois d'affilés ou plus, les fois suivantes la transaction se fini jusqu'au bout, ma table n'est pas créée et j'ai comme message : Commande(s) réussie(s).

Question subsidiaire : Y a t'il des opérations non 'ROLLBACKABLE' ?

Merci

Effectivement le message apparaît lors de la première exécution du bloc T-SQL alors qu'il n'apparait pas lors de la 2ème exécution du bloc ! mais il n'y a jamais de mistère il y a toujours une explication.

Explication
Lors de la première exécution le message est dû à l'étape de compilation du bloc T-SQL SQL:StmtRecompile , chose qui ne produit pas lors de la 2ème exécution du bloc -SQL.
Code:

1 2 3 4 5 6 7 8 <Event id="166" name="SQL:StmtRecompile"> <Column id="55" name="IntegerData2">510</Column> <Column id="63" name="SqlHandle">02000000E752F0198F0461DB01E5A3043623E32D74FAC9B7</Column> <Column id="8" name="HostName">xxxxxxx</Column> <Column id="64" name="SessionLoginName">sa</Column> <Column id="1" name="TextData">DELETE FROM TOTO WHERE titi = 4; -- suppression d'un élément qui n'existe pas </Colonne> …...
Pour corriger ce problème du moins erratique, il faut rajouter l'instruction
SET XACT_ABORT ON

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SET XACT_ABORT ON ---- ligne à rajouter BEGIN TRANSACTION -- je commence ma transaction BEGIN TRY CREATE TABLE TEST(ID int NOT NULL); -- je créé une table DELETE FROM TOTO WHERE titi = 4; -- suppression d'un élément qui n'existe pas END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN PRINT 'ROLLBACK imminent !' ROLLBACK TRANSACTION; END END CATCH IF @@TRANCOUNT > 0 BEGIN PRINT 'COMMIT imminent !' COMMIT TRANSACTION END GO
A+

Citation:

Envoyé par le5ejumeau

Bonjour SQL pro,

En fait dans l'entreprise pour laquelle je travaille pour chaque mise à jour de notre base de données, on crée un script qui contient toutes les nouveautés (CREATE VIEW, ALTER TABLE, INSERT, ...)

Entre deux releases, nous avons donc un ensemble de scripts à passer chez chaque client.

L'idée est d'embarquer cet ensemble de scripts dans une transaction afin de revenir à l'état initial s'il y a un problème.

Certaines choses ne sont pas transactionnable (par exemple la gestion des utilisateurs, privilèges....) de plus certaines commandes ne peuvent pas faire partie du même lot (exemple une vue)... Et il faut distinguer ce qui est des données de ce qui est l'architecture de la base.

Pour les évolutions de schéma, il est plus simple de prendre une sauvegarde de la base avant le modif et mettre en place un trigger DDL sur événement ALL_DATABASE qui tracera automatiquement toutes ces modif d'architecture.

Exemple :
Code:

1 2 3 4 5 6 7 8 -- créer dans msdb une table de suivi : USE msdb; GO CREATE TABLE dbo.T_TRACK_DDL_EVENT_TDE (TDE_ID INT NOT NULL IDENTITY PRIMARY KEY, TDE_DATABASE sysname, TDE_DATA xml) GO
Code:

1 2 3 4 5 6 7 8 9 -- création d'un trigger DDL sur tous les événements de modification d'architecture de la base CREATE TRIGGER E_ALL_DDL ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS INSERT INTO msdb.dbo.T_TRACK_DDL_EVENT_TDE VALUES (DB_NAME(), EVENTDATA()); GO
Pourquoi en effet réinventer la roue ?

A +

20/05/2011, 10h04
le5ejumeau

Bonjour et merci à tous les deux pour vos explications très détaillées.

Je ne connaissais pas 'SET XACT_ABORT', et j'ai maintenant bien compris l'étape de compilation du bloc T-SQL

Pour les modifications de schéma, je vais me pencher sur la voie que me propose 'SQL pro'.

A +