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

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 Mon Dec 29 1997 - 00:00:00 CST