XACT_ABORT not rolling back a transaction on SQL Server 2012

Question

Here is a simple test I coded.

SET XACT_ABORT ON

BEGIN TRANSACTION
GO

CREATE PROCEDURE [usp_MyTest1]
AS
GO

CREATE PROCEDURE [usp_MyTest1]
AS
GO

CREATE PROCEDURE [usp_MyTest2]
AS
GO

COMMIT TRANSACTION

It was my understanding that since I have SET XACT_ABORT ON, when the second create procedure fails, the entire transaction will roll back. Instead, only things proceeding the error (the first create procedure) are rolled back and the last create procedure executes just fine. I then get a message saying The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

I've seen people have an issue with RAISEERROR, but the documentation for XACT_ABORT says that RAISEERROR does not honnor XACT_ABORT. But this doesn't mention anything about an error such as creating a duplicate procedure not honoring XACT_ABORT.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR.

This is with SQL Server 2012, if that is relevant.

See this answer by Remus Rusanu:stackoverflow.com/questions/34178270/… — TheGameiswar
See this as well for more details:sommarskog.se/error-handling-I.html#scope-abortion — TheGameiswar
Transactions are run time constructs, not lexical constructs. When the transaction is rolled back, then create of usp_MyTest2 proceeds in its own transaction, presumably with auto-commit. The transaction does not span from create to commit/rollback from analyzing the text. Also, xact-abort will abort the batch, but go is a batch separator, so last create is in its own batch. — Shannon Severance

Martin Smith Martin Smith · Accepted Answer · 2018-01-08T21:04:39

It does rollback the transaction.

You can see this quite clearly with

SET XACT_ABORT ON

BEGIN TRANSACTION
GO
SELECT @@TRANCOUNT, 'Point1'
GO
CREATE PROCEDURE [usp_MyTest1] AS
GO
SELECT @@TRANCOUNT, 'Point2'
GO
CREATE PROCEDURE [usp_MyTest1] AS
GO
/*
  The earlier transaction has now been rolled back and 
  now running outside an explicit transaction. @@TRANCOUNT is 0
 */
SELECT @@TRANCOUNT, 'Point3'
GO
CREATE PROCEDURE [usp_MyTest2] AS
GO
SELECT @@TRANCOUNT, 'Point4'
GO
/*Nothing to commit so error*/
COMMIT TRANSACTION

Which returns

By the time you get to point 3 the transaction has been rolled back (including the initial creation of usp_MyTest1) and the creation of usp_MyTest2 happens in a new auto commit transaction. So the end result is the database contains usp_MyTest2 and not usp_MyTest1 assuming neither were there at the start.

Which is why you also see the error at the end

Msg 3902, Level 16, State 1, Line 19 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

To terminate the script execution after an error has occurred and the transaction is rolled back (instead of ploughing on and executing subsequent batches) the most reliable method is to enable sqlcmd mode in SSMS and add this to the top of the script :on error exit

XACT_ABORT not rolling back a transaction on SQL Server 2012

1 Answers