Dúvida com locks no Oracle
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:
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):
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:
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:
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:
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.
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:
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!
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:
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):
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:
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:
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:
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.
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:
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!
Aroldo Rique
Curtidas 0
Respostas
Lourival Queiroz
21/10/2014
Vc pode travar a tabela sem nenhum problema, mas sugiro a vc utilizar uma sequence para utilização do código, no qual anualmente vc pode zerar a mesma e vc não irá ter problema nenhum de concorrência.
GOSTEI 0