Oracle-L: Followup: DBMS_LOCK.ALLOCATE

From: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Wed, 14 May 2003 14:59:40 -0800
Message-ID: <F001.00598F68.20030514145940@fatcity.com>

After a little more digging I've traced the issue to the DBMS_LOCK_ALLOCATED table... The issue is that these string both map to the same LOCKID in the DBMS_LOCK_ALLOCATED table... i.e.

select * from SYS.DBMS_LOCK_ALLOCATED where LOCKID = 1073741826;

NAME							LOCKID	EXPIRATION
PVM_VIEWS_NRAW_000_LCK				1073741826	05/24/2003
16:58:00
PARTITION_C02_NRAW_000_H0_2003050500_LCK	1073741826	05/24/2003

16:58:00

Now... How did that happen!?!?!?! And, to make matter worse, it appears that there are other candidates for this issue... i.e.

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

LOCKID	COUNT(*)
1073741824	2
1073741826	2
1073741827	2
1073741828	2
1073741829	2
1073741830	2
1073741831	2
1073741832	2
1073741833	2
1073741836	2

Oh well... And yes, I do have a TAR open...

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

Followup: DBMS_LOCK.ALLOCATE_UNIQUE