9

We have recently been parachuted to a new ETL project with very bad code. I have in my hands a query with 700 rows and all sort of update.

I would like to debug it with SET XACT_ABORT ON; and the goal is to rollback everything if only one transaction fails.

But I find several way to archive it on StackOverflow like this:

BEGIN TRANSACTION;
BEGIN TRY

-- Multiple sql statements goes here

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

or this:

BEGIN TRY
BEGIN TRANSACTION

-- Multiple sql statements goes here

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT(ERROR_MESSAGE())
ROLLBACK TRANSACTION
END CATCH

and none of these uses SET XACT_ABORT ON;.

I don't understand, is SET XACT_ABORT ON the same as using BEGIN TRY BEGIN TRANSACTION?

Can I just use:

SET XACT_ABORT ON;

-- Multiple sql statements goes here

and get ridof all the:

BEGIN TRANSACTION;
BEGIN TRY

?

And also, should I use BEGIN TRANSACTION and then BEGIN TRY or the other way around?

Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113

3 Answers3

3

It is not the same. It decides when errors are thrown.

You should always use SET XACT_ABORT ON, because it is more consistent; almost always, an error will stop execution and throw an error. Else, half things throw errors and the other half continue execution.

There is a great article about this whole subject on Erland Sommarskog's site, and if you go at this point you will see a table which describes this strange behaviour. In conclusion, I recommend the General Pattern for Error Handling, as it is very well documented as well as provide you the opportunity to tweak it according to its own documentation.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • You are saying that XACT_ABORT ON will cause all errors to hit the CATCH block? – variable Feb 01 '22 at 05:53
  • Not all, but more. Click the link "this point" of my answer to see a full list. – George Menoutis Feb 01 '22 at 08:12
  • When SET XACT_ABORT is set to ON, then what purpose does the try/catch serve? Looking at the table you shared (from that link), when using TRY CATCH, for the CATCHABLE column it doesn't matter whether XACT_ABORT is ON or OFF. – variable Feb 01 '22 at 08:16
3

If you have XACT_ABORT ON there is no need to manually catch any errors, unless you are doing error logging. XACT_ABORT will cause all errors to doom the transaction and roll it back.

All you need is

SET XACT_ABORT ON;
BEGIN TRAN;
--do stuff
COMMIT;

If there is only one statement then you don't even need BEGIN TRAN; and COMMIT;

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I tested it @Charlieface and if I don't use `BEGIN TRAN;` and `COMMIT;` the table is created in my DB and data are inserted. this means that `SET XACT_ABORT ON;` needs `BEGIN TRAN;` and `COMMIT;` – Francesco Mantovani Nov 08 '21 at 12:22
  • You misunderstand: `BEGIN TRAN` `COMMIT` is needed if you want atomicity between two statements. `XACT_ABORT ON` means that an opened transaction is automatically aborted in event of an error. So *if* you use a transaction then you should use `XACT_ABORT`. But a single statement doesn't need a transaction, it has its own one anyway – Charlieface Nov 08 '21 at 13:30
2

Thank you for the resources @George Menoutis.

I post here my practical solution:

SET XACT_ABORT ON;
BEGIN TRY
      BEGIN TRANSACTION;


      -- Multiple sql statements goes here


      COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
      THROW;
END CATCH;
GO

SET XACT_ABORT OFF;
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113