Re: Question about commit ++ oracle's read-consistency and isolation level model
Date: Sat, 18 Sep 2004 07:32:10 -0400
Message-ID: <cr2dnSukHfJTgdHcRVn-sw_at_comcast.com>
"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
news:Xns9567B45DDEF27SunnySD_at_68.6.19.6...
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in
| news:4PmdnevrfIVHc9fcRVn-gw_at_comcast.com:
|
| >
| > "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
| > news:Xns956745E1C17DDSunnySD_at_68.6.19.6...
| >| Markus Breuer <markus.breuer_at_gmx.de> wrote in
| >| news:cie4qd$s30$1_at_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 Received on Sat Sep 18 2004 - 13:32:10 CEST