Re: Question about commit

"Markus Breuer" <markus.breuer_at_gmx.de> wrote in message news:ciel6a$q3h$1_at_pentheus.materna.de...
| Mark C. Stock wrote:
|
| > "Markus Breuer" <markus.breuer_at_gmx.de> wrote in message
| > 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. So
| > | process B starts "select ..." but does not get the previously inserted
| > | row. The timespan between commit and select is very short.
| > | (NOTE: two different sessions are used)
| > |
| > | Questions:
| > | 1.) Does commit when returning from call ensure, that all changes are
| > | immediatelly visible to all other Sessions/transactions?
| > | 2.) Does commit ensure only that all data is stored persistent, but
| > | changes are deferred visible to other transactions?
| > | 3.) May the "select ..." cause the problem? Other than dml statements
a
| > | select does not start a transaction. Would "select for update" instead
| > | solve the problem?
| > |
| > | regards markus
| >
| > the commit completes the transaction before returning control to the
issuing
| > application, and the data is immediately available to all other users
with
| > privileges
| >
| > is the second process selecting from a view?
|
| Is there any difference between selecting from a table and form a view?
| Our processes directly access the table.
|
| > do you have VPD policies?
|
| I dont know what VPD means...
|
| > some more details about the processes and SQL involved would help.
likely
| > the version would also be helpful
|
| We use a Oracle in the Versions 8.1.7.4 and 9.2.0.5. Both show the same
| problem. The Application is written in c++ using the oracle oci. The
| described processes A and B are threads within the same process. But
| that should not make a difference.
|
| The main Question is: Does oracle ensure that a returned commit makes
| changes immediatelly available to all other sessions. And: could there
| be an restriction when using simple select statements? => select does
| not start its own transaction.
|
| regards markus
|
|

If you're selecting from a view, the view may have a predicate that filters out the new row.

VPD: Virtual Private Database -- VPD policies automatically add predicates to SELECT statements issued against tables or view, which could also filter out the row.

Threads within the same process should not make a difference in and of itself -- but does the 'B' thread have any SET TRANSACTION READ ONLY statements? That would set transaction-level read consistency so that you would not see the newly committed data.

++ mcs Received on Fri Sep 17 2004 - 15:59:51 CEST