Browse All Articles > Blocking Locks -- Which Row Caused the Problem?
By Devinder S Virdi
Introduction:
One of the regular task we must do is to check if there are any blocking locks. Sometimes we find deadlocks, and as a DBA we are unable to say which row caused them.
Management keeps on asking for the solution and we keep on saying: improper use of commit, some missing FK indexes, insufficient initrans parameter etc., were the causes. When they asked to check the blocking locks during application testing and they assign us the task to find the row which is causing this, we tried to escape from this test :-)
Normally we don't have any answer other than, session 1 is blocking session 2. And that is not really all that useful to know.
To Find Blocking locks we will use the following SQL sequences:
select SID, Status, sql_hash_value, sql_id, prev_sql_id from gv$session where sid in(219, 213) and inst_id=1;
We are only providing the above part to development team if there are some blocking locks and preferred to kill the session.
Let's move forward to find which row has caused this blocking.
THe query below will give you the object_id, File_id, Block_id and Row_id as:
Find Rowid based on above using:
Since you know that object temp_5 is blocked, put username followed by table name to find exact row as:
Feasibility of above code
Above process will take a long time to find locked row specially working with production. Also it’s very confusing when tying to find multiples session with multiple locks.
Here is the procedure which displays the first three columns of every locked row in a table including bind variables value.
This procedure also displays the history of blocker using ASH.
blocking-info.sql
Other Problems created by Blocking Locks
One of the biggest problem of blocking lock is that it can produce Dead Locks. Let's take the example and understand how.
To confirm who is the blocker and blockee sessions, open third session with dba permission and use the following command:
Now if first session is trying to update the same row that is already been updated by second session, then Oracle detects dead lock. In this case second session will detect that dead lock.
It is possible that detection of the deadlock takes few seconds may be 5,10 or more.
Who is responsible of Dead Locks? Developers, DBA or Business requirement ?
I would say all are responsible for that. Do not just blame on Developers only. Let's start with Developers.
Scenario 1: Developers Mistake:-
A bad coding which span multiple process (threads from programming language etc) and trying to update same records.
OR
Improper use of commit. I.e., if record is updating multiple time through different processes, then missing frequent commit can cause Blocking and Dead Locks.
Scenario 2: DBA Mistake, which is invisible to all:-
DBA responsibility is to provide the efficient way of defining objects and their relationships with other objects. In that way database should not hit any performance issue related to queries. This also includes indexes on Foreign key, which is sometime ignored by DBAs. Missing of this may cause Dead locks.
Do the following in order to understand how it can effects your Production/UAT/Warehouse databases.
CASE 1.
CASE 2.
If session 1 again tries to run the last command, deadlock will be detected. Look at this:
Yes this is a DBA mistake not to create an FK index at the time of relationships declaration.
Whenever there are missing FK key indexes, Delete takes over the Table-level locks instead of row-level; therefore if you are updating a single row, Oracle will not let you update other records. It's the DBA's responsibility to maintain performance of the system and hence this type of blocking issues belongs to the DBA.
In order to see about locks information, do the following:
Create index temp_dev4_ndx on temp_dev4(c,d);
Now re-run the senario 2. There will be no locks because Oracle locks only one row this time instead of the table.
Scenario: Business Requirement Problem.
Sometimes we need constraints that refers to same table in order to avoid integrety checks by batch, trigger code etc. By creating self reference constraint on a table, we totally forget the bad and big effect that can appear. Here is the example of this bad business rule:-
Since we are updating totally different rows, but because of the self references, records are locked internally. Oracle need to maintain indexes and other read consistency information, therefore, Oracle is locking many rows that may not be of user interest.
Can this also produce Deadlocks?
I challenge you, as an exercise, to find out for yourself! Use technique like these I've demonstrated here. It's the best way to gain knowledge.
Good Luck.
Devinder Singh Virdi
Introduction:
One of the regular task we must do is to check if there are any blocking locks. Sometimes we find deadlocks, and as a DBA we are unable to say which row caused them.
Management keeps on asking for the solution and we keep on saying: improper use of commit, some missing FK indexes, insufficient initrans parameter etc., were the causes. When they asked to check the blocking locks during application testing and they assign us the task to find the row which is causing this, we tried to escape from this test :-)
Normally we don't have any answer other than, session 1 is blocking session 2. And that is not really all that useful to know.
To Find Blocking locks we will use the following SQL sequences:
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
;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 6select SID, Status, sql_hash_value, sql_id, prev_sql_id from gv$session where sid in(219, 213) and inst_id=1;
SID STATUS SQL_HASH_VALUE SQL_ID PREV_SQL_ID
--- -------- -------------- ------------- -----------
213 ACTIVE 662921877 cn5hy5hms6snp 1dju1fycmanwn
219 INACTIVE 0 2nmkmqsupnn8kselect sql_fulltext from v$sql where sql_id='cn5hy5hms6snp';SQL_FULLTEXT
----------------------------------------
delete temp_5 where a=:"SYS_B_0"We are only providing the above part to development team if there are some blocking locks and preferred to kill the session.
Let's move forward to find which row has caused this blocking.
THe query below will give you the object_id, File_id, Block_id and Row_id as:
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 1Find Rowid based on above using:
select dbms_rowid.rowid_create(1, 1289617, 19, 3059159, 1) row_id from dual;ROW_ID
------------------
AAE62RAATAALq3XAABSince you know that object temp_5 is blocked, put username followed by table name to find exact row as:
select *from temp_5 where rowid='AAE62RAATAALq3XAAB';A B
-- --
1 1Feasibility of above code
Above process will take a long time to find locked row specially working with production. Also it’s very confusing when tying to find multiples session with multiple locks.
Here is the procedure which displays the first three columns of every locked row in a table including bind variables value.
This procedure also displays the history of blocker using ASH.
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;
/blocking-info.sql
Other Problems created by Blocking Locks
One of the biggest problem of blocking lock is that it can produce Dead Locks. Let's take the example and understand how.
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.To confirm who is the blocker and blockee sessions, open third session with dba permission and use the following command:
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 520Now if first session is trying to update the same row that is already been updated by second session, then Oracle detects dead lock. In this case second session will detect that dead lock.
update temp_dev2 set c2=c2 where p1=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resourceIt is possible that detection of the deadlock takes few seconds may be 5,10 or more.
Who is responsible of Dead Locks? Developers, DBA or Business requirement ?
I would say all are responsible for that. Do not just blame on Developers only. Let's start with Developers.
Scenario 1: Developers Mistake:-
A bad coding which span multiple process (threads from programming language etc) and trying to update same records.
OR
Improper use of commit. I.e., if record is updating multiple time through different processes, then missing frequent commit can cause Blocking and Dead Locks.
Scenario 2: DBA Mistake, which is invisible to all:-
DBA responsibility is to provide the efficient way of defining objects and their relationships with other objects. In that way database should not hit any performance issue related to queries. This also includes indexes on Foreign key, which is sometime ignored by DBAs. Missing of this may cause Dead locks.
Do the following in order to understand how it can effects your Production/UAT/Warehouse databases.
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 4CASE 1.
For blocking locks only.
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;CASE 2.
For Dead Lock. You can expand CASE 1 to create Dead locks.
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.If session 1 again tries to run the last command, deadlock will be detected. Look at this:
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.98Yes this is a DBA mistake not to create an FK index at the time of relationships declaration.
Whenever there are missing FK key indexes, Delete takes over the Table-level locks instead of row-level; therefore if you are updating a single row, Oracle will not let you update other records. It's the DBA's responsibility to maintain performance of the system and hence this type of blocking issues belongs to the DBA.
In order to see about locks information, do the following:
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 1Create index temp_dev4_ndx on temp_dev4(c,d);
Now re-run the senario 2. There will be no locks because Oracle locks only one row this time instead of the table.
Scenario: Business Requirement Problem.
Sometimes we need constraints that refers to same table in order to avoid integrety checks by batch, trigger code etc. By creating self reference constraint on a table, we totally forget the bad and big effect that can appear. Here is the example of this bad business rule:-
Create a table with self reference and create all required indexes. (We saw that missing FK indexes creates a problem, so this time we are creating the indexes)
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.Since we are updating totally different rows, but because of the self references, records are locked internally. Oracle need to maintain indexes and other read consistency information, therefore, Oracle is locking many rows that may not be of user interest.
Can this also produce Deadlocks?
I challenge you, as an exercise, to find out for yourself! Use technique like these I've demonstrated here. It's the best way to gain knowledge.
Good Luck.
Devinder Singh Virdi
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: