Comportement étrange de la commande GO dans une transaction

Question

Si j'exécute ce qui suit :

CREATE TABLE t1  
    (a INT NOT NULL PRIMARY KEY);  
CREATE TABLE t2  
    (a INT NOT NULL REFERENCES t1(a));  

INSERT INTO t1 VALUES (1);  
INSERT INTO t1 VALUES (3);  
INSERT INTO t1 VALUES (4);  
INSERT INTO t1 VALUES (6);  
GO  

et après cela, j'exécute ceci :

SET XACT_ABORT ON; 

BEGIN TRANSACTION
INSERT INTO t2 VALUES (1)

INSERT INTO t2 VALUES (2) -- Foreign key error, entire transaction rolled back

INSERT INTO t2 VALUES (3)

COMMIT TRANSACTION

alors tout va bien ; la transaction est annulée parce que nous n'avons pas la valeur 2 dans t1 et par conséquent la table t2 est vide

Mais si j'ajoute un GO au milieu de cette transaction, la transaction n'est pas annulée et le numéro 3 est inséré dans la table t2. Voici le code :

SET XACT_ABORT ON; 

BEGIN TRANSACTION
INSERT INTO t2 VALUES (1)

INSERT INTO t2 VALUES (2) -- Foreign key error.  

GO  --this command breaks the transaction

INSERT INTO t2 VALUES (3)

COMMIT TRANSACTION

Je sais que GO n'est pas une instruction SQL mais plutôt une commande utilitaire de SQL Server Management Studio qui n'est pas réellement envoyée au serveur SQL.

C'est pourquoi, après avoir exécuté le dernier extrait de code ci-dessus (celui qui inclut la commande GO), je peux voir le numéro 3 dans le tableau t2.

J'ai utilisé SQL Server Management Studio

Answer 1

Quand SET XACT_ABORT est ON Si une instruction Transact-SQL soulève un une erreur d'exécution, la transaction entière est interrompue et annulée.

La transaction a été ouverte dans le premier lot fait sont automatiquement annulées en cas d'erreur.

Ensuite, SSMS voit qu'il y a un autre lot à traiter et déclenche

INSERT INTO t2 VALUES (3)

COMMIT TRANSACTION

La première transaction étant à présent clôturée, une nouvelle transaction de validation automatique est lancée. Et à la fin, il y a un COMMIT TRANSACTION ce qui entraîne une erreur.

Vous pouvez modifier le deuxième lot pour vérifier si la transaction du premier lot a été annulée sans cérémonie.

SET XACT_ABORT ON; 

BEGIN TRANSACTION
INSERT INTO t2 VALUES (1)

INSERT INTO t2 VALUES (2) -- Foreign key error.  

GO 

IF @@TRANCOUNT = 0
    BEGIN
    RAISERROR('Transaction closed',16,1);
    RETURN;
    END

INSERT INTO t2 VALUES (3)

COMMIT TRANSACTION

Vous pouvez également activer le mode SQLCMD et utiliser

:on error exit

Si vous souhaitez qu'aucun autre lot ne soit traité après une erreur.