DDL Lock timeout você conhece?

Fala pessoal, tudo beleza por ai? Espero que todos estejam bem!

A dica de hoje é referente a um recurso que surgiu no Oracle 11G, que muitas pessoas não conhecem, mas que pode facilitar muito em ambientes extremamente transacionais.

Vamos falar um pouco sobre DDL Lock timeout, até a versão do Oracle 10. E m ambientes transacionais extremamente concorridos, era um caos realizar algumas alterações devido à não conseguirmos pegar lock. Com isso, era necessária a criação de códigos passíveis de serem executados várias vezes dentro de um loop com sleep para que conseguíssemos pegar o lock e realizar nossa alteração, alterações essas que muitas vezes são simples, porém, na principal tabela transacional do nosso banco de dados, por exemplo uma tabela de pedidos.

Vocês podem estar pensando: “Mas no meu ambiente, nunca tive problemas com lock para alterar objetos no Oracle”, provavelmente é que seu ambiente não é extremamente transacional a esse ponto de não conseguir pegar lock!

Mas, o que acontece quando tentamos alterar um objeto e não conseguimos pegar lock? Vamos simular praticando.

1 – Vamos criar nossa tabela que será utilizada em nosso exemplo.

create table pedido 
(
    idPedido number(18,0) GENERATED ALWAYS AS IDENTITY primary key,
    idProduto number(18,0),
    dataPedido date
);

Agora que já temos nossa tabela criada, precisamos de mais dois scripts para que possamos simular o ambiente transacional e o erro de “resource busy”.

2 – Script que simulará ambiente com vários inserts ocorrendo na nossa tabela de pedido.

set echo on
set timing on
set serveroutput on size unlimited
DECLARE
    V_QTDE NUMBER(9,0);
    V_CONT number(9,0);
    V_LIMITE number(9,0) := 30000;
BEGIN
    V_CONT := 0;
    V_QTDE := 1000000;
    WHILE V_CONT < V_QTDE
    LOOP

        INSERT INTO /*+ APPEND */ PEDIDO (idProduto,dataPedido)
        SELECT round(dbms_random.value(1,999999)) as idProduto,
                TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2020-01-01','J'),TO_CHAR(DATE '2022-12-31','J'))),'J') as dataPedido
        FROM DUAL;


       	if (MOD(V_CONT,V_LIMITE)=0) then
          DBMS_OUTPUT.PUT_LINE('Realizando commit: '|| to_char(V_CONT) ||' \o/');
          commit;
          V_LIMITE := V_limite + 30000;
        end if;

       V_CONT := V_CONT + 1;
    END LOOP;
    commit;
END;
/

3 – Script de criar um index, para forçar o erro.

create index IDX_dataPedido on pedido (datapedido);

Já temos tudo que precisamos, agora vamos abrir duas conexões com banco de dados, uma será o script do nosso insert e outra, a criação do index.

Na conexão 1, colocaremos o nosso insert em execução, como no exemplo abaixo.

Em quanto na conexão 1 deixamos executando o insert, na conexão 2 vamos tentar criar o index em nossa tabela e devemos tomar o erro.

Como esperado, tivemos o erro “ORA-00054“. Agora, repetiremos os mesmos passos, porém, no script de criação do index ad o comando ddl wait, conforme exemplo abaixo.

set echo on
set timing on
set serveroutput on size unlimited
alter session set ddl_lock_timeout=300;
create index IDX_dataPedido on pedido (datapedido);

Na conexão 1 colocaremos novamente o insert para executar, como fizemos anteriormente.

Na conexão 2 executaremos o script de criar o index, porém, agora temos o alter session do ddl lock.

Agora que incluímos o ddl_lock_timeout, nossa conexão ficou esperando até que conseguisse pegar o lock e criar nosso index e como em nosso script dos inserts temos o commit a cada 30.000 inserts, nesse momento que houve um commit na conexão 1, a conexão 2 já aguardava o lock e conseguiu executar a criação do nosso index e na sequencia a conexão 1 continuou o insert até finalizar.

Então, essa é a dica de hoje, pessoal! Espero que possa ser útil no dia a dia de vocês!!!