SQL Server Rollback Transaction From Error Outside Transaction

Question

I have a TRY CATCH statement, where there is some code in a transaction in the TRY, and other code outside of the transaction in the TRY. If the code outside the transction (still in TRY) throws an error, my transaction does not get rolled back in the CATCH. If I, however, remove the code outside of the transaction, if there is an error the transaction will get rolled back. Why does this behave this way? SQL Server 2014.

My Code. If Remote proc throws error, the transaction does not get rolled back:

BEGIN  try
begin transaction

  INSERT INTO bos_south_florida_job_map (job_id, original_job_id, 
created_date, updated_date,completed_status_sent_ind, 
assigned_status_sent_ind, status_prev)
VALUES (9999, '1234', getdate(), getdate(),0,0,'CREATED');


COMMIT TRANSACTION

declare @sql varchar(max)

set @sql = ''

select @sql = '
   declare @error1 varchar(255),
   @error2 varchar(255),
   @error3 varchar(255)

   Exec NEXTGEN.DBO.wbAf_ConfirmDispatchedReservation ''AFFWEB'', ''A10596'', ''Admin'', ''Admin'', '''+cast(preassignedsubconcode as varchar(100))+''', '''+cast('1234' as varchar(20))+''', '''+convert(char(23),ISNULL(ScheduledDispatchDateTime,''),121)+''', '+cast('1234' as varchar(12))+',null,@error1 output,@error2 output,@error3 output

   if @error1 is not null or @error2 is not null or @error3 is not null
   begin
        set @error1 = @error2 + '' '' + @error3 + '' '' + @error1
        RAISERROR (@error1, 16, 1)
   end
   '
from [BCCUATWSQL290].NEXTGEN.DBO.tbRideResCurDispatch
where resno = '35002616'

   exec(@sql) at [BCCUATWSQL290]

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0  
ROLLBACK TRANSACTION

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

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

INSERT INTO bos_south_florida_error_log (original_job_id, [action], 
error_datetime, [error_message])
SELECT
    '1234',
    'CREATE_JOB',
    GETDATE(),
    'Msg: ' + ISNULL(CONVERT(VARCHAR, ERROR_NUMBER()), 'N/A') + ', Level: ' + ISNULL(CONVERT(VARCHAR, @ErrorSeverity), 'N/A') + ', Line: ' + ISNULL(CONVERT(VARCHAR, ERROR_LINE()), 'N/A') + ', Error: ' + ISNULL(@ErrorMessage, 'N/A')


END CATCH

This will rollback the transaction:

BEGIN  try
begin transaction

  INSERT INTO bos_south_florida_job_map (job_id, original_job_id, 
created_date, updated_date,completed_status_sent_ind, 
assigned_status_sent_ind, status_prev)
VALUES (9999, '1234', getdate(), getdate(),0,0,'CREATED');

RAISERROR ('BLAH', 16, 1)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0  
ROLLBACK TRANSACTION

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

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

INSERT INTO bos_south_florida_error_log (original_job_id, [action], 
error_datetime, [error_message])
SELECT
    '1234',
    'CREATE_JOB',
    GETDATE(),
    'Msg: ' + ISNULL(CONVERT(VARCHAR, ERROR_NUMBER()), 'N/A') + ', Level: ' + ISNULL(CONVERT(VARCHAR, @ErrorSeverity), 'N/A') + ', Line: ' + ISNULL(CONVERT(VARCHAR, ERROR_LINE()), 'N/A') + ', Error: ' + ISNULL(@ErrorMessage, 'N/A')


END CATCH

Chris F Carroll Chris F Carroll · Accepted Answer · 2018-10-09T10:33:36

Recall that every Begin Transaction increases @@Trancount by 1 and every Commit decreases it by 1.

In your first example, the transaction is already committed and @@TranCount is reduced to zero before you throw the error, so in the Catch clause, @@Trancount will be zero. This is good. A committed transaction is already history, it can't be rolled back.

In your second example when you raise the error after Begin but before Commit, then @@TranCount is 1 when you reach the Catch clause, and you rollback.

Presumably the behaviour you want is that an error in the remote call should cause a rollback? Which you can achieve by moving the Commit to after the remote call, making it the very last statement before End Try.

Note however that cross-server transactions are relatively expensive, and require MS DTC to be running on both servers. DBAs may frown upon doing that on heavily-loaded servers.

Btw, a transaction around a single insert is usually pointless. Normal behaviour is 'autocommit' mode, which means insert/update/delete statements are 'bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement'

SQL Server Rollback Transaction From Error Outside Transaction

1 Answers