[Oracle] Sequences não garantem valores sequenciais

Olá pessoal, hoje trago um conteúdo bastante fundamental e simples, mas que por muitas vezes é esquecido.

Vez ou outra preciso discutir com desenvolvedores sobre a geração de valores sequenciais para os registros de uma tabela. Essa é uma discussão um tanto quanto cansativa, pois os desenvolvedores muitas das vezes optam por gerar esse número sequencial na aplicação, mas na maioria dos casos isso é bem menos performático do que fazê-lo no banco de dados, o qual já possui mecanismos e arquitetura pronta para tal, porém é necessário estar ciente das limitações do uso de sequences no Oracle Database, por exemplo.

Ao optar pelo uso de sequences precisamos assimilar que o Oracle Database não garante valores sequenciais mesmo se utilizadas para uma só tabela e ainda que usando as cláusulas ORDER, INCREMENT BY 1 e NOCACHE. Você eventualmente observará números faltantes no uso dessa sequence. Isso se deve ao fato de que transações que falham ou sofrem rollback fizeram o uso e carregaram um valor da sequence. A transação que utiliza a sequence sofre o rollback, mas não o valor solicitado à sequence. Vejamos um exemplo.

Primeiramente vamos conectar no nosso banco de dados. Aqui estou utilizando meu Autonomous Transaction Processing Database TESTDB:

Vamos então criar uma tabela e uma sequence para usar de exemplo:

Ao tentar extrair o valor atual da sequence o erro ORA-08002 é retornado, pois a sequence ainda não foi inicializada:

Insiro um registro utilizando a sequence e este já a inicializa, em seguida faço rollback:

Insiro novamente um registro utilizando a sequence e em seguida consulto o valor inserido. Vejam que mesmo após o rollback o valor da sequence foi incrementado e assim ficamos sem o valor 1 na tabela:

Ao consultar o valor atual vemos que ainda se mantém em 2 e ao consultar o próximo valor vemos o 3:

Quando inserimos utilizando novamente um novo valor constatamos que o valor 4 foi inserido e armazenado na tabela:

Ao analisar os exemplos podemos concluir que toda vez que a função NEXTVAL é chamada a sequence é incrementada e mesmo que ocorra uma operação de rollback esta não influencia em nada no incremento da sequence.

Sendo assim é mais fácil preparar a lógica na aplicação para não estar relacionada a valores sequenciais, mesmo porque se um registro precisar ser removido este também deixará um vão na sequência.

A partir da versão 12cR2 é possível já configurar uma coluna para ser sequencial por padrão: GENERATED BY DEFAULT AS IDENTITY. Mas isso é assunto para outro post.

Por hoje é só. Espero que tenham gostado.

Abraços e até o próximo post,

Franky