What is Application Dealock ? How to Reproduce and Resolve Deadlock - with Example

"My database connection is very slow" This is a common complain a Database administrator used to hear from users. However, there could be many reasons for this issue, but Many times I have observed that Application Deadlock is one of the reason for them. So, here I am solving an application Dead lock issue in this post. 

The Application Deadlock is not reported in to Database Alert log files, Which makes this more difficult to resolve. The approach used in this post is general method for resolving slow database session So, A DBA can also use this approach for other Slow User session performance issues.

What is Deadlock ?

Suppose there are two friends A and B. A has an apple and want a mango and B has a mango and wants apply 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 Application Deadlock

In this kind of situation remote DBA has to investigate what is causing this issue. Remote DBA Execute below command to find out any blocking session. In Single Instance use V$session and in RAC env. use gv$session to find blocking session.

1. Find Deadlock Session:

SQL>COLUMN username FORMAT a10;
SQL>COLUMN WAIT_CLASS FORMAT a15;

SQL> select sid, username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;
       SID USERNAME      COMMAND STATUS   PROGRAM                                          SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION WAIT_CLASS
---------- ---------- ---------- -------- ------------------------------------------------ ------------- ----------------- ---------------- ---------------
        47 SCOTT               6 ACTIVE   sqlplus@database.example.com (TNS V1-V3)         2rbwgj3zdsnus                 1               34 Application

Here from BLOCKING_INSTANCE and BLOCKING_SESSION values, this is clear that sid 47 (Session #2) is blocked by an Instance number 1 and session number 34. let's find out in what both blocking and blocked session are executing.

2. Blocked Session Detail:

Using below command find what query is getting executed by sid 47 (get SQL_ID from above commnd).
SQL> select * from v$sqltext where sql_id='2rbwgj3zdsnus';

ADDRESS  HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
-------- ---------- ------------- ------------ ---------- ----------------------------------------------------------------
3EDAC70C 4275852120 2rbwgj3zdsnus            6          0 update dept set dname=:"SYS_B_0" where deptno=:"SYS_B_1"

Database Administrator finds This session (Blocked Session) is executing an update statement on dept table.

3. Blocking session detail:

Let' find what blocking session is doing using below command

SQL> select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where sid=34;

       SID USERNAME      COMMAND STATUS   PROGRAM                                          SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION WAIT_CLASS
---------- ---------- ---------- -------- ------------------------------------------------ ------------- ----------------- ---------------- ---------------
        34 SCOTT               3 INACTIVE sqlplus@database.example.com (TNS V1-V3)         17d40vwcct4g6                                    Idle

SQL> select * from v$sqltext where sql_id='17d40vwcct4g6';
ADDRESS  HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
-------- ---------- ------------- ------------ ---------- ----------------------------------------------------------------
3ED7F660  416059878 17d40vwcct4g6            3          0 select instance_name from v$instance

This query shows the last sql statement executed was "select instance_name from v$instance". which could never be a blocking statement for any session. But from v$session this is evident that this is the blocking session. Now DBA has to further dig in to session 34 using v$active_session_history or gv$active_session_history.

4. Digging into Blocking Session for Hunting SQL Query:

Remote DBA find out all queries executed by session id 34 in recent time using below command.
SQL> select SQL_ID,SQL_OPNAME,PLSQL_OBJECT_ID from v$active_session_history where SESSION_ID='34';

SQL_ID        SQL_OPNAME                                                       PLSQL_OBJECT_ID
------------- ---------------------------------------------------------------- ---------------
dtbhjabjx3v1u SELECT
g4y6nw3tts7cc PL/SQL EXECUTE

SQL> select * from v$sqltext where sql_id in ('dtbhjabjx3v1u','g4y6nw3tts7cc');

ADDRESS  HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
-------- ---------- ------------- ------------ ---------- ----------------------------------------------------------------
3DB443F0 4087094668 g4y6nw3tts7cc           47          0 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
3ECC92A0 3822185530 dtbhjabjx3v1u            6          0 update dept set dname=:"SYS_B_0" where deptno=:"SYS_B_1"

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.

5. Which OS User Execute Culprit Command:

SQL> select sid,username,command,status,program,sql_id,TERMINAL,PROGRAM from gv$session where sid=34;

       SID USERNAME      COMMAND STATUS   PROGRAM                                          SQL_ID        TERMINAL   PROGRAM
---------- ---------- ---------- -------- ------------------------------------------------ ------------- ---------- ------------------------------------------------
        34 SCOTT               0 INACTIVE sqlplus@database.example.com (TNS V1-V3)                       pts/1      sqlplus@database.example.com (TNS V1-V3)

The user having terminal pts/1 has executed update command and he did not commit or rollback this.

6. Final Solution:

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.

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

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.

Please share your feed back about this post. 

No comments:

Post a Comment