Milan Babuskov wrote:

> Doug Chamberlin wrote:
>
>>>It can still lead to problems.
>>
>>How so?
>
>
> Here's what you wrote earlier:
>
> "Maintaining generator values which do not conflict with existing field
> values is better done in the client. What I do while my client
> application is starting up is to query the max existing value and if the
> generator is currently set below that then I reset it to max+1."

Hmmm, now that I think of it, examine this scenario:

MAX(PK) = 20;
Generator xyz = 10;

1. Users 1 and 2 open application at the same time. They both read
values (10 and 20). And the both start transaction to do SET GENERATOR
xyz TO 21.

2. Now, imagine that user1 does it first, commits, and goes on with the
work, while user2 has network problems, and it doesn't execute immediately.

3. User1 works fast, enters a new record in table, with correct PK = 22.

4. User2's network packets finally go through, and set generator value
back to 21.

5. The next insert would result in PK violation.


I'm trying to make the point that it is never safe to set generator to
anything, only to increment it. Prehaps Lucas Franzen's idea would be
good here. Increment it by a difference between generator and max(pk). I
currently can't think of a way for that to fail...

--
Milan Babuskov
http://fbexport.sourceforge.net