I'm using the table approach, and it works pretty well for me. I got a
stored procedure like the one below to retrieve the next id number.

Good Luck,

Elkins



create procedure SP_GET_ID (PID_NAME varchar(40))

returns (RID integer)

as

BEGIN



SELECT ID_SEQUENCE + 1

FROM ID_TABLE

WHERE ID_NAME =:PID_NAME

WITH LOCK

INTO :RID;



UPDATE ID_TABLE

SET ID_NAME = :RID

WHERE ID_NAME =:PID_NAME;



SUSPEND;

END





De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] En nombre de Lars
Enviado el: domingo, 28 de noviembre de 2010 03:52 p.m.
Para: firebird-support@yahoogroups.com
Asunto: [firebird-support] Re: Generator or table ?





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
<mailto:firebird-support%40yahoogroups.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
>

[Non-text portions of this message have been removed]