On Sat, Feb 11, 2012 at 12:05 PM, Kjell Rilbe <[email protected]> wrote: > > If you want to avoid sequences that perhaps have different syntax for > different servers, may I suggest that you have a separate table: > > create table "PK" ( > "TableName" varchar(30) not null primary key, > "LastUsedId" int not null default 0 > ) > > Enter one row in this table for each table you need a PK "generator" > for. To aquire a key, start your transaction with: > > update "PK" > set "LastUsedId" = "LastUsedId" + 1 > where "TableName" = :TableName > > Then > > select "LastUsedId" > from "PK" > where "TableName" = :TableName >
Just be sure to do that in a separate transaction with a retry on deadlock/update conflict. That's exactly the strategy used to create a gateway record that prevents concurrent updates to a table. Good luck, Ann
