Get Data Moving 1
Results 1 to 2 of 2

Thread: What is the scope of isolation levels in Oracle RDB?

  1. #1
    Patricio is offline Junior Member
    Join Date
    Aug 2006
    Posts
    5
    Rep Power
    0

    What is the scope of isolation levels in Oracle RDB?

    Hello again,

    I was wondering if you have any information on the scope of isolation levels in the Oracle RDB data source.

    Thanks in advance,
    Patricio

  2. #2
    Andre is offline Documentation Manager
    Join Date
    Aug 2006
    Posts
    147
    Rep Power
    14

    Oracle RDB Isolation Levels and Locking

    Hi Patricio,

    Here's what Attunity's documentation has to say on the subject:

    Oracle RDB uses a locking mechanism for controlling concurrency and enforcing logical and physical integrity of the database. The strategy for locking objects is as follows:

    • Lock the object
    • Perform the required work on the object
    • Unlock the object at a later time, most likely at the end of the Transaction


    Among lockable objects the following hierarchy is found:

    • Database
    • Table
    • Page
    • Row
    • Index node


    Oracle RDB implements a dedicated logic that chooses the appropriate object and adjusts the lock granularity. It selects a suitable lock object and level which are based on the operation being performed in a given context. All this is aimed at minimizing potential lock conflicts.

    Note that the locking scope is normally associated with a transaction. The SET TRANSACTION syntax contains elements for controlling the LOCKING applied:

    • READ/WRITE
    • WAIT [x]/NOWAIT


    Nevertheless Oracle RDB locking policy is rather strict. Lock conflicts are a matter of routine in a database that serves multiple users concurrently. A user can be a 'Blocker', while holding a locked resource required by others, or 'Blocked By', while waiting for a resource locked by others to be released or 'Both'.

    Circular lock conflicts can lead to deadlock situations where User A locks Resource 1 and waits for Resource 2, which is locked by User B who is waiting for Resource 1.

    The Oracle RDB data source driver is aware of Oracle RDB potential locking conflicts and provides certain built-in relief measures:

    • The default transaction WAIT parameter is 0. This translates to NOWAIT, which actually means that a default transaction initiated by the Oracle RDB data source will not be blocked. Instead it will inform of a lock conflict if one is encountered.
    • The default proposed isolation level is READ COMITTED which reduces lock contention and increases the degree of concurrency. Note that this affects 'pure' transactional integrity, since data committed by others is visible in your transaction.
    • The Oracle RDB data source maintains dual (multiple) connections to the database. This notion is based on the fact that when snapshots are enabled for a database (the default), READ ONLY transactions do not lock the rows they read. Data is read from the snapshot maintained by Oracle RDB. Therefore the Oracle RDB data source driver normally holds two connections to the database. The first, denoted as NAV in the data source driver's log, is used for WRITE transactions, which are subject to LOCK conflicts. The second, denoted as NAVREAD, serves the READ transactions. This notion of duplicating connections is also extended to separating DDL and stored procedure locking activities from the main data source driver's course, which is READ/WRITE operations.
    • The Oracle RDB data source provides explicit control on elements that affect locking along the transaction. This is carried out by assigning appropriate values to configurable parameters, as described above. In particular, ISOLATION LEVEL can be set explicitly both for READ and WRITE transactions as follows:


    readCommitted: Allows your transaction to see all data committed by other transactions.

    repeatableRead: Guarantees that if you execute the same query again, your program receives the same rows it read the first time. However, you may also see rows inserted and committed by other transactions (known as Phantoms).

    serializable: Guarantees that the operations of concurrently executed transactions are not affected by any other transaction.

    Cheers,
    Andre

Posting Permissions

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