Results 1 to 6 of 6

Thread: Isolation Level

  1. #1
    Join Date
    Oct 2003
    Posts
    71

    Unanswered: Isolation Level

    We have SQL Server db which has an oracle db set up as its linked server. The oracle client is installed on it.

    There is a job on sql server, which reads data from oracle every 5 min and loads iit into sql server tables.

    Is there any way to set up the siolation level itself in the connection so that sql server can read dirtly data from oracle withoutl blocking any oracle tables/rows.

    Thanks
    Reema

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: Isolation Level

    Depending on what you are really asking, no. You cannot read blocks that another user has updated but not yet committed. This class of dirty blocks contains a redirect flag that sends all DML transactions issued by other users to the corresponding rollback/undo blocks.

    Oracle programmers do have the ability to set the isolation level. For example, "alter session set isoliation level serializable".

    I hope that helps.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Oct 2003
    Posts
    71

    Re: Isolation Level

    Mark,

    may be i didnot put it clearly.

    What I am asking is to read the data with lowest isolation level from oracle db into SQL Server database.
    There is no way to set the isolation level in the distributed query created on sql server side which reads data from oracle.
    So I was wondering if there is anyway of setting isolation level in the oracle connection itself or somewhere in tnsnames.ora file .

    Hope I a clear this time.

    Thanks
    Reema
    Originally posted by markrem
    Depending on what you are really asking, no. You cannot read blocks that another user has updated but not yet committed. This class of dirty blocks contains a redirect flag that sends all DML transactions issued by other users to the corresponding rollback/undo blocks.

    Oracle programmers do have the ability to set the isolation level. For example, "alter session set isoliation level serializable".

    I hope that helps.

  4. #4
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Yes, I understand now. You do not need to do that, since you're querying Oracle. It gives you consistant queries, unlike many other databases.

    If you want to set the isolation level, ithen iIt must be set in the Oracle session that implements you query. That is why it will not work from a distributed database. If you are using a tool that does not allow your Oracle session to be tuned, then you cannot do it. For example, the tool must allow you to issue ALTER SESSION commands or allow you to set options that have the same effect.

    By default, all Oracle queries are executed with a isolation level of read-committed. Even if users are updating data, your query will not see the new data. At the start of each "statement", Oracle determines the latest committed transaction and guarantees you will see no data newer than that transaction, until your statement ends. Each statement is run against a different (and higher) transaction consistancy point.

    Optionally, you can set the isolation level to serializable. This is similar to above, except all of your statements within your transaction (until you commit or rollback) will see the old data.

    Oracle never locks data for read operations. Therefore, using either level of isolation will not block other users. You can block users, if you want to, by preceding your query with a SELECT FOR UPDATE command.

    For more info, see your Oracle9i documentation "http://yourserver/appdev.920/a96590/adg08sql.htm#2712" or related Oracle8i document.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  5. #5
    Join Date
    Dec 2003
    Location
    Kekaha, Kauai, Hawaii
    Posts
    22
    Mark gave an excellent description of Oracle's locking, except he left out one thing. With the default isolation level Oracle readers do not block writers. If someone wants to update a row that someone else is reading then they can do that - thel before image of the data is what everyone else will see in the rollback segments until the transaction is committed.

    This is one of the things that makes Oracle much cooler than SQL Server - though of course no one here on this forum is at all biased in that respect.

  6. #6
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    I meant to say that in my first post, so I didn't both in my second post, but re-reading it today I see I didn't cover that item very well. Thanks for the follow up!

    -Mark

Posting Permissions

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