I do think that generators are better option on heavily concurrent
environment, but sometimes you need more control on the sequence like
receipt numbers (my case). I have about 15 concurrent users creating about
500 receipts per day. I use wait parameter in transactions to deal with the
DeadLock.



Elkins



De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] En nombre de Ann W. Harrison
Enviado el: martes, 30 de noviembre de 2010 01:02 p.m.
Para: firebird-support@yahoogroups.com
Asunto: Re: [firebird-support] Re: Generator or table ?

On 11/30/2010 10:59 AM, Elkins Villalona wrote:
> 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.
>

Your procedure will be fine for a single user system, or for
an application where only one transaction calls the procedure.
In a heavily concurrent environment, you'll get large numbers
of Deadlock / Update Conflict errors on the select with lock
that will require you to rollback and retry the transaction.

Good luck,

Ann
>
>
> 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
>
>
>





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