Oracle Nested Transactions - or lack thereof


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Oracle Nested Transactions - or lack thereof

  1. #1
    Andy Cortwright Guest

    Oracle Nested Transactions - or lack thereof


    In SQL Server, you can write nested transactions. For
    example I could write code like in the example below. The nested transactions
    can be independently committed or rolled back.

    BEGIN TRANSACTION
    UPDATE ....
    INSERT ....
    BEGIN TRANSACTION
    UPDATE...
    UPDATE...
    UPDATE...
    COMMIT or ROLLBACK
    BEGIN TRANSACTION
    INSERT...
    INSERT...
    COMMIT or ROLLBACK
    COMMIT or ROLLBACK

    Also, in SQL Server, I can write stored procedures that each have a
    transaction that either performs commit or rollback at the end. If I call
    several such sp's and I can nest them within one transaction and either
    commit or roll back the entire transaction (see pseudo code below).

    BEGIN TRANSACTION
    STORED PROCEDURE #1
    STORED PROCEDURE #2
    STORED PROCEDURE #3
    COMMIT or ROLLBACK (if rollback, then all changes in sp's are rolled back)

    STORED PROCEDURE #1
    BEGIN TRANSACTION
    INSERT ...
    UPDATE ...
    COMMIT or ROLLBACK

    STORED PROCEDURE #2
    BEGIN TRANSACTION
    INSERT ...
    DELETE ...
    COMMIT or ROLLBACK

    STORED PROCEDURE #3
    BEGIN TRANSACTION
    DELETE ...
    UPDATE ...
    COMMIT or ROLLBACK

    In Oracle, however, there are no such things as nested transactions and
    once a COMMIT is executed, inside or outside of a stored procedure,
    everything is committed and a new transaction begins.

    My question is this...How are transactions typically handled in Oracle
    within the context of writing stored procedures. Say I need to save data
    to 10 tables as one independent transaction and want to use independent stored
    procedures to do it, how could I?

    Any insight you have would be appreciated.


  2. #2
    Craig Clearman Guest

    Re: Oracle Nested Transactions - or lack thereof

    Andy,

    >My question is this...How are transactions typically handled in Oracle
    >within the context of writing stored procedures. Say I need to save data
    >to 10 tables as one independent transaction and want to use independent stored
    >procedures to do it, how could I?


    Generally, you do not write any transaction flow in a stored
    procedure. That is considered program-domain. Your program should
    start the transaction, and commit / rollback the transaction depending
    upon its entire sequence of events.

    Ciao, Craig


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center