Solved

Using DBMS_LOCK

Posted on 2004-04-01
9
2,654 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

Suggested Solutions

Title # Comments Views Activity
oracle report printing 2 pages in one page 2 67
Not able to drop or recreate an Oracle stored procedure 1 26
create a nested synonym 4 24
Oracle collections 15 16
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

813 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

14 Experts available now in Live!

Get 1:1 Help Now