Re: "read only" lock in Oracle?

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.

>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