sequence numbers

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 - 07:17:07 CDT