Re: Interesting question about locks !

To do what you want, you must select for update, let a session hang for a second, open a second session in the background, check the blocking locks, then timeout the waiting session from the application level. There is no way to get the blocker without establishing a blocking situation.

This logic can be built into an application, and won't feel much different than a nowait to the user.

--
Jeremiah

Didier LENQUETTE wrote:


> 

> Hi !

> 

> Your request is very useful, but I have already one like this.

> 

> My problem is not an administrator problem, but a user problem.

> 

> Assume, your are in a sqlplus session.

> You make a SELECT ... FOR UPDATE NOWAIT. that returns you ONLY ONE ROW

> You become ORA-0054 : ressource busy

> You want to know who is locking the row.

> 

> When you  launch your SQL, you become 100 rows, because 100 others users are

> working on this table !

> But only ONE user is locking the row want to lock.... But Who ?

> 

> Another idea ?

> 

> Iosif Tanasescu <iosif.tanasescu_at_cgi.ca> a écrit dans le message :

> 38B571EA.7B55EBAB_at_cgi.ca...

> > Hi

> >

> > try:

> >

> > select     ORACLE_USERNAME||' is currently locking

 '||owner||'.'||object_name,


> >        ' from terminal '||os_user_name||' with the program:'||PROGRAM

> > from

> >    v$locked_object a,

> >    all_objects b,

> >    v$session c

> >  where

> >    a.object_id = b.object_id

> >    and c.sid=a.session_id

> > /

> >

> > bye!

> >

> > Didier LENQUETTE wrote:

> >

> > > Hi !

> > >

> > > I manage locks into my database with SELECT ... FOR UPDATE NOWAIT.

> > >

> > > When a record is already locked by another user, I wish to know who is

> > > locking the record I am trying to lock.

> > >

> > > I know the locked objects with the v$lock view and the "locker", but I

 don't


> > > know who locks a particular record !

> > >

> > > Example :

> > > User A lock row #1 and #3 of table1

> > > User B tries to lock row #3 of table1 : he becomes error -54 OK !

> > > I want to know who is locking row #3 in order to display a message like

> > > 'User A locks row #3'

> > >

> > > An idea ?

> > >

> > > --

> > >

> > > ________________

> > > Didier LENQUETTE

> > > Tel :  (33) 5 62 12 20 40

> > > Fax : (33) 5 61 31 07 02

> >