Skip to main content
Solved

Nested transaction can cause async transaction state

  • September 11, 2024
  • 1 reply
  • 33 views

Dave Bieleveld Starcode
Vanguard
Forum|alt.badge.img+2

Let's consider this code 

    begin try
    begin transaction;

    --control_proc_id:      convert_camel_case_to_snake_case
    --template_id:          convert_camel_case_to_snake_case
    --prog_object_item_id:  convert_camel_case_to_snake_case
    --template_description: 

    declare @to_tag_id big_text = dbo.camel_case_to_snake_case(@input)

    exec  task_rename_tag
          @from_tag_id = @input
    ,     @to_tag_id   = @to_tag_id

    if @@trancount > 0 -- ALWAYS CHECK FOR ACTIVE TRANSACTIONS

    commit transaction;
    end try
    begin catch
        if @@trancount > 0
        begin
            rollback transaction;
        end;

        ; -- Throw original exception
        throw;
    end catch;

When the task “task_rename_tag” invokes a rollback, the calling procedure will try to commit the transaction when there is no guard that checks @@trancount.
 

 

I think it would be always a best practice to always check trancount, to prevent illegal commit or rollback. The “atomic” templates that are used can be easily expanded with the safeguard if @@trancount > 0

 

Is there any reason it isn't implemented, should I treat transactions differently?

Best answer by Dave Bieleveld Starcode

NVM. Regarding the problem, the nested procedure should use savepoints. Using the guard won't prevent the error,

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

1 reply

Dave Bieleveld Starcode
Vanguard
Forum|alt.badge.img+2

NVM. Regarding the problem, the nested procedure should use savepoints. Using the guard won't prevent the error,


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings