Re: sequence numbers

It occurred to me that for this case you can probably condense that to...

UPDATE ..
SET <sequence_col> = <sequence_col> + 1
WHERE ...
RETURNING <sequence_col> INTO ...

COMMIT; Assuming your DB version supports the returning clause.

Richard

Richard Kuhler wrote:
>
> 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:44:44 CDT