Re: how synchronous is Oracle?

If "Process B" executes it's select after the commit, then yes, the row inserted & committed by "Process A" would be available. The row may not have been written to the datafile yet and may exist in the buffer_cache, but, Oracle knows about it for Read Consistency sake.

To explain further consider the following, if "Process B" executes it's select before the insert and the commit, then, no it will not be visible to "Process B", this is because the Oracle has already built the result set for "Process B's" select and therefore does not know about the new row. This would still be true if the insert/commit happened in the middle of "B's" select. If "Process B" re-issues the select after the commit, it would then be available.

HTH,
Pete's

"Robert A.M. van Lopik" <lopik_at_mail.telepac.pt> wrote in message news:<3e66fee5$0$10173$a729d347_at_news.telepac.pt>...
> Consider the following scenario:
> I have two processes, A and B. process A logs on to the database, inserts a
> record into a table, logs of and signals (one way or another) process B.
> Process B was already logged on and does a select that should retrieve the
> record just inserted.
> The question is: does Oracle guarantee that committed inserts are
> "immediately" visible to other users, or is there a possibility, e.g. on a
> heavily loaded system, that proces B will not retrieve the record, although
> it will see it at a somewhat later time?
>
> I found a thread in this forum about serializability around 1999, but that
> was too theoretical for my liking. For now I just would like to rule out the
> possibility that records would not be immediately visible, or learn how to
> make this happen if the guarantee is not there.
>
> B.T.W. The concrete question is about Oracle Version 8.1.7
>
> Thanks,
> Rob van Lopik
Received on Thu Mar 06 2003 - 12:01:46 CST