Cannot roll back subtransaction. No transaction or savepoint of that name was found

Question

i have a loop while in sql which do something as it

    begin tran one

    do some inserts in others tables

      --start loop
     begin tran two
      --do something
      begin try
--if something fail then a trigger does rollback and this return a error (and this goes to catch), then don't i need do the rollbak in catch? this could not be dissable because this is working on production
      --something finished ok 
      commit tran two
      end try
      begin catch
     rollback tran two
      end catch

    --finished loop
    commit


----------

i got this error

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

begin tran one
begin tran two

rollback tran two

doing this code i get this:

Cannot roll back two. No transaction or savepoint of that name was found.

I only want the subquery to rollback the second loop and continue with others records.

Answer 1

Operator rollback rolls back all transaction, for roll back only second loop you you must use savepoints:

  begin tran one

-- do some inserts in others tables

  --start loop
  save tran two -- begin tran two

  --do something
  begin try
     update product set id = 1 --if something fail then a trigger does rollback and this return a error (and this goes to catch), then don't i need do the rollbak in catch? this could not be dissable because this is working on production

  --something finished ok 
  commit tran two
  end try
  begin catch

    rollback tran two
  end catch

--finished loop
commit

trigger example:

create table product (id int)
GO  
create trigger product_trigger on product for update
as
  set xact_abort off

  if (select count(*) from inserted i join product p on i.id=p.id)=0 begin 
    if (@@trancount>0) begin 
      /* rollback */ 
      raiserror('product does not exist', 16, 1) 
    end 
  end

Answer 2

In my case, was my code was calling, thru an EF DbContext method, a SQL Server stored procedure, which contained a non-nested transaction.

Since, as @NotMe has already pointed-out, that "there is no such as a nested transaction in SQL Server", I began wondering whether my process was really transaction-nestingless.

Suspecting, that my DbContext had some guilt, I started checking on DbContext options, until DbContext.Configuration.EnsureTransactionsForFunctionsAndCommands = True caught my attention.

So, as soon as I changed it value to True, everything worked successfully.

MyDbContext.Configuration.EnsureTransactionsForFunctionsAndCommands = false;

What happened?

Well, in my opinion, EF's ObjectContext.ExecuteFunction method was managing its own outer transaction as a wrapper to my stored procedure's inner transaction, so, when my stored procedure's ROLLBACK TRAN was hit, there was no pending transaction when EF's COMMIT/ROLLBACK code was hit.

Oddly enough, while gathering some references on EnsureTransactionsForFunctionsAndCommands property, I found that this default behaviour is due to one of the worst (in my opinion) EF team's decision ever, since it collides diretly with every ROLLBACK TRAN inside a T-SQL script.

For further details on EF, check insightfull SO's QA at EF6 wraps every single stored procedure call in its own transaction. How to prevent this?

Basically, everyone should check @@trancount > 0 before issuing a ROLLBACK command, whether named or not, specially inside stored procedure.

CREATE PROCEDURE Proc1 AS
BEGIN
    BEGIN TRAN
    EXEC Proc2
    IF(@@trancount > 0)
        COMMIT TRAN
END

CREATE PROCEDURE Proc2 AS
BEGIN
    BEGIN TRAN
    ROLLBACK TRAN
END

For better awareness about Microsoft SQL Server's nested transactions, I would suggest reading the following article Be careful using ROLLBACK on nested transaction in SQL Server!

Hope it helps someone :-)