SEQUENCE跳号问题

[Lian...@gmail.com]

很早就听到其他项目组的同事说使用sequence时发生跳号问题，由于不是自己项目组的，所以一直不是很重视。此次项目组的同事也遇到了这个问题，于
是就查了下。
主要是两个java程序，java1会不断地实时insert数据，其中主键通过sequence生成，java2会不断地实时通过主键select，
由于默认sequence连续，查询条件是通过上次成功获取的最大主键+1计算得到，所以在发生sequence跳号时，就导致select不出数据，
从而报错了。
第一个反应是由于在insert时使用了seqence，但是由于其他原因报错导致事务回退，从而造成sequence不连续，但是查了应用级的日志，
并没有找到相关的报错日志，于是便去翻相关的文档了。查到如下内容：
The database might skip sequence numbers if you choose to cache a set
of sequence numbers. For example, when an instance abnormally shuts
down (for example, when an instance failure occurs or a SHUTDOWN ABORT
statement is issued), sequence numbers that have been cached but not
used are lost. Also, sequence numbers that have been used but not
saved are lost as well. The database might also skip cached sequence
numbers after an export and import.

看来很有可能是cache造成的，于是试图重现问题。

SQL> DROP SEQUENCE TEST.SEQ1;
序列已删除。

SQL> CREATE SEQUENCE TEST.SEQ1
2 START WITH 1
3 MAXVALUE 99
4 MINVALUE 1
5 NOCYCLE
6 CACHE 10
7 ORDER;
序列已创建。

SQL> select test.seq1.nextval from dual;

NEXTVAL
----------
1

SQL> SELECT *
2 FROM all_sequences
3 WHERE sequence_owner = 'TEST' AND sequence_name = 'SEQ1';

SEQUENCE_OWNER SEQUENCE_NAME
MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------
---------- ---------- ------------ - - ---------- -----------
TEST SEQ1
1 99 1 N Y 10 11

可以看到由于被cache，此时的last number已经是11了，不过，这并不影响seq的连续

SQL> select test.seq1.nextval from dual;

NEXTVAL
----------
2

但是，如果发生了一些instance failure，由于sequence的cache放在shared pool中，所以通过flush
shared pool来模拟这个现象

SQL> alter system flush shared_pool;
系统已更改。

SQL> select test.seq1.nextval from dual;

NEXTVAL
----------
11

问题如期出现，再做个nocache的实验

SQL> DROP SEQUENCE TEST.SEQ1;
序列已删除。
SQL> CREATE SEQUENCE TEST.SEQ1
2 START WITH 1
3 MAXVALUE 99
4 MINVALUE 1
5 NOCYCLE
6 NOCACHE
7 ORDER;
序列已创建。
SQL> SELECT *
2 FROM all_sequences
3 WHERE sequence_owner = 'TEST' AND sequence_name = 'SEQ1';
SEQUENCE_OWNER SEQUENCE_NAME
MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------
---------- ---------- ------------ - - ---------- -----------
TEST SEQ1
1 99 1 N Y 0 1
SQL> select test.seq1.nextval from dual;

NEXTVAL
----------
1

SQL> SELECT *
2 FROM all_sequences
3 WHERE sequence_owner = 'TEST' AND sequence_name = 'SEQ1';
SEQUENCE_OWNER SEQUENCE_NAME
MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------
---------- ---------- ------------ - - ---------- -----------
TEST SEQ1
1 99 1 N Y 0 2

SQL> alter system flush shared_pool;
系统已更改。

SQL> select test.seq1.nextval from dual;

NEXTVAL
----------
2

可见在nocache时，问题就不会出现，所以在必须要sequence连续时，则就不能使用cache。

同时，也可以看出对于压力较大的系统，用于shared pool中的数据会被频繁的age out（LRU算法），所以如果此时sequence使用
了cache，也就很有可能出现跳号，如果想防止被age out，可以考虑使用DBMS_SHARED_POOL.KEEP来pin住
sequence的cache。

对于项目组的问题，由于并不要求主键连续，所以考虑到性能，不使用nocache的方法，考虑到这属于偶发现象，所以通过捕捉异常，并使用
“select min(主键) from XXX where (主键) > 计算得出的错误主键值”来获取准确的下一sequence值