Home » SQL & PL/SQL » SQL & PL/SQL » locked rows (solaris, oracle 9i)
locked rows [message #326658] Thu, 12 June 2008 03:25 Go to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Hi Experts,
How to find out which row is locked and by which session?

Could you please help me on this?


Thanks,
Sheldon
Re: locked rows [message #326693 is a reply to message #326658] Thu, 12 June 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By dumping the blocks.
This is the only way.

Regards
Michel
Re: locked rows [message #326698 is a reply to message #326658] Thu, 12 June 2008 05:31 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Could you please explain me what do you mean by dumping the blocks.
Re: locked rows [message #326700 is a reply to message #326698] Thu, 12 June 2008 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't mind, it is just a hacking way.

Regards
Michel
Re: locked rows [message #326702 is a reply to message #326693] Thu, 12 June 2008 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try doing a SELECT ...FOR UPDATE NOWAIT on each row individually. That would tell you whether a row was locked at the point that query checked it.
Re: locked rows [message #326742 is a reply to message #326658] Thu, 12 June 2008 08:32 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
I have hundreds of rowid to check.
so i have to each individually.
Re: locked rows [message #326745 is a reply to message #326742] Thu, 12 June 2008 08:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Locks are transient things generally. What is the problem that you're trying to solve?
If you've got locks blocking other locks, there are ways to find them.
Re: locked rows [message #326748 is a reply to message #326658] Thu, 12 June 2008 08:40 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Actually the client has given me some rowid and asked me to check
whether this row_id are locked, if yes then by which user.
Even i am confued about this.
I know that how to find session and object but i have never done for rowid.
Re: locked rows [message #326754 is a reply to message #326742] Thu, 12 June 2008 08:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you can do is fire up two SQL*Plus sessions.

In session 1 you will try and get locks on the rows for the specified ids.
Run a piece of sql like this:
DECLARE
  cursor c_lock is
    SELECT * 
    FROM   <table_name>
    WHERE  rowid in (<rowid1>,<rowid2>,.....)
    FOR UPDATE;
BEGIN
  open c_lock;
  close c_lock;
END;
/


If there is a lock on that rowid, then this session will wait for the lock to become free.

If that happens, go to session 2 and run this query:
select s.sid,s.username
from   v$session s
      ,v$lock l
where  l.block = 1
and    l.sid = s.sid;

This will get you the usernames of the users holding the lock on the rows you are trying to update
Re: locked rows [message #326766 is a reply to message #326748] Thu, 12 June 2008 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually the client has given me some rowid and asked me to check
whether this row_id are locked, if yes then by which user.

The question is why they need that?

Regards
Michel
Re: locked rows [message #326769 is a reply to message #326658] Thu, 12 June 2008 09:38 Go to previous message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Thanks for your help.
I really appreciate your involvement.
Previous Topic: Schema
Next Topic: How can I solve SP2-0024: Nothing to change.
Goto Forum:
  


Current Time: Sat Dec 03 20:27:47 CST 2016

Total time taken to generate the page: 0.08203 seconds