does a single session suport concurrent transactions?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: does a single session suport concurrent transactions?

  1. #1
    Join Date
    Jan 2003
    Location
    NJ
    Posts
    14

    does a single session suport concurrent transactions?

    what circumstances does a single session have multiple concurrent transactions?

    thanks.

    Vince

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    no circumstances directly, but you can use DBMS_JOB to submit multiple jobs in parallel. They actually execute in a different session though.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sort of: autonomous transactions.

    When using autonomous transaction your session actaully executse two concurent transactions: a normal transaction and inside it (kind of "wrapped") a totaly separate autonomous transaction.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are autonomous transactions sequential, or parallel?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Jurij :

    Still than it cannot be called concurrent, as I belive its SEQUENTIAL ...

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Autonomous transactions ar not sequential, they are parallel. When you open an autonomous transaction from within a "normal" transaction, you have two concurent transactions opened. They are totaly independant one from another. And once you are inside an autonomous transaction, you can again open yet another autonomous transaction. There are no actual limits on this nesting of autonomous transactions, other than available resource limits.

    So again, it is not serializable proces (like the start of one transaction implicitely ends the previous transaction), it is parallel - at the same time you can have N number of opened transactions (one "normal" transaction and N-1 autonomous transactions). However it is true that at any one time you have control of closing only one transaction - the one that was opened the last. With each new autonomous transaction you implicitely suspend the execution of its outer transaction. Once you commit or rollback the innermost autonomous transaction you gat back the control over its outer transaction.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2004
    Posts
    15
    i think AUTONOMOUS TRANSACTIONS is what you are looking for.
    From within a PL/SQL you may be in middle of tranx and start an AUTONOMOUS transaction involving other tables...
    It all happens from within the same session.
    Best luck...

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by and
    i think AUTONOMOUS TRANSACTIONS is what you are looking for.
    Hm, I wonder what we have been discussing in last few posts of this thread.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by jmodic
    However it is true that at any one time you have control of closing only one transaction - the one that was opened the last. With each new autonomous transaction you implicitely suspend the execution of its outer transaction. Once you commit or rollback the innermost autonomous transaction you gat back the control over its outer transaction.
    Ah, that's the kind of serialization i meant, not the transaction-control serialization -- shall we call it "temporal serialization"?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    shall we call it "temporal serialization"?
    Fair enough.

    And BTW, as far as the original question is concerned ("what circumstances does a single session have multiple concurrent transactions?"): it's not only explicit authonomous transactions that kind-of cause this - there are also "implicit" autonomous transactions, so called "recursive transactions". Even before the autonomous transactions were made available in 8i, RDBMS could still implicitely execute recursive (implicitely autonomous) transactions from within "normal" transactions. So even if you never use autonomous transactions, strictly speaking there are still moments when your session actually owns two concurrent transactions. That's also the reason why the default value of the parameter TRANSACTIONS is 10% greater than the parameter SESSIONS.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width