Re: HowTo - create user defined sequence with proper serialization

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 Received on Tue Aug 29 2006 - 13:10:34 CDT