Why does Oracle lock table in share mode?

Hello!

I am using problems with Oracle 8.0.5.1.0 EE for Linux.

Can somebody tell me if the following behavior of Oracle is normal?

With "lockmode" and "block" I mean the columns from the table v$lock.

When a transaction inserts, updates or deletes a certain row in a table and has not yet committed, Oracle will generate two locks that can be seen in v$lock

The first is an "exclusive" (lmode=6) TX row lock for the modified row. The second is a "row exclusive" (lmode=3) TM lock on that table.
This second lock is only used to indicate that some rows in this table are locked exclusively.

Other transaction still may insert, update or delete OTHER rows in this table. ONLY if they try to modify the SAME row that is already locked waits will occur.

So far everything is okay.

Now I have two tables A and B and every row in B is linked to a certain row in A through a foreign key.

Now when I delete a row from A Oracle first checks that there is no row in B referring to this row in A. If there is none the row in A will be deleted, if there is one an error occurs (I do not want cascading deletes).

Still everything is as it should be.

Here comes my problem:

I have a long running transaction that has inserted a row in B and has not yet committed. So there is a "row exclusive" lmode=3 TM table lock on B.

Now another transaction tries to delete a row from A that has no corresponding row in B so that row should be deleted from A.

But now Oracle AUTOMATICALLY tries to obtain a "share" lmode=4 TM table lock on B and is blocked by the first transaction and waits.

From this point in time every other transaction that tries to modify table B will wait for the "Delete-A" transaction that is already waiting to get its lmode=4 share table lock.

So I get lots and lots of waiting transactions ....

Again:

1. I have a lmode=3 row exclusive lock that allows other transactions to acquire lmode=3 locks also.
2. The I want to delete a row from another table that is referenced through a foreign key from this table.
3. Oracle tries to obtain a lmode=4 share lock on this table, but it has to wait because of the existing lmode=3 locks.
4. Because there is a lmode=4 lock waiting to be acquired all further lmode=3 locks will also wait ...

My Question is: Why does oracle acquire a lmode=4 share table lock on table B when I want to delete a row from table A.

In the Oracle documentation it says:

A SHARE lmode=4 table lock is ONLY acquired for a table by the explicit command:

LOCK TABLE xxx IN SHARE MODE

but I just try to DELETE a row from another table.

This whole things causes lots of waits until that first long running transaction commits, but I think these waits are not neccessary ...

Is this normal and can the lock behavior of oracle be configured by some parameters?

I would appreciate any help very much!

Bye
Stefan Received on Sat Dec 11 1999 - 05:13:56 CST