Re: Why does Oracle lock table in share mode?

This used to be influenced by indexing the foreign key or not. If there was an index on the foreign key, Oracle was raising row level locks. However if there was no index, Oracle would raise a table level lock. This problem has been adressed and I simply don't know whether that happened in 8.0 or 8i

Hth,

--
Sybrand Bakker, Oracle DBA
Stefan Willfahrt <sw_at_debis.com> wrote in message news:385231F4.321F2B1B_at_debis.com...
> 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 - 07:14:54 CST