Home » RDBMS Server » Performance Tuning » LOCKWAIT (10g AIX)
LOCKWAIT [message #362396] Tue, 02 December 2008 03:32 Go to next message
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 #362401 is a reply to message #362396] Tue, 02 December 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't post your TITLE in UPPER
2/ Commit or rollback first session

Regards
Michel
Re: LOCKWAIT [message #362406 is a reply to message #362396] Tue, 02 December 2008 03:52 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
i dont understand u.
Re: LOCKWAIT [message #362419 is a reply to message #362406] Tue, 02 December 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The proper ways to release locks is to commit or rollback.

Regards
Michel
Re: LOCKWAIT [message #362421 is a reply to message #362419] Tue, 02 December 2008 04:14 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
but its now regualr session
in oracle PROCESS session
Re: LOCKWAIT [message #362424 is a reply to message #362421] Tue, 02 December 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand you.

Regards
Michel
Re: LOCKWAIT [message #362448 is a reply to message #362396] Tue, 02 December 2008 06:06 Go to previous messageGo to next message
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 messageGo to next message
varunvir
Messages: 363
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 #362950 is a reply to message #362936] Thu, 04 December 2008 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. join v$session and v$sql% view
2. you can't commit or rollback a transaction/session outside it, you can just kill it

Regards
Michel
Re: LOCKWAIT [message #362954 is a reply to message #362950] Thu, 04 December 2008 11:56 Go to previous messageGo to next message
varunvir
Messages: 363
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
Michel Cadot
Messages: 64109
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
Previous Topic: how to find the query optimized or Not?
Next Topic: whats syntax to keep table in memory?
Goto Forum:
  


Current Time: Mon Dec 05 07:12:23 CST 2016

Total time taken to generate the page: 0.16764 seconds