Table Locking

Greetings!

I have recently been running into an odd problem and was wondering if someone less has experienced the same thing. I am running Oracle 8.1.7 and it seems that certain processes are creating shared table locks (LOCK MODE = 4 or 5). From my understanding, this type of LOCK will still allow other process to acquire locks on their rows of interest but will not allow other process to actually update to the table. In other words, another process can select and lock but not finish the transaction because the original process has a lock on the table of type 4 or 5. So the application acts as if it is hung.

From my understanding, Shared Table Locks (LOCK MODE = 4) or Share Row Exclusive Table Locks (LOCK MODE = 5) can only occur when an explicit "LOCK TABLE ..." statement is executed by a process. However, noting in my code does this. My processes are designed to only acquire an Row Exclusive Table Locks(LOCK MODE = 3) for their database transactions. Just simple INSERT, DELETE, UPDATE, or SELECT FOR UPDATE type of SQL statements.

My question is: what could be causing processes to attain LOCK MODES 4 and 5 when there is noting explicitly in my code to do so? I have written some scripts using the V$ views to try to analyze the problem. Is there any canned oracle tools or scripts whcih may be able may give me more information so can pinpoint the problem? Is there any logs which I should look at which may give some clues? Thanks in advance!

Cheers,
JCD Received on Thu Dec 02 2004 - 08:03:10 CST