Locar tabelas em uma trigger

[Aroldo Rique 0]

Prezados, boa tarde.

 

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

 

Precisamos criar um campo código na tabela MANIFESTACAO deverá ser no formato AAAANNNNNNN.Exemplos:

20140000001

20140000002

.

.

.

20140000325

.

.

.

20150000001

20150000002

 

Para cada registro guardado na tabela manifestação, é gerado um código que é o incremento do código anterior, concatenado com o ano do cadastro da manifestação.

 

 

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.

 

O código da nossa trigger para a tabela MANIFESTACAO é o seguinte:

CREATE OR REPLACE TRIGGER TRIGGER_GERA_CODIGO BEFORE INSERT ON MANIFESTACAO
FOR EACH ROW
DECLARE
    PANO NUMBER(4);
    PCONTADOR NUMBER(11);    
    PCODIGO NUMBER(11);
BEGIN

    select EXTRACT(YEAR FROM :NEW.DT_CADASTRO_MANIFESTACAO)
    INTO PANO
    from dual;

    SELECT NVL(MAX(contador)+1,1) 
    INTO PCONTADOR
    FROM GERACAO_CODIGO WHERE ANO = PANO;
    
    IF (PCONTADOR = 1) THEN
        INSERT INTO GERACAO_CODIGO values(PANO,1);
    ELSE
        UPDATE GERACAO_CODIGO
        SET CONTADOR = PCONTADOR
        WHERE ANO = PANO;
    END IF;

    PCODIGO := TO_NUMBER(PANO || LPAD(PCONTADOR,7,0));
    :NEW.CODIGO := PCODIGO;
    
END;

A minha dúvida é a seguinte:

 

Essa trigger faz o lock da tabela GERACAO_CODIGO?

Em caso de negativo, como eu faço para lockar a tabela GERACAO_CODIGO, para que não haja o risco de que se gere 2 codigos iguais, quando haver 2 transações concorrentes?

 

Antecipadamente agradeço a ajuda!

 

Observação: Minha classe java está com a annotation @Transactional do Spring acima da classe.

[Motta 645]

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2130052

 

 

 

Estando em transaction faça um select for update antes do update da tabela, vai garantir o acesso único , outro session irá esperar a liberação do registro, mas cuidado que se de fato a transação for demorada um gargalo poderá ser criado.

 

 

Pelo visto uma solução via sequence não atende face a questão do ano e possivelmente não pode haver "banguelas" ie sequencias faltantes.

[Aroldo Rique 0]

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2130052

 

 

 

Estando em transaction faça um select for update antes do update da tabela, vai garantir o acesso único , outro session irá esperar a liberação do registro, mas cuidado que se de fato a transação for demorada um gargalo poderá ser criado.

 

 

Pelo visto uma solução via sequence não atende face a questão do ano e possivelmente não pode haver "banguelas" ie sequencias faltantes.

 

Motta, obrigado pela ajuda.

 

Não tem como eu dar um SELECT FOR UPDATE em algum lugar de dentro da trigger?

[Motta 645]

Antes do update do geracao_codigo.

[Aroldo Rique 0]

Antes do update do geracao_codigo.

 

Alterei a trigger e ela ficou assim:

CREATE OR REPLACE TRIGGER TRIGGER_GERA_CODIGO BEFORE INSERT ON manifestacao
FOR EACH ROW
DECLARE
    PANO NUMBER(4);
    PCONTADOR NUMBER(11);    
    PCODIGO NUMBER(11);
BEGIN

    SELECT EXTRACT(YEAR FROM :NEW.DT_CADASTRO_MANIFESTACAO)
    INTO PANO
    from dual;

    SELECT NVL(MAX(contador)+1,1) 
    INTO PCONTADOR
    FROM geracao_codigo WHERE ANO = PANO FOR UPDATE;
    
    IF (PCONTADOR = 1) THEN
        INSERT INTO geracao_codigo values(PANO,1);
    ELSE
        UPDATE geracao_codigo
        SET CONTADOR = PCONTADOR
        WHERE ANO = PANO;
    END IF;

    PCODIGO := TO_NUMBER(PANO || LPAD(PCONTADOR,7));
    :NEW.CODIGO := PCODIGO;
    
END;

Mas o Oracle não aceitou o resultado e deu a seguinte mensagem de erro:

 

[1]: ORA-24344: êxito com erro de compilação

[1]: 13/52 PL/SQL: ORA-01786: FOR UPDATE desta expressão de consulta não é permitido

[1]: 11/5 PL/SQL: SQL Statement ignored

[Motta 645]

Creio ser isto , falta testar.

from dual; 

SELECT NVL(MAX(contador)+1,1) 
INTO PCONTADOR 
FROM geracao_codigo 
WHERE ANO = PANO; 


begin
  select ano into from geracao_codigo where ano = pano for update;
exception
  when others then 
    null;
end;

IF (PCONTADOR = 1) THEN 
  INSERT INTO geracao_codigo values(PANO,1); 
ELSE 
  UPDATE geracao_codigo SET CONTADOR = PCONTADOR WHERE ANO = PANO; END IF;
  ...

[Aroldo Rique 0]

Obrigado, Motta, amanhã eu vou testar.

 

Só não entendi bem este trecho de código:

begin
  select ano into from geracao_codigo where ano = pano for update;
exception
  when others then 
    null;
end;

Acho que não tem esse "into" no meio, não é mesmo?

 

 

Creio ser isto , falta testar.

from dual; 

SELECT NVL(MAX(contador)+1,1) 
INTO PCONTADOR 
FROM geracao_codigo 
WHERE ANO = PANO; 


begin
  select ano into from geracao_codigo where ano = pano for update;
exception
  when others then 
    null;
end;

IF (PCONTADOR = 1) THEN 
  INSERT INTO geracao_codigo values(PANO,1); 
ELSE 
  UPDATE geracao_codigo SET CONTADOR = PCONTADOR WHERE ANO = PANO; END IF;
  ...

[Motta 645]

Faltou a variável para receber o valor.

 

O "for update" não serve com funções de agregação com max etc.

 

Editei no tablet , uma ₩4/)@ ...

 

Se tiver tempo posto uma solução melhor amanhã, com LOCK TABLE talvez.

[Aroldo Rique 0]

Faltou a variável para receber o valor.

 

O "for update" não serve com funções de agregação com max etc.

 

Editei no tablet , uma ₩€4/)@ ...

 

Se tiver tempo posto uma solução melhor amanhã

Ah tá, blz!

Motta, só mais uma dúvida.

 

Eu preciso impedir que 2 transações concorrentes passem, ao mesmo tempo, por este trecho de código:

SELECT NVL(MAX(contador)+1,1) 
INTO PCONTADOR 
FROM geracao_codigo 
WHERE ANO = PANO; 

Se 2 transações atribuírem o mesmo valor para a variável PCONTADOR, o meu campo CODIGO da tabela, que é formado assim:

PCODIGO := TO_NUMBER(PANO || LPAD(PCONTADOR,7));
:NEW.CODIGO := PCODIGO;

terá o mesmo valor para as 2 transações.

 

Então, preciso arranjar um jeito que, após uma transação passar por este trecho de código:

SELECT NVL(MAX(contador)+1,1) 
INTO PCONTADOR 
FROM geracao_codigo 
WHERE ANO = PANO; 

a tabela geracao_codigo fique bloqueada, inclusive para leitura. Só depois que eu finalizar a transação anterior, é que a segunda transação poderá passar por esse trecho de código e ler corretamente o valor correto do campo CONTADOR, que já terá sido incrementado pela transação anterior.

 

 

 

Creio ser isto , falta testar.

from dual; 

SELECT NVL(MAX(contador)+1,1) 
INTO PCONTADOR 
FROM geracao_codigo 
WHERE ANO = PANO; 


begin
  select ano into from geracao_codigo where ano = pano for update;
exception
  when others then 
    null;
end;

IF (PCONTADOR = 1) THEN 
  INSERT INTO geracao_codigo values(PANO,1); 
ELSE 
  UPDATE geracao_codigo SET CONTADOR = PCONTADOR WHERE ANO = PANO; END IF;
  ...

[Motta 645]

teste assim ...

CREATE OR REPLACE TRIGGER TRIGGER_GERA_CODIGO BEFORE INSERT ON manifestacao
FOR EACH ROW
DECLARE
    PANO NUMBER(4);
    PCONTADOR NUMBER(11);    
    PCODIGO NUMBER(11);
BEGIN
    PANO := to_number(tochar(:NEW.DT_CADASTRO_MANIFESTACAO,'yyyy'));

    SELECT NVL(MAX(contador)+1,1)
    INTO PCONTADOR
    FROM geracao_codigo WHERE ANO = PANO;
    
    LOCK TABLE geracao_codigo
         IN EXCLUSIVE MODE
         NOWAIT;
    
    IF (PCONTADOR = 1) THEN
        INSERT INTO geracao_codigo values(PANO,1);
    ELSE
        UPDATE geracao_codigo
        SET CONTADOR = PCONTADOR
        WHERE ANO = PANO;
    END IF;

    PCODIGO := TO_NUMBER(PANO || LPAD(PCONTADOR,7));
    :NEW.CODIGO := PCODIGO;
    
END;

Force concorrências, creio que funcione mas nunca precisei fazer assim, em geral sequences resolvem.

[Aroldo Rique 0]

Blz, obrigado pelas dicas.

 

Tive um problema e não pude ir ao trabalho hoje, mas amanhã eu irei e farei os testes.

 

Andei pensando aqui e vi que não é necessário utilizar a função MAX para recuperar o contador, pois a tabela GERACAO_CODIGO terá SEMPRE apenas 1 registro por ano, sacou?

 

Aqui também usamos sequences para gerar a chave primária. O problema é que o cliente quer um código mais intuitivo para poder guardar e realizar consultas e sugeriu que fosse AAAANNNNNNN.

 

Nunca tinha trabalhado com locks de tabelas no Oracle.

 

Analisei o seu código e você não acha que o trecho:

LOCK TABLE geracao_codigo
         IN EXCLUSIVE MODE
         NOWAIT;

não deveria vir antes do trecho abaixo:

SELECT NVL(MAX(contador)+1,1)
    INTO PCONTADOR
    FROM geracao_codigo WHERE ANO = PANO;

É porque se o lock vier depois do trecho acima, 2 transações podem ter o mesmo valor atribuído à variável PCONTADOR, sacou?

 

Mais uma vez, obrigado pela ajuda!

 

 

teste assim ...

CREATE OR REPLACE TRIGGER TRIGGER_GERA_CODIGO BEFORE INSERT ON manifestacao
FOR EACH ROW
DECLARE
    PANO NUMBER(4);
    PCONTADOR NUMBER(11);    
    PCODIGO NUMBER(11);
BEGIN
    PANO := to_number(tochar(:NEW.DT_CADASTRO_MANIFESTACAO,'yyyy'));

    SELECT NVL(MAX(contador)+1,1)
    INTO PCONTADOR
    FROM geracao_codigo WHERE ANO = PANO;
    
    LOCK TABLE geracao_codigo
         IN EXCLUSIVE MODE
         NOWAIT;
    
    IF (PCONTADOR = 1) THEN
        INSERT INTO geracao_codigo values(PANO,1);
    ELSE
        UPDATE geracao_codigo
        SET CONTADOR = PCONTADOR
        WHERE ANO = PANO;
    END IF;

    PCODIGO := TO_NUMBER(PANO || LPAD(PCONTADOR,7));
    :NEW.CODIGO := PCODIGO;
    
END;

Force concorrências, creio que funcione mas nunca precisei fazer assim, em geral sequences resolvem.

[Motta 645]

De fato o LOCK antes faz mais sentido.

 

Faz o teste.