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

Home -> Community -> Mailing Lists -> Oracle-L -> why would enable constraint cause a delete to wait on library cache lock?

why would enable constraint cause a delete to wait on library cache lock?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 12 Jan 2004 14:29:25 -0800
Message-ID: <F001.005DC735.20040112142925@fatcity.com>


I am only an egg, so I hope someone else can explain this to me.

Oracle Enterprise Edition 8.1.7.4.1 on Windows 2000

I was experimenting to see if an "alter table enable validate constraint" would cause DML statements to wait. I thought it wouldn't. But in real life I see something different.

I have a table X (object_id 429995) with about 100 million rows. In one session (sid 15) I enable a check constraint, and in another session (sid 14) I simultaneously delete a few rows from the table. The delete "waits" on the enable constraint to complete, and it's waiting on a library cache lock. Why would that be? At the end of this e-mail you can see the contents of DBA_LOCKS, V$LOCKED_OBJECT, V$SESSION_WAIT. I read the system state dump and I still don't understand why there would be a library cache lock.

Session ID 15
--- sid-15-SQL1

alter table hes_a_keeper.many_rows add
  (constraint ck1 check (dummy_column > 'B') disable) ;
--- sid-15-SQL2 (simultaneous with sid-14-SQL1)
alter table hes_a_keeper.many_rows enable validate constraint ck1 ;

Session ID 14
--- sid-14-SQL1 (simultaneous with sid-15-SQL2)
delete from hes_a_keeper.many_rows where rownum < 10 ;

Session ID 10
--- statements issued while sid-14-SQL1 and sid-15-SQL2 are running
alter session set events 'immediate trace name systemstate level 10' ;

select * from dba_locks where session_id in (14,15) ;
select * from v$locked_object where object_id = 429995 ;
select * from v$session_wait where sid in (14,15) ;


SQL> select * from dba_locks where session_id in (14,15) ;
SESSION_ID LOCK_TYPE                  MODE_HELD
---------- -------------------------- ----------------------------------------
MODE_REQUESTED                           LOCK_ID1
---------------------------------------- ----------------------------------------
LOCK_ID2                                 LAST_CONVERT BLOCKING_OTHERS
---------------------------------------- ------------ ----------------------------------------
        15 DML                        Share
None                                     429995
0                                                  10 Not Blocking


SQL> select * from v$locked_object where object_id = 429995 ;

   XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME

--------- --------- --------- --------- ---------- ------------------------------
OS_USER_NAME                   PROCESS   LOCKED_MODE
------------------------------ --------- -----------
        0         0         0    429995         15 JRK
jkilchoe                       1540:260            4


SQL> select * from v$session_wait where sid in (14,15) ;
      SID      SEQ# EVENT
--------- --------- ----------------------------------------------------------------
P1TEXT                                                                  P1 P1RAW
---------------------------------------------------------------- --------- --------
P2TEXT                                                                  P2 P2RAW
---------------------------------------------------------------- --------- --------
P3TEXT                                                                  P3 P3RAW    WAIT_TIME
---------------------------------------------------------------- --------- -------- ---------
SECONDS_IN_WAIT STATE
--------------- -------------------
       15      5005 db file scattered read
file#                                                                   13 0000000D
block#                                                               19516 00004C3C
blocks                                                                   8 00000008         0
              0 WAITING

       14       142 library cache lock
handle address                                                    47483828 02D48BB4
lock address                                                      40114204 0264181C
10*mode+namespace                                                       21 00000015         0
              9 WAITING

SQL>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Mon Jan 12 2004 - 16:29:25 CST

Original text of this message

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