Well, Generators are the way. I experimented (Delphi and FIBPlus) with tables and the problem shows its ugly head when # of transactions per second increases and I want to avoid deadlocks.

I think I have settled on storing the generator name in the clients setup record and limit the client specific generators to order #'s and invoice #'s. All other record ID's will use a table specific generator for PK and use entered values for other indexes.

Thanks for your insight..

Cheers!

- Lou

--- In firebird-support@yahoogroups.com, "Ann W. Harrison" <aharrison@...> wrote:
>
> On 11/27/2010 11:46 PM, Lars wrote:
> >
> > What I am thinking I need is a stored proc that will get next value by doing following:
> >
> > Query the record for update, lock it, increment value, update record
> > release lock and return the incremented value to calling app. The
> > Same storedproc may also log the value returned by having the caller
> > pass in the primary key of record it is requesting an order value for.
>
> That's going to work very badly. Firebird's MVCC works like two-phase
> record locking, meaning that records are "locked" when they are added,
> updated, or deleted. The "locks" are not released until the transaction
> commits. You're producing hot spots that will lead to lots of record
> update conflicts that can be resolved only by rolling back the
> transaction that got the error.
>
> That's why generators were created.
> >
> > Is this even possible to do? Has anyone encountered this problem?
>
> It's very common.
>
> > I really do not wish to add generators for each client for Order ,
> > invoice and other sequence values.
>
> Generators are cheap, much cheaper than tables. In terms of database
> space, each table requires at least three pages. A generator is 64
> bits. They don't deadlock. They don't serialize updates or inserts.
>
> Good luck,
>
> Ann
>