Skip to main content
Answer

Nested transaction can cause async transaction state

  • September 11, 2024
  • 1 reply
  • 47 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,

This topic has been closed for replies.

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,