Re: Sequence Table

"Jason Webber" <webber_at_mminternet_spam.com> wrote:

>Right now I have a send table that is:
>UNITID NUMBER(8,0)
>SEQNUMBER NUMBER(3,0)
>TIMESTAMP DATE/TIME
>MESSAGE LONG RAW
>RETRIES NUMBER(1,0)
>
>UNITID,SEQNUMBER is the primary key. TIMESTAMP represents the time to send
>the message(which can be in the future).

> SEQNUMBER is just a number between 0-2047.

Interesting scenario. Is SEQNUMBER size limited by the phsyical equipment, or does that just make part of the raw message? Does SEQNUMBER really need to be in sequence, or can it be any random number, as long as it is unique within that UNITID?

I also assume that the problem as you described in your original posting is that you will need a unique sequence generator for SEQNUMBER per UNITID?

I can think of 4 ways to address the problem.

A unique sequence generator per unit. This will require some PL/SQL wrappers and dynamic SQL to make it work seamlessly.

A manual sequence calculation. This will cause the serialisation of transactions for a specific unitid (akin to page locking all the rows for a unitid when inserting). You need to do a 'select max(seqno)' from the table, after locking all the rows for that unit. Do the insert, then commit and release the lock. This kind of defeats row level locking and parallel transactions.

Generating a unique random number on the fly. This can be done using a datetime stamp and some kind of random number/hashing algorythm. However, this can not always guarantee a unique number - there will always be that that few billion in one change that another message may be created at the exact same millisecond.

The best solution is to have a single sequence for generating message sequence numbers - assuming that message sequences can be larger than just 3 digits.

--
Billy