CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlstored-procedures
Paul Fryer
Paul Fryer

Reputation: 9527

Where is the Begin/Commit mismatch in this stored procedure?

I'm getting the following error:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

When I try to execute this stored procedure:

create procedure [dbo].[SynchCustomerSubscriptions] (
  @subscriptions Subscriptions readonly )
as

begin transaction

begin tran SynchTransaction
  begin try
    delete s
    from Subscription s (nolock)
    where s.Customer in (
      select Customer
      from @subscriptions)
    exec AddSubscriptions
      @subscriptions
  end  try
  begin catch
    rollback tran SynchTransaction
    return
  end  catch
commit tran SynchTransaction

Does anyone spot the problem?

Upvotes: 0

Views: 3000

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294187

Starting a named transaction is actually very bad inside stored procedures. It is not possible to rollback just the named transaction, as you apparently try, if the procedure is called inside the scope of another transaction, see MSDN:

Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error.

What you probably want is a savepoint, which is a different kind of beast. If you want to mix nested transaction, savepoints and exception try/catch blocks, things are bit more complex. Is better to use a pattern like the one from this article Exception handling and nested transactions, which considers current @@TRANCOUNT and exception XACT_STATE:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

Upvotes: 0

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

Try this...

create procedure [dbo].[SynchCustomerSubscriptions] (    
  @subscriptions Subscriptions readonly )    
as    

begin transaction SynchTransaction    

begin tran    
  begin try    
    delete s    
    from Subscription s (nolock)    
    where s.Customer in (    
      select Customer    
      from @subscriptions)    
    exec AddSubscriptions    
      @subscriptions    
  end  try    
  begin catch    
    rollback tran SynchTransaction    
    return    
  end  catch    
end tran
commit transaction SynchTransaction

Upvotes: 0

Mark Ransom
Mark Ransom

Reputation: 308101

begin transaction 

begin tran SynchTransaction

Sometimes you look at this stuff too long, and the simplest things escape you.

Upvotes: 3

Related Questions