Question with XACT_STATE() value inside a Catch block

Question

I've got this doubt about the value provided on an error for the xact_state function. This is my code:

IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
    DROP TABLE dbo.Products

CREATE TABLE Products (
    ProductID int  NOT NULL PRIMARY KEY,
    ProductName nvarchar(100) null,
    UnitPrice decimal(18,2) not null,
    UnitsInStock int not null,
    UnitsOnOrder int null,
    DateIn datetime
);
GO

create or alter procedure InsertProduct(
    @ProductId int,
    @ProductName nvarchar(100),
    @UnitPrice decimal(18,2),
    @UnitsInStock int,
    @UnitsOnOrder int
)
as
begin
    begin try
        begin transaction

        insert  into Products(ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, DateIn)
            values(@ProductID, @ProductName, @UnitPrice, @UnitsInStock, @UnitsOnOrder, GETDATE())

        commit transaction 
    end try
    begin catch
        print N'value of XACT_STATE()' + convert(nvarchar(20),XACT_STATE());
        if XACT_STATE() = 1
        begin 
            print N'Rollback Necessary' 
            rollback transaction
        end;
        throw 51000, N'There is an error with the application',1    

    end catch

end
GO

exec InsertProduct @ProductID = 1, @ProductName = 'B', @UnitPrice = 10.0, @UnitsInStock = 2, @UnitsOnOrder = 1

select * from Products

-- Generating an error
exec InsertProduct @ProductID = 1, @ProductName = 'C', @UnitPrice = 10.0, @UnitsInStock = 2, @UnitsOnOrder = 1

select * from Products

In the end I've received this error message:

My question is, why the value for this function is 1 and not -1 given that the second insert is uncommittable for the violation on the primary key definition.

If you want to read more on error handling within transactions I strongly suggest you study what Erland Sommerskog has to say on this subject: http://www.sommarskog.se/error_handling/Part1.html — Thailo, Dec 16 '19 at 22:19

score 2 · Answer 1 · answered Dec 16 '19 at 21:44

The answer is that a

Violation of PRIMARY KEY constraint

error does not cause an uncommittable transaction. The constraint prevents the insert from happening and therefore the transaction is actually in a perfectly happy state.

If you want that error to cause an uncommittable transaction then add at the start of your stored procedure;

SET XACT_ABORT ON;

which will ensure that all errors result in an uncommittable transaction.

Question with XACT_STATE() value inside a Catch block

1 Answers1