jan 072015
 

Não tem como dizer que a vida do DBA não é cheio de desafios.

Que tal uma aplicação que gera muitos locks ? É fácil, identificar o motivo do lock na aplicação e alterá-la.
Mas o que dizer se você não tiver o código fonte ou então caso seja um produto pronto ?
Se for um produto, talvez de para apelar para o fornecedor, mas o que dizer se ele for backlevel ?

Nesse caso, não tem muito o que fazer…

Mas no cenário que vivenciei, os locks tinham uma particularidade, a sessão da aplicação deixava em lock outra sessão da própria aplicação, e ela depois ficava em idle indefinidamente.

O que poderia ser feito ?

A opção seria usar o parâmetro idle_time do profile do usuário.
Esse parâmetro basicamente elimina toda e qualquer sessão em idle a X minutos.
Na verdade não elimina, mas marca como Sniped para que o PMON resolva isso posteriormente.

Vamos a mão na massa.

A primeira coisa a fazer é criar ou alterar o profile.

Vou configurar  o idle_time para 1 minuto:


SQL> create profile cauduro_p limit idle_time 1;

Profile created.

SQL> alter user cauduro profile cauduro_p;

User altered.
 

Lembrando que essa alteração só vai afetar novas conexões.

Asseguro que o resource_limit esta ativo:

    
SQL> show parameter resource_limit

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
resource_limit			     boolean	 TRUE
 

Pronto ! Toda sessão ociosa do usuário será marcada como sniped, e posteriormente o PMON (background process) vai eliminá-la.
Até isso acontecer, a sessão ainda consome algum recurso computacional, visto que a sessão ainda existe.

Mas será que realmente funciona ?

Olhem só:

    
Sessão 1 (sid 33):

SQL> update test_table set column_1=2 where column_1=1;

2 rows updated.

Sessão 2 (sid 29):

SQL> update test_table set column_1=2 where column_1=1;

A sessão 2 (29) esta em lock.
 

Vamos dar uma olhada na v$session ?


SQL> select sid, serial#, username,status,event,seconds_in_wait 
from v$session where username='CAUDURO';

       SID    SERIAL# USERNAME			     STATUS   EVENT							       SECONDS_IN_WAIT
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ---------------
	29	  522 CAUDURO			     ACTIVE   enq: TX - row lock contention						    24
	33	  200 CAUDURO			     INACTIVE SQL*Net message from client						    34

 

A sessão 33 esta bloqueando a 29 pelo “enq: TX – row lock contention”, um lock simples.

Vamos esperar um pouco …..

……

E agora ?


SQL> select sid, serial#, username,status,event,seconds_in_wait 
from v$session where username='CAUDURO';

       SID    SERIAL# USERNAME			     STATUS   EVENT							       SECONDS_IN_WAIT
---------- ---------- ------------------------------ -------- ---------------------------------------------------------------- ---------------
	29	  522 CAUDURO			     INACTIVE SQL*Net message from client						    49
	33	  200 CAUDURO			     SNIPED   SQL*Net message from client						   281

 

A sessão 29 que estava em idle há mais de um minuto foi marcada como sniped, mesmo tendo um update sem commit.
O Oracle fez rollback das alterações e eliminou o lock.

Pronto !

Mas essa solução tem impactos ? Sim, afinal a aplicação trabalha com um pool, e a cada x minutos uma conexão é desfeita do pool e aplicação tem de refazer.
Mas como a aplicação é inteligente o suficiente para refazer o pool isso não é um problema.

Espero que tenham gostado !

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)