Which query caused the lock?

[huchen]

Hello, Could anybody help me to find out what query causes a database lock?

Here is what I want to know:
#1
How do I identify which lock is bad lock from v$lock?(I can get 200 rows from this view)
#2
If I found the sid that cause the bad lock from v$lock, how do I found out which query caused it?
#3
V$LOCKED_OBJECT.LOCKED_MODE, what does value 2 or 3 mean?

Thank you very much.
Huchen

 

[Dagon]

A query should not normally cause a lock unless it's a select ... for update or you're trying to perform DDL against the table such as creating an index.

Restrict your query on the request and block columns e.g.

select * from v$lock where request <> 0 or block <> 0

This should show you any processes waiting for locks or blocking other users. You can then look in v$session and v$sqlarea to get more information.

 

[huchen]

Thank you very much. I still do not know how to join v$lock and v$sqlarea. Could you please show me?

 

[Dagon]

You have to go via v$session.

select s.sid, a.sql_text from v$sqlarea a, v$session s
where s.sql_address = a.address
and  s.sql_hash_value = a.hash_value
and  s.sid = XXX