When is it valid to use XACT_ABORT () in a script that is already using TRY / CATCH?

Question

When is it valid to use XACT_ABORT () in a script that is already using TRY / CATCH?

Navigation

#1 by (2 votes)

0

Assuming the following code:

/*
DROP TABLE #Teste;
CREATE TABLE #Teste(id INT);
INSERT INTO #Teste VALUES(1);
CREATE TABLE LogErros(id INT IDENTITY(1,1) PRIMARY KEY, nomeTransaction VARCHAR(100), errorMessage VARCHAR(500), horaErro DATETIME)
DROP TABLE LogErros
SELECT * FROM LogErros
*/


SET XACT_ABORT ON
DECLARE @TranName VARCHAR(20) = 'TR_Teste';

BEGIN TRANSACTION @TranName
BEGIN TRY
    INSERT INTO #Teste VALUES(2);
    INSERT INTO #Teste VALUES(3);
    --INSERT INTO #Teste VALUES('Oi');

    COMMIT TRANSACTION @TranName
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION @TranName
    INSERT INTO LogErros VALUES(@TranName, CAST(ERROR_LINE() as VARCHAR) + ' - ' + ERROR_MESSAGE(), GETDATE());
END CATCH

Is it worth using XACT_ABORT() in such cases?

I saw in the international stackoverflow that the previous function can handle cases where the procedure suffers timeout eg problem that TRY/CATCH can not handle. Thinking about that case, then the use is valid, correct?

But I also saw people saying that XACT_ABORT() was obsolete after TRY/CATCH , is this true? So can TRY/CATCH handle all the errors alone?

sql sql-server

asked by anonymous 31.07.2018 / 14:40

1 answer

Problem working with resolution with Bootstrap C # - How to print all the values of an array with the command Console.WriteLine (???) ;?

score 2 · Accepted Answer

`TRY CATCH`

The TRY CATCH checks whether the code inside block TRY was successful, otherwise the execution is transferred to block CATCH and the error handling code is executed. But, it has a limitation that must be taken into account:

try catch does not catch compilation errors that happen within the same scope!

What can be a problem when your code within the block TRY plus simple query runs store procedures or triggers .

`SET XACT_ABORT ON`

Using SET XACT_ABORT ON at the beginning of procedure activates an option that is disabled by default, but is another security option, in cases that TRY CATCH can not handle, such as the above above.

With XACT_ABORT ON any open transaction is rolled back and immediately execution is aborted, which makes this option a more reliable error handling for transaction handling, in particular for the default transaction behavior where there are several situations where execution can be aborted without any transaction being rolled back, even with TRY CATCH .

For good error handling, it is important to use TRY CATCH and SET XACT_ABORT , both of which work as a complement.

I leave the following articles as a reference reading: