By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

How to read UNCOMMITED data in Oracle?

P: n/a

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
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 <me*********@dbforums.com> wrote in message news:<34****************@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.

Jul 19 '05 #2

P: n/a

"francis70" <me*********@dbforums.com> wrote in message
news:34****************@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?

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

Jul 19 '05 #3

P: n/a
"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<rjbjb.562792$Oz4.523703@rwcrnsc54>...
"francis70" <me*********@dbforums.com> wrote in message
news:34****************@dbforums.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
Jul 19 '05 #4

P: n/a
"Database Guy" <db******@hotmail.com> wrote in message
news:7f**************************@posting.google.c om...
"Jim Kennedy" <ke****************************@attbi.net> wrote in message

news:<rjbjb.562792$Oz4.523703@rwcrnsc54>...
"francis70" <me*********@dbforums.com> wrote in message
news:34****************@dbforums.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
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.