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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_LOCK.ALLOCATE_UNIQUE - BEWARE!!!

RE: DBMS_LOCK.ALLOCATE_UNIQUE - BEWARE!!!

From: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Thu, 15 May 2003 09:46:44 -0800
Message-ID: <F001.00599E3D.20030515094644@fatcity.com> 





More followup...  I'm still working this with Oracle trying to convince the
support analyst that this really is a bug...  Ugh...  Anyway...  I looked
around on my systems and it appears to me that you this is not just a
9.2.0.3 issue...  I looked at the prvtlock.plb in a 9.2.0.2 install and it
also has this issue...  Which means you can also encounter this situation by
simply running catproc on an existing database...  So...  Be careful out
there if you use DBMS_LOCK...  I'm not sure how far back this situation
exists...



Tim



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


Sent: Wednesday, May 14, 2003 10:07 PM


To: Multiple recipients of list ORACLE-L




nice bit of detective work there Tim.



Jared




"Johnston, Tim" <TJohnston_at_quallaby.com>
Sent by: root_at_fatcity.com


 05/14/2003 05:00 PM


 Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        DBMS_LOCK.ALLOCATE_UNIQUE - BEWARE!!!






Ok...  I think I figured it out...  catalog and catproc are run during the
upgrade...  If you look in catproc, you will see that it calls
prvtlock.plb...  Look in prvtlock.plb and you will see the following...



create table dbms_lock_allocated(

        name            varchar2(128) primary key,
        lockid          integer,
        expiration      date)




/


drop sequence dbms_lock_id


/


create sequence dbms_lock_id start with 1073741824 maxvalue 1999999999
/




The table already exists since this was a 9.2.0.2 database...  But, it 
drops


and recreates the sequence...  The next lock allocate is therefore given 
an


id that ALREADY EXISTS in the dbms_lock_allocated table!!!  i.e.



Notice the table was created several months ago...



select object_name, created, last_ddl_time from dba_objects where
object_name = 'DBMS_LOCK_ALLOCATED';


OBJECT_NAME                                              CREATED    
LAST_DDL_TIME
DBMS_LOCK_ALLOCATED              11/15/2002 16:03:32 11/15/2002 16:03:32





But, the sequence was just created when I upgraded...



select object_name, created, last_ddl_time from dba_objects where
object_name = 'DBMS_LOCK_ID';


OBJECT_NAME                              CREATED  LAST_DDL_TIME
DBMS_LOCK_ID             05/06/2003 17:56:33             05/06/2003 


17:56:33




And of course it is starting over...



select sequence_name, last_number from dba_sequences where sequence_name =
'DBMS_LOCK_ID';



SEQUENCE_NAME            LAST_NUMBER
DBMS_LOCK_ID             1073741855





But, there are already values in dbms_lock_allocated that are greater then
the values it is issuing...



select count(*) from sys.dbms_lock_allocated where lockid > 1073741855;



COUNT(*)



116




And, here is the relevant part of the catproc log that shows this
occurring...




Package body created.



create table dbms_lock_allocated(


             *


ERROR at line 1:


ORA-00955: name is already used by an existing object





Sequence dropped.




Sequence created.




Now, if you look in a prvtlock.plb from a 8.1.7.4 upgrade you see the
following...



drop table dbms_lock_allocated


/


create table dbms_lock_allocated(

        name            varchar2(128) primary key,
        lockid          integer,
        expiration      date)




/


drop sequence dbms_lock_id


/


create sequence dbms_lock_id start with 1073741824 maxvalue 1999999999
/




So, it looks like someone removed the drop table dbms_lock_allocated from
the 9.2.0.3 prvtlock.plb....



HTH



Tim



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


Sent: Wednesday, May 14, 2003 6:02 PM


To: Multiple recipients of list ORACLE-L




Hi Everyone...



  I've encountered a strange issue and wanted to know if anyone else have
ever seen something like this...  I've had this issue occur on two 
different


systems...  In each case the systems Solaris 9 running 9.2.0.2 and then
upgraded to 9.2.0.3...  The issue is when you supply a string to
DBMS_LOCK.ALLOCATE_UNIQUE you should get a unique lock handle back...  For
example...



SQL> Declare


  2    sLockName1   Varchar2(100) :=


'PARTITION_C02_NRAW_000_H0_2003050400_LCK';


  3    sLockName2   Varchar2(100) := 'PVM_VIEWS_NRAW_000_LCK';
  4    sLockHandle1 Varchar2(100);
  5    sLockHandle2 Varchar2(100);




  6  Begin

  7    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  8    dbms_output.put_line(sLockHandle1);
  9    Dbms_Lock.Allocate_Unique( sLockName2, sLockHandle2 );
 10    dbms_output.put_line(sLockHandle2);


 11  End;


 12  /


10737418251073741825148



10737418261073741826149




PL/SQL procedure successfully completed.




But, I am encountering a situation where two DIFFERENT lock names are
producing the EXACT SAME lock handle...  i.e.




SQL> Declare


  2    sLockName1   Varchar2(100) :=


'PARTITION_C02_NRAW_000_H0_2003050500_LCK';


  3    sLockName2   Varchar2(100) := 'PVM_VIEWS_NRAW_000_LCK';
  4    sLockHandle1 Varchar2(100);
  5    sLockHandle2 Varchar2(100);




  6  Begin

  7    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  8    dbms_output.put_line(sLockHandle1);
  9    Dbms_Lock.Allocate_Unique( sLockName2, sLockHandle2 );
 10    dbms_output.put_line(sLockHandle2);


 11  End;


 12  /


10737418261073741826149



10737418261073741826149




PL/SQL procedure successfully completed.




Has anyone seen anything like this before?



Regards,


Tim Johnston


Tel:  978-322-4226


Fax: 978-322-4100



-- 



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


-- 



Author: Johnston, Tim


  INET: TJohnston_at_quallaby.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).


-- 



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


-- 



Author: Johnston, Tim


  INET: TJohnston_at_quallaby.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).






-- 



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


-- 



Author: 


  INET: Jared.Still_at_radisys.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).


-- 



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


-- 



Author: Johnston, Tim


  INET: TJohnston_at_quallaby.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 Thu May 15 2003 - 12:46:44 CDT












Original text of this message













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