Waiting for Type 4 lock

From: Doug C <dcowles_at_i84.net>
Date: Sun, 05 Aug 2001 21:10:22 -0700
Message-ID: <F001.00360FF8.20010805211021@fatcity.com>

I have an insert statement that is hung waiting for a type 4 lock. I have dug around a bit and found that a common problem is not enough space for ITL in block headers.

However, the table in question is set at Oracle defaults - 10% free 40% used, initrans 1, maxtrans 255 on a 4K block size. (Oracle 8.1.7 on AIX 4.3.3)

There are only two sessions in the entire database. So, I find it hard to believe that a block would be on the free list (40%) without having enough room to accomodate what I assume are 2, (only 2), interested transactions.

So - I pose the following -
1) Is there a way to prove or disprove an ITL problem? There is no specific block, row, object in v$session being waited on. The v$session_wait indicates an enqueue

2) Can anyone suggest any other scenarios where a transaction which shows an active insert would be sitting around waiting for a type 4 (S) lock indefinitely.

To my knowlege, the blocking sid has nothing more than a large pile of inserts on the same table that are uncommited. Also, primary key contention is not an issue because sequences are used for the primary key. All foreign keys are indexed.

Thanks,
Doug
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Doug C
  INET: dcowles_at_i84.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sun Aug 05 2001 - 23:10:22 CDT