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

Asked

Viewed 916 times

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

The use of XACT_ABORT() in such cases?

I saw in international stackoverflow that the previous function manages to deal with cases where the previous process suffers timeout for example, problem that the TRY/CATCH can’t treat (I may have misunderstood). Thinking about this case, then the use is valid, correct?

But I also saw people saying that XACT_ABORT() became obsolete after the TRY/CATCH, Is that true? So, the TRY/CATCH can handle all mistakes on its own?

sql sql-server transactions

  • I don’t have time to go into more detail about this, but I recommend this one reading ;)

  • Great reading, @Tuxpilgrim, if you want to earn 10 points, if no one answers the question, you can turn it into a question that I accept.

  • If later someone doesn’t answer I’ll do it!

1 answer

2


TRY CATCH

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

The try catch does not capture build errors that happen within the same scope!

What may be a problem when your code inside the block TRY besides query simple runs store procedures or triggers.

SET XACT_ABORT ON

Use the SET XACT_ABORT ON at the beginning of Procedure activates an option that is disabled by default, but is more a security option, in cases that the TRY CATCH can’t handle it, like the one above.

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

For a good handling of errors, it is important to use the TRY CATCH and of SET XACT_ABORT, and the two end up working as a complement.

I leave as reference reading the following articles:

Browser other questions tagged sql sql-server transactions

You are not signed in. Login or sign up in order to post.