Re: "read only" lock in Oracle?

On 9 Mar 2002, junkmbox_at_yahoo.com wrote:
> Gentlemen,

> In this parent-child example provided in the excerpt, it would work
> perfectly fine in Informix, because Transaction A would place a shared
> lock on the parent row, while reading it (i.e. by doing a SELECT on
> that row), so Transaction B will fail to delete the parent
> row. However, to make this example work properly in Oracle,
> Transaction A has to do a SELECT..FOR UPDATE, thereby placing an
> exclusive lock on the parent row. Alright, for the purposes of the
> example it would be sufficient, because Transaction B will fail to
> delete the parent row. But what if at the same time there is another
> Transaction C running, that tries to do exactly the same task as
> Transaction A - i.e. read parent, then insert another child. In
> Oracle, Transaction C will fail or will have to wait for Transaction A
> to complete, although it does not try to do anything conflicting and
> could proceed in parallel to Transaction A, if it weren't for the
> exclusive lock. In Informix (and I believe, some other databases) a
> mere shared row lock on the parent row will prevent deletion of that
> row, but will not prevent Transactions A and C to work in parallel. In
> our system we have multiple "Transaction A" types of operations
> running all at the same time. If they all have to exclusively lock the
> same parent row, this will bring the system down, pretty much.

Couldn't this be solved by setting the transaction isolation level to serializable?

SQL> create table parent (fld1 integer, attr1 varchar2(10)); Table created.

SQL> insert into parent values (1, 'A'); 1 row created.

SQL> insert into parent values (2, 'B'); 1 row created.

SQL> create table child (fld2 integer, fld1 integer, attr1 varchar2(10)); Table created.

SQL> alter table parent add constraint parent_pk primary key (fld1); Table altered.

SQL> alter table child add constraint child_fk foreign key (fld1) references parent(fld1); Table altered.

SQL> insert into child values (1,1,'AA'); 1 row created.

SQL> insert into child values (2,2,'BB'); 1 row created.

SQL> commit;
Commit complete.

SQL> select * from parent;

      FLD1 ATTR1
---------- ----------

	 1 A
	 2 B

SQL> select * from child;

      FLD2 FLD1 ATTR1
---------- ---------- ----------

	 1	    1 AAAA
	 2	    2 BB

Now, in two sqlplus sessions,

sql1> set transaction isolation level serializable;

sql2> set transaction isolation level serializable;

sql1> update child set attr1 = 'AAAA' where fld2 = 1; 1 row updated.

sql2> delete parent where fld1 = 1;
 {This will wait now wait until the sql1 session completes}

sql1> commit;
Commit complete.

sql2 sees:

    delete parent where fld1 = 1

           *
    ERROR at line 1:
    ORA-08177: can't serialize access for this transaction

Therefore, any session trying to update the child could do so, while the session trying to delete the parent would fail.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.