É comum se deparar com sessões travadas no banco de dados, a maior parte das vezes por falha do usuário ou da aplicação.
O lock ocorre quando duas sessões tentam efetuar alterações na mesma tabela.
No exemplo abaixo o usuário ira efetuar um insert na tabela CLIENTE.
Apesar do INSERT ser efetuado, a operação ainda não sofreu commit, dessa forma, outras sessões não tem essa visualização.
Agora, antes do commit ser efetuado, outra sessão tenta efetuar uma alteração na mesma tabela:
Nesse momento a sessão ficou travada, quando a primeira sessão efetuar commit ou rollback a sessão travada deve ser liberada, porém muitas vezes a sessão é fechada sem finalização da transação e a segunda sessão permanece aguardando.
Podemos executar o script abaixo para verificar as sessões que estão em locks no banco:
col "Sessao" FOR a40
col ID1 for a50
SET lines 190
SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || 'Inst-> ' || s.inst_id ||' Sid:serial-> ' ||l.sid ||','
|| s.serial# "Sessao", s.serial#, SUBSTR(s.username,1,15) Username, l.id1, l.id2, l.lmode, l.request, l.type, sql_hash_value
FROM GV$LOCK L, GV$SESSION S
WHERE (l.id1, l.id2, l.type) IN (SELECT l2.id1, l2.id2, l2.type FROM GV$LOCK l2 WHERE l2.request > 0)
AND s.sid = l.sid
AND s.inst_id = l.inst_id
ORDER BY l.id1, l.request;
O script retornou as seguintes informações:
A partir destas informações podemos verificar a sessão que causou o lock em "Holder" e a sessão
aguardando em "Waiter", em seguida possuímos o número da instância e o SID e SERIAL das sessões.
Vamos utilizar o SID e o SERIAL para encerrar a sessão em Holder, fazendo com que a operação efetuar na sessão em Waiter seja efetivada.
alter system kill session '33,299' immediate;
Após a sessão ser encerrada, quando a primeira sessão tentar efetuar alguma transação recebera a seguinte mensagem:
A segunda sessão será destravada e a operação que estava aguardando será efetuada:
Esta é uma forma rápida e simples de identificar e eliminar sessões em locks, podemos coletar mais informações da sessão antes de encerrá-la, sabendo que pode se tratar de um erro da aplicação o qual deve ser investigado, vamos abordar mais sobre locks em postagens futuras.
Fonte: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm
