Deadlock entre sessões (ORA-00060)

Num deadlock, duas ou mais sessões esperam umas pelas outras em ciclo, e nenhuma anda. O Oracle quebra o ciclo devolvendo o ORA-00060 a uma delas e grava o grafo de espera no trace dessa sessão. O tipo de lock do grafo aponta a causa.

Sintomas

No Diagnóstico, aparece um achado DEADLOCK para cada deadlock do trace. O título é o diagnóstico seguido das sessões do ciclo, como Deadlock de linha (TX em modo X) entre as sessões <a> e <b>. Quando o trace tem mais de um deadlock e o horário de cada um é conhecido, ele vem no fim do título, entre parênteses. A severidade é sempre alta, sem percentual do tempo total.

O texto explica a causa provável e a correção e lembra que o Oracle desfez só o comando da sessão que recebeu o ORA-00060: a transação dela continua aberta até o commit ou o rollback. Quando o trace informa, traz o sql_id do comando que recebeu o erro. Termina com Ver deadlock.

O achado vem do bloco DEADLOCK DETECTED, em dois tipos de arquivo:

• o trace só de deadlock, que o Oracle grava para a sessão que recebeu o erro (como pegar o trace de deadlock). O relatório não tem perfil, e o Resumo mostra Deadlocks (ORA-00060), Detectado em, Sessões envolvidas e Diagnóstico;
• o trace 10046 da sessão que recebeu o erro. O relatório é o completo, e em geral traz também o achado de erros Oracle no trace, pela linha ERROR do ORA-00060, e a espera enq: TX - row lock contention ou enq: TM - contention logo antes do deadlock.

O Diagnóstico mostra um achado para cada um dos primeiros deadlocks do trace, e a seção Deadlocks traz o detalhe deles.

Causa

O diagnóstico vem do tipo de lock no grafo de espera e do modo pedido:

A página do ORA-00060 na documentação da Oracle resume o erro.

Resolução

Ler o deadlock no relatório

1. Clique em Ver deadlock.

   Resultado: abre o cartão Deadlock n, com o diagnóstico, o horário e a linha do trace em que o bloco começa.

2. Siga o ciclo: cada sessão espera a seguinte, e a última volta à primeira.
3. No Grafo de espera, veja para cada recurso o Tipo, a Sessão que espera com o Modo pedido e a Sessão que segura com o Modo seguro.
4. Em Sessões envolvidas, veja Usuário, Máquina, Programa, Módulo, Ação e o SQL de cada sessão. A marcada com recebeu o ORA-00060 é a dona do trace.
5. Em Linha esperada, veja o object_id, o arquivo, o bloco, o slot e o rowid da linha que cada sessão esperava. Sem Linha esperada, a espera não era por uma linha: é o caso de TM, UL, ITL e chave única.
6. Em Pilha PL/SQL da sessão que recebeu o erro, ache a unidade PL/SQL e a linha do código que executou o comando.

Com o dono e o nome da tabela do object_id, o rowid mostra a linha disputada, se ela ainda existe:

select owner, object_name, object_type
  from dba_objects
 where object_id = &object_id;

select *
  from exemplo.pedido
 where rowid = '&rowid';

Deadlock de linha (TX em modo X)

• Altere as linhas sempre na mesma ordem em todas as rotinas, pela chave, por exemplo.
• Trave as linhas antes de alterar, com select ... for update, na mesma ordem.
• Encurte a transação: menos linhas presas por menos tempo deixam menos chance de ciclo.

Transação com espera em modo S

• Chave única: veja se duas rotinas inserem as mesmas chaves em ordens diferentes, ou se a mesma carga roda duas vezes ao mesmo tempo.
• ITL: aumente o INITRANS da tabela ou do índice do grafo, como em espaço de transação no bloco.
• Índice bitmap em tabela com DML concorrente: troque por índice B-tree.

Lock de tabela (TM)

Crie índice nas colunas da chave estrangeira da tabela filha. O object_id aparece no texto do achado e na coluna Tipo do grafo; na console, ele abre a página do objeto, com o bloco Tabela: índices e as colunas de cada um. Para achar todas as chaves estrangeiras sem índice do esquema, use a consulta de indexar a chave estrangeira.

Lock de usuário (UL)

Peça os locks de DBMS_LOCK sempre na mesma ordem em todas as rotinas e solte cedo, com release_on_commit ou DBMS_LOCK.RELEASE.

Tratar o ORA-00060 na aplicação

O Oracle desfez só o comando que recebeu o erro. A transação continua aberta, com as linhas que já tinha travado: a aplicação precisa fazer o rollback dela e, se a operação puder ser repetida, tentar de novo. Sem isso, a sessão continua segurando as linhas e trava as outras.

Como confirmar

Deadlock é intermitente: um trace sem ele não prova a correção. Acompanhe, no período em que ele costumava acontecer, se o ORA-00060 volta. Cada ocorrência grava um trace novo da sessão que recebeu o erro e uma linha no alert log. No 19c, a visão v$diag_alert_ext lê o alert log pelo banco:

select originating_timestamp, message_text
  from v$diag_alert_ext
 where message_text like '%ORA-00060%'
 order by originating_timestamp desc;

Se o erro voltar, envie o trace novo e compare o diagnóstico, as sessões e os comandos com os do deadlock anterior.

No relatório

O relatório de exemplo não tem deadlock, e a seção Deadlocks só aparece quando o trace tem algum.

• Deadlocks: um cartão por deadlock, com o diagnóstico, o ciclo, o Grafo de espera, as Sessões envolvidas e a pilha PL/SQL (deadlocks).
• Diagnóstico: um achado por deadlock, com o link Ver deadlock (Diagnóstico).
• Resumo: no trace só de deadlock, os quadros Deadlocks (ORA-00060), Detectado em, Sessões envolvidas e Diagnóstico (resumo).
• Erros: no trace 10046, o cartão do ORA-00060 tem o link Ver o deadlock (erros).
• Comandos SQL por contribuição: no trace 10046, o comando que recebeu o erro, pela coluna SQL ID (comandos SQL).

Antes do deadlock, as sessões costumam esperar umas pelas outras sem fechar o ciclo; esse tempo aparece em espera por bloqueio de outra sessão.