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

Home -> Community -> Mailing Lists -> Oracle-L -> UPDATE - DBMS_LOCK.ALLOCATE_UNIQUE

UPDATE - DBMS_LOCK.ALLOCATE_UNIQUE

From: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Fri, 23 May 2003 10:21:43 -0800
Message-ID: <F001.005A1A0D.20030523102143@fatcity.com> 





Hi Everyone...



  Some of you may remember my DBMS_LOCK issue from last week...  If you do,
I need someone to complain to...  My frustration is directed at Oracle
support...  Here goes...  I finally get the support analyst to understand my
issue...




"I understand what you are saying. And why the sequence number are starting

over, because the drop table dbms_lock_allocated is not being dropped when
running the prvtlock.plb package. Because of this the system can reuse old
dbms_lock_allocated number, thus causing duplicate numbers. I'll need to
consults with our kernal team to see if a bug is the correct method to relay
it to Development and explain to Development about how you tested it."



And the kernel team agrees with her so she opens a bug...  A non published
bug though...  They won't even let me see it...  So, a few days later, I get
this back from support...




"Development has indicated that the "drop table dbms_lock_allocated" was

removed from prvtlock.plb to fix Bug: 2334789. And that they need to
investigate more and better understand the behavior of how "SAME LOCK HANDLE
for 2 different LOCK NAME" are being generated. They have requested that you
provide them with a testcase of same sort."




My reply...




"I'll work on a testcase... But, I have to tell you that I'm very surprised

that they don't understand this... I can understand they removed the table
drop for some other issue but they should have also removed the drop
sequence also... It angers me that I have to do something like this for a
fairly simple and straight forward problem... But, that's the way things are
when dealing with Oracle so that's what I'll do... I'll attach the testcase
when I get it together..."



<RANT>


They need a friggin test case?  Are you f'ing kidding me?  They don't
understand the issue without one?  And this person is one of the developers
of this stuff?  So, now I have to spend my time to produce a nice and simple
test case so they understand this...  ARGH!
</RANT>



Thanks...  I just needed to get that off my chest...



For those of you that are interested...  I've attached a log of my testcase
run to illustrate the issue...  I still can't believe I needed to provide
this level of detail...



I will now allocate unique ids for five different locks



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID1';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418241073741824147




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID2';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418251073741825148




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID3';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418261073741826149




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID4';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418271073741827150




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID5';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418281073741828151




PL/SQL procedure successfully completed.



And here is the current contends of the DBMS_LOCK_ALLOCATED table



SQL> select * from SYS.DBMS_LOCK_ALLOCATED order by LOCKID;



NAME








    LOCKID EXPIRATIO







TEST_CASE_LOCK_ID1


1073741824 01-JUN-03


 

TEST_CASE_LOCK_ID2


1073741825 01-JUN-03


 

TEST_CASE_LOCK_ID3


1073741826 01-JUN-03


 

TEST_CASE_LOCK_ID4


1073741827 01-JUN-03


 

TEST_CASE_LOCK_ID5


1073741828 01-JUN-03


 

And here is the last id number issued from the lock sequence



SQL> 



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


SEQUENCE_NAME                  LAST_NUMBER

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

DBMS_LOCK_ID                    1073741844






And there is one and only one occurence of a lock id in the
DBMS_LOCK_ALLOCATED table



SQL> select lockid, count(*) from SYS.DBMS_LOCK_ALLOCATED group by lockid;



    LOCKID   COUNT(*)







1073741824          1



1073741825          1



1073741826          1



1073741827          1



1073741828          1




Now that I have run catalog and catproc...  Next I will allocate five more
new lock ids



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID6';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418241073741824147




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID7';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418251073741825148




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID8';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418261073741826149




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID9';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418271073741827150




PL/SQL procedure successfully completed.



SQL> 



SQL> Declare


  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID10';
  3    sLockHandle1 Varchar2(100);


  4  Begin


  5    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  6    dbms_output.put_line(sLockHandle1);
  7  End;


  8  /


10737418281073741828151




PL/SQL procedure successfully completed.



Notice that I now have duplicate lock ids in the DBMS_LOCK_ALLOCATED table



SQL> select * from SYS.DBMS_LOCK_ALLOCATED order by LOCKID;



NAME








    LOCKID EXPIRATIO







TEST_CASE_LOCK_ID1


1073741824 01-JUN-03


 

TEST_CASE_LOCK_ID6


1073741824 01-JUN-03


 

TEST_CASE_LOCK_ID2


1073741825 01-JUN-03


 

TEST_CASE_LOCK_ID7


1073741825 01-JUN-03


 

TEST_CASE_LOCK_ID3


1073741826 01-JUN-03


 

TEST_CASE_LOCK_ID8


1073741826 01-JUN-03


 

TEST_CASE_LOCK_ID4


1073741827 01-JUN-03


 

TEST_CASE_LOCK_ID9


1073741827 01-JUN-03


 

TEST_CASE_LOCK_ID5


1073741828 01-JUN-03


 

TEST_CASE_LOCK_ID10


1073741828 01-JUN-03


 


10 rows selected.



That is because the lock sequence was dropped and recreated which caused it
to reissue lock ids



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


SEQUENCE_NAME                  LAST_NUMBER

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

DBMS_LOCK_ID                    1073741844






Which results in duplicates in the DBMS_LOCK_ALLOCATED table



SQL> select lockid, count(*) from SYS.DBMS_LOCK_ALLOCATED group by lockid
having count(*) > 1;



    LOCKID   COUNT(*)







1073741824          2



1073741825          2



1073741826          2



1073741827          2



1073741828          2




Now I will show you the two lock handles giving you the same id



SQL> Declare

  2    sLockName1   Varchar2(100) := 'TEST_CASE_LOCK_ID1';
  3    sLockHandle1 Varchar2(100);
  4    sLockName2   Varchar2(100) := 'TEST_CASE_LOCK_ID6';
  5    sLockHandle2 Varchar2(100);




  6  Begin


  7    Dbms_Lock.Allocate_Unique( sLockName1, sLockHandle1 );
  8    dbms_output.put_line('The lock handle I got for TEST_CASE_LOCK_ID1
was: '||sLockHandle1);


  9    Dbms_Lock.Allocate_Unique( sLockName2, sLockHandle2 );
 10    dbms_output.put_line('The lock handle I got for TEST_CASE_LOCK_ID6
was: '||sLockHandle2);


 11    dbms_output.put_line('Notice that these two DIFFERENT lock names
result in the');


 12    dbms_output.put_line('same exact lock handle being
issued!!!!!!!!!!');


 13  End;


 14  /


The lock handle I got for TEST_CASE_LOCK_ID1 was: 10737418241073741824147



The lock handle I got for TEST_CASE_LOCK_ID6 was: 10737418241073741824147



Notice that these two DIFFERENT lock names result in the



same exact lock handle being issued!!!!!!!!!!




PL/SQL procedure successfully completed.



SQL> 



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).


Received on Fri May 23 2003 - 13:21:43 CDT












Original text of this message













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