SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess, id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request
;
Above query will only provide the list of Holder and blocked session only. To get the result in one row, use the following.
select
(select distinct username from gv$session where inst_id=a.inst_id and sid=a.sid) blocker,
a.inst_id, a.sid,
' is blocking ' "IS BLOCKING",
(select distinct username from gv$session where inst_id=b.inst_id and sid=b.sid) blockee,
b.inst_id, b.sid, a.LMODE "Blocker Lock mode", a.REQUEST "Blocker Request", b.LMODE "Waiter Lock mode", b.REQUEST "Waiter Request"
from gv$lock a, gv$lock b
where a.inst_id = b.inst_id
and a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
;
BLOCKER INST_ID SID IS BLOCKINGBLOCKEE INST_ID SID Blocker Lock mode Blocker Request Waiter Lock mode Waiter Request
------- ------- --- ------------------ ------- ------- ----------------- --------------- ---------------- --------------
USER_1 1 219 is blocking USER_1 1 213 6 0 0 6
Once you get the information, then search for queries they are using:-
SID STATUS SQL_HASH_VALUE SQL_ID PREV_SQL_ID
--- -------- -------------- ------------- -----------
213 ACTIVE 662921877 cn5hy5hms6snp 1dju1fycmanwn
219 INACTIVE 0 2nmkmqsupnn8k
Find the SQL which is blocked using:
select sql_fulltext from v$sql where sql_id='cn5hy5hms6snp';
Use the above to find the SQL, which is blocking session 213 use PREV_SQL_ID (there is no gurantee that this column save blocking sql rather it is storing the last SQL fired).
SQL_FULLTEXT
----------------------------------------
delete temp_5 where a=:"SYS_B_0"
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid = 213;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
1289617 19 3059159 1
select dbms_rowid.rowid_create(1, 1289617, 19, 3059159, 1) row_id from dual;
ROW_ID
------------------
AAE62RAATAALq3XAAB
Now the last step is to find the row which is causing this problem using the following:
select *from temp_5 where rowid='AAE62RAATAALq3XAAB';
A B
-- --
1 1
set serveroutpout on
set linesize 1000
define User_name = &1
declare
Table_name varchar2(30);
cursor C1 is SELECT l.sid, l.inst_id, Blocking_session, object_name FROM GV$LOCK l, gv$session s, dba_objects o
WHERE (l.id1, l.id2, l.type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
and DECODE(request,0,0,1)=1
and l.sid=s.SID
and l.inst_id = s.INST_ID
and s.ROW_WAIT_OBJ#=o.object_id
order by l.sid, logon_time;
Curr_row_id varchar2(90);
Curr_row varchar2(4000);
cursor holder_ses is select distinct rpad(session_id || ' ' || session_serial#, 11, ' ') || rpad (bc.Name || ' ' || bc.value_string,20,' ') || ' ' || ah.SQL_ID || ' ' || substr(sql_text,1,90) Holding_ses_info,
bc.value_string, bc.name, session_id
from gv$ACTIVE_SESSION_HISTORY ah, gv$sql s, gv$sql_bind_capture bc
where
ah.inst_id = s.inst_id
and ah.sql_id = s.sql_id
and bc.sql_id(+) = ah.sql_id
and bc.value_string is not null
and (SESSION_ID, SESSION_SERIAL#) in ( select s.sid, s.serial# from gv$lock l, gv$session s
where l.inst_id = s.inst_id and l.ADDR=s.taddr
and (s.sid, s.inst_id) in ( SELECT sid, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
and DECODE(request,0,0,1)=0 ) )
and SAMPLE_TIME BETWEEN sysdate - 1/24 and sysdate
order by session_id
;
Holders_sql holder_ses%rowtype;
------------FUNCTION
Function com_sep_val ( tab_name varchar, usr_name varchar) return varchar is
ret_char varchar(2000);
rec_val varchar(1000);
type cur_ref is ref cursor;
c1 cur_ref;
begin
open c1 for 'select column_name from ( select column_name from dba_tab_columns where owner=''' || usr_name || ''' and table_name = ''' || tab_name || ''' order by column_id) where rownum <=3';
ret_char := '';
loop
fetch c1 into rec_val;
exit when c1%notfound;
ret_char := ret_char || ',' || rec_val;
end loop;
close c1;
return substr(ret_char,2);
end;
---------- END FUNCTION
begin
DBMS_OUTPUT.PUT_LINE( chr(10) );
DBMS_OUTPUT.PUT_LINE('SID SERIAL BIND VALUE SQL_TEXT');
DBMS_OUTPUT.PUT_LINE('--- ------ -------- --------- -------------------------------------------------------');
open holder_ses;
loop
fetch holder_ses into Holders_sql;
exit when holder_ses%notfound;
DBMS_OUTPUT.PUT_LINE ( Holders_sql.Holding_ses_info );
end loop;
DBMS_OUTPUT.PUT_LINE( chr(10) );
close holder_ses;
for rec in C1 loop
select --row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create(1, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# ) into Curr_row_id
from v$session
where sid = ( rec.sid );
DBMS_OUTPUT.PUT( chr(10) || rec.sid || ' ' || rec.object_name || ' ' || curr_row_id || ' ');
declare
TYPE rec_def IS RECORD(COL VARCHAR(4000), col2 varchar(1000), col3 varchar(1000));
rec_val rec_def;
TYPE cur_ref is ref cursor;
c2 cur_ref;
begin
open c2 for 'select ' || com_sep_val ( rec.object_name, '&User_name') || ' from &User_name.' || rec.object_name || ' where rowid= ''' || curr_row_id || '''' ;
loop
fetch c2 into rec_val;
exit when c2%notfound;
dbms_output.put( '--- ');
dbms_output.put(rec_val.col || ' ' || rec_val.col2 || ' ' || rec_val.col3);
dbms_output.put(' --- Blocked by:' || rec.BLOCKING_SESSION);
declare
cursor child_cur is select distinct ' ' || bc.name || ':' || bc.value_string || ' ' || substr(sql.SQL_TEXT, 1, 90) Ses_info
from gv$session s, gv$sql sql, gv$sql_bind_capture bc
where
s.inst_id = sql.inst_id
and s.sql_id=sql.SQL_ID
and sql.sql_id = bc.sql_id(+)
and s.sid = rec.sid
and bc.value_string is not null;
begin
for rec_child in child_cur loop
dbms_output.put_line(rec_child.ses_info);
end loop;
end;
end loop;
close c2;
exception when others then
declare
cursor child_cur is select distinct ' --- Blocked by:' || rec.BLOCKING_SESSION || ' ' || bc.name || ': ' || bc.value_string || ' Sql_id: ' || sql.sql_id || ' Stmt: ' || substr(sql.SQL_TEXT, 1, 90) Ses_info
from gv$session s, gv$sql sql, gv$sql_bind_capture bc
where
s.inst_id = sql.inst_id
and s.sql_id=sql.SQL_ID
and sql.sql_id = bc.sql_id(+)
and s.sid = rec.sid
and bc.value_string is not null;
begin
for rec_child in child_cur loop
dbms_output.put_line(rec_child.ses_info);
end loop;
end;
end;
end loop;
end;
/
Also available as a download:
From first session do the following :-
update temp_dev2 set c2=c2 where p1=1;
From second session do the following :-
update temp_dev2 set c2=c2 where p1=3;
update temp_dev2 set c2=c2 where p1=1; ------- Session is blocked by first session.
select INST_ID, SID, SERIAL#, blocking_instance, blocking_session from gv$session where blocking_session is not null;
INST_ID SID SERIAL# BLOCKING_INSTANCE BLOCKING_SESSION
---------- ---------- ---------- ----------------- ----------------
1 537 6706 1 520
update temp_dev2 set c2=c2 where p1=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
create table temp_dev3( a number, b number, c number, d number, primary key (a,b));
create table temp_dev4(a number, b number, c number, d number, foreign key (c,d) references temp_dev3(a,b));
-- Insert data like this.
select *from temp_dev3;
A B C D
---------- ---------- ---------- ----------
1 1 1 1
1 2 1 1
1 3 1 1
2 1 2 2
2 2 2 2
2 3 2 2
2 4 2 2
2 5 1 1
select * from temp_dev4;
A B C D
---------- ---------- ---------- ----------
1 1 1 1
1 2 1 1
1 2 1 1
1 4 1 1
2 1 2 2
2 2 2 2
2 3 2 2
2 4 2 2
1 1 1 2
1 1 1 3
1 1 2 1
1 1 2 2
1 1 2 3
1 1 2 4
SQL> delete temp_dev4 where c=1 and d=1;
4 rows deleted.
SQL> delete temp_dev3 where a=1 and b=1;
1 row deleted.
-- From Other Session (This session will hang)
SQL> delete temp_dev3 where a=1 and b=2;
Session 1.
delete temp_dev3 where a=2 and b=5;
Session 2.
delete temp_dev4 where c=1 and d=1;
delete temp_dev3 where a=1 and b=1;
Session 1.
delete temp_dev3 where a=1 and b=2;
It will hang
Session 2.
insert into temp_dev3 values (2,5,5,5);
This will also hang because record with PK 2,5 is locked.
Session 1. Detects a Deadlock.
delete temp_dev3 where a=1 and b=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:02:00.98
Now the question is: Why this is a DBA problem? The DBA doesn't directly deal with data.
Session 1.
delete temp_dev3 where a=2 and b=5;
Session 2.
delete temp_dev4 where c=1 and d=1;
delete temp_dev3 where a=1 and b=1;
Session 1. It will hang
delete temp_dev3 where a=1 and b=2;
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess, id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request
;
SESS ID1 ID2 LMODE REQUEST TY INST_ID
------------------------------ ---------- ---------- ---------- ---------- -- ----------
Holder: 537 1308910 0 3 0 TM 1
Waiter: 520 1308910 0 0 4 TM 1
Now create required indexes on child tables.
In Session 1:
create table temp_dev5 (a number, b number, c number, d number, primary key (a,b), foreign key (c,d) references temp_dev5(a,b));
create index temp_dev5_ndx on temp_dev5(C,D); ---- Make sure you have all FK indexes.
-- insert data like this
select * from temp_dev5;
A B C D
---------- ---------- ---------- ----------
1 1
1 2 1 1
1 3 1 2
1 4 1 3
2 2
2 1 1 1
2 3 2 2
2 4 1 4
update temp_dev5 set c=null, d=null where c=1 and d=4;
select * from temp_dev5;
A B C D
---------- ---------- ---------- ----------
1 1
1 2 1 1
1 3 1 2
1 4 1 3
2 2
2 1 1 1
2 3 2 2
2 4
update temp_dev5 set b=6 where a=1 and b=4;
In Session 2:
update temp_dev5 set d=6 where a=1 and b=3;
-- This session will hang although all required indexes are there, and we are trying to update different rows.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Author
Commented: