TO FIND A DEAD LOCKS 

Written By : Harish Ramakkagari

Purpose : To Detect Application Dead Locks.

Tested By : Harish Ramakkagari

Contact : harish.ramakkagari@attra.com.au  +919591792886  or harishr227@gmail.com   +918880598503



What is Dead Lock ?
 
   Suppose there are two friends A and B. A has an apple and want a mango and B has a mango and wants apple,
   and none of them is ready to leave what they have and want what other have. Now, this  is called as Deadlock.
   Same happens in case of Database Sessions, One session holds lock on an row and want to another,
   one which is hold by second one and both need others rows which cause a deadlock.

Steps to Reproduce Application Deadlock :
-----------------------------------------
   I opened two database session from same database user scott and update same row in both sessions,
   without commit and generate a dead lock situation.

  Session #1
  ----------
   SQL> desc dept;

   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   DEPTNO                                    NOT NULL NUMBER(2)
   DNAME                                              VARCHAR2(14)
   LOC                                                VARCHAR2(13)

SQL> select deptno,dname from dept;
 
DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> update dept set dname='operation' where deptno=40;
1 row updated.


  Session #2
  -----------

SQL> update dept set dname='sale' where deptno=30;
1 row updated.

SQL> update dept set dname='operation_new' where deptno=40;

     Above update statement is hang and not responding has generated a Deadlock.
     DBA cross check alert log file of database which is not showing any error message
     like 'ORA-0060 Deadlock Detected'.

 Steps to Resolve Apllication Deadlock:
 --------------------------------------
    1. Find Dead lock Session:
         
       SQL>COLUMN username FORMAT a10;
       SQL>COLUMN WAIT_CLASS FORMAT a15;
       SQL>set linesize 200
       SQL> select sid, username,command,status,program,sql_id,
            BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;

        (Here we find from BLOCKING_INSTANCE and BLOCKING_SESSION values)

   2. Blocked Session Details
     
      QL> select * from v$sqltext where sql_id='2rbwgj3zdsnus';


   3. Blocking Session Details :
     
      SQL>select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,
          WAIT_CLASS from gv$session where sid=34;
 
     
      SQL> select * from v$sqltext where sql_id='17d40vwcct4g6';

   4. Find the sql statment from blocking session

       SQL>select SQL_ID,SQL_OPNAME,PLSQL_OBJECT_ID from v$active_session_history where SESSION_ID='34';
   5.  SQL> select * from v$sqltext where sql_id in ('dtbhjabjx3v1u','g4y6nw3tts7cc');

             (Database Administrator finds an update command on same table in this result.
              Here the culprit command  is "update dept set dname=:"SYS_B_0" where deptno=:"SYS_B_1".
              Session 34 executes this command which was causing session 47 to hang.
              Now next step to find out user details.)
 
   6.  Find which user is executing Clurpit command.
           
      SQL> select sid,username,command,status,program,sql_id,TERMINAL,PROGRAM from gv$session where sid=34;

   7. Finall Solution is
        DBA has to ask either network admin to find user having terminal,
        pts/1 or he has to directly ask user to either commit or terminate his session.
        I execute commit in session 1 and it automatically release locks and session 2 also got his row updated.
   8.

       Session #1
       SQL> commit;
       Commit complete.

       Session #2
       SQL> update dept set dname='operation_new' where deptno=40;
       1 row updated.
      SQL>

       Let's cross check with v$session view.
     
      SQL>select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,
          WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;
       
          no rows selected.
     
        Note :
             
            The problem is solved now. Though this is a small test case, you might face different scenario,
            but for situation where a particular user session is not performing well.
            This could be the initial approach to investigate.
            If, This doesn't help to troubleshoot, you can generate AWR Report and Analyze for any other blocking events.