?
Solved

Using DBMS_LOCK

Posted on 2004-04-01
9
Medium Priority
?
2,707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 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