Solved

Using DBMS_LOCK

Posted on 2004-04-01
9
2,648 Views
Last Modified: 2008-02-26

I have a question using the Oracle built in package DBMS_LOCK. This package is being used
to take out a lock against a lock name.
I was wondering if there is anyway of checking if a lock has been applied without actually
trying to lock the object again. Basically I want to say - here is a lock name, has it
been applied by anyone?
I have tried using some DBA view such as DBMS_LOCK, however,I wanted to use something that
would show information about the name of the lock being used and not just give me process id's.
0
Comment
Question by:jpboy
9 Comments
 
LVL 6

Expert Comment

by:musdu
ID: 10730694
Hi,

you should see lock name in DBMS_LOCK_ALLOCATED table.
0
 
LVL 1

Expert Comment

by:patilyogesh
ID: 10732317
hi..
u can use
this also
DBA_DDL_LOCKS
DBA_DML_LOCKS
and
DBMS_LOCK_ALLOCATED
0
 
LVL 11

Expert Comment

by:vc01778
ID: 10732410
Well,  it's pretty straight-forward:

declare
    l_id   int;
      l_stat int;
begin
  l_id := 123;
  l_stat := dbms_lock.request
             (  id                => l_id,
                lockmode          => dbms_lock.x_mode,
                timeout           => 0,
                release_on_commit => TRUE );
end;


-- the query:

select sid, type, id1 from v$lock where type='UL'

-- result:

8      UL      123

The user lock (UL) has the id of 123.

VC
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:vc01778
ID: 10732441
Despite your unwillingnes to use the request/release pair,  it's more performant than quering a system view:

declare
    l_id   int;
      l_stat int;
begin
  l_id := 123;
  l_stat := dbms_lock.request
             (  id                => l_id,
                lockmode          => dbms_lock.x_mode,
                timeout           => 0,
                release_on_commit => TRUE );
  if l_stat = 1 then
    dbms_output.put_line('Lock has already been taken');
  else
    dbms_output.put_line('The lock is available');
      l_stat := dbms_lock.release(id => l_id);
  end if;
end;
/

VC
0
 

Author Comment

by:jpboy
ID: 10756125
I tried using DBMA_LOCK_ALLOCATED, however, this doesnt tell me when the lock will expire or if the calling code is actually still locking the object. The expiration in this table only refers to Oracles perceived time of expiration and not the actua; expiry time.

Is there any way that I can use the lockid in dbms_lock_allocated to find out what time the lock expired or if in fact the lock is still running?
0
 
LVL 11

Expert Comment

by:vc01778
ID: 10756225
dbms_lock_allocated has got nothing to do with the fact whether the lock identifier is used or not.  It simply shows whether the identifier was allocated and when it's supposed to expire.

Earlier,  I showed you two ways of how to determine whether the lock has been requested by another session:

1.  Using the v$lock table
2. programmatically by trying to request a lock with a given identifier.

There is simply no other way to do it.
0
 
LVL 11

Accepted Solution

by:
vc01778 earned 500 total points
ID: 10756226
dbms_lock_allocated has got nothing to do with the fact whether the lock identifier is used or not.  It simply shows whether the identifier was allocated and when it's supposed to expire.

Earlier,  I showed you two ways of how to determine whether the lock has been requested by another session:

1.  Using the v$lock table
2. programmatically by trying to request a lock with a given identifier.

There is simply no other way to do it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now