Sequences and Transactions

[scohan]

How can you avoid gaps with oracle seqeunces used within transactions that can rollback? From what I've just read about the create sequence command, sequences are incremented independent of the transaction committing or rolling back. Therefore, the sequence will be incremented even though, say an insert into a table rolled back.

Is there a good way to get the sequence back to what it was before it was incremented for failed transactions? I suspect there's all kinds of problems successful vs. failed transactions (i.e., resetting the sequence for a failed transaction at the same time another transaction succeeds.)

I guess a sequence shouldn't be used in this case and the number should be stored in table instead.

 

[sem]

There were a number of discussins on this issue in this and many other forums. Your suggestion is suitable only for single-user environment. Or at least when concurrent inserts are almost improbable.

Regards, Dima

 

[scohan]

I've searched for the the word 'sequence' here and got no results.

Thanks.

 

[jimbopalmer]

If you MUST have consecutive numbers, use a single row table, no indexes and cache the table in the SGA. Expect locks and code no waits around them. Ugly.

I tried to remain child-like, all I acheived was childish.

 

[scohan]

Jimbo, what do you mean by code no waits? Thanks.

 

[jimbopalmer]

Select Counter_column into new_PO_number from PO_counter for update of Counter_column nowait;
{you do stuff with POs, try not to wait on a Form here!}
Update PO_counter set Counter_column = Counter_column +1;
commit;

with the nowait a second user who gets to that line while a first user is still before the commit will not 'hang', he will get an error message. You can make it explain some one else is picking a PO number, try again. If not, he will hang til the first user gets to commit, which can be bad over lunch hour....

As you can see Sequences are mush cleaner, but leave gaps, single row tables are ugly and slow and tend to make the users fight, but do not leave gaps, building multi row tables of next values is only worse, better to add colums to your single row table, better yet to use Sequences.

I tried to remain child-like, all I acheived was childish.

 

[karluk]

You can probably do this, if your only goal it to avoid gaps in the table's keys, rather than have the key values reflect the order of inserts. You would create a table of "skipped_keys". When doing an insert, you would query the "skipped_keys" table first and only use the sequence if the table is empty. If you find a value in skipped_keys, delete it and commit the delete. That gives you a key that no one else can use. If you later decide to rollback your insert, you would do the rollback, then insert the key back into the skipped_key table and commit.

This procedure only handles rollbacks that are successfully processed withing your application logic. In order to handle other rollbacks you would have to have a batch job that periodically compared the table's row count with the highest key value. If they didn't match, it would have to read through the table to find the missing keys and update the "skipped_keys" table.

It's pretty cumbersome, so it may not be worth the effort. However, you could also handle deletes from the table with the same process. When deleting rows, you insert the deleted keys into "skipped_keys" so they become available for reuse.

 

[SantaMufasa]

SCohan,

I'm still fascinated by a design that depends upon "no gaps" in the sequence. Sequences are usually for automatic generation of Primary Keys. Remember, one of the characteristics of a good Primary Key is that it is "stupid", meaning that it carries with it no other business or application "intelligence" or meaning besides being just a unique identifier.

Please explain what business risk occurs with a gap in your sequence that some other data or logic could not/should not handle for you.

Dave
Sandy, Utah, USA @ 17:44 GMT, 10:44 Mountain Time