RE: TX Enqueues - mode 4

Mladen,
There are certainly cases where TX enqueues can wait on mode 4, that is, where REQUEST is mode 4.

Off the top of my head:

• ITL slot shortage (note that this will not happen to tables on INSERT, since Oracle is smart enough to grab another block off the freelist. It still could happen on an index on the table, on an insert, though)
• Overlapping uncommitted primary key values in two sessions. (session a enters key=1, then session b enters key=2, then session a enters key=2, then session b enters key=1)
• Concurrent sessions overlap on usage of a bitmap index segment. (This is why OLTP and bitmap indexes do not mix.)
• Too many freelists for a segment can cause a shortage of transaction freelists. (In my experience this is very rare. I've never seen it outside of a constructed experiment to prove it can happen.)

Hope that helps,

-Mark

-----Original Message-----

From:	Mladen Gogala [mailto:mladen_at_wangtrading.com]
Sent:	Wed 4/7/2004 9:40 AM
To:	oracle-l_at_freelists.org
Cc:	
Subject:	Re: TX Enqueues - mode 4

On 04/07/2004 09:01:48 AM, "Fedock, John (KAM.RHQ)" wrote:
> I know I have TX enqueues, with a mode = 4. From all my research, I bet it is ITL related.

Transaction enqueues with LMODE=4? In my reference document, LMODE=4 is "shared", while TX enqueues are always with mode 6 like here:

QL> select * from v$lock where sid=28;  

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST -------- -------- ---------- -- ---------- ---------- ---------- ----------

     CTIME BLOCK
---------- ----------

734B89C0 734B8ACC         28 TX     196645     100864          6          0
        62          0
 
7345BFA8 7345BFBC         28 TM      40371          0          3          0
        62          0
 

Here, I have a locked row. TX lock (row lock) is mode 6 (eexclusive) and DDL lock (TM) is mode 3 (Shared, row-exclusive). I don't see how can you have 4 in the LMODE field and TX in the Type field.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



-- Binary/unsupported file stripped by Ecartis --
-- Type: application/ms-tnef
-- File: winmail.dat


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------