Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> TX mode 4 locks

TX mode 4 locks

From: srivenu <srivenu_at_hotmail.com>
Date: 17 May 2003 01:38:35 -0700
Message-ID: <1a68177.0305170038.3f19591e@posting.google.com> 





I'am facing some inexplicable TX mode locks in my instance.
Can someone please explain me the reason.



sys_at_XXX> l


  1  select a.sid,

  2      decode(command,0,'None',2,'Insert',3,'Select',
  3              6,'Update',7,'Delete',10,'Drop Index',12,'Drop
Table',
  4              45,'Rollback',47,'PL/SQL',command) command,
  5      event




  6  from v$session_wait a,V$session b


  7  where b.sid=a.sid


  8  and (a.sid>10 and event not in('SQL*Net message from client',

  9                     'SQL*Net message to client')
 10  or (a.sid<10 and event not in ('rdbms ipc message','smon timer',
 11     'pmon timer','SQL*Net message from client')))


 12* order by decode(event,'pipe get','A',event),p1,p2
sys_at_XXX> /



             Event


 SID Command  Waiting For

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


 186 Select  direct path write


 105 None    enqueue


 203 None    enqueue


   9 None    log file sync


 101 None    rdbms ipc message



5 rows selected.




sys_at_XXX> l


  1  select sid,decode(type,'TX','Transaction(TX)','TM','DML
Enqueue(TM)',type) type,

  2      id1,id2,
  3      decode(lmode,0,'None(0)',1,'Null(1)',2,'Row Share(2)',3,'Row
Exclu(3)',
  4              4,'Share(4)',5,'Share Row Ex(5)',6,'Exclusive(6)')
lmode,
  5      decode(request,0,'None(0)',1,'Null(1)',2,'Row
Share(2)',3,'Row Exclu(3)',
  6              4,'Share(4)',5,'Share Row Ex(5)',6,'Exclusive(6)')


request1


  7  from v$lock


  8  where sid>5


  9  and type not in ('MR','RT')


 10* order by decode(request,0,0,2),block,5
sys_at_XXX> /



 SID Lock Type               ID1         ID2 Lock Held      Lock
Request


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



  60 Transaction(TX)       65617       28134 Exclusive(6)   None(0)
 105 Transaction(TX)      196668       27536 Exclusive(6)   None(0)
 159 Transaction(TX)      524357       29296 Exclusive(6)   None(0)
 216 Transaction(TX)      131161       32213 Exclusive(6)   None(0)
 203 Transaction(TX)      589827       28121 Exclusive(6)   None(0)
   8 DML Enqueue(TM)       18098           0 Row Exclu(3)   None(0)
   8 DML Enqueue(TM)       17919           0 Row Exclu(3)   None(0)
   8 DML Enqueue(TM)       17894           0 Row Exclu(3)   None(0)
 105 DML Enqueue(TM)       17903           0 Row Exclu(3)   None(0)
 105 DML Enqueue(TM)       18089           0 Row Exclu(3)   None(0)
 159 DML Enqueue(TM)       17900           0 Row Exclu(3)   None(0)
 216 DML Enqueue(TM)       17900           0 Row Exclu(3)   None(0)
 216 DML Enqueue(TM)       18481           0 Row Exclu(3)   None(0)
 216 DML Enqueue(TM)       18024           0 Row Exclu(3)   None(0)
 203 DML Enqueue(TM)       17903           0 Row Exclu(3)   None(0)
 203 DML Enqueue(TM)       18089           0 Row Exclu(3)   None(0)
 203 DML Enqueue(TM)       17933           0 Row Exclu(3)   None(0)
 159 DML Enqueue(TM)       18481           0 Row Exclu(3)   None(0)
 159 DML Enqueue(TM)       17892           0 Row Exclu(3)   None(0)
 159 DML Enqueue(TM)       18024           0 Row Exclu(3)   None(0)
 216 DML Enqueue(TM)       17892           0 Row Exclu(3)   None(0)
 105 DML Enqueue(TM)       17933           0 Row Exclu(3)   None(0)
   8 DML Enqueue(TM)       17933           0 Row Exclu(3)   None(0)
   8 DML Enqueue(TM)       18089           0 Row Exclu(3)   None(0)
   8 DML Enqueue(TM)       17903           0 Row Exclu(3)   None(0)
   8 Transaction(TX)      720909       24687 Exclusive(6)   None(0)
 105 Transaction(TX)      720909       24687 None(0)        Share(4)
 203 Transaction(TX)      720909       24687 None(0)        Share(4)





28 rows selected.




sys_at_XXX> l


  1  select a.sid sid,b.sql_text curr


  2  from v$session a, v$sql b


  3  where a.sql_address=b.address


  4  and a.sid in(105,203)


  5* order by 1



sys_at_XXX> /



 SID      Current SQL

---- ----------------------------------------
 105 INSERT INTO NOTES ( DATE_ENTERED,NO
     TES_ID,DOCUMENT_NUMBER,LAST_MODIFIED_DAT
     E,LAST_MODIFIED_USERID,NOTE_TEXT,NOTES_S
     EQUENCE,USER_ID,CIRCUIT_DESIGN_ID,CIRCUI
     T_NOTE_IND,DOCUMENT_NUMBER_SRC,SYSTEM_GE
     N_IND,EXCHANGE_CARRIER_CIRCUIT_ID )  VAL
     UES ( ASAP.PKG_GMT.SF_NOW_GMT,:b1,:b2,SY
     SDATE,:b3,:b4,DECODE(:b5, NULL ,1,:b5 +
     1 ),:b3,:b8,DECODE(:b8, NULL ,'N','Y'),D
     ECODE(:b8, NULL , NULL ,:b2),'Y',:b12  )

 203 INSERT INTO NOTES ( DATE_ENTERED,NO
     TES_ID,DOCUMENT_NUMBER,LAST_MODIFIED_DAT
     E,LAST_MODIFIED_USERID,NOTE_TEXT,NOTES_S
     EQUENCE,USER_ID,CIRCUIT_DESIGN_ID,CIRCUI
     T_NOTE_IND,DOCUMENT_NUMBER_SRC,SYSTEM_GE
     N_IND,EXCHANGE_CARRIER_CIRCUIT_ID )  VAL
     UES ( ASAP.PKG_GMT.SF_NOW_GMT,:b1,:b2,SY
     SDATE,:b3,:b4,DECODE(:b5, NULL ,1,:b5 +
     1 ),:b3,:b8,DECODE(:b8, NULL ,'N','Y'),D
     ECODE(:b8, NULL , NULL ,:b2),'Y',:b12  )






2 rows selected.




sys_at_XXX> l


  1  select table_name,chain_cnt,pct_free,pct_used,
  2  avg_space,avg_row_len,ini_trans,freelists
  3  from dba_tables


  4* where table_name ='NOTES'


sys_at_XXX> /


                                                 Avg
                                                Free
                                         %   % Space    Avg
                                 Chain  Fr  Us  In A    Row   Ini 
Free
Table Name                       Count  ee  ed Block Length Trans


Lists


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




NOTES                                0  15  40  1250     97    10    


4



1 row selected.




sys_at_XXX> l


  1  select index_name,num_rows,


  2      distinct_keys*100/decode(num_rows,0,1,num_rows)
distinctiveness,

  3      AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,
  4      ini_trans,freelists




  5  from dba_indexes


  6  where table_name= upper('&1')


  7* order by 1


sys_at_OMPROD> l


  1  select index_name,index_type,


  2      distinct_keys*100/decode(num_rows,0,1,num_rows)
distinctiveness,

  3      AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,
  4      ini_trans,freelists




  5  from dba_indexes


  6  where table_name='NOTES'


  7* order by 1


sys_at_XXX> /


                                                            Avg    Avg
                                                       %   Leaf   Data
                                                    Dist Blocks Blocks
Ini Free
                                                    inct    Per    Per
Tra  Lis
Index Name           INDEX_TYPE                     keys    Key    Key


 ns   ts


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

--- ----

FKIDX_SRC_NOTES      NORMAL                        94.61      1      1
 10    4
IDX_DOC_SEQ          NORMAL                       100.00      1      1
 10    4
IDX_NOTES_CIR_DES_ID NORMAL                        90.72      1      1
 10    4
PKIDX_NOTES          NORMAL                       100.00      1      1


 10    4



4 rows selected.




sys_at_OMPROD> select count(*) from pending_sessions$;



  COUNT(*)





         0



1 row selected.



sys_at_OMPROD> select count(*) from dba_2pc_pending;



  COUNT(*)





         0



1 row selected.




    
  
  1.   I have only 3 sessions currently inserting into the NOTES table
whereas the table and all its indexes have initrans set to 10.
  
  2.   All the foreign keys on NOTES table are indexed.
  
  3.   None of the indexes are BITMAP indexes.
  
  4.   There are no pending 2PC transactions.






Can someone please tell me why the 2 sessions are waiting for a TX
mode 4 lock on the NOTES table ?


Are there any other cases where the TX mode 4 lock is acquired ?




thanks in advance.


regards


Srivenu
Received on Sat May 17 2003 - 03:38:35 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US