Solved

Using DBMS_LOCK

Posted on 2004-04-01
9
2,633 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
Comment Utility
Hi,

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

Expert Comment

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

Expert Comment

by:vc01778
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

771 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

12 Experts available now in Live!

Get 1:1 Help Now