Re: DBMS_LOCK.ALLOCATE_UNIQUE and savepoints (7.3.3)

From: Shajith <joseph_sumalbag_at_bose.com>
Date: 1997/12/01
Message-ID: <3483306A.6229@bose.com>#1/1

Hi remember me ?

   maybe this will help answer your question

excerpt ......

---

if (lockid mod 100) = 0 then

            delete dbms_lock_allocated where expiration < sysdate;

            commit;
          end if;
------------------------

As you can see there is a commit on the code .... without the commit , you'll have a lock contention for the dbms_lock_allocated ....

Ciao !

Francis Small wrote:
>
> My system is:
>
> HP-UX 9.05
> SQL*Plus: Release 3.3.3.0.0 - Production on Mon Nov 24 12:36:16 1997
> Oracle7 Server Release 7.3.3.0.0 - Production Release
> PL/SQL Release 2.3.3.0.0 - Production
>
> Is DBMS_LOCK.ALLOCATE_UNIQUE incompatible with savepoints?
>
> I've defined the following .sql files
>
> ssp.sql:
> begin savepoint sp_1; end;
> /
>
> rsp.sql:
> begin rollback to sp_1; end;
> /
>
> And in SQL*Plus can do:
>
> SQL> @ssp;
> PL/SQL procedure successfully completed.
> SQL> @rsp;
> PL/SQL procedure successfully completed.
>
> So far, so good. I can also request, convert and release user-allocated locks:
>
> SQL> var rv number;
> SQL> exec :rv := DBMS_LOCK.REQUEST(1, 1);
> PL/SQL procedure successfully completed.
> SQL> @rsp;
> PL/SQL procedure successfully completed.
> SQL> exec :rv := DBMS_LOCK.CONVERT(1, 2);
> PL/SQL procedure successfully completed.
> SQL> @rsp;
> PL/SQL procedure successfully completed.
> SQL> exec :rv := DBMS_LOCK.RELEASE(1);
> PL/SQL procedure successfully completed.
> SQL> @rsp;
> PL/SQL procedure successfully completed.
>
> Still happy as a clam. Why the heck am I posting this? Well, NOW try:
>
> SQL> var lh VARCHAR2(128);
> SQL> exec DBMS_LOCK.ALLOCATE_UNIQUE('a', :lh);
> PL/SQL procedure successfully completed.
> SQL> @rsp;
> begin rollback to sp_1; end;
> *
> ERROR at line 1:
> ORA-01086: savepoint 'SP_1' never established
> ORA-06512: at line 1
>
> As far as I can tell, any call to DBMS_LOCK_ALLOCATE_UNIQUE obliterates all of
> your savepoints.
>
> SAY IT'S NOT SO!!! :-( Any help, out there?
>
> Francis Small
>
> --
> . . . . . . . . . | 1 . . . . . . . . . 2 . . . . . . . . . 3 . . . . . . . .
> Giants Francis Small - M/S M3RA
> Bums Hewlett-Packard Company
> Rockies Microwave Instruments Div.
> Padres 1212 Valley House Dr.
> Rohnert Park CA, 94928
> 707 794 3305
> fthes_at_sr.hp.com
> ***************** Final 1997 National League West Standings *****************

-- 
================================================================
============================
| Joseph Sumalbag                                                                           
|
| Oracle DBA                                                                                
|
|                                                                                           
|
| The opinions expressed above are my own and doesn't 
 necessarily                           |
|reflect the opinion of any of my client company or my employer.                            
|
================================================================
============================

Received on Mon Dec 01 1997 - 00:00:00 CST