Discussion :

[le5ejumeau]

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 :

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

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 :

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

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

[hmira]

Ci-dessous les explications du fait observé :

Cas 1

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
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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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

Solution pour Cas 2

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
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 :

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

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

A+

[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 +

[le5ejumeau]

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 :

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

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 :

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

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

[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.

[SQLpro]

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 :

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

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 :

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

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 +

[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 +

[hmira]

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

Code :

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

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 :

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

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 :

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
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+