I'm using the sequence for table primary key and it increases by 1,
I have noticed sometimes the data get lost and the sequence get skipped, that means when ever I inserted a record say the seq was 10, the next one should be 11, instead it skips to 13 and I see 2 records has been missing.
What causes this problem Please let me know if anyone knows the answer to this question.
Sequences are a one-way procedure. I believe that every time you attempt to insert a row that uses a sequence, that sequence is advanced, even if the INSERT fails.
This can be especially important in a heavy multi-user application, where many users may be trying to INSERT to a table at the same time. Thus if for some reason there is too much resource contention, a sequence may be advanced, although the INSERT fails.
Remember, sequences are independent of the table. Sequences can be advanced simply by calling SELECT nextval('squence_name');. Once advanced, they cannot be reversed. In fact, for this reason, a sequence can be shared among several tables, because every sequence call is atomic.
Sequences aren't for the purpose of keeping a perfect numerical progression, but for guaranteeing uniqueness.
So, your problem doesn't mean records are missing. It just means that the sequence has been advanced, but not used in the table.