Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using DBMS_LOCK

Posted on 2004-04-01
9
Medium Priority
?
2,737 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.

596 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