LOCKWAIT [message #362396] |
Tue, 02 December 2008 03:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
lioracle
Messages: 68 Registered: February 2008 Location: Israel
|
Member |
|
|
i run this command
"
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
2 and l1.id1=l2.id1
3 4 and l1.id2=l2.id2
5 6 /
BLOCKING
--------------------------------------------------------------------------------
SID 481 is blocking 492
481 = oracle@r3_mar (QMNC)
492 = oracle@r3_mar (CJQ0)
what that mean and how i release this?
thanks
|
|
|
|
|
|
|
|
Re: LOCKWAIT [message #362448 is a reply to message #362396] |
Tue, 02 December 2008 06:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your results mean the the Session identified by SID=481 has a lock on some resource, most likely one or more rows in a table.
The session identified by SID=491 wants to get a lock on the same resource (Probably wants to update/delete the rows)
The second session cannot proceed until the first session has released the lock.
This is usually done by the session finishing it's current transaction with either a commit or a rollback.
If neccessary, you can kill the first session, but this should only be done if there is not other way to get it to release the lock.
|
|
|
Re: LOCKWAIT [message #362936 is a reply to message #362448] |
Thu, 04 December 2008 10:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Hi Experts,
Can you explain me some points:-
1,How can I get the sql statement from blocking session from this.
2,If the session is owned by sys user then how can I commit or
rollback it.I would be thnakfull If you can elaborate on this
point .
Regards,
Varun Punj,
|
|
|
|
Re: LOCKWAIT [message #362954 is a reply to message #362950] |
Thu, 04 December 2008 11:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Thanks Michel.
Is this the right statement to get sql statement:-
select sql_text from v$sql where sql_id in( select prev_sql_id from v$session where sid='sid of blocking session')
Regarding the second point,You mean There is no way to commit or
rollback session of other users.We can only kill them. Please tell me some document related to locks.
Regards,
Varun Punj
|
|
|
Re: LOCKWAIT [message #362958 is a reply to message #362954] |
Thu, 04 December 2008 12:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ sql_id or prev_sql_id in v$session depending on the case (active, inactive...)
2/ Yes
Regards
Michel
|
|
|