This is an update to my problem with transactions and allocating unique ids.
I think it shows that there is something broken within Interbase. When it is
supposed to wait for a lock, its returning an error instead. To make this
easier to show, here is an example Embedded SQL program. Either there is
something wrong with this program or else something wrong with Interbase. If
there is some other SET TRANSACTION options that make this work, please let
me know if them!

The code depends on you having a database called test.gdb with a table
created like this:

create table id_table (
table_name varchar(30) not null,
next_id integer not null,
primary key (table_name)
);
insert into id_table values ('testing', 0);
commit;

I compiled unique_id2.e like this:

gpre -c unique_id2.e
gcc -Wall -c unique_id2.c
gcc unique_id2.o -lgds -lcrypt -ldl -o unique_id2

This is on a Linux Mandrake 7.2 system running Interbase classic server
version 6.0 from Borland.

After you run one copy of the program, you can check next_id in id_table and
it should be 10000. Then try to run two copies of the program at once. When
I do this, I get errors like this:

Statement failed. SQLCODE = -913
deadlock
-update conflicts with concurrent update

From all I've read and understand, the WAIT in the SET TRANSACTION should
stop this from happening. Please let me know if I've done something wrong
and if not, there is a problem within Interbase.

--
Brad Pepers
brad@linuxcanada.com