Re: "read only" lock in Oracle?

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 - 21:21:03 CST