Re: sequence numbers

You need to lock the row so no one else can select & update it at the same time.

SELECT ...
FOR UPDATE UPDATE ... COMMIT; If someone is updating the sequence, then their select will wait until the lock is released (commit) and then they will select/lock the newly updated row. Don't hold the lock for a long period or processes may timeout waiting for their turn to lock. Ideally, do this in your function using an autonomous transaction. However, as before, you need to know what you're going to do if the process fails or decides to not use the sequence number it received.

Richard

art wrote:
>
> I have a problem with generating a 'next sequence number'. I have a table
> (products) with n rows of configuration information. One column in the table
> is 'nsn' (next sequence number) which is the next sequence value to be
> generated for that product. This is a number which a process selects (via
> SELECT), uses and then updates (vai UPDATE) with nsn+1. There is a NSN for
> each product e.g.
>
> Product NSN
> -------- -----
> Prod1 1
> Prod2 37
> Prod3 1
>
> The fact that a process does a select/use/update on the NSN means that if
> two processes are processing the same product then they can both SELECT
> simultaneously and have the same NSN (which would be wrong and very bad for
> the system). I want NSN to remain sequential per product, and so cannot use
> an individual sequence for all products, and do not want a spearate sequence
> for each product as users can add and remove products arbitrarily.
>
> I want a stored procedure with the following prototype which guarantees that
> two processes sharing a database cannot establish the same NSN, and the NSN
> remains sequential on a per product basis.
>
> number GetNextSequenceForProduct(product_number)
> {
> -- Atomically get and update the NSN for product_number
> -- return established NSN
> }
>
> I am very confused about how to do this in a process safe manner. Any
> guidance would be much appreciated.
>
> Cheers,
> .
Received on Mon May 20 2002 - 18:34:51 CDT