<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Blocking Locks -- Which Row Caused the Problem?

Published on
20,087 Points
11,587 Views
Last Modified:
Approved
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 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
;

Open in new window

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
;

Open in new window

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

Open in new window

Once you get the information, then search for queries they are using:-
select 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                            2nmkmqsupnn8k

Open in new window

Find the SQL which is blocked using:
select sql_fulltext from v$sql where sql_id='cn5hy5hms6snp';

Open in new window

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"

Open in new window


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;

Open in new window

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
1289617	      19             3059159         1

Open in new window


Find Rowid based on above using:

select dbms_rowid.rowid_create(1, 1289617, 19, 3059159, 1) row_id from dual;

Open in new window

ROW_ID
------------------
AAE62RAATAALq3XAAB

Open in new window

Now the last step is to find the row which is causing this problem using the following:
Since 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';

Open in new window

A  B
-- --
1  1

Open in new window


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.
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;
/

Open in new window

Also available as a download:
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.

Open in new window


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              520

Open in new window


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.
update temp_dev2 set c2=c2 where p1=1
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Open in new window


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.

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

Open in new window


CASE 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;

Open in new window


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.

Open in new window


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.98

Open in new window

Now the question is:  Why this is a DBA problem?  The DBA doesn't directly deal with data.

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:
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

Open in new window

Now create required indexes on child tables.

    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:-

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.

Open in new window


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
0
Comment
1 Comment
LVL 15

Author Comment

by:Devinder Singh Virdi
Thanks Dan Rollins.
0

Featured Post

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Join & Write a Comment

This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month