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.
