"art" <art_teqNOSPAM_at_hotmail.com> wrote in message
news:3ce8e945$1_1_at_nnrp1.news.uk.psi.net...
> 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.
Richard has given you what looks to be a good answer for your how do I
achieve this question. I'd urge you if at all possble to relax the sequence
numbers must be sequential requirement. This 'requirement' gurantees serial
access to a resource and thus almost certainly guarantees a lack of
scalability in the app (assuming any volume of insert activity on the
database). No gap sequences are a Bad Thing TM.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Received on Tue May 21 2002 - 06:13:42 CDT
