Re: application seq. no. definition without commit (C/S env).

Why not use Oracle sequences?

The idea with generating unique values beforehand is fine. But I wonder how do you check if a number is already used? Or, alternatively, how do you check which is the next number to use? If a transaction keeps one number and does not release it, you are not able to determine if this number is free or not until the pending transaction terminates (either way). For that period of time, you cannot determine the next number to be used because it may be the number considered in the pending transaction.

I suggest you use sequences if you only need unique numbers that may leave gaps. If you may not accept gaps, you cannot use sequences as their increment is not rolled back (that's the reason of their speed). If the numbers must also be monotonuous you must use ordered sequences. This a bit slower.

Martin Haltmayer

Bob Cunningham wrote:
>
> On Mon, 29 Dec 1997 16:29:05 +0200, Yochi Wieman <ordnkv_at_shani.net>
> wrote:
>
> >Using application seq. no. definition without commit (C/S env).
> >
> >In an application which defines seq. numbers (not ORACLE SEQ.),
> >at multiple levels within the application, in an Oracle table,
> >in the same D.B. as other application data,
> >how is it possible to increment the seq. no. by multiple tasks
> >simultaneously within the application without COMMIT??
>
> Given that you are managing assignment of sequence numbers in a table,
> then each task will need to update the table to obtain the "next
> unique" sequence number. The row will not be updateable by other
> tasks until the first task either COMMITs or ROLLBACKs.
>
> Some things come to mind to allow concurrent assignment by multiple
> tasks.
>
> One method would be to break the sequence up across multiple rows so
> that each row represented just a segment of the sequence's total
> domain. Each task would simply obtain a sequence number from the
> first row it could lock. This would limit the maximum number of
> concurrent sequence number assignments to the number of rows across
> which the sequence had been distributed...but you get more than just
> one task at a time. This setup would probably, over time, assign most
> sequence numbers from the first row (or couple of rows) so that their
> available sequence numbers would deplete faster than the remaining
> rows. Ultimately, the earlier rows may be consumed completely which
> now reduces the number of rows containing available sequence
> numbers...reducing the number of concurrent task assignments
> supported. Of course, this could be guarded against by distributing
> the available numbers across the rows in a fashion that more closely
> represents the concurrent assignment activities.
>
> A variant of that technique would be to create a number of "single
> value" rows from the base sequence set on a regular basis (perhaps
> nightly). The application tasks would obtain a sequence number from
> one of these rows and then delete it. If the application issues a
> ROLLBACK, the sequence number would be resuable by another task since
> the deleted row would reappear. As the sequence numbers are consumed,
> the number of "single value" rows reduces but they can be replenished
> by just creating more of them using the next avialable sequence
> numbers within the set. Worst case would be that all single value
> rows are consumed, and the application tasks have to get their next
> sequence number from the one row that contains the set of remaining
> sequence numbers....your system now slows down due to contention on
> the base row but doesn't fail...and it can be fixed by just creating
> more "single value" rows.
>
> Another method would be to set up a single task that is responsible
> for assigning the sequence numbers and have all the application tasks
> communicate with it...instead of directly accessing your sequence
> number table. The centralized task would operate in its own session
> and perform all the necessary update/commits against the sequence
> number table (and be written properly so that it can be guaranteed
> that the same sequence number value cannot be issued more than once.
> Whenever an application task required a sequence number, it would
> acquire it from the central task. In a PL/SQL environment DBMS_PIPE
> could be used for the inter-session communications.
>
> HTH
>
> Bob Cunningham
> bcunn_at_oanet.com
Received on Fri Jan 02 1998 - 00:00:00 CST