How to read UNCOMMITED data in Oracle?

[francis70]

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.


--
Posted via http://dbforums.com

[Mauro]

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.

"Serializab le"
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@db forums.comwrote in message news:<3482578.1 066194657@dbfor ums.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.

[Jim Kennedy]

Re: How to read UNCOMMITED data in Oracle?


"francis70" <member41542@db forums.comwrote in message
news:3482578.10 66194657@dbforu ms.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?
>
>

That really isn't a feature; it is a behavior to get around a poor locking
model.

>
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???
>
>

So you are going to serialize database usage a lot, ouch! Why do you want
to do this? If most data is read and not changed then this is going to
cause you a lot of headaches.
Jim

>
Thanks & Regards,
>
Channa.
>
>
--
Posted via http://dbforums.com

[Database Guy]

Re: How to read UNCOMMITED data in Oracle?

"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message news:<rjbjb.562 792$Oz4.523703@ rwcrnsc54>...

"francis70" <member41542@db forums.comwrote in message
news:3482578.10 66194657@dbforu ms.com...

I have these 2 problem? Is there a way in Oracle to read UNCOMMITED
data. [snip]

>
That really isn't a feature; it is a behavior to get around a poor locking
model.

An alternative view would be that this has long been part of the ANSI
SQL standard, virtually all other major DBMS vendors support it, yet
with Oracle we still can't do it.


DG

[Jim Kennedy]

Re: How to read UNCOMMITED data in Oracle?

"Database Guy" <dbguy101@hotma il.comwrote in message
news:7fdee71c.0 310151623.15840 a56@posting.goo gle.com...

"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message

news:<rjbjb.562 792$Oz4.523703@ rwcrnsc54>...

"francis70" <member41542@db forums.comwrote in message
news:3482578.10 66194657@dbforu ms.com...

I have these 2 problem? Is there a way in Oracle to read UNCOMMITED
data. [snip]

That really isn't a feature; it is a behavior to get around a poor

locking

model.

>
An alternative view would be that this has long been part of the ANSI
SQL standard, virtually all other major DBMS vendors support it, yet
with Oracle we still can't do it.
>
>
DG

It is an ANSI standard because a lot of databases have a brain dead
concurrency model. Being a standard makes it a standard, not a feature. So
if most people jumped off the Brooklyn Bridge you would also?

Why do you want to do it in Oracle? So your application isn't ACID?
Jim