--- In firebird-support@yahoogroups.com, "Fabiano Bonin" <fabiano@...>
wrote:

>
> Hi all,
>
> I had an usual problem with a customer about 4 times in the last 4

months.

>
> The user starts to complain he can't insert records, and when we look
> at the database, the generator values are less than the table pk
> values, and we just create pk values using gen_id, so i'm sure the
> values were generated by the generators.

Whenever I have traced similar problems, it has always been that a
user has connected to the database and inserted their own records into
that table.

I would eliminate this as a cause before looking for anything more
serious. The easiest way is to modify the before insert triggers to do
something like.

...
AS
DECLARE VARIABLE CURRENTVALUE BIGINT;
BEGIN
IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(GEN_MYTABLEID, 1);
END
ELSE
BEGIN
CURRENTVALUE = GEN_ID(GEN_MYTABLEID, 0);
WHILE (NEW.ID > CURRENTVALUE) DO
BEGIN
-- Someone is misbehaving and did not use the generator
-- to fill this, fix the generator value
CURRENTVALUE = GEN_ID(GEN_MYTABLEID, 1);
END
END
END

Whilst not bulletproof and subject to concurrency issues, it will get
you out of jail 99% of the time when someone is doing the wrong thing.
(And if you do the right thing, it will not cause any issue).

I don't know if two lock manager processes is bad, someone else will
need to help you there.

Adam