CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqltransactions
Stanislav Polimac
Stanislav Polimac

Reputation: 71

T-SQL transaction encounters errors but is not canceled

By definition T-SQL transaction should be cancelled if any statement inside it results with error. However, I stumbled upon the situation where transaction passes with an error happening in the middle of it.

Example environment setup:

--test tables:
IF OBJECT_ID('t2', 'U') IS NOT NULL  
    DROP TABLE t2;  
GO      
IF OBJECT_ID('t1', 'U') IS NOT NULL  
    DROP TABLE t1;  
GO     

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

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

Transaction test:

BEGIN TRANSACTION
     INSERT INTO t2 VALUES (1); 
     INSERT INTO t2 VALUES (2); -- Foreign key error will be thrown.  
     INSERT INTO t2 VALUES (3);  

COMMIT TRANSACTION;

This should have been cancelled because of the foreign key error, but it isn't:

SELECT * FROM t2;

results with two rows returned - values 1 and 3.

Adding SET XACT_ABORT ON does the job, but how is it possible that entire transaction holds up when there's an error in the middle of it?

Upvotes: 0

Views: 369

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Depending on the specific error, the T-SQL batch will continue after an error without XACT_ABORT ON. I recommend TRY/CATCH with IF @@TRANCOUNT > 0 ROLLBACK; in the CATCH BLOCK plus XACT_ABORT ON to make sure the rollback occurs immediately in the case of a query cancel or client timeout (which prevents the CATCH block from being executed):

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO t2 VALUES (1); 
    INSERT INTO t2 VALUES (2); -- Foreign key error will be thrown.  
    INSERT INTO t2 VALUES (3);  
    COMMIT TRANSACTION;  
END TRY
BEGIN CATCH 
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH;

Upvotes: 2

Related Questions