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

    Unanswered: Is Oracle 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 2004
    Posts
    9

    Re: Is Oracle 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

    Hi dear

    why dont you use savepoint function i hope it will help u.

    by take care

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is Oracle support nested transaction?

    No, transactions are not nested. Any COMMITs or ROLLBACKs in SP1, SP2 or SP3 are at the same "level". So if SP1 issued a COMMIT, then any subsequent ROLLBACK in SP2 or SP3 will not undo what SP1 committed.

    There is something called an "autonomous transaction", but this is a totally separate transaction, again not nested within a higher transaction in the way you mean.

Posting Permissions

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