Re: help - transaction control

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 09:46:08 -0400

Roger,

Yes transactions are nested but will always depend on the outer most
transaction to successfully commit before any of the inner ones are
committed. If a Rollback is issued anywhere along the way ALL transactions
are immediately rolled back. This is be design and is how it has always
worked. So that is why it is imperative that before you issue a Commit or
Rollback you check the @@TRANCOUNT variable to see if there is an open tran
and avoid these errors.

IF @@TRANCOUNT > 0
        COMMIT TRAN

or

IF @@TRANCOUNT > 0
    ROLLBACK TRAN 

-- 
Andrew J. Kelly  SQL MVP
"roger" <xrsr@rogerware.com> wrote in message
news:Xns9525D7095B374rsrrogerwarecom@216.148.227.77...
>
> I need to create (lots of) stored procedures that
> operate transactionally, but may call each other or
> be called as part of a larger operation.
>
> My understanding of the way that transactions nest
> is that I should be able to have each SP
> begin and commit a transaction within itself,
> and if the SP happens to be called from some context
> that has begun a transaction, then the SP would participate
> in that transaction context.
> And that's exactly what I want.
>
> Like here's a generic sort of structure of an SP then:
>
>  drop procedure trans_test
>  go
>
>  create procedure trans_test
>  as
>  begin
> begin tran
> print 'trancount 1= ' + str(@@trancount)
>     -- do stuff ...
>     if @@error <> 0
>       goto error -- something wrong, abort the transaction
>     -- everything OK, commit the transaction, or decrement trancount
> commit tran
> print 'trancount 2= ' + str(@@trancount)
> return 0
>  error:
> print 'error trancount 1= ' + str(@@trancount)
> rollback tran
> print 'error trancount 2= ' + str(@@trancount)
>     return -1
>  end
>  go
>
>  begin tran
>  declare @status int
>  exec @status = trans_test
>  if @status = 0 begin
> print 'commit 1'
> commit tran
>  end
>
>
> And this seems to work right if trans_test does its commit.
> But, if instead it takes the goto error path and rolls back
> the transaction, then I get the following messages printed via isqlw
>
>  trancount 1=          2
>  error trancount 1=          2
>  Server: Msg 266, Level 16, State 2, Procedure trans_test, Line 16
>  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
>  TRANSACTION statement is missing. Previous count = 1, current count = 0.
>  error trancount 2=          0
>
> As far as the transaction handling goes, it does seem
> that my SP is doing the right thing, but what's with the messages?
>
> I understand that error handling is problematic with T-SQL, but
> I think I'm working within its capabilities here.
>
> Any ideas, or tips?
>
> Thanks
>
>

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: help - transaction control
    ... >transaction to successfully commit before any of the ... If a Rollback is issued anywhere along the ... > ROLLBACK TRAN ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL SERVER Rollback Problems
    ... It will not be fully committed until you issue a COMMIT TRAN. ... If your sp had an error or was manually stopped the transaction is still ... issue the ROLLBACK ALL operations are rolled back up until that first BEGIN ... > execution. ...
    (microsoft.public.sqlserver.programming)
  • Re: Named Transactions
    ... You cannot do rollback using a transaction name to a BEGIN ... You have to do such rollback to a save point, created with SAVE TRAN. ... > My problem is not that 'rollback tran b' rolls the whole of transactions ...
    (microsoft.public.sqlserver.programming)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)