"Ana C. Dent" <an*******@hotmail.com> wrote in message
news:Xn*********************@68.6.19.6...
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in
| news:4P********************@comcast.com:
|
| >
| > "Ana C. Dent" <an*******@hotmail.com> wrote in message
| > news:Xn*********************@68.6.19.6...
| >| Markus Breuer <ma***********@gmx.de> wrote in
| >| news:ci**********@pentheus.materna.de:
| >|
| >| > I have a question about oracle commit and transactions. Following
| >| > scenario:
| >| >
| >| > Process A performs a single sql-INSERT into a table and commits the
| >| > transaction. Then he informs process B (ipc) to read the new date.
| >|
| >| As strange as this may sound PRIOR to issuing the SELECT,
| >| Process B needs to issue a COMMIT.
| >|
| >
| > That does sound strange... the only reason for this would be if B is
| > in a read-only transaction... (see my other post).
| >
| > Issuing a COMMIT to see other user's changes is never a requirement.
|
| Never, say "never". ;-)
|
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.
| >
| > If B is in a read-only transaction, then a COMMIT or ROLLBACK should
| > only be entered when the read-only transaction is completed (per the
| > business functionality specification), not as a work around to a
| > scenario that is not yet fully analyzed.
| >
| > ++ mcs
| >
| >
|
| Oracle is too brain dead to know about "read-only" transactions.
| Oracle GUARENTEES a read consistant view of the database.
| If Process B has issued a SELECT prior to Process A doing the COMMIT,
| then Oracle ENSURES Process B won't see the changed data. This is
| because Oracle can't know what Process B intends to do with the
| data from the 1st SELECT. The only way I know how to convince Oracle
| that my process wants to see "new data" is to issue a COMMIT (or
| ROLLBACK) to indicate all my previous activity is a completed
| transaction.After my session issues a COMMIT, Oracle will present
| to my session data as it exists at the time of my next SELECT!
|
| You are free to disagree & provide proof to the contrary.
|
|
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
