Ana C. Dent wrote:

There are several scenarios & purposes possible.

If the purpose is to communicate intermediate results to other processes,
autonomous transactions might be useful. They can do their own commits
without affecting the transaction's committment state.

"Ana C. Dent" <anacedent (AT) hotmail (DOT) com> wrote

With default Oracle behavior, issuing a COMMIT to for another user's changes
to be seen by a new SELECT is never, never a requirement -- issuing a COMMIT
or ROLLBACK to end a read-only transaction is a requirement (as noted in my
last post, just below), or to start a new serialized transaction if the
session's isolation level has been set to to SERIALIZABLE.

My point is do not (never) throw in a COMMIT based on a wrong assumption,
i.e., that other user's COMMITs are insufficient to make their changed data
permanent (the main quesion of the OP) -- find out why normal default
behavior is not working in your application.


This is really a matter of understanding Oracle's read-consistency model
(and transaction isolation levels). By default oracle operates with
statement level read-consistency, but it can be set to session level.

If you find that you need to issue a commit to see data committed by other
users, then something in your environment is establising session-level
read-consistency (ie, SET TRANSACTION READ ONLY) or your session is set to
SERIALIZABLE.

As for as statement-level read consistency, Oracle has worked that way at
least since the beginning (at least v4, don't remember when SET TRANSACTION
and isolation levels where implemented).

This is thoroughly covered in the docs and is really Oracle 101. From page
10-7 of the 7.3.2 manual:
----------------------------------------------
Transaction Level Read Consistency
Oracle also allows the option of enforcing transaction-level read
consistency. When a transaction executes in serializable mode (see below),
all data accesses reflect the state of the database as of the time the
transaction began. This means that the data seen by all queries within the
same transaction is consistent with respect to a single point in time,
except that queries made by a serializable transaction do see changes made
by the transaction itself. Therefore, transaction-level read consistency
produces repeatable reads and does not expose a query to phantoms.

Oracle Isolation Levels

Oracle provides three transaction isolation modes:

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 will never read dirty (uncommitted)
data.
Because Oracle does not prevent other transactions from modifying the data
read by a query, that data may be changed by other transactions between two
executions of the query. Thus, a transaction that executes a given query
twice may experience both non-repeatable read and phantoms.

Serializable transactions
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 see non-repeatable 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.
----------------------------------------------

Anybody have any other comments/clarification on read-consistency and
isolations levels?

++ mcs