Dúvidas com lock de tabela dentro de uma trigger

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Aroldo Rique
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Ter, 21 Out 2014 7:26 pm

Prezados, boa tarde.

Estamos com uma dúvida aqui no meu trabalho com relação a uma trigger.

Temos uma tabela chamada MANIFESTACAO, que possui uma sequence como chave primária (campo ID_MANIFESTACAO). Essa tabela também possui vários outros campos. Até agora, toda consulta para identificar cada manifestação é realizada através do campo ID_MANIFESTACAO.

Nosso cliente quer, agora, que a consulta seja feita através de outro código, que seja mais legível. Sugeriu, então, que cada manifestação, daqui por diante, ganhe um novo código que irá identificá-la. Esse código deve ser no formato AAAANNNNNNN, onde o AAAA é o ano corrente e os NNNNNNN é uma sequencia de números. Na virada de ano, a sequencia de números é zerada.

Exemplos desse novo código:

Selecionar tudo

20140000001
20140000002
.
.
.
20140000325
.
.
.
20150000001
20150000002

Para cada registro guardado na tabela manifestação, é gerado um código que é o incremento do código anterior, exceto na virada do ano.

Precisamos criar um campo CODIGO (que será unique) na tabela MANIFESTACAO. Até aí, tudo bem. O problema que estou passando é justamente como gerar esse código.

Criamos uma trigger para a geração automática desses códigos. Criamos também uma tabela chamada GERACAO_CODIGO, que possui 2 campos, ANO e CONTADOR, para ajudar na criação dos códigos.

Essa tabela GERACAO_CODIGO terá apenas 2 campos, o campo ANO e o campo CONTADOR. Inicialmente, a tabela conterá apenas 1 registro (abaixo):

Selecionar tudo

ANO     CONTADOR
2014	0
Essa tabela servirá apenas para ajudar na geração do campo CODIGO da tabela MANIFESTACAO. O campo CODIGO será formado pelo ano corrente da data do cadastro da manifestação concatenado om o campo CONTADOR + 1 da tabela GERACAO_CODIGO.

Então, após o primeiro insert na tabela MANIFESTACAO, o campo CODIGO receberá o valor 20140000001 e a tabela GERACAO_CODIGO sofrerá um update, ficando assim:

Selecionar tudo

ANO     CONTADOR
2014	1
Com novas inserções na tabela MANIFESTACAO, novos códigos serão gerados e novos updates na tabela GERACAO_CODIGO serão relizados:

Selecionar tudo

ANO     CONTADOR
2014	2

ANO     CONTADOR
2014	3

.
.
.

ANO     CONTADOR
2014	1569
Para a geração do CODIGO, optamos por utilizar uma trigger BEFORE INSERT para a tabela MANIFESTACAO.

O código para essa trigger é o seguinte:

Selecionar tudo

CREATE OR REPLACE TRIGGER TRIGGER_GERA_CODIGO BEFORE INSERT ON MANIFESTACAO
FOR EACH ROW
DECLARE
    P_ANO NUMBER(4);
    P_CONTADOR NUMBER(11);    
    P_CODIGO NUMBER(11);
BEGIN

	--recupera o ano da data do cadastro da manifestação
	P_ANO := to_number(tochar(:NEW.DT_CADASTRO_MANIFESTACAO,'yyyy'));

	--recupera o valor do campo contador e coloca o resultado na variável P_CONTADOR, já incrementando o seu valor. A variável P_CONTADOR ajudará a formar o campo CODIGO
    SELECT NVL(CONTADOR + 1,1) 
    INTO P_CONTADOR
    FROM GERACAO_CODIGO WHERE ANO = PANO;
    
	--atualiza a tabela GERACAO_CODIGO. Se a variável P_CONTADOR for maior que 1, devo apenas atualizar o campo CONTADOR do ano correspondente com o seu novo valor, já incrementado
    IF (P_CONTADOR > 1) THEN
		UPDATE GERACAO_CODIGO
        SET CONTADOR = P_CONTADOR
        WHERE ANO = P_ANO;        
    ELSE --atualiza a tabela GERACAO_CODIGO. Se a variável P_CONTADOR for igual a 1, significa que houve virada de ano, então eu devo inserir um registro na tabela GERACAO_CODIGO
        INSERT INTO GERACAO_CODIGO values(P_ANO,0);
    END IF;
	
	--gera o valor do campo CODIGO, a partir do ano da manifestação e da variável P_CONTADOR
    PCODIGO := TO_NUMBER(PANO || LPAD(P_CONTADOR,7,0));
    :NEW.CODIGO := PCODIGO;
    
END;
A trigger está funcionando e os códigos estão sendo gerados corretamente. A minha dúvida é com relação à concorrência. Se duas transações passarem pelo trecho da trigger abaixo ao mesmo tempo, elas terão o mesmo valor atribuído à variável P_CONTADOR, o que gerá um CODIGO igual ao da outra transação, causando um erro na hora do insert, posto que o campo CODIGO é UNIQUE.

Selecionar tudo

SELECT NVL(CONTADOR + 1,1) 
INTO P_CONTADOR
FROM GERACAO_CODIGO WHERE ANO = PANO;
Para resolver isso, eu preciso bloquear a tabela GERACAO_CODIGO para que apenas 1 transação possa ler os dados dessa tabela por vez, ou seja, antes do trecho de código acima, a tabela teria que ser lockada e só liberada após um commit ou rollback.

Não possuo experiência com locks de tabelas no Oracle, gostaria que, por favor, alguém com mais experiência me ajudasse a resolver este problema. Um colega em outro fórum, sugeriu que eu colocasse o seguinte trecho de código bem antes do último trecho de código citado acima:

Selecionar tudo

LOCK TABLE geracao_codigo
IN EXCLUSIVE MODE
NOWAIT;
Ainda não cheguei a testar, pois não pude ir ao trabalho hoje. Irei testar amanhã. Alguém possui mais alguma sugestão. Esse lock descrito acima garante que a tabela GERACAO_CODIGO fique locada inclusive para leitura?

Antecipadamente agradeço a ajuda!
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Aroldo,

A trigger se caracteriza por ser um contador onde o problema de concorrência é perfeitamente administrável. Com base nela, não seria teoricamente possível que dois usuários concorrentes conseguissem o mesmo valor dela.

Agora, acho que você criará realmente uma concorrência nesta tabela ao fazer o UPDATE atualizando o valor.

Uma sugestão: porque você não cria uma sequence composta de AAAANNNNNNN ? Inicie ela com o ano corrente (ex: 20140000001). Ao final de cada ano, você poderia disparar uma rotina para posicionar esta sequence no próximo ano (ex: 20150000001).

Acho que com isso, você evitaria o problema de concorrência, e - dependendo da intensidade de carga na tabela - poderia colocar esta sequence em cache. Talvez até fosse mais performático, pois ela não precisaria ficar executando o código PL/SQL da trigger a cada INSERT.

Só uma coisa: adotando a trigger, você não conseguirá evitar "buracos" na sua nomeração. Elas podem ser causadas por falhas/erros na transação (ex: falta espaço tablespace) ou por shutdown do banco (ex: se a sua sequence estiver em cache).

Não sei a sugestão que lhe faço seria uma visão simplista de seu problema, mas acredito que a SEQUENCE seria a melhor solução (ou mais simples) para lidar com LOCK e concorrência de tabelas.

Talvez os foristas possam lhe dar outras sugestões sobre este problema.

Abraços,

Sergio Coutinho
Aroldo Rique
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Ter, 21 Out 2014 7:26 pm

Sergio, obrigado pela ajuda!

Se eu pudesse fazer tudo pela sequence, realmente seria muito melhor, mas como eu faço esta rotina que citastes?

Ao final de cada ano, você poderia disparar uma rotina para posicionar esta sequence no próximo ano (ex: 20150000001).


E se eu colocar um FOR UPDATE neste trecho do código:

Selecionar tudo

select nvl(contador + 1,1)

   into p_contador

  from geracao_codigo

where ano = pano

FOR UPDATE;
Isso deixará a minha tabela GERACAO_CODIGO lockada, inclusive para leitura?
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Aroldo,

Acho que você poderia primeiro fazer um teste com comandos DDL.
Depois você poderia pensar em colocar tudo em uma procedure (com comandos EXECUTE IMMEDIATE)

Roteiro (isso teria que ser feito com uma manutencao programada. teste em desenvolvimento antes)
--------
A) Digamos que você tem uma sequence (SQ_CONTA) com o valor de 20140001002
B) Faça a conta 20150000001-20140001002-10 (isso seria para você ter um valor aproximado do salto)
C) Você deverá ter um valor aproximado de 9998989
D) Execute os comandos

Selecionar tudo

-- Se ela estiver em cache
ALTER SEQUENCE SQ_CONTA NOCACHE;
-- Altere o salto da sequence
ALTER SEQUENCE SQ_CONTA INCREMENT BY 9998989;
-- Faca o salto
SELECT SQ_CONTA.NEXTVAL FROM DUAL
-- Volte o salto novamente para 1
ALTER SEQUENCE SQ_CONTA INCREMENT BY 1;
-- Se a sequence estaca em cache, informe o cache usado (ex: 20);
ALTER SEQUENCE SQ_CONTA CACHE 20;
E) Isso vai te colocar próximo à 20150000001. Veja o valor corrente:

Selecionar tudo

SELECT SQ_CONTA.CURRVAL FROM DUAL
F) Se estiver abaixo do valor esperado, efetue até chegar ao valor o comando:

Selecionar tudo

SELECT SQ_CONTA.NEXTVAL FROM DUAL 
Talvez exista uma forma mais fácil de fazer tudo isso, mas é o que me vem à cabeça no momento. Talvez os foristas aqui tenham uma solução menos complicada que a minha.

Abraços,

Sergio Coutinho
Aroldo Rique
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Ter, 21 Out 2014 7:26 pm

Sergio, muito obrigado pela ajuda! O pessoal aqui está estudando a utilização de sequences para resolver o problema.

Até o momento, a solução encontrada foi esta:

Criamos uma tabela chamada GERACAO_CODIGO, que possui 2 campos: ANO e CONTADOR. Inserimos os seguintes valores nessa tabela:

Selecionar tudo

ANO  CONTADOR 
2014	 0
2015  0
2016  0
*Podem ser feitas novas inserções para os próximos anos.

Para a tabela MANIFESTACAO, criamos uma TRIGGER BEFORE INSERT:

Selecionar tudo

DECLARE
    P_ANO NUMBER(4);
    P_CONTADOR NUMBER(11);    
    P_CODIGO NUMBER(11);
BEGIN

    --recupera o ano da data do cadastro da manifestação  
    P_ANO := to_number(to_char(:NEW.DT_CADASTRO_MANIFESTACAO,'yyyy'));
    
    --recupera o valor do contador
    SELECT contador 
    INTO P_CONTADOR
    FROM ouvidoria.geracao_codigo WHERE ANO = P_ANO FOR UPDATE;
    
    --incrementa o contador
    P_CONTADOR := P_CONTADOR + 1;
    
    UPDATE ouvidoria.geracao_codigo
    SET CONTADOR = P_CONTADOR
    WHERE ANO = P_ANO;
    
    P_CODIGO := TO_NUMBER(P_ANO || LPAD(P_CONTADOR,7,0));    
    :NEW.CODIGO := P_CODIGO;
    
END;
A tabela GERACAO_CODIGO só possui um SELECT que é o que existe nesta TRIGGER. Como o SELECT que consulta essa tabela está com o FOR UPDATE, caso outra transação tente passar por essa instrução, terá que aguardar a anterior sofrer um rollback ou commit.
Responder
  • Informação