Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Another angle on this....

Re: Another angle on this....

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 3 Mar 2002 18:23:31 -0800
Message-ID: <a5ulr302m4u@drn.newsguy.com>


In article <cb748650.0203031138.22b09bd1_at_posting.google.com>, tom.mcclelland_at_mondas.com says...
>
>From what I've seen the behaviour is more extreme than implied by the
>other respondant.
>
>Just because something has been committed it does not follow that even
>a select statement that begins *after* the commit will see it. As I
>understand it the guarantee from a select statement is that you will
>see a consistent view of the database, but not necessarily the most
>up-to-date. You can see this by doing an update then commit in one
>PLSQL session, and immediately querying the data on another
>connection. You don't always see the update that you just commited
>yet.

thats not right. if the select is opened AFTER the commit, that select will in fact see it. if the sequence of events is this:

session 1                   session 2
-----------------           -------------------
update row
commit
                            open query that selects that row
                            fetch


session two will SEE the committed changes always.

If the sequence of events is this:

session 1                    session 2
-------------------          -------------------
udpate row
                             open query that selects that row (won't block
                             or anything like that)
commit
                             fetch row


then session 2 "won't" see that. It's for performance and consistency -- to get an answer that actually existed in the database at the point in time your query began. All details here:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c23cnsis.htm#17882

if you are interested.

>
>Slightly counter-intuitive, but having read the docs I think I can see
>the justification for this behaviour, on performance grounds.
>
>Regards
>
>Galen Boyer <galenboyer_at_hotpop.com> wrote in message
>news:<usn7jihpt.fsf_at_rcn.com>...
>> On Thu, 21 Feb 2002, no.spam_at_ntlworld.com wrote:
>>
>> > The "don't necessarilly see recent commited updates from other users"
>> > thing explains more fully
>>
>> Can you point me to where this was written about? I was under the
>> impression that once data was committed it was able to be seen by all.
>> I'd like to be aware of when this isn't the case.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Sun Mar 03 2002 - 20:23:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US