Next sequence number jumps sometimes 3

[evergreean43]

I have a sequence and trigger that automatically increases my primary key on my table after each Oracle 9i record insert.

It works but was wondering why it sometimes skips more than a thousand numbers on some inserts.

For example:

id    name
1     Jones
2     Barnes
1023  Johnson
2005  Baker
3006  Miller

If I insert records right after another (such as id 1 and id 2) it increments to the next number. But sometimes a record might be inserted a week later after example id 2 (such as id 1023) and that is when it jumps sometimes a 1000 in the next number.

Please advise.

 

[Beilstwh]

Not true, ORDER is only signigicant if you are useing multiple servers (RACS), NO CACHE means read from the sequence table (internal lock, increment value, internal commit) for every sequence. However if you get a sequence number and then issue a rollback, that sequence number will NOT be available again. Oracle sequences use a special locking system that is totally independent of your individual transaction. It is simular to aa procedure running as a AUTONOMOUS TRANSACTION. NOTHING guarantees no gaps when using sequences except NEVER issuing a rollback, NEVER deleteing a row, always commiting every transaction, and NEVER have the database crash.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Excellent synopsis, Bill. Please accept a Purple Star for your eloquent explanation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

@beilstwh:

Please provide some docummentation/link because we do have sequences created as "NO CACHE ORDER" and we experience no gaps, but the dowfall is that when some transaction has generated the next sequence, but not yet commited, we experience lock's where we have to kill one of the sessions.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
And you confirmed that it is the Sequence Value Request that is causing the lock?
What exactly is being Locked?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

Next week I'll have the developers try and reproduce the lock.

Have a great loooooong weekend!

Enjoy.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb