Eliminando Enqueue no Oracle

Modelo de dados construidos por desenvolvedores, aplicações que não foram devidamente testadas, bancos que cresceram fora do planejado e uma série de outras causas podem resultar no famoso pedido de socorro (nada técnico) "O banco está travado"!

Vamos tentar desmistificar um pouco do que acontece nesses momentos de "tensão" dentro do banco de dados. O principal ponto nesse tema é o mecanismo de "lock" do banco de dados Oracle. Para permitir que múltiplos usuários acessem e manipulem as linhas das tabelas dentro de um esquema o Oracle possui um mecanismo para garantir a integridade e consistência dos dados. o lock. Caso queiram entender melhor os detalhes segue um link da documentação do produto: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT221.

Dependendo do tipo de "lock" que um comando submetido ao banco de dados o Oracle poderá sempre que necessário utilizar um estrutura de memória para garantir o bom funcionamento do banco, essa estrutura de memória é chamada de Enqueue (http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/enqueues.htm).

Reunimos agora todos os ingredientes necessários para chegarmos onde vocês esperam, códigos. Quando o "banco está travado" o primeiro candidato a causa do "problema" é algum objeto estar com um lock que impede que outra sessão utilize o recurso desejado, temos então um enqueue. Para identificar se existe um lock ou um enqueue podemos utilizar a seguinte consulta:

SQL> select a.username ||'@'||a.machine || ' session id = ' ||a.sid || ' esta bloqueando '
|| b.username ||'@'||b.machine || 'session id = '||b.sid
from v$lock c,
v$session a,
v$lock d,
v$session b
where a.sid=c.sid
and b.sid=d.sid
and c.block=1
and d.request > 0
and c.id1 = d.id1
and d.id2 = d.id2;

Existem inúmeras consultas para identificar um lock e possivelmente um enqueue, sinta-se à vontade para utilizar a mais conveniente para você, algumas delas já apontam qual tabela, qual linha e até qual bloco estão envolvidos no lock, mas meu objetivo aqui é como você evita (ou antecipa) um deadlock.

Um deadlock ocorre quando uma dependência ciclica ocorre envolvendo as estruturas de lock e enqueue. Após um determinado tempo o banco de dados elimina uma das sessões para que a outra consiga prosseguir com sua transação e o banco de dados possa retomar ao seu curso normal, porém esse tempo pode não ser interessante para você, ou ainda, você pode querer optar por qual das sessões você quer eliminar (de repente aquela procedure que alguem disparou no momento errado).

Para eliminar a sessão que está causando o enqueue no seu banco, primeiro a identifique, por exemplo, a partir da consulta acima descrita, depois execute os seguintes comandos:

SQL> select sid, serial#
from v$session
where sid = 999;

-- substitua 999 pelo sid que você identificou como responsável pelo enqueue.

Passo 1) Seja sutil

SQL> ALTER SYSTEM KILL SESSION 'sid_number, serial_number';

-- substitua o sid_number e o serial_number pelos valores obtidos na primeira consulta

Caso após essa tentativa a sessão em questão ficar com o status KILLED você pode aguardar o banco fazer a parte dele ou antecipar alguns passos. Resumindo a sessão está "agonizando" e você quer "acabar com o sofrimento dela".

SQL> select sid, status
from v$session
where sid=999;

Verifique se o status da sessão está KILLED, caso esteja prossiga com os próximos passos, caso não existam linhas para o SID em questão, a sessão já passou dessa para uma melhor.

Passo 2) Últimas palavras

Para "acabar com o sofrimento" da sessão identifique qual o processo do sistema operacional é responsável por aquela sessão (cuidado se você utiliza dispatchers):

SQL> select spid
from v$process
where addr=(select paddr from v$session where sid=999);

Uma vez que você identificou o processo do sistema operacional vá para a shell do linux ou unix como root e:

kill -9 99999

Onde 99999 é o valor retornado para o SPID da consulta anterior. (Verifique antes se o processo que você está executando kill é um processo do oracle, com padrão oracleSID, onde SID é o nome da sua instância).

Passo 3) Sumindo com o corpo.

Talvez o kill -9 não seja suficiente para você eliminar de vez essa sessão, para essas sessões que costumo chamar de sessões "Mcloud" você ainda tem um último artificio:

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid_number,serial_number' IMMEDIATE;

Bom amigo se após esse passo sua sessão continua como KILLED, só o tempo ou um shutdown no seu banco para ela ir embora de vez.

Vou ficando por aqui e sei que passei por cima (muito rapidamente) de vários conceitos, portando deixem seus comentários que vou respondendo aos poucos.

Abraços,
Caio Spadafora.