Re: "read only" lock in Oracle?

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?

Just Repeating what Tom Told....

• Writers Dont Block Readers and Readers Dont Block Writers *

When Stmt 1 Is Issuing a Insert and Session B Selects from the table it will still be able to see the All the Records that have been Commited excluding session A's new insert.

HTH Regards,
Ganesh R

> 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 Sat Mar 09 2002 - 00:26:41 CST