"read only" lock in Oracle?

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.

Any help would be highly appreciated.
Serge Received on Fri Mar 08 2002 - 17:05:07 CST