Locks no Oracle Database: Identificação e Resolução
Sergio BenderLocks (bloqueios) são um mecanismo essencial em bancos de dados relacionais como o Oracle para garantir a consistência e integridade dos dados em ambientes multiusuário. No entanto, quando mal gerenciados, podem causar atrasos, deadlocks ou até paralisações em aplicações críticas. Neste artigo, vou explicar o que são locks, por que eles acontecem e como você pode identificá-los e resolvê-los no Oracle Database usando scripts práticos.
O que são Locks no Oracle Database?
No Oracle, um lock é um mecanismo que controla o acesso concorrente aos recursos do banco de dados, como linhas de uma tabela ou objetos inteiros. Ele impede que múltiplas transações modifiquem os mesmos dados simultaneamente, evitando inconsistências. Existem vários tipos de locks, como:
Row Exclusive (RX): Ocorre em operações DML (INSERT, UPDATE, DELETE) em uma linha específica.
Exclusive (X): Bloqueia um recurso completamente, como em um
LOCK TABLE IN EXCLUSIVE MODE.Share (S): Permite leitura, mas bloqueia modificações por outras sessões.
Embora locks sejam normais, problemas surgem quando uma sessão bloqueia um recurso que outra precisa, criando uma espera prolongada ou até um deadlock (quando duas ou mais sessões se bloqueiam mutuamente).
Por que Monitorar Locks?
Monitorar locks é crucial para:
Identificar gargalos de desempenho em aplicações.
Resolver bloqueios que afetam usuários ou processos críticos.
Evitar deadlocks que podem travar transações.
No Oracle, as views dinâmicas de desempenho (como GV$SESSION, GV$LOCK e GV$LOCKED_OBJECT) fornecem informações detalhadas sobre locks, permitindo que DBAs ajam rapidamente.
Script 1: Visualizando a Árvore de Bloqueio
O primeiro script que utilizo é excelente para visualizar a hierarquia de bloqueios — quem está bloqueando quem — em um ambiente Oracle RAC (multi-instância). Ele gera uma saída estruturada, mostrando as sessões bloqueadoras e bloqueadas, junto com detalhes como evento, SQL_ID e tempo de espera.
-- Verificar sessão em lock - árvore de bloqueio
WITH a AS (
SELECT LEVEL lev,
CONNECT_BY_ROOT COL1 BLOCKER, col1 lev1
FROM ((SELECT inst_id||','||sid col1, event, sql_id, program, blocking_instance||','||blocking_session col2, seconds_in_wait
FROM gv$session))
CONNECT BY NOCYCLE PRIOR col2=col1
START WITH col2 IN (SELECT blocking_instance||','||blocking_session FROM gv$session WHERE blocking_session IS NOT NULL)
),
b AS (
SELECT DISTINCT lev1 FROM a WHERE (a.lev, a.blocker) IN (SELECT MAX(lev), blocker FROM a GROUP BY blocker)
)
SELECT
LPAD(' ', 3*(LEVEL-1)) || col1 sessao, username, osuser, event, sql_id, seconds_in_wait
FROM ((SELECT inst_id||','||sid col1, username, osuser, event, sql_id, program, blocking_instance||','||blocking_session col2, seconds_in_wait
FROM gv$session))
CONNECT BY NOCYCLE PRIOR col1=col2
START WITH col1 IN (SELECT lev1 FROM b);
SELECT DECODE(l.request, 0, 'Bloqueando: ', 'Aguardando: ') || 'Inst-> ' || s.inst_id || ' Sid:serial-> ' || l.sid || ',' || s.serial# "SESSAO",
SUBSTR(s.username, 1, 15) Username,
'ALTER SYSTEM KILL SESSION ''' || l.SID ||', '|| s.SERIAL# ||''' IMMEDIATE;',
l.lmode, l.request, l.type, sql_hash_value, ctime "TEMPO"
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
ORDER BY l.id1, l.request, l.ctime DESC;
Como funciona?
A primeira parte usa uma consulta hierárquica (
CONNECT BY) para construir a árvore de bloqueio, identificando sessões raiz (bloqueadoras) e suas dependências.A segunda parte detalha as sessões envolvidas, indicando se estão bloqueando ou aguardando, com o comando
ALTER SYSTEM KILL SESSIONpronto para uso, caso necessário.
Saída típica:
Sessão raiz (ex.:
1,123), username, evento (ex.:enq: TX - row lock contention), SQL_ID e tempo de espera.Lista de sessões bloqueadas com informações adicionais, como modo do lock (
lmode) e tipo (type).
Esse script é ideal para uma análise rápida e para decidir se uma sessão precisa ser terminada.
Script 2: Análise Completa de Locks
O segundo script é mais abrangente, fornecendo um relatório detalhado sobre sessões bloqueadoras e bloqueadas, incluindo informações do sistema operacional, tempo de bloqueio formatado, programa de conexão e até o texto da query em execução.
SET SERVEROUTPUT ON
set serveroutput on
set lines 200
Set pages 200
set long 999999
declare
v_query_max_lock varchar2(20);
vloop_lock_qtd varchar2(20);
vvalid varchar2(90);
JBQB VARCHAR2(90) := CHR(13) || CHR(10);
vtmps varchar2(90) := 0;
vtmpm varchar2(90) := 0;
vtmph varchar2(90) := 0;
vtmpd varchar2(90) := 0;
v_hist varchar2(20) := ''; --HISTORICO_EXEC_S_N
v_tables varchar2(20) := ''; --MOSTRAR_TABELAS_BLOQU_S_N
begin
if v_hist is null then
v_hist := 'N';
end if;
if v_tables is null then
v_tables := 'N';
end if;
for ljb in (select l1.sid, max(l2.ctime) ctime, l1.id1, l1.id2, l1.TYPE
from gv$lock l1, gv$lock l2
where l1.block > 0
and l2.block = 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2
group by l1.sid, l1.id1, l1.id2, l1.TYPE
order by 2 asc) loop
for x in (select s.saddr,
s.sid,
s.prev_hash_value,
s.sql_hash_value,
s.username,
s.status,
s.osuser,
s.machine,
s.program,
s.serial#,
i.instance_name,
i.host_name,
s.sql_id,
s.inst_id
from gv$session s, gv$instance i
where sid = ljb.sid
and s.inst_id = i.inst_id
and username is not null
) loop
vvalid := x.username;
dbms_output.put_line(rpad('+', 40, '+') || ' BLOQUEADOR ' ||
rpad('+', 40, '+') || chr(10));
dbms_output.put_line('DATABASE INFORMATION:');
dbms_output.put_line(rpad('USUARIO BLOQUEADOR:', 29, '.') || chr(32) ||
lpad(x.username, 10, ' ') || chr(32) || chr(32) ||
rpad(' | STATUS:', 29, '.') || chr(32) ||
x.status);
dbms_output.put_line(rpad('SID:', 29, '.') || chr(32) ||
lpad(x.sid, 10, ' ') || chr(32) || chr(32) ||
rpad(' | SERIAL#:', 29, '.') || chr(32) ||
x.serial#);
dbms_output.put_line(rpad('INSTANCE:', 29, '.') || chr(32) ||
lpad(x.instance_name, 10, ' ') || chr(32) ||
chr(32) ||
rpad(' | SERVIDOR INSTANCE:', 29, '.') ||
chr(32) || x.host_name || chr(10));
vtmpm := substr(ljb.ctime / 60, 1, (INSTR(ljb.ctime / 60, '.')) - 1);
if vtmpm is null then
vtmpm := substr(ljb.ctime / 60, 1, (INSTR(ljb.ctime / 60, ',')) - 1);
if vtmpm is null then
vtmpm := ljb.ctime / 60;
end if;
end if;
vtmph := substr((vtmpm / 60), 1, (INSTR(vtmpm / 60, '.')) - 1);
if vtmph is null then
vtmph := substr((vtmpm / 60), 1, (INSTR(vtmpm / 60, ',')) - 1);
if vtmph is null then
vtmph := vtmpm / 60;
end if;
end if;
vtmpd := substr((vtmph / 24), 1, (INSTR(vtmph / 24, '.')) - 1);
if vtmpd is null then
vtmpd := substr((vtmph / 24), 1, (INSTR(vtmph / 24, ',')) - 1);
end if;
if ljb.ctime < 60 then
dbms_output.put_line('TIME LOCK:................... ' || ljb.ctime ||
' SEGUNDO(s)' || JBQB);
elsif ljb.ctime < 3600 then
dbms_output.put_line('TIME LOCK:................... ' || vtmpm ||
' MINUTO(s) E ' || (ljb.ctime - (vtmpm * 60)) ||
' SEGUNDO(s)' || JBQB);
elsif ljb.ctime > 3600 then
dbms_output.put_line('TIME LOCK:................... ' || vtmph ||
' HORA(s) E ' || (vtmpm - (vtmph * 60)) ||
' MINUTO(s)' || JBQB);
end if;
-- Forma de acesso
dbms_output.put_line('FORMA DE CONEXAO (programa usado):');
dbms_output.put_line('SESSION PROGRAM:................. ' ||
x.program || JBQB);
-- Dados SO
dbms_output.put_line('S.O INFORMATION:');
for xy in (
select nvl(spid, 0) spid
from gv$process p, gv$session s
where p.addr = s.paddr
and s.sid = x.sid
) loop
if xy.spid <> 0 then
dbms_output.put_line('PID:......................... ' || xy.spid);
end if;
end loop;
dbms_output.put_line('S/O USER:.................... ' || x.osuser);
dbms_output.put_line('MACHINE:..................... ' || x.machine || JBQB);
dbms_output.put_line('KILL SESSION:');
dbms_output.put_line('alter system kill session ''' || x.sid || ',' ||
x.serial# || ',@' || x.inst_id ||
''' immediate;' || JBQB);
-- Dados do lock
if x.sql_hash_value > 0 then
dbms_output.put_line('LOCK INFORMATION:');
dbms_output.put_line('TIPO DO LOCK:..... ' || x.sql_hash_value);
dbms_output.put_line('HASH ATUAL:....... ' || x.sql_hash_value);
dbms_output.put_line('QUERY TEXT:.................. select sql_fulltext from gv$sql where sql_id=''' ||
x.sql_id || ''';' || JBQB);
else
dbms_output.put_line('LOCK INFORMATION:');
dbms_output.put_line('NESTE MOMENTO O HASH_VALUE ESTA COMO 0');
end if;
if upper(v_hist) = 'S' or upper(v_hist) = 'Y' then
dbms_output.put_line(chr(10) || 'HISTORICO DE EXECUCAO: ');
dbms_output.put_line('QUERY TEXT');
for oc in (select SQL_ID
from gv$open_cursor
where sid = x.sid
and user_name = x.username)
LOOP
for ot in (select distinct sql_text
from v$sql
where
((upper(SQL_FULLTEXT) like upper('UPDATE %') or
upper(SQL_FULLTEXT) like upper('% UPDATE %'))
or (upper(SQL_FULLTEXT) like upper('DELETE %') or
upper(SQL_FULLTEXT) like upper('% DELETE %'))
or (upper(SQL_FULLTEXT) like upper('LOCK TABLE%') or
upper(SQL_FULLTEXT) like upper('% LOCK TABLE %')))
and SQL_ID = oc.SQL_ID)
LOOP
dbms_output.put_line('select sql_fulltext from v$sql where SQL_ID=' ||
chr(39) || oc.SQL_ID || chr(39) || ';');
END LOOP;
END LOOP;
end if;
-- Mostra o tipo do objeto e a quantidade em lock
for tab_z in
(
SELECT distinct O.OBJECT_TYPE
FROM gv$locked_object l, DBA_OBJECTS O, gv$session s
where L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = x.sid
) loop
vloop_lock_qtd := 0;
for tab_y in
(
SELECT O.OBJECT_TYPE
FROM gv$locked_object l, DBA_OBJECTS O
where L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = x.sid
) loop
if tab_y.OBJECT_TYPE = tab_z.OBJECT_TYPE then
vloop_lock_qtd := vloop_lock_qtd + 1;
end if;
end loop;
dbms_output.put_line(chr(10) || 'QTD DE OBJETOS EM LOCK:...... ' ||
vloop_lock_qtd || ' ' || tab_z.OBJECT_TYPE || JBQB);
end loop;
if upper(v_tables) = 'S' or upper(v_tables) = 'Y' then
for tab_z in
(
SELECT distinct O.OBJECT_TYPE
FROM gv$locked_object l, DBA_OBJECTS O
where L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = x.sid
) loop
dbms_output.put_line(tab_z.OBJECT_TYPE ||
'(s) EM LOCK:::::::::::::');
for tab_x in
(
SELECT O.OBJECT_NAME,
O.OWNER,
Decode(l.LOCKED_MODE,
0,
'None',
1,
'Null (NULL)',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share (S)',
5,
'S/Row-X (SSX)',
6,
'Exclusive (X)',
l.LOCKED_MODE) LOCKED_MODE
FROM gv$locked_object l, DBA_OBJECTS O
WHERE L.OBJECT_ID = O.OBJECT_ID
AND O.OBJECT_TYPE = tab_z.OBJECT_TYPE
AND L.SESSION_ID = x.sid
) loop
dbms_output.put_line(rpad(tab_x.OWNER || '.' ||
tab_x.OBJECT_NAME || chr(32),
50,
'-') || '> ' || tab_x.LOCKED_MODE);
end loop;
end loop;
end if;
dbms_output.put_line(chr(10) || rpad('+', 92, '+') || chr(10));
dbms_output.put_line('============================ BLOQUEADO ============================ ');
for v_block in (
select s.inst_id,
s.sid,
s.serial#,
s.sql_id,
l.TYPE,
s.username,
s.osuser
from gv$session s, gv$lock l
where s.sid = l.sid
and request > 0
and l.id1 = ljb.id1
and l.id2 = ljb.id2
) loop
dbms_output.put_line('.... SID: ' || rpad(v_block.sid, 6, ' ') ||
' | SERIAL#: ' ||
rpad(v_block.serial#, 6, ' ') ||
' | Tipo do Lock: ' ||
rpad(v_block.type, 6, ' ') || ' | S/O USER: ' ||
rpad(v_block.osuser, 15, ' ') ||
' | USER DB: ' ||
rpad(nvl(v_block.username, '- - - - - - - -'),
15,
' ') || ' | SQL_ID: ' ||
rpad(nvl(v_block.sql_id, '- - - - - - - '),
15,
' ') || ' | INSTANCIA: ' ||
v_block.inst_id);
end loop;
dbms_output.put_line(JBQB || JBQB);
end loop;
end loop;
if vvalid is null then
dbms_output.put_line('--');
dbms_output.put_line('- NAO EXISTE LOCKS DE USUARIOS NESTE MOMENTO -');
dbms_output.put_line('- NO HAY BLOQUEOS DE USUARIO EN ESTE MOMENTO -');
dbms_output.put_line('- THERE ARE NO USER LOCKS AT THIS TIME -');
dbms_output.put_line('--');
dbms_output.put_line(JBQB || JBQB);
end if;
end;
/
Como funciona?
Usa um bloco PL/SQL para iterar sobre as sessões bloqueadoras (
GV$LOCKcomblock > 0) e suas bloqueadas (request > 0).Exibe informações como:
Usuário, SID, Serial#, instância e hostname.
Tempo de bloqueio em segundos, minutos ou horas.
Programa de conexão, usuário do SO e máquina.
Comando para matar a sessão (
ALTER SYSTEM KILL SESSION).Tipo de lock e SQL em execução (via
GV$SQL).
Opções configuráveis:
v_hist(histórico de execução) ev_tables(listar objetos bloqueados).
Saída típica:
++++++++++++++++++++++++++++++++++++++++ BLOQUEADOR ++++++++++++++++++++++++++++++++++++++++
DATABASE INFORMATION:
USUARIO BLOQUEADOR:......... SCOTT | STATUS:............. ACTIVE
SID:........................ 123 | SERIAL#:............ 45678
INSTANCE:................... RAC1 | SERVIDOR INSTANCE:.. server01
TIME LOCK:................... 15 MINUTO(s) E 30 SEGUNDO(s)
FORMA DE CONEXAO (programa usado):
SESSION PROGRAM:............. sqlplus.exe
S.O INFORMATION:
PID:......................... 54321
S/O USER:.................... scott_user
MACHINE:..................... workstation01
KILL SESSION:
alter system kill session '123,45678,@1' immediate;
============================ BLOQUEADO ============================
.... SID: 124 | SERIAL#: 98765 | Tipo do Lock: TX | S/O USER: john | USER DB: JOHN | SQL_ID: abc123xyz | INSTANCIA: 1
Esse script é perfeito para uma análise detalhada, especialmente em ambientes complexos.
Dicas de Tuning e Resolução
Identifique a Causa Raiz: Use os scripts para encontrar a query ou transação responsável pelo lock (via
SQL_ID).Otimize Consultas: Queries mal escritas ou sem índices adequados podem prolongar locks. Analise o plano de execução com
EXPLAIN PLAN.Mate Sessões com Cautela: Antes de usar
ALTER SYSTEM KILL SESSION, verifique o impacto na aplicação.Previna Deadlocks: Reduza transações longas e evite atualizações concorrentes nos mesmos dados.
Locks são inevitáveis em um banco de dados multiusuário, mas com as ferramentas certas, como os scripts apresentados, você pode monitorá-los e resolvê-los rapidamente. O primeiro script é ideal para uma visão geral, enquanto o segundo oferece uma análise profunda. Experimente adaptá-los às suas necessidades e mantenha seu Oracle Database rodando suavemente!
Subscribe to my newsletter
Read articles from Sergio Bender directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by