Solucionando o erro ORA-01591 – lock held by in-doubt distributed transaction

O erro: ORA-01591: lock held by in-doubt distributed transaction, ou em um bom português ORA-01591: bloqueio retido pela transação distribuída 3251.6.3549 geralmente é causado por timeout entre as instâncias de um Oracle RAC.

A consulta por transações retidas ficam na tabela: DBA_2PC_PENDING. Um simples select nessa tabela retorna informações sobre as transações locais e globais:

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,STATE,
'EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('||''''||LOCAL_TRAN_ID||''''||');'||chr(13)||'COMMIT;'
FROM DBA_2PC_PENDING
ORDER BY 1 ASC;

Para limpar as transações, basta executar o comando abaixo, onde o @local_tran_id é o campo LOCAL_TRAN_ID e o @COMMIT# é o campo COMMIT# da tabela DBA_2PC_PENDING:

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('@LOCAL_TRAN_ID');
COMMIT;

Em alguns casos é preciso realizar o rollback da transação através do comando ROLLBACK FORCE ‘@LOCAL_TRAN_ID’,’@COMMIT#’ ficando o script completo dessa maneira:

ROLLBACK FORCE '@LOCAL_TRAN_ID','@COMMIT#';
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('@LOCAL_TRAN_ID');
COMMIT;

Para automatizar essa limpeza podemos utilizar o seguinte script no crontab ou no Oracle Scheduler e agendar da forma como seu ambiente se comporta, podendo ser diário, semanal ou mensal:

declare
xid varchar2(22);
begin
for c1 in (select LOCAL_TRAN_ID FROM DBA_2PC_PENDING) loop
dbms_transaction.PURGE_LOST_DB_ENTRY(c1.LOCAL_TRAN_ID);
commit;
end loop;
end;

Importante: Em situações que não for possível purgar a transação via pacote DBMS, o procedimento poderá ser feito de forma manual executando os 3 deletes abaixo:

delete from sys.pending_trans$ where local_tran_id = 'LOCAL_TRAN_ID';

delete from sys.pending_sessions$ where local_tran_id = 'LOCAL_TRAN_ID';

delete from sys.pending_sub_sessions$ where local_tran_id = 'LOCAL_TRAN_ID';

commit;

Há também situações mais graves em que sua transação não aparece nem nas V$ de controle do Oracle, e a solução é um pouco mais difícil, inclusive consegui a solução através desse link. Comece fazendo o select abaixo:

SELECT a.KTUXEUSN + ‘.’ + a.KTUXESLT + ‘.’ + a.KTUXESQN as “transação”, a.* FROM x$ktuxe a WHERE a.ktuxesta <> ‘INACTIVE’;

Insira o valor que está na coluna transação nas seguintes tabelas:

INSERT INTO sys.pending_trans$
(local_tran_id, global_tran_fmt, global_oracle_id, state, status, session_vector, reco_vector, type#, fail_time, reco_time)
VALUES
('19.5.14679063', 306206, 'XXXXXXX.12345.1.2.3', 'prepared', 'P', HEXTORAW('00000001'), HEXTORAW('00000000'), 0, SYSDATE, SYSDATE);

INSERT INTO sys.pending_sessions$
VALUES
('19.5.14679063',1,HEXTORAW('05004F003A1500000104'),'C', 0, 30258592,  '', 146);

COMMIT;

Após inserir os dados nas tabelas, execute o comando abaixo para fazer rollback da transação e verifique se os registros foram expurgados das views: sys.pending_trans$ e sys.pending_sessions$. Se sim, seu problema está resolvido, senão, faça o próximo procedimento!

rollback force '19.5.14679063';
COMMIT;

select * from sys.pending_sessions$ where local_tran_id= '19.5.14679063';
select * from sys.pending_trans$ where local_tran_id= '19.5.14679063'

Caso os registros ainda se encontrem nas views acima, execute o comando abaixo:

EXEC dbms_transaction.purge_lost_db_entry('19.5.14679063');
COMMIT;

Se ainda sim, o registro ainda estiver nas views e bloqueando as execuções, abra um chamado na oracle para apoio.

É importante frisar que esse procedimento resolve os problemas das transações retidas e não elimina a causa raiz do problema, procure encontrar evidências no ALERT.LOG, no log do GRID para resolver o problema por completo.