Re: HowTo - create user defined sequence with proper serialization

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 Received on Tue Aug 29 2006 - 12:22:46 CDT