Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    9

    Unanswered: Does Sybase support nested transaction?

    I have the follow 3 stored procedures
    SP1, SP2, SP3

    Both SP1, SP2 and SP3 have their own Begin Transaction. Commit Transaction and Rollback.

    SP1 and SP2 is wrapped into SP3.
    The structure may like
    SP3:
    Begin Transaction
    call SP1
    call SP2

    commit

    On error
    rollback

    I would like to know if SP1 is run successfully, i.e. it is self-committed in SP1, if there is error on running SP2, then the rollback action of SP3 can rollback both SP1 and SP2?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353

    Re: Does Sybase support nested transaction?

    Originally posted by ywchan
    I have the follow 3 stored procedures
    SP1, SP2, SP3

    Both SP1, SP2 and SP3 have their own Begin Transaction. Commit Transaction and Rollback.

    SP1 and SP2 is wrapped into SP3.
    The structure may like
    SP3:
    Begin Transaction
    call SP1
    call SP2

    commit

    On error
    rollback

    I would like to know if SP1 is run successfully, i.e. it is self-committed in SP1, if there is error on running SP2, then the rollback action of SP3 can rollback both SP1 and SP2?

    Thanks
    Yes, Sybase supports nested transactions. However, a ROLLBACK will rollback to the previous BEGIN TRAN, not to the outermost
    BEGIN TRAN.
    Personally I tend to return non-0 values from stored procedures if
    they fail, so that I do things like this:

    create proc foo
    ( @P1, .... )
    as
    declare ...

    begin tran
    save tran foo_tran

    ... do some stuff

    @rc = exec some_other_proc
    if @rc != 0
    begin
    rollback tran foo_tran
    commit tran
    return @rc
    end
    ...
    -- if all is well
    commit tran foo_tran

    return 0
    --END

    If all your procs adhere to this layout you can nest them in any order and they will behave correctly.

    Michael

  3. #3
    Join Date
    Jan 2004
    Posts
    19
    Please correct me if i am wrong but i guess rollback tran will rollback the transaction to the outermost open tran(will reduce the @@trancount to 0 ,whatever it's value was initially).So a rollback tran in SP3 will rollback to the first begin tran.
    Regds,
    Andy
    Last edited by Andy2004; 01-07-04 at 21:53.

  4. #4
    Join Date
    Nov 2002
    Posts
    9

    Does Sybase support nested transaction?

    Let me explain my case in detail.

    SP1 and SP2 are designed to perform some tasks. So there will be transaction control within SP1 and SP2.

    in SP3, I will perform some other tasks including the tasks in SP1 and SP2...I don't want to copy the codes from SP1 and SP2 and integrated(yet it is a solution for me) them to SP3....So that's why I want to know whether the rollback action in SP3 can rollback those database update performed in SP1 and SP2.

    Thanks

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Originally posted by Andy2004
    Please correct me if i am wrong but i guess rollback tran will rollback the transaction to the outermost open tran(will reduce the @@trancount to 0 ,whatever it's value was initially).So a rollback tran in SP3 will rollback to the first begin tran.
    Regds,
    Andy
    You are of course correct. I'm so used to using savepoints that I forgot that functionality.

    So in the case of the original poster, absent any transaction names or savepoints a rollback in SP2 or SP3 will roll back the entire transaction to the BEGIN TRAN in SP1.

    Michael

  6. #6
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353

    Re: Does Sybase support nested transaction?

    Originally posted by ywchan
    Let me explain my case in detail.

    SP1 and SP2 are designed to perform some tasks. So there will be transaction control within SP1 and SP2.

    in SP3, I will perform some other tasks including the tasks in SP1 and SP2...I don't want to copy the codes from SP1 and SP2 and integrated(yet it is a solution for me) them to SP3....So that's why I want to know whether the rollback action in SP3 can rollback those database update performed in SP1 and SP2.

    Thanks
    Yes, provided that SP3 is called while the outermost transaction is still in progress.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •