> From: Woody [mailto:woody.tmw@...]
> >
> > > I think too that generator is not a good option because the
> > > counters would be consecutive 1,2,3,4,5.
>
>
> If I'm not mistaken, I think (I am assuming here :) that he requires
> consecutive numbers for each type of counter. One generator
> wouldn't do so
> there would have to be a separate generator for each one. I
> do this myself
> by maintaining a separate table containing ID values and the
> next counter
> ID. I lock the table and then get the next ID, and update it
> for the next
> person.
>

I am doing something similar (when consecutive series without holes are
needed) - I have table with the ID values for several "counters". When new
ID is needed, I make something like

UPDATE XXX
SET ID=ID
WHERE COUNTER=....

to lock only one row containing the desired ID and then when no update
conflict raises, I increment the value and continue. Otherwise the client
app is informed about lock conflict, then it sleeps 500 ms and tries again.
This loop tries to make the update 3 times and in case of no success informs
the user about conflict and let him choose "try again or cancel". What do
you think about this approach?
Ales