Add support for SET XACT_ABORT

[BOB10]

Currently Fabric does not support SET XACT_ABORT ON.

We request to add this support to reduce breaking changes when migrating and to improve transaction support for the scenario below

This results in a breaking change in how transactions are handled and makes Fabric behave differently to other editions like

• SqlPool (Dedicated)
• SqlDbEngine

As an example consider this Sproc below

CREATE PROC usp_TranTest

AS

BEGIN

      BEGIN TRY 

             BEGIN TRAN

                    select * from table_that_doesnt_exist

                    --Do other Work

             COMMIT TRAN

      END TRY

      BEGIN CATCH

                    ROLLBACK TRAN

                    print 'error'

      END CATCH

      print @@TRANCOUNT

END

GO

exec usp_TranTest

This will result in the following behaviour

Sql Pool (Dedicated): Rollback and zero trancount

Fabric: No rollback and tran leak

On Premise SqlDbEngine: No rollback and tran leak

On Premise SqlDbEngine with SET XACT_ABORT ON: Rollback and zero trancount

So the ask for Fabric DW is when migrating from SqlPool we need one of three things:

1. Make engine beghave same way as SqlPool
2. or
3. Add the option of SET XACT_ABORT, so we can use this for backwards compatibility with SqlPool behaviour.

Or

1. Both