Re: HowTo - create user defined sequence with proper serialization

p.santos000_at_gmail.com wrote:
> Steve Howard wrote:
> > p.santos..._at_gmail.com wrote:
> > > Steve,
> > > The need to reuse sequence values needs to exist. Therefore we have
> > > to do a linear search via a cursor to identify the lowest/next sequence
> > > value. I'm not sure where the select for update would go in the
> > > function I listed...
> > >
> > > -peter
> >
> > Hi Peter,
> >
> > You can still put the FOR UPDATE clause in your function, but it looks
> > like you would be locking multiple rows at once. IOW, if a user comes
> > along and does your SELECT, she gets one cursor of rows. The next user
> > may a different set of values, based on whatever changes the first user
> > made. I can't imagine this would scale well though, since it would be
> > locking multiple rows at once.
> >
> > Once the function returns the value and she inserts the row(s) and
> > commits, the row lock(s) taken in the function are released and the
> > next user gets a fresh view of the data with which to use your
> > algorithm to return the next value.
> >
> > Is the function deterministic? Can two completely different rows ever
> > return the same value in the function?
> >
> > You could also look into dbms_lock, as it may apply.
> >
> > HTH,
> >
> > Steve
>
> I understand that locking multiple rows may not scale well, but the
> reality is that per customer one will never have that much currency if
> any at all. The key thing is that if in the event that there are 2
> people creating groups for the same customer .. it's important that one
> blocks the other so that they both don't pull back the same value.
>
> Because of the need to re-claim sequences, I just don't see another way
> of doing this.
>
> thanks for your thoughts!
> -peter

Do you *really* need to be able to use a sequence? Why? I'll admit I don't really understand the app, but if you have to re-use a sequence to identify something, then then that something is not really identified by the sequence but by a combination of sequence, from data, and to date. I'd really question the business case for re-using these keys. Received on Wed Aug 30 2006 - 07:45:05 CDT