Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    15

    Question Unanswered: Set Isolation Levels

    In DB2 i came across the following code snippet in a procedure

    DECLARE C2 CURSOR FOR SELECT * FROM test FOR READ ONLY WITH RR

    where 'RR' defines the isolation level(Repeatable Read) set for the statement

    for the above snippet I need the equivalent in Oracle...
    which of the following will be the best to implement

    1. to lock the tables before the cursor is declared as follows

    begin transaction;
    Lock Table test in exclusive mode

    declare c2 cursor for select * from test;

    open c2
    :
    :
    close c2
    commit;

    or

    2. to set the transaction isolation level before the declaration statement


    set transaction isolation level Repeatable read;
    declare c2 cursor for select * from test;

    open c2

    :
    :
    close c2
    commit;


    please clarify me which one of the above wud be the best equivalent.
    Even u can suggest any other better equivalents available...

    Thanks in advance

    Sathya

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think you need to do anything special to get "repeatable read" behaviour in an Oracle transaction - I think within the scope of a read-only transaction you will always be getting the same result set as existed at the beginning of your transaction.

  3. #3
    Join Date
    Oct 2004
    Posts
    15
    along with the above question
    pls let me know the equivalent for the Uncommitted read in the Oracle

    by default oracle offers 'ReadCommitted ' isolation level
    how to set uncommittd read isolation level

    does this work out
    set transaction isolation level read uncommitted;

    thanks
    Sathya

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oracle doesnt offer the ability to view uncommitted data from any session. After all why do you need it, lesser databases require it as they have locking issues

    Look up the "oracle multiversioning" on google to see why Oracle does it much better or go here http://www.ss64.com/orasyntax/10locking.html.

    Alan

Posting Permissions

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