Oracle-L: DBMS_LOCK.ALLOCATE_UNIQUE

From: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Wed, 14 May 2003 16:00:15 -0800
Message-ID: <F001.00599038.20030514160015@fatcity.com>

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

Received on Wed May 14 2003 - 19:00:15 CDT

DBMS_LOCK.ALLOCATE_UNIQUE - BEWARE!!!