Introdução

Um tempo atrás eu estava validando a configuração de um monitoramento de Deadlock no OEM, e precisava que esse tipo de evento ocorresse com frequência em algum banco de dados dados enquanto realizava os ajustes, para validar que a monitoração estava apresentando o comportamento esperado.

Para não depender de um Deadlock real gerado pela aplicação (que poderia nunca acontecer), montei o procedimento abaixo em um das bases de testes, o que permite gerar um Deadlock sob demanda, assim facilita qualquer tipo de trabalho de ajuste ou validação de monitoramento.

Preparando o ambiente

1) Com o SYS, crie um usuário de teste:

create user app_teste identified by teste;
grant create session, resource, unlimited tablespace to app_teste;
grant execute on sys.dbms_lock to app_teste;

2) Conecte-se com o novo usuário de teste:

conn app_teste/teste@freepdb1

3) Crie uma tabela de teste:

CREATE TABLE tableA (id NUMBER, colA VARCHAR2(20) );

INSERT INTO tableA values (12,'teste12');
INSERT INTO tableA values (21,'teste21');
COMMIT;

4) Crie uma procedure de teste com uma lógica que pode resultar em Deadlock:

CREATE OR REPLACE PROCEDURE procUpdate ( id1 IN NUMBER, id2 IN NUMBER ) IS
BEGIN
   UPDATE tableA set colA = 'upd1' where id = id1;
   dbms_lock.sleep (20);
   UPDATE tableA set colA = 'upd2' where id = id2;
   commit;
END;
/

Gerando o Deadlock

5) Abra duas sessões com SQLPLUS simultaneamente, uma em cada terminal.

terminal1> sqlplus app_teste/teste@freepdb1

terminal2> sqlplus app_teste/teste@freepdb1

Dica: Se for um Oracle RAC, prefira abrir as duas sessões na mesma instância para facilitar a verificação do alert.log em apenas um dos nodes.

6) Na sessão 1, execute a procedure passando os valores 21 e 12:

exec procUpdate( 21, 12 );

7) Na sessão 2, execute a procedure invertendo os valores (12 e 21):

exec procUpdate( 12, 21 );

Em alguns segundos uma das sessões deve receber um erro de Deadlock e essa informação deve ser gerada no alert log da instância.

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "APP_TESTE.PROCUPDATE", line 5
ORA-06512: at line 1

Para gerar um novo Deadlock sob demanda, basta repetir os passos 6 e 7 conforme necessidade.

Desfazendo o caso de teste

Quando não for mais necessário realizar os testes, pode dropar o usuário de teste para não deixar sujeira e um ponto de vulnerabilidade no ambiente:

drop user app_teste cascade;