Robby wrote:

>Thanks, I checked out that article along with finding a previous
>recent thread that someone had the same kind of problem I had.
>
>However, from what I've seen, that article covers a way to have a
>single, reusuable sequential ID pool that isn't the primary key.
>This is part of what I want, but I also need a seperate pool for
>each member (see my previous example in this thread). In effect, we
>have two tables, member and foo, and the basic foo schema might look
>like:
>
>CREATE TABLE foo (
>idnum INT NOT NULL PRIMARY KEY,
>memberid INT NOT NULL REFERENCES member,
>fooid INT NOT NULL,
>UNIQUE(memberid, fooid) <<<------- this is the important part, a
>composite key that end-users use to reference any given entry
>);
>
>Maybe I'm just being brain dead, but how could I get multiple
>seperate pools for each member using that method?
>
>After I've thought about it a bit, the best solution I've gotten was
>to 1. as per that article, not use the 'member' primary key field as
>my member ID field, 2. for foo, use the "NEW.fooid = MAX(fooid) + 1
>WHERE memberid = NEW.memberid" generation scheme and somehow write
>lock it so that no other transaction could be creating a record in
>that table at that time.
>
>Robby
>
>

Robby,

I sugested that article because I think that the key to solve your
problem is to use the same "lock" mecanism used in the "Auditable Series".

Take a look on this topics

Mix in a little conflict...
Multi-user concurrency control
Making use of Planned Deadlocks
How deliberate deadlocking helps


I think you should use somekind of temporary table to store the maximum
fooid for each memberid, than you lock and update that record to get a
new number, then release it.

I think this way you will not get duplicate values.

There is a new feature in FB that you can lock some rows, never used it,
maybe it could help you too... take a look on release notes page 15

But keep in mind to keep the lock time short, or your users can be annoyed.

Got the point now ?

See you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br