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