Christian Christian - 5 months ago 13
SQL Question

Strange behavior of GO command within a transaction

If I ran the following:

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


and after that I run this:

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


then anything is ok; the transaction is rolled back because we don't have value 2 in t1 and as a result table t2 is empty

But if I add a
GO
command in the middle of this transaction then the transaction is not rolled back and number 2 is inserted into table t2. Here is the 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


I know that
GO
is not a sql statement but rather a SQL Server Management Studio utility command that is not actually sent to the SQL server.

So, why after running the last code snippet above (the one that includes GO command) I can see number 2 in table t2

I used SQL Server Management Studio

Answer

The transaction opened in the first batch does get automatically rolled back on error.

Then SSMS sees there is another batch to process and fires off

INSERT INTO t2 VALUES (3)

COMMIT TRANSACTION

As the first transaction is now closed this starts a new auto commit transaction. And at the end there is an unexpected COMMIT TRANSACTION so that raises an error.

You can alter the second batch to check if the transaction in the first batch was unceremoniously rolled back.

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

Or you can turn on SQLCMD mode and use

:on error exit

If you want no further batches to be processed after an error.

enter image description here

Comments