It won't see it because to it it has not happened yet.(the transaction has
not committed.)
Jim
"y" <y_at_y.y> wrote in message news:3C897FCA.5A8BA0F1_at_y.y...
>
>
> Thomas Kyte wrote:
>
> > In article <1c6a0599.0203081505.45d0b1d4_at_posting.google.com>,
junkmbox_at_yahoo.com
> > says...
> > >
> > >Hello to the Oracle database gurus out there!
> > >
> > >We are porting our app. from Informix to Oracle. Our problem is the
> > >differences in locking behavior between the two databases. In
> > >particular, the following example demonstrates how Informix places a
> > >"read-only" type of lock.
> > >
> > >Suppose, we create a table with two rows (and we are in row-locking
> > >mode, as opposed to page-locking):
> > >
> > >CREATE TABLE A (I INT);
> > >INSERT INTO A VALUES (22);
> > >INSERT INTO A VALUES (33);
> > >COMMIT;
> > >
> > >** The example:
> > >
> > >***Session 1:
> > >SELECT * FROM A WHERE I = 22;
> > >//success, places read-lock
> > >
> > >***Session 2:
> > >SELECT * FROM A WHERE I = 22;
> > >//success, places read-lock
> > >
> > >UPDATE A SET I = 25 WHERE I = 22;
> > >//fails or waits due to lock owned by session1
> > >
> > >DELETE FROM A WHERE I = 22;
> > >//fails or waits due to lock owned by session1
> > >
> > >UPDATE A SET I = 35 WHERE I = 33;
> > >// success, because this row had no locks
> > >
> > >***Session 1:
> > >UPDATE A SET I = 25 WHERE I = 22;
> > >//fails or waits due to lock owned by session2
> > >
> > >DELETE FROM A WHERE I = 22;
> > >//fails or waits due to lock owned by session2
> > >
> > >This is Informix' default behavior. The questions is, what would be
> > >the SQL statements for the given example that reproduce the same
> > >locking behavior in Oracle.
> > >
> >
> > There is no such locking behavior in Oracle -- this is what truly sets
databases
> > apart, locking and concurrency.
> >
> > In Oracle -- the above situation is not possible, we have no such thing
as a
> > "read lock" used by other databases to provide consistent reads -- we
use
> > multi-versioning.
> >
> > You will need to alter the way your application processes -- in Oracle
reads do
> > not block writes and (even better) writes do not block reads. Its a
whole
> > different ball game.
>
> Ok, I saw the multi-versioning. I just want to know if session 1 inserts
some rows,
> for example like thomas'senario, before this transaction end (commit or
rollback),
> if session 2 wants to select row 22 or 33, it have to issue the request
again and
> again?
> Regards,
>
>
> >
> >
> > >Any help would be highly appreciated.
> > >Serge
> >
> > --
> > 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 Fri Mar 08 2002 - 23:45:13 CST
