Detectando sessões bloqueadoras e sessões bloqueadas no Oracle: DBA_WAITERS, V$LOCKED_OBJECT, V$LOCK, V$SESSION

Olá,

Uma das tarefas de um DBA é monitorar o banco de dados de forma que seja identificado possíveis problemas que possam vir a comprometer a performance ou a disponibilidade do mesmo. Dentre alguns problemas que podem ser identificados antecipadamente, estão aos bloqueios (locks), em especial, aqueles onde existam sessões bloqueadoras e sessões bloqueadas. Dependendo da situação, descobrir de forma rápida a sessão (SID) no banco de dados que está bloqueando por muito tempo um recurso, é fundamental para se evitar maiores problemas. Dependendo do recurso bloqueado, (tabela, linha, etc.) várias sessões ficarão bloqueadas até a liberação do bloqueio do recurso pela sessão bloqueadora. O problema em si não é o bloqueio, mas por quanto tempo a sessão fica bloqueada. Em determinados casos, por falhas na aplicação, entre outros, o bloqueio pode ficar eternamente e, em casos extremos, somente matando a sessão bloqueadora (kill session ...) irá liberar o recursos e resolver o problema.

Enfim, o objetivo deste artigo será demonstrar algumas formas de identificar as sessões que estão bloqueando outras sessões. Serão apresentadas algumas views e consultas SQL que poderão ser úteis para este fim. Irei simular abaixo um ambiente na qual uma sessão no Oracle irá bloquear outras sessões. Segue demonstração abaixo:

SESSÃO 1

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mai 4 10:17:20 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select distinct sid from v$mystat;

       SID
----------
       278

SQL> create table t1 (id number);

Tabela criada.

SQL> insert into t1 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> update t1 set id=10;

1 linha atualizada.

Acima, a sessão de SID 278 realizou um UPDATE na tabela T1 e não efetuou COMMIT.

SESSÃO 2

SQL> select distinct sid from v$mystat;

       SID
----------
       165

SQL> create table t2 (id number);

Tabela criada.

SQL> insert into t2 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> update t2 set id=100;

1 linha atualizada.

SQL> update t1 set id=20;
-- Aguardando

Na sessão de SID 165 acima, tanto na tabela T2 quanto na tabela T1 foram realizados UPDATEs. No entanto, ao realizar o UPDATE na tabela T1, a sessão ficou bloqueada, ou seja, aguardando que a sessão de SID 278 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T1.

SESSÃO 3

SQL> select distinct sid from v$mystat;

       SID
----------
        41

SQL> update t2 set id=30;
-- Aguardando

 

A sessão de SID 41 efetuou um UPDATE na tabela T2 e ficou bloqueada, ou seja, aguardando que a sessão de SID 165 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T2. Enfim, diante da situação das sessões de banco de dados acima, poderemos ver abaixo, consultando a view dinâmica de desempenho V$SESSION, quais sessões estão bloqueadas. Caso deseje ver por quanto tempo a sessão está bloqueada, basta adicionar a coluna SECONDS_IN_WAIT na instrução SELECT a seguir. 

SQL> select sid,
  2 serial#,
  3 status,
  4 username,
  5 osuser,
  6 program,
  7 blocking_session blocking,
  8 event
  9  from v$session
 10  where blocking_session is not null;

   SID  SERIAL# STATUS   USERNAME   PROGRAM      BLOCKING EVENT
------ -------- -------- ---------- ------------ -------- -----------------------------
    41    17090 ACTIVE   SYS        sqlplus.exe       165 enq: TX - row lock contention
   165     6770 ACTIVE   SYS        sqlplus.exe       278 enq: TX - row lock contention

Pelo resultado acima, é possível verificar que a sessão de SID 41 está sendo bloqueada pela sessão de SID 165, que está sendo bloqueada pela sessão de SID 278. Uma outra forma de ver essa situação é consultar as views DBA_WAITERS e DBA_BLOCKERS. Caso estas views não estejam criadas no banco de dados, as mesmas poderão ser criadas através do script $ORACLE_HOME/rdbms/admin/catblock.sql. Segue o resultado da view DBA_WAITERS conforme demonstrado abaixo.
 

SQL> select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION
--------------- ---------------
             41             165
            165             278

Uma outra forma que temos para visualizar situações com esta é executar o script $ORACLE_HOME/rdbms/admin/utllockt.sql. Este script é muito útil quando existem várias sessões bloqueadoras e bloqueadas e precisamos saber qual sessão iniciou todo o processo de bloqueio. A visualização do resultado é em forma de uma Tree View, que demonstra em uma hierarquia, as sessões bloqueadoras e as sessões bloqueadas.
 

SQL> @utllockt.sql

Tabela criada.

Tabela criada.

2 linhas criadas.

Commit concluído.

Tabela eliminada.

1 linha criada.

Commit concluído.

WAITING_SESSION   LOCK_TYPE      MODE_REQUESTED MODE_HELD    LOCK_ID1   LOCK_ID2
----------------- -------------- -------------- ------------ ---------- ---------
278               None
   165            Transaction    Exclusive      Exclusive    393227     454
      41          Transaction    Exclusive      Exclusive    524312     454

Tabela eliminada.

Acima, é possível determinar que a sessão de SID 278 iniciou o processo de bloqueio que fez com que a sessão de SID 165 ficasse bloqueada. Como consequência, a sessão de SID 165 também bloqueou a sessão de SID 41. Em relação ao objeto (tabela) que está sofrendo bloqueio, é possível determiná-lo consultando as views DBA_OBJECT, V$LOCKED_OBJECT, V$LOCK. O código PL/SQL abaixo retorna um resultado na qual é possível ver o SID da sessão bloqueadora, o SID da sessão bloqueada e o recurso (objeto) que detém o bloqueio (lock).
  

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.enable (1000000);
  3
  4     FOR do_loop IN (SELECT session_id,
  5                            a.object_id,
  6                            xidsqn,
  7                            oracle_username,
  8                            b.owner owner,
  9                            b.object_name object_name,
 10                            b.object_type object_type
 11                       FROM v$locked_object a, dba_objects b
 12                      WHERE xidsqn != 0 AND b.object_id = a.object_id)
 13     LOOP
 14        DBMS_OUTPUT.put_line ('.');
 15        DBMS_OUTPUT.put_line ('Blocking Session   : ' || do_loop.session_id);
 16        DBMS_OUTPUT.put_line (
 17              'Object (Owner/Name): '
 18           || do_loop.owner
 19           || '.'
 20           || do_loop.object_name);
 21        DBMS_OUTPUT.put_line ('Object Type        : ' || do_loop.object_type);
 22
 23        FOR next_loop
 24           IN (SELECT sid
 25                 FROM v$lock
 26                WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
 27        LOOP
 28           DBMS_OUTPUT.put_line (
 29              'Sessions being blocked   :  ' || next_loop.sid);
 30        END LOOP;
 31     END LOOP;
 32  END;
 33  /
.
Blocking Session   : 278
Object (Owner/Name): SYS.T1
Object Type        : TABLE
Sessions being blocked   :  165
.
Blocking Session   : 165
Object (Owner/Name): SYS.T1
Object Type        : TABLE
Sessions being blocked   :  41
.
Blocking Session   : 165
Object (Owner/Name): SYS.T2
Object Type        : TABLE
Sessions being blocked   :  41

Procedimento PL/SQL concluído com sucesso.