| locked rows [message #326658] |
Thu, 12 June 2008 03:25  |
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 #326702 is a reply to message #326693] |
Thu, 12 June 2008 05:53  |
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 #326745 is a reply to message #326742] |
Thu, 12 June 2008 08:36 |
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 |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
