Serial ID in replication
We are using Informix Version 9.20 in a two-way replicated environment. We are having some trouble with unique serial identifiers. Since you cannot use serial columns in replication for unique primary keys we set up a separate system for generating serial keys that are machine/database specific. The serial system we devised uses a separate, non-replicated table, on each database to generate serialized numbers and then that is concatenated with a CHAR(2) database specific identifier, this new CHAR is used as the primary key. This system has been working fine, but we are finding in high-load situations the serial key generating table is occasionally locked and thus we are not able to get a serial/primary key. Does anyone have a better system for doing this? Or any idea at all how to work with primary keys in two-way replication. Much thanks Cory Clarke cory@cybersites.com Sent via Deja.com http://www.deja.com/
This is a problem.
The easiest think to do is to define the primary as a composit key of a
constant + the serial number..
define table tab1 (
PK1 char(2) default "s1",
PK2 serial,
...
...
primary key (PK1, PK2)
) with CRCOLS lock mode row;
On the second server, define the default of PK1 as "s2"...
This way there is no locking or collisions.
Another possibability would be...
close database;
drop database cmpdb;
create database cmpdb with log;
create table serial_seed (
col1 serial
) lock mode row;
create function get_sequence ()
returning int;define i int;
insert into serial_seed (col1) values (0); select dbinfo('sqlca.sqlerrd1') into i from systables where tabid =
1;
let i = i * 10;
let i = i + 1;
return i;
end function;
execute function get_sequence();
execute function get_sequence();
execute function get_sequence();
Each execution of the function get_sequence() returns an incremental
value of 11, 21, 31, 41,... If on the second server, the second let is
"let i = i + 2" then it would return 12, 22, 32, 42, etc. This would
not result in any collisions. If you want, you could delete the row from
serial_seed immediatly after inserting it, but that might cause a bit of
locking. It's only 4 bytes so it wouldn't be that much extra wasted
space simply to let it stay. A nightly purge could be run to remove the
unneeded entries in the table.
There is a down side to this approch. It would be nice to write
somthing like insert into my table values (get_sequence(), .....). The
only problem with this is the rule about stored procedures not inserting
if called from an insert statement. However, if this logic were
converted into a UDR, then the it might work.
Now for the good news.
This issue was one of the main requests at the user conference in
Orlando and we've listened. - Can't say any more just yet... ;-)
elgrdo@my-deja.com wrote:
> We are using Informix Version 9.20 in a two-way
> replicated environment. We are having some
> trouble with unique serial identifiers. Since you
> cannot use serial columns in replication for
> unique primary keys we set up a separate system
> for generating serial keys that are
> machine/database specific. The serial system we
> devised uses a separate, non-replicated table, on
> each database to generate serialized numbers and
> then that is concatenated with a CHAR(2) database
> specific identifier, this new CHAR is used as the
> primary key. This system has been working fine,
> but we are finding in high-load situations the
> serial key generating table is occasionally
> locked and thus we are not able to get a
> serial/primary key.
>
> Does anyone have a better system for doing this?
> Or any idea at all how to work with primary keys
> in two-way replication.
>
> Much thanks
>
> Cory Clarke
> cory@cybersites.com
>
> Sent via Deja.com
> http://www.deja.com/