Hi Lester,

> ... The trigger is just using a MAX+1 for the transact_no, and has
> the primary key unique index, but in theory a user will be take several seconds
> between each action on an individual ticket, and the times confirm that.

I'm thinking uncommitted data means your MAX+1 is returning the same
MAX+1 as used by, but not yet committed by, another user.

> ...

> The question ... would this have had to have been several stuck transactions
> that had not commited?

I get this quite often (on Oracle databases mind you) when a vendor
writes an application without fully understanding what they are doing.
Our problems are caused by people not finishing a transaction and
heading off for lunch etc. Their transaction remains uncommitted, and
someone else's gets the same ID number and attempts to store it.

At that point, the second (and all the subsequent) sessions hang on an
enqueue until the first session either commits or rolls back. It's a
monumental PITA and reduces the application to effectively, a single
user one.

> I had to gfix, backup, restore without indexes enabled,
> fix duplicates then re-enable indexes to get the database live again. But it's
> the first time I've seen this problem in 14 years of interbase/firebird data -
> some 30 million master records across dozens of sites. Obviously I'm wondering
> if I have a hole I a missing ;)

I'm honestly not sure. 30 million records is a huge amount to have
simply been "lucky" that it hasn't happened before. Sorry. :-(

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767