Número de SEQUENCE “pulando” após algum erro 

Olá pessoal,

Espero que estejam bem.

Estava num grupo de WhatsApp de uma comunidade de DBAs do Brasil (alô, DBA Brasil!), e surgiu uma discussão interessante.

Um DBA do grupo estava se deparando com uma situação “inexplicável”.

Ele possuía um objeto do tipo SEQUENCE, configurada com o “increment by” em 1, cache_size em 20.
Aí ele rodou um select seq_cliente.nextval from dual;

O valor da sequence foi para 281, até aí sem nenhum problema. Veja screenshot abaixo:

Após isso, ele simulou uma ação que a aplicação executa, provavelmente por engano:

create sequence seq_cliente;

O seguinte erro era retornado:
ORA-00955: name is already used by an existing object.

OK, até aí sem novidade ainda, certo?

Pois bem, ao realizar um novo select seq_cliente.nextval from dual, eis que o valor exibido foi de 301:

Ou seja, pulou 20 números!

Como?

Bem, fiz um test case também. Veja abaixo:

Aqui eu criei a sequence:

SQL> create sequence test;
Sequence created.

Abaixo eu verifico o INCREMENT_BYMETN_BY, CACHE_SIZE e LAST_NUMBER:

SQL> select sequence_name,increment_by,cache_size,last_number from user_sequences where sequence_name='TEST';

SEQUENCE_N INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
TEST 1 20 1

Rodo duas vezes o NEXTVAL:

SQL> select test.nextval from dual;
NEXTVAL
----------
1


SQL> select test.nextval from dual;
NEXTVAL
----------
2

Verifico novamente o INCREMENT_BY, CACHE_SIZE e LAST_NUMBER.

SQL> select sequence_name,increment_by,cache_size,last_number from user_sequences where sequence_name='TEST';


SEQUENCE_N INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
TEST 1 20 21

Observe que tudo está de acordo. É normal o LAST_NUMBER exibir o número 21. Porque?

De acordo com a documentação oficial:

Ou seja, como a sequence usa cache, ela já pre-alocou os valores e deixou os valores escritos em disco, mas, AINDA confia nos valores em cache que estão em memória.

Bem, vamos simular um erro, vamos tentar recriar a sequence:

SQL> create sequence test;
create sequence test
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Sem problemas, mensagem de erro esperada.

Vamos novamente rodar um NEXTVAL para a sequence:

SQL> select test.nextval from dual;
NEXTVAL
----------
3


SQL> select test.nextval from dual;
NEXTVAL
----------
4


SQL> select test.nextval from dual;
NEXTVAL
----------
5


SQL> select test.nextval from dual;
NEXTVAL
----------
6


SQL> select test.nextval from dual;
NEXTVAL
----------
7

Banco de Dados funcionando como o esperado, certo?

Bom, vamos fazer um teste, vamos fazer um flush da Shared Pool:

SQL> alter system flush shared_pool;
System altered.

Hora de rodar um NEXTVAL novamente:

SQL> select test.nextval from dual;
NEXTVAL
———-
21

Opa! Como assim?

Como temos agora esse pulo de 7 para 21?

Isso também é explicado pela documentação inicial:

Ou seja, ocorreu um “erro de sistema”, todos os valores que estavam em cache e não usadas por transações commitadas, são perdidos.

Quando isso ocorre, o cache da sequence sofre um flush e a quando você tentar alocar o próximo valor da sequence, pegará o último valor que estava escrito em disco pelo LAST_NUMBER.

Apenas lembrando que o isso ocorreu não apenas por causa do “erro de sistema”, mas, principalmente pelo flush na Shared Pool, já que valores de sequence em cache são armazenados no Data Dictionary Cache, um dos caches da Shared Pool:

Aqui outro test case. Forçando um novo erro:

SQL> create sequence test;
create sequence test
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> select test.nextval from dual;
NEXTVAL
----------
41


SQL> select test.nextval from dual;
NEXTVAL
----------
42


SQL> select test.nextval from dual;
NEXTVAL
----------
43

Aqui disparei um novo flush:

SQL> alter system flush shared_pool;
System altered.

SQL> select test.nextval from dual;
NEXTVAL
----------
61

SQL> create sequence test ;

Sequence created.

SQL>

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 1

SQL> select test.nextval from dual;

NEXTVAL

----------

1

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 21

SQL> create sequence test ;

createsequencetest

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 21

SQL> select test.nextval from dual;

NEXTVAL

----------

21

SQL>

SQL>

SQL>

SQL> select test.nextval from dual;

NEXTVAL

----------

22

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_N LAST_NUMBER

---------- -----------

TEST 41

Espero que seja útil,

Um abraço.

Vinicius