Re: How to read UNCOMMITED data in Oracle?

Hi,

extracted from "Oracle 9i Concepts":

(1) UNCOMMITTED DATA
###
Oracle provides these transaction isolation levels.

"Read committed"
 This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that executes a given query twice can experience both nonrepeatable read and phantoms.  

"Serializable"
 Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.  

"Read-only"
 Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.
###

Thus, you can't read uncommitted data.

(2) DEFAULT LOCKING
###
Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE

The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.

The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.

A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.

In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.

Oracle's automatic locking can be overridden at the transaction level or the session level.

At the transaction level, transactions that include the following SQL statements override Oracle's default locking:

The SET TRANSACTION ISOLATION LEVEL statement The LOCK TABLE statement (which locks either a table or, when used with views, the underlying base tables)
The SELECT ... FOR UPDATE statement
Locks acquired by these statements are released after the transaction commits or rolls back.

At the session level, a session can set the required transaction isolation level with the ALTER SESSION statement. ###

Bye

francis70 <member41542_at_dbforums.com> wrote in message news:<3482578.1066194657_at_dbforums.com>...
> Hi,
>
>
>
> I have these 2 problem? Is there a way in Oracle to read UNCOMMITED
> data. i.e. in Oracle the normal behaviour is that a user's updates to a
> table are visible to other users ONLY when the user commits. But in
> Informix there is this thing called ISOLATION LEVELS. For example by
> setting the ISOLATION LEVEL to DIRTY READ, a user will read dirty data,
> i.e. the last uncommited updated value of a field by some other user. Is
> this possible in Oracle by setting some parameter, say in the INIT file?
>
>
>
> Also WHAT IS THE DEFAULT LOCKING BEHAVIOUR IN ORACLE? I mean if I want
> Oracle to automatically issue a READ LOCK (so that nobody can update a
> record, but view only) everytime a table (or row) is read, and for this
> to be made effective for the ENTIER DATABASE, how can we achive this? Is
> there a parameter to change in some INIT file???
>
>
>
> Thanks & Regards,
>
> Channa.
Received on Wed Oct 15 2003 - 12:19:55 CEST