NServiceBus handler and stored procedure call. Should I use SET XACT_ABORT ON or TRY CATCH?

Question

I want to call my ms sql stored procedure from nservicebus handler and I wonder if I still need to use something like

CREATE PROCEDURE [dbo].[proc_fd_SomeEntitySyncRawWithStage]

AS BEGIN

BEGIN TRY

BEGIN TRANSACTION

-- Batch of inserts and updates that I want to keep transactional

COMMIT TRANSACTION;

END TRY BEGIN CATCH

ROLLBACK TRANSACTION;

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();


RAISERROR (@ErrorMessage, 
           @ErrorSeverity, 
           @ErrorState
           );

END CATCH; END

Do I really need all this TRY BEGIN TRAN... CATCH ROLLBACK .. or SET XACT_ABORT ON... stuff to rollback my transaction or NServiceBus will rollback it in case of any sql exception in message handler ?

P.S. Keeping in mind that:

"If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error"

and

"When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing."

Reading the docu related to SET XACT_ABORT it should be left to ON. From the NServiceBus point of view what happens is that the message handling processing is wrapped in a transaction (that depending on he transport can be distributed) meaning that each error will rollback the TX. Given that I'd say that the BEGIN / END and TRY / CATCH statements are not required. Not being a SQL guru I sincerely do not know what happens to batch processing. — Mauro Servienti

Alexey Zimarev Alexey Zimarev · Accepted Answer · 2015-02-02T09:54:50

Normally, you should always wrap your procedure calls in a transaction, would you use ORM or ADO.NET. Wht ADO.NET you would do something like

var ts = myConnection.BeginTransaction();

and then call your procedure within a try-catch block.

Further depends on your logic. If you procedure could fail because of some logic, you will need to have a return code and throw an exception based on that. If your procedure just fails with SQL exception, the try-catch block will handle it.

In the catch block you need to rollback the transaction.

If you re-throw your exception in the catch block, the message will be retried by FLR and if configured - by SLR. Usually, this helps when having deadlicks in the database, FLR normally handles this.

NServiceBus handler and stored procedure call. Should I use SET XACT_ABORT ON or TRY CATCH?

1 Answers