Coskan’s Approach to Oracle

February 6, 2007

Who is locking with what type of lock ?

Filed under: How To — coskan @ 12:54 pm

Some of our main application processes hanged today (not the database only the processes) while i was at lunch time. When I arrived the company my manager asked me to look at the database for understanding the cause of hang. I simply realised that there is a lock on somewhere which is waiting other sessions.

I followed the following way to understand what was going on for solving the problem.

1- who is blocking
select holding_session from dba_blockers;

2- what is he blocking

select sid,serial#,sql_text,username from v$sql s,v$session se where se.sql_id=s.sql_id where sid=’holder';

3- when i looked in the sql_text i understood session is working on table X

after that i looked at what is the type of lock for being sure about the cause of the problem.

/*who is locking ? what type of lock ?*/

select A.sid,A.type,DECODE (A.REQUEST,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
) REQUEST,
DECODE( A.lmode,
0,’None’ ,
1, ‘Null’ ,
2, ‘Row-S’ ,
3, ‘Row-X’ ,
4, ‘Share’ ,
5, ‘S/Row-X’,
6, ‘Exclusive’
) LMODE,
DECODE (A.BLOCK,
0, ‘None’ ,
1, ‘BLOCKING’) BLOCK,
B.os_user_name,
C.object_name
from v$lock A, v$locked_object B, dba_objects C
where B.session_id=A.sid and C.object_id = B.object_id
AND OWNER=’OWNER’ and c.object_name=’X;

4- The session was one of the DBA’s session so the session could be killed and i killed the session

alter system kill session ‘sid,serial#’

5-I looked back to blockers table and became sure that the problem is solved.

select holding_session from dba_blockers;

>no rows

About these ads

1 Comment »

  1. Thanks for sharing this information. Really is pack with new knowledge. Keep them coming.

    Comment by Great Home Improvement Info — August 17, 2007 @ 12:57 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: