Deadlock no Oracle: como diagnosticar e resolver de verdade

Recebi uma ligação às 2h40 da manhã de um cliente do varejo dizendo que o banco tinha "travado". No alert.log havia um ORA-00060: deadlock detected while waiting for resource, e o time de sustentação já estava discutindo reiniciar a instância. Não precisava. O deadlock já tinha sido resolvido pelo próprio Oracle segundos depois de acontecer, e o que sobrou foi uma transação da aplicação em estado inconsistente porque ninguém tratou a exceção. Esse é o padrão que mais vejo quando o assunto é deadlock Oracle: o banco fez o trabalho dele e a aplicação não fez o dela.

lock, bloqueio e deadlock são coisas diferentes

Vale separar os três antes de qualquer diagnóstico, porque a maior parte da confusão nasce aqui.

Lock é o mecanismo normal de controle de concorrência. Toda vez que você faz um UPDATE, o Oracle coloca um lock exclusivo naquela linha. Isso acontece o tempo todo, em todo banco saudável, e não é problema nenhum.

Bloqueio (ou block, ou espera) é quando uma sessão precisa de uma linha que outra sessão já travou e ainda não commitou. A segunda sessão fica esperando no evento enq: TX - row lock contention. Pode esperar cinco segundos ou pode esperar até o fim do expediente, porque o Oracle não tem timeout padrão para isso. Se o desenvolvedor abriu uma tela, fez o UPDATE e foi almoçar sem commitar, a fila cresce. Bloqueio o banco não resolve sozinho.

Deadlock é um ciclo fechado. A sessão A espera por um recurso que a sessão B segura, e a sessão B espera por um recurso que a sessão A segura. Nenhuma das duas vai sair dali por conta própria. O Oracle detecta esse ciclo em poucos segundos, escolhe uma das sessões como vítima e faz rollback apenas da instrução que fechou o ciclo, devolvendo ORA-00060 para o cliente. A transação continua aberta, com tudo que já foi feito antes ainda pendente. Isso costuma surpreender quem vem de outros bancos.

reproduzindo um deadlock em duas sessões

Monte a tabela:

CREATE TABLE tst_deadlock (id NUMBER PRIMARY KEY, valor NUMBER);
INSERT INTO tst_deadlock VALUES (1, 100);
INSERT INTO tst_deadlock VALUES (2, 200);
COMMIT;

Agora abra duas sessões no SQL*Plus e siga a ordem.

Sessão 1, primeiro passo:

UPDATE tst_deadlock SET valor = valor + 1 WHERE id = 1;

Sessão 2, segundo passo:

UPDATE tst_deadlock SET valor = valor + 1 WHERE id = 2;

Sessão 1, terceiro passo (vai ficar pendurada, esperando a linha 2):

UPDATE tst_deadlock SET valor = valor + 1 WHERE id = 2;

Sessão 2, quarto passo:

UPDATE tst_deadlock SET valor = valor + 1 WHERE id = 1;

No instante em que a sessão 2 pede a linha 1, o ciclo fecha. Em geral em menos de três segundos uma das duas recebe:

UPDATE tst_deadlock SET valor = valor + 1 WHERE id = 2
       *
ERRO na linha 1:
ORA-00060: deadlock detected while waiting for resource

Repare que a sessão que recebeu o erro continua com transação aberta e ainda segura o lock da linha que atualizou antes. Quem tem que decidir entre ROLLBACK e refazer a instrução é a aplicação. Se o código engolir a exceção e seguir adiante, você acabou de criar um bloqueio permanente onde antes havia um deadlock de três segundos.

como diagnosticar depois que ele acontece

O alert.log registra a ocorrência e aponta o arquivo de trace com o detalhe:

ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 errors.
More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18432.trc

Se quiser localizar o diretório sem sair do banco:

SELECT name, value FROM v$diag_info WHERE name IN ('Diag Trace','Default Trace File');

Dentro do trace está a parte que interessa:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090016-00002a1f        41      132     X            57      201           X
TX-000a0021-00001b3c        57      201     X            41      132           X

Rows waited on:
  Session 132: obj - rowid = 0001A2F3 - AAAaLzAAEAAAAJvAAB
  Session 201: obj - rowid = 0001A2F5 - AAAaL1AAEAAAAKDAAA

A leitura é direta: a sessão 132 segura o recurso TX-00090016 em modo exclusivo e espera o TX-000a0021, que a 201 segura. Fecha o ciclo. Logo abaixo, o trace traz o SQL de cada sessão, o que normalmente já entrega qual rotina da aplicação está envolvida.

O número em obj é o object_id. Para saber qual tabela era:

SELECT owner, object_name, object_type
FROM   dba_objects
WHERE  object_id = TO_NUMBER('0001A2F3','XXXXXXXX');

Um detalhe que muda o diagnóstico: se o resource name começar com TM em vez de TX, e o modo for 4 (Share) ou 5 (Share Row Exclusive), você quase certamente está diante de lock de tabela, e o suspeito número um é chave estrangeira sem índice.

consultas para investigar ao vivo

Enquanto a espera está acontecendo, v$session já responde quem trava quem:

SELECT s.sid, s.serial#, s.username, s.event, s.seconds_in_wait,
       s.blocking_session, s.blocking_session_status, s.sql_id,
       s.row_wait_obj#, s.row_wait_row#
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL;

E v$lock mostra o detalhe do enqueue, incluindo quem é bloqueador (block = 1):

SELECT l.sid, l.type, l.id1, l.id2, l.lmode, l.request, l.ctime, l.block,
       o.object_name
FROM   v$lock l
LEFT   JOIN dba_objects o ON o.object_id = l.id1 AND l.type = 'TM'
WHERE  l.type IN ('TX','TM')
ORDER  BY l.block DESC, l.ctime DESC;

Para o caso mais comum, que é descobrir o que houve durante a madrugada, o histórico do ASH resolve (lembrando que DBA_HIST exige licença do Diagnostics Pack):

SELECT sample_time, session_id, session_serial#, blocking_session,
       event, sql_id, current_obj#
FROM   dba_hist_active_sess_history
WHERE  sample_time BETWEEN TIMESTAMP '2026-07-24 02:30:00'
                       AND TIMESTAMP '2026-07-24 03:00:00'
AND    event LIKE 'enq: T%'
ORDER  BY sample_time;

as duas causas que respondem por quase tudo

A primeira é ordem inconsistente de acesso aos recursos. A rotina de faturamento atualiza PEDIDO e depois ESTOQUE. A rotina de devolução atualiza ESTOQUE e depois PEDIDO. Em baixa concorrência ninguém percebe. No pico de vendas as duas se encontram e o ciclo se forma. Já perdi noite por causa disso em um cliente de locação de equipamentos, e a correção final foram três linhas trocadas de lugar em uma procedure.

A segunda é chave estrangeira sem índice, que muita gente nunca ouviu falar. Quando você faz DELETE ou atualiza a coluna de chave primária da tabela pai, o Oracle precisa verificar as linhas filhas. Sem índice na coluna de FK, ele pega um lock de tabela na filha inteira em vez de travar apenas as linhas relacionadas. Duas sessões mexendo em pais diferentes passam a competir pela mesma tabela filha e o deadlock aparece sem nenhuma explicação aparente no código. Para achar as FKs desindexadas:

SELECT c.table_name, c.constraint_name, cc.column_name, cc.position
FROM   user_constraints c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
AND    NOT EXISTS (SELECT 1
                   FROM   user_ind_columns ic
                   WHERE  ic.table_name = cc.table_name
                   AND    ic.column_name = cc.column_name
                   AND    ic.column_position = cc.position)
ORDER  BY c.table_name, c.constraint_name, cc.position;

prevenção

Padronize a ordem de acesso às tabelas em todas as rotinas transacionais, de preferência documentada e cobrada em code review. Mantenha as transações curtas, sem interação humana no meio delas. Indexe as colunas de chave estrangeira, especialmente onde existe DELETE no pai. Onde faz sentido do ponto de vista do negócio, SELECT ... FOR UPDATE NOWAIT ou WAIT n deixa o problema explícito na hora em vez de escondê-lo em uma espera longa.

Sendo honesto com você: deadlock recorrente é sintoma de problema de aplicação. O DBA diagnostica, aponta as sessões, mostra o SQL envolvido e prova qual é o ciclo, mas a correção quase sempre mora no código, e essa conversa com o time de desenvolvimento é parte do trabalho.

Entender lock, enqueue e leitura de trace é o tipo de base que separa quem apaga incêndio de quem resolve o problema uma vez só. É isso que eu ensino no curso Oracle Fundamentals, com a arquitetura do banco explicada de um jeito que você consegue aplicar no ambiente de produção na segunda-feira seguinte.