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

"Markus Breuer" <markus.breuer (AT) gmx (DOT) de> wrote

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?
do you have VPD policies?
some more details about the processes and SQL involved would help. likely
the version would also be helpful

++ mcs

Mark C. Stock wrote:

Is there any difference between selecting from a table and form a view?
Our processes directly access the table.

I dont know what VPD means...

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

Markus Breuer <markus.breuer (AT) gmx (DOT) de> wrote in
news:cie4qd$s30$1 (AT) pentheus (DOT) materna.de:

As strange as this may sound PRIOR to issuing the SELECT,
Process B needs to issue a COMMIT.

I suspect that this will allow Process B to "see" the new data.

"Markus Breuer" <markus.breuer (AT) gmx (DOT) de> wrote

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

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

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.

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

Markus Breuer wrote:

Instead of trying to fix your solution, you might be able to use other
Oracle capabilities to fix your problem ...

If you are attempting to communicate between session, you might want to look
at "autonomous transactions" to do the interaction using tables or
"dbms_pipe" for interaction using IPCs.

No, read consistency was net set. But in general, could this problem
happen? I think of database and session default, no changes appear to
the current session...

regards markus

Mark C. Stock wrote:

I think you're right. If read-only tranactions are the only reason to
hide changes to the other session, my error must be located within the
application.

Thx for your answer!

regards markus

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in
news:4PmdnevrfIVHc9fcRVn-gw (AT) comcast (DOT) com:

Never, say "never". ;-)

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.