help - transaction control

From: roger (xrsr_at_rogerware.com)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 04:05:36 GMT

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: [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)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... delayed allocation, starting a new transaction could to happen a lot to ...
    (Linux-Kernel)
  • Re: [PATCH 2/2] improve ext3 fsync batching
    ... array than it takes to complete the transaction. ... when commit times go up to seconds? ... Transactions on that busier drive would take longer, we would sleep ... longer which would allow us to batch up more into one transaction. ...
    (Linux-Kernel)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... How would you like to assert for inode being freed? ...
    (Linux-Kernel)
Loading